We’ve covered the basics of JDBC in JDBC — What is JDBC?

In this article we will talk about how to connect to MySQL database using JDBC.

First, basic operation

First we need a database and a table:

CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `user` (
	`id` INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT, 
	`username` VARCHAR(20) NOT NULL.`password` VARCHAR(20) NOT NULL
);
Copy the code

Jar: mysql-connector-java-8.0.20.jar

How to import jar packages or use Maven dependencies is not covered here.

Basic steps:

1. Registration drive
2. Get the connection object
3. Create SQL statement
4. Create a Statement object that executes SQL statements
5. Execute SQL statement
6. Release resources

1. Add, delete, change

public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    try {
        // Register the driver
        Class.forName("com.mysql.cj.jdbc.Driver");
        // Get the connection object
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test? serverTimezone=Asia/Shanghai&useSSL=false"."root"."123456");
        // Create an SQL statement
        String sql = Insert into user values (null, '1234', '1234')";
        // Create a Statement object
        stmt = conn.createStatement();
        // Execute the SQL statement
        int i = stmt.executeUpdate(sql);
        System.out.println(i);
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } finally {
        if(stmt ! =null) {// Avoid null pointer exceptions
            try {
                stmt.close();// Release resources
            } catch(SQLException e) { e.printStackTrace(); }}if(conn ! =null) {
            try {
                conn.close();
            } catch(SQLException e) { e.printStackTrace(); }}}}Copy the code

The code for DML statements is the same

2. The query

public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        // Register the driver
        Class.forName("com.mysql.cj.jdbc.Driver");
        // Get the connection object
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test? serverTimezone=Asia/Shanghai&useSSL=false"."root"."123456");
        // Create an SQL statement
  		String username = "Little view of action";
         String password = "1234";
         String sql = "select * from user where username = '" + username + "' and password = '" + password + "'";// Concatenates a string
        // Create a Statement object
        stmt = conn.createStatement();
        // Execute the SQL statement
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            float money = rs.getFloat("money");
            System.out.println(id + "--" + name + "--"+ money); }}catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } finally {
        if(rs ! =null) {
            try {
                rs.close();
            } catch(SQLException e) { e.printStackTrace(); }}if(stmt ! =null) {// Avoid null pointer exceptions
            try {
                stmt.close();// Release resources
            } catch(SQLException e) { e.printStackTrace(); }}if(conn ! =null) {
            try {
                conn.close();
            } catch(SQLException e) { e.printStackTrace(); }}}}Copy the code

Second, the explanation of class

1. DriverManager

(1) Register the driver through DriverManager

Class.forname (” com.mysql.cj.jdbc.driver “) loads the Driver Class into memory.

We look at the source code for the Driver class and find static code blocks:

static {
    try {
        DriverManager.registerDriver(new Driver());
    } catch (SQLException var1) {
        throw new RuntimeException("Can't register driver!"); }}Copy the code

The static code block as the class is loaded and executed, once performed, then through the DriverManager registerDriver (new Driver ()) registration drive.

(2) Obtain the Connection object from DriverManager

DriverManager.getConnection(url, username, password)
Copy the code

We only need to provide three parameters: the URL of the database, username, and password.

Note: time zone is required in the URL.

2. Connection

conn.createStatement()
Copy the code

Create a Statement object using the Connection object

3. Statement

This object can execute static SQL statements and return execution results.

4. ResultSet

A data table representing a database result set, typically generated by a statement that executes a query to the database.

The result set can be traversed using the next() method

SQL injection

A serious problem with the above example is that the SQL statements we wrote were static; in other words, the SQL statements were concatenated using strings. Such as:

String username = "Little view of action";
String password = "1234";
String sql = "select * from user where username = '" + username + "' and password = '" + password + "'";
Copy the code

After we substitute the variable, the effect is:

select * from user where username = 'Row view' and password = '1234'
Copy the code

That’s fine.

But now things have changed:

String username = "Little view of action";
String password = "1' or '1' = '1";
Copy the code

After we substitute the variable, the effect is:

select * from user where username = 'Row view' and password = '1' or '1' = '1'
Copy the code

Or ‘1’=’1′; or ‘1’=’1′

select * from user
Copy the code

So the whole table will be queried.

So that’s SQL injection.

Use PreparedStatement to prevent SQL injection

The Statement object is used to execute static SQL statements. If specially constructed statements are executed, SQL injection may occur and security vulnerabilities may occur.

Using a PreparedStatement object avoids this problem.

PreparedStatement objects are objects of precompiled SQL statements inherited from statements.

What are pre-compiled SQL statements?

Static SQL statement

String sql = "select * from user where username = '" + username + "' and password = ' " + password + "'";
Copy the code

Change to a precompiled SQL statement:

String sql = "select * from user where username = ? and password=?";
Copy the code

Instead of concatenating strings, write SQL statements using question marks? Placeholders replace variables.

The steps for using JDBC have changed:

1. Registration drive
2. Get the connection object
3. Create SQL statement
4. Create a PreparedStatement object that executes SQL statements
5. Give? The assignment
6. Execute SQL statement
7. Release resources

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        // Register the driver
        Class.forName("com.mysql.cj.jdbc.Driver");
        // Get the connection object
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test? serverTimezone=Asia/Shanghai&useSSL=false"."root"."123456");
        // Create an SQL statement
        String username = "Little view of action";
        String password = "1234";
        String sql = "select * from user where username = ? and password = ?";
        // Create a PreparedStatement object
        pstmt = conn.prepareStatement(sql);
        / / to? The assignment
        pstmt.setString(1, username);
        pstmt.setString(2, password);
        // Execute the SQL statement
        rs = pstmt.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("id");
            String unm = rs.getString("username");
            String pwd = rs.getString("password");
            System.out.println(id + "--"+ unm + "--"+ pwd); }}catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } finally {
        if(rs ! =null) {
            try {
                rs.close();
            } catch(SQLException e) { e.printStackTrace(); }}if(pstmt ! =null) {// Avoid null pointer exceptions
            try {
                pstmt.close();// Release resources
            } catch(SQLException e) { e.printStackTrace(); }}if(conn ! =null) {
            try {
                conn.close();
            } catch(SQLException e) { e.printStackTrace(); }}}}Copy the code

Five, further encapsulation

The above example has several disadvantages:

1. There is a lot of duplicate code.
2. The fully qualified class name of the driver class, the URL of the database, username, and password are written in the code, and you need to modify the code if you change these values.

First, we write the Driver’s fully qualified class name and database information in the configuration file database.properties. We obtain these values by reading the configuration file. When we need to change the information, we can directly modify the information in the configuration file without modifying the code.

driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/test? serverTimezone=Asia/Shanghai&useSSL=false username=root password=123456Copy the code

We then encapsulate the actions of registering driver classes, obtaining connections, and releasing resources into the utility class JdbcUtil.

public class JdbcUtil {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    static {
        try {
            Properties pro = new Properties();
            ClassLoader classLoader = JdbcUtil.class.getClassLoader();
            URL resourceURL = classLoader.getResource("database.properties");
            String path = resourceURL.getPath();
            pro.load(new FileReader(path));
            driver = pro.getProperty("driver");
            url = pro.getProperty("url");
            username = pro.getProperty("username");
            password = pro.getProperty("password");
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch(ClassNotFoundException e) { e.printStackTrace(); }}public static Connection getConnection(a) {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void close(Statement stmt, Connection conn) {
        if(stmt ! =null) {// Avoid null pointer exceptions
            try {
                stmt.close();// Release resources
            } catch(SQLException e) { e.printStackTrace(); }}if(conn ! =null) {
            try {
                conn.close();
            } catch(SQLException e) { e.printStackTrace(); }}}public static void close(ResultSet rs, Statement stmt, Connection conn) {
        close(stmt, conn);

        if(conn ! =null) {
            try {
                conn.close();
            } catch(SQLException e) { e.printStackTrace(); }}}}Copy the code

When we use JDBC again, we can use utility classes to simplify the code.

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        // Get the connection
        conn = JdbcUtil.getConnection();
        // Create an SQL statement
        String username = "Little view of action";
        String password = "1234";
        String sql = "select * from user where username = ? and password = ?";
        // Create a PreparedStatement object
        pstmt = conn.prepareStatement(sql);
        / / to? The assignment
        pstmt.setString(1, username);
        pstmt.setString(2, password);
        // Execute the SQL statement
        rs = pstmt.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("id");
            String unm = rs.getString("username");
            String pwd = rs.getString("password");
            System.out.println(id + "--"+ unm + "--"+ pwd); }}catch (SQLException e) {
        e.printStackTrace();
    } finally{ JdbcUtil.close(rs, pstmt, conn); }}Copy the code

Please correct me if there are any mistakes

The article was first published on the public account “Pedestrian View learning”.