JDBC: You always have to learn to connect to a database!

BWH_Steven: Love you again with JDBC

(1) Getting started with JDBC

(1) Overview

Java Database Connectivity (JDBC) is an application interface in the Java language that specifies how client programs access the database, providing methods such as querying and updating data in the database.JDBC is also a trademark of Sun Microsystems.JDBC is relational database oriented.

Simple explanation: Operate the database by executing sql statements in the Java language

(2) Origin

To operate different databases through Java, specific APIs should be executed depending on the database. For simplification, Sun defined a set of APIs for all relational databases, JDBC, which only provides interfaces, and the implementation is handed over to the database manufacturer, and we are the developers.We only need JDBC-based operations for data databases

(2) Simple use of JDBC

We simply use JDBC to query the data in the database and output it to the console

For a quick demonstration, let's create a very simple table

CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    score DOUBLE(4,1)
);

INSERT student(id,NAME,score) VALUES (1,'Zhang San',98);

INSERT student(id,NAME,score) VALUES (2,'Li Si',96);

INSERT student(id,NAME,score) VALUES (3,'King Five',100);

We write a corresponding student class based on the information in the database

public class Student {
    private int id;
    private String name;
    private double score;
    //Omit construction, Get, Set, toString methods
    ...... 
}

Here is a simple use of the JDBC query function

package cn.ideal.jdbc;

import cn.ideal.domain.Student;

import java.sql.*;

public class JdbcDemo {
    public static void main(String[] args) {
        //Import Database Driven Package

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            //Load Driver
            Class.forName("com.mysql.jdbc.Driver");
            //Get the connection object to the database
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "root99");
            //Define sql statement
            String sql = "SELECT * FROM student";
            //Gets the object statement that executes the sql statement
            statement = connection.createStatement();
            //Execute sql statement to get result set
            resultSet = statement.executeQuery(sql);

            //Traversing through the resulting set
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                Double score = resultSet.getDouble(3);

                Student student = new Student();
                student.setId(id);
                student.setName(name);
                student.setScore(score);

                System.out.println(student.toString());
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //Release resources, then call first release
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

//Run Results
Student{id=1, name='Zhang San', score=98.0}
Student{id=2, name='Li Si', score=96.0}
Student{id=3, name='King Five', score=100.0}

Let's begin by explaining in detail the objects used above

(3) JDBC Object Details

(1) DriverManager

A: Load Driver-->Register Driver

First of all, we need to know what the two words load driver and register driver mean. When we first come into contact, some people will always have friends who use Class.forName(com.mysql.jdbc.Driver) as a registered database-driven statement, but otherwise, it loads the class represented by the parameter into memory and initializes it, the same asStatic variables are also initialized and static code blocks are executed

  • Confusion: Can you use the loadClass() method in the ClassLoader class?

    • No, this method is loaded but not initialized
//Class Class Source Selection-jdk8
* A call to {@code forName("X")} causes the class named
* {@code X} to be initialized.

As for initialization, let's just go back to our main line

Why not initialize a class and you can't choose one?

This is because the real registration driver (telling the program which database to use to drive the jar) is:

static void registerDriver(Driver driver)

We found the class Driver in the jar package and looked at its source code

//Static code blocks in the com.mysql.jdbc.Driver class
static {
    try {
        DriverManager.registerDriver(new Driver());
    } catch (SQLException var1) {
        throw new RuntimeException("Can't register driver!");
    }
}

After the class is loaded, the static method DriverManager in the class is executed to register the driver

We may have seen the code in the following 2, but the driver is actually loaded twice because it executes

The new com.mysql.jdbc.Driver() has already loaded the driver once

//1. Recommendation
Class.forName("com.mysql.jdbc.Driver");
//2. Not recommended
DriverManager.registerDriver(new com.mysql.jdbc.Driver())

So why bother?Isn't it good to write new com.mysql.jdbc.Driver() directly like this?

But we still choose to reject!Why?

If we do this, we rely heavily on the jar package. If we face multiple projects or need to modify the database, we need to modify the code and recompile it, but if we use Class class loading, it ensures that the registration-driven methods contained in the static code block will be executed and will refer toNumbers become strings, so we can then handle the problem more flexibly by modifying the contents of the configuration file''+ adding jar packages, without having to recompile!

Note: Driver jar packages after mysql5 can omit this step of registering drivers, because check the META-INF/services/java.sql.Driver file in the jar package

com.mysql.jdbc.Driver
com.mysql.fabric.jdbc.FabricMySQLDriver

B: Get a database connection

static Connection getConnection(String url, String user, String password) 
/*
    jdbc:mysql://ip Address (domain name): port number/database name
    Eg:jdbc:mysql://localhost:3306/db1
    Local mysql, with port 3306 as default, can be abbreviated: jdbc:mysql:///database name
*/

(2) Connection (Database Connection Object)

A: Get the object that executes sql

//Create a statement object that sends sql statements to the database
Statement createStatement()

//Create a repareStement object that sends precompiled sql statements to the database
PreparedStatement prepareStatement(String sql)  

B: Manage Transactions

//Open Transaction: Set the parameter to false to open the transaction
setAutoCommit(boolean autoCommit) 

//Submit Transaction
commit() 

//Rollback transaction
rollback() 

(3) Statement (object to execute sql statement)

//Execute DQL (query records (data) of tables in the database)
ResultSet executeQuery(String sql)

//Perform DML (add or delete data from tables in the database)
int executeUpdate(String sql)

//Execute any sql statement, but the target is not clear enough to use less
boolean execute(String sql)

//Place multiple sql statements in the same batch
addBatch(String sql)

//Send a total batch of sql statements to the database for execution
executeBatch()

Code demonstration (with an example of adding a piece of data)

package cn.ideal.jdbc;

import java.sql.*;

public class StatementDemo {
    public static void main(String[] args) {

        Connection connection = null;
        Statement statement = null;
        try {
            //Load Driver
            Class.forName("com.mysql.jdbc.Driver");

            //Get Database Connection Object
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "root99");

            //Define sql statement
            String sql = "INSERT student(id,NAME,score) VALUES (NULL,'Malacca',88);";

            //Gets the object that executes the sql statement
            statement = connection.createStatement();

            //Execute sql statement
            int count = statement.executeUpdate(sql);
            System.out.println(count);
            if (count > 0) {
                System.out.println("Added Successfully");
            } else {
                System.out.println("Failed to add");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

(4) ResultSet (result set object, encapsulating query results)

ResultSet represents the result set of the sql statement - the result of execution, when the Statement object executes excuteQuery(), a ResultSet object is returned

//The cursor moves down a row to determine if the current row is the end of the last row (if there is data)
//Returns false if it is, true if it is not
boolean next()

//Get data, Xxx stands for data type  
getXxx(parameter)

Eg:int getInt() ,    String getString()
    
1. int: Number of representative column,From the beginning, such as: getString(1)
2. String: Represents the column name.For example: getDouble("name")

A case can be loaded with a collection framework after attempting to read the data in the database itself, using the Quick Use section at the beginning

(4) Double the effort with half the effort - Tool class

By encapsulating some methods to make a more generic tool class appear, we can make the information more intuitive and easy to maintain through the properties configuration file

package cn.ideal.jdbc;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    /**
     * File Read
     */
    static {

        try {
            //Create Properties Collection Class
            Properties pro = new Properties();
            //Get files under src path
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
            URL res = classLoader.getResource("jdbc.properties");
            String path = res.getPath();

            //load file
            pro.load(new FileReader(path));
            //get data
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");

            //Register Driver
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }

    /**
     * Get Connections
     *
     * @return Connecting Objects
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * Release Resources
     *
     * @param statement
     * @param connection
     */
    public static void close(Statement statement, Connection connection) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Release Resources
     *
     * @param resultSet
     * @param statement
     * @param connection
     */
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

Tool Class Test Class

package cn.ideal.jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtilsTest {
    public static void main(String[] args) {

        Connection connection = null;
        Statement statement = null;
        try {

            connection = JDBCUtils.getConnection();

            //Define sql statement
            String sql = "INSERT student(id,NAME,score) VALUES (NULL,'Malacca',88)";

            //Gets the object that executes the sql statement
            statement = connection.createStatement();

            //Execute sql statement
            int count = statement.executeUpdate(sql);
            System.out.println(count);
            if (count > 0) {
                System.out.println("Added Successfully");
            } else {
                System.out.println("Failed to add");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(statement,connection);
        }
    }
}

In the previous articles, respectively through collection implementation, IO implementation, and learning database, we can try to store data through the database, write a simple login registration case!In the fifth point there is a reference to roaring

(5) Supplement: PreparedStatment

//Create a prepareStatement that sends precompiled sql statements to the database
PreparedStatement prepareStatement(String sql) 

prepareStatement inherits from Statement and is, in short, stronger and simpler than its parent class!

(1) Advantages

A: Efficiency

Statement directly compiles the SQL statement, sends it directly to the database for execution, and repeats the SQL statement several times. PreparedStatement precompiles the SQL and refills the parameters, which is more efficient (precompiled SQL is stored in PreparedStatement)

B: Readability

Variables in Java are often used when defining SQL statements. In some complex cases, quotation marks and single quotation marks are frequently used. The more variables, the more complex, and PreparedStatement can use placeholder'?'Instead of a parameter, assign the parameter next, which makes the code readable

C: Security

PreparedStatement is precompiled to avoid SQL injection attacks caused by the possible splicing of strings and variables in Statement [Write a permanent equation, bypass password login]

Let's start by writing a simple login to Demo and creating a table!

CREATE TABLE USER(
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(32),
    PASSWORD VARCHAR(32)
);

SELECT * FROM USER;

INSERT INTO USER VALUES(NULL,'admin','admin888');
INSERT INTO USER VALUES(NULL,'zhangsan','123456');

Then write the code

package cn.ideal.login;

import cn.ideal.jdbc.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class LoginDemo {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("enter one user name");
        String username = sc.nextLine();
        System.out.println("Please input a password");
        String password = sc.nextLine();
        
        boolean flag = new LoginDemo().login(username, password);

        if (flag) {
            System.out.println("Login Successful");
        } else {
            System.out.println("ERROR Incorrect username or password");
        }
    }

    /**
     * Login Method
     */
    public boolean login(String username, String password) {
        if (username == null || password == null) {
            return false;
        }

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtils.getConnection();
            //Define sql
            String sql = "SELECT * FROM USER WHERE username = '" + username + "' AND password = '" + password + "' ";
            //Get the object that executes sql
            statement = connection.createStatement();
            //Execute Query
            resultSet = statement.executeQuery(sql);

            return resultSet.next();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(resultSet,statement, connection);
        }
        return false;
    }
}

Simply put, such a simple login to Demo is all right, but at this point, a situation arises in the SQL injection problem. Perhaps you've heard that in the early years, vulnerabilities were still quite common. Some hackers or script boys often used some SQL injection to back-end their target websites.Invasion, one of which we are talking about today, is called SQL Universal Injection (SQL Universal Password)

Let's first look at the section of the above code about the SQL statement

 String sql = "SELECT * FROM USER WHERE username = '" + username + "' AND password = '" + password + "' ";

That is, it synthesizes the username and password that we enter into an SQL query statement. When no such field exists in the database, it represents an input error. For programs with SQL injection vulnerabilities, you can construct special strings to log in and test the results by pasting them out first.

//Run results
 enter one user name
admin
 Please input a password
1' or '1' = '1
 Login Successful

What if we replaced the password part of the code above with our own?

 String sql = "SELECT * FROM USER WHERE username = 'admin' AND PASSWORD = '1' or '1' = '1' ";

Supplement: Logical operators have precedence in SQL statements, = takes precedence over and, and takes precedence over or

So the AND in the above equation is executed first, of course it returns an error, and then the or part is executed. The return value is always true for a permanent equation''1'='1', so the result of the SQL query is true and you can log in successfully.

//Use PrepareStemen instead of main parts

//Define sql
String sql = "SELECT * FROM USER WHERE username = ? AND password = ?";
//Get the object that executes sql
preparedStatement = connection.prepareStatement(sql);
//assign to?
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);

//Execute Query
resultSet = preparedStatement.executeQuery();

//Run results
 enter one user name
admin
 Please input a password
1' or '1' = '1
 ERROR Incorrect username or password

‚Äč

Ending:

If there are any inadequacies or errors in the content, you are welcome to leave a message for me, Crab and Crab!^^

If you can help, pay attention to me!(The series will be updated at the first time on Public Number)

We don't know each other here, but we are working hard for our dreams.

A public slogan insisting on the original Java technology: more than 20 years

Tags: Java SQL JDBC Database

Posted on Sun, 04 Aug 2019 00:02:03 -0400 by ThYGrEaTCoDeR201