JDBC usage details

introduction

JDBC (Java DataBase Connectivity) is a method for Java to connect to a database; In fact, the essence is a set of interfaces customized by SUN company, which can realize the connection between Java and various databases, as shown in the following figure:

Note: This paper uses IDEA demonstration, and the database uses MySQL database;

Steps of importing database connection Jar package from IDEA

First, we need to have the corresponding MySQL database connection driver jar package. If not, you can find me in the comment area;
Create an IDEA project normally, as shown in the figure:

Next, import the jar package as shown in the figure;

In this way, I created a folder named lib, and then pasted mysql-connector-java-8.0.16.jar into the Lib directory;
As shown in the figure:

The last step is to import the jar package into the project, that is, right-click the jar package and click Add as Library;


This will import the jar package;
Next is the basic operation of JDBC;

JDBC programming operations

JDBC can be summarized into five steps in Java code:

  1. Register driver (determine connected database)
  2. Get connection (open channel between JVM process and database process)
  3. Get the database operation object (which can be used to execute sql statements)
  4. Execute SQL statement (if it is a select query statement, the query results need to be processed)
  5. Release resources (close the process channel opened in the second step)

I created a test02 database myself, and one of the tables is t_user, as shown in the figure:

Let's show the login operation of connecting to the database using JDBC (existing problems):

package jdbctest01;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;

/*
    Simulate user login function
    SQL injection problem, unsafe;
*/
public class JdbcTest01 {
    public static void main(String[] args) {
        // Initialization interface
        Map<String, String> userLoginInfo = initUI();
        // Verify user name and password
        boolean loginSuccess = login(userLoginInfo);
        System.out.println(loginSuccess ? "Login succeeded" : "Login failed");
    }

    /**
     * Verify user name and password
     * @param userLoginInfo User login information
     * @return false Indicates failure, and true indicates success
     */
    private static boolean login(Map<String, String> userLoginInfo) {
        boolean loginSuccess = false; // sign
        String loginName = userLoginInfo.get("loginName"); // Get user name
        String password = userLoginInfo.get("password"); // Get login password
        // JDBC code
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            // Register driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            // Get connection
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test02?serverTimezone=UTC", "root", "020216");
            // Get database operation object
            statement = connection.createStatement();
            // Execute sql statement
            String sql = "select * from t_user where loginName = '"+ loginName +"' and loginPwd = '"+ password +"'";
            resultSet = statement.executeQuery(sql);
            // Processing result set
            if (resultSet.next()) {
                loginSuccess = true;
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // Release resources (release in order)
            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();
                }
            }
        }
        return  loginSuccess;
    }

    /**
     * Initialize user interface
     * @return Login information such as user name and password entered by the user
     */
    private static Map<String, String> initUI() {
        Scanner scan = new Scanner(System.in);

        // Enter the user name;
        System.out.print("user name:");
        String userName = scan.nextLine();

        // Input password;
        System.out.print("password:");
        String password = scan.nextLine();

        // Put in map
        Map<String, String> userLoginInfo = new HashMap<>();
        userLoginInfo.put("loginName", userName);
        userLoginInfo.put("password", password);
        return userLoginInfo;
    }
}

This implements a login function


However, there is a problem with SQL injection. SQL injection can collect data by yourself. In short, it is not safe. Let me demonstrate:

You can see that Zhang San's password is 200002, but I can log in successfully through SQL injection statement 1 'or' 1 '=. This is a big problem;
To solve the SQL injection problem, you can use the following methods:
The code is as follows:

package jdbctest01;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;

/*
Resolve SQL injection issues
 */
public class JdbcTest02 {
    public static void main(String[] args) {
        Map<String, String> userLoginInfo = initUI();
        boolean loginSuccess = login(userLoginInfo);
        System.out.println(loginSuccess ? "Login succeeded" : "Login failed");
    }

    private static boolean login(Map<String, String> userLoginInfo) {
        boolean loginSuccess = false;
        String userName = userLoginInfo.get("userName");
        String password = userLoginInfo.get("password");
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        // JDBC code
        try {
            // Register driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            // Get link
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test02?serverTimezone=UTC", "root", "020216");
            // Get data operation object (different here)? Is a placeholder
            String sql = "select * from t_user where loginName = ? and loginPwd = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, userName); // Set first placeholder? Is username
            preparedStatement.setString(2, password); // Set second placeholder? password
            // Execute sql statement
            resultSet = preparedStatement.executeQuery();
            // Processing result set
            if (resultSet.next()) {
                loginSuccess = true;
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // Release resources
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return loginSuccess;
    }
    private static Map<String, String> initUI() {
        Scanner scan = new Scanner(System.in);
        System.out.print("Please enter user name:");
        String userName = scan.nextLine();
        System.out.print("Please input a password:");
        String password = scan.nextLine();
        Map<String, String> userLoginInfo = new HashMap<>();
        userLoginInfo.put("userName" , userName);
        userLoginInfo.put("password", password);
        return  userLoginInfo;
    }
}

SQL injection failed:

It can be found that the main difference is that the PreparedStatement is used instead of the Statement when obtaining the database operation object, which can solve the problem of SQL injection; Therefore, in practice, there are more preparedstatements and placeholder operations. It is also very simple. There are comments in the code. You can understand it by trying it yourself; You can look at the code several times to find the difference;

This is the most basic operation of JDBC. In fact, it is these fixed steps. I really don't understand. Just remember them first, and then I will understand them when I use them more;

Next, we will encapsulate some operations of JDBC;

Custom JDBC tool class

Here, we encapsulate the registration, connection and closing operations to form a tool class;
The code has detailed comments;

package jdbctest01.mytest;

import java.sql.*;

/*
    JDBC Tool class to simplify JDBC programming
*/
public class DBUtil {
    /**
     * Constructors in tool classes are private
     * Because the methods in the tool class are static, you can call them directly through the class name.
     */
    private DBUtil(){}
    
    /**
     * Static code block, which is executed when the class is loaded
     * Put the code of the registered driver in the static code block to avoid repeated calls when obtaining the connection object many times
     */
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    
    // Get connection
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/test02?serverTimezone=UTC", "root", "020216");
    }
    
    // Closing method
    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        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();
            }
        }
    }
}

This operation can also be realized by yourself, which is not difficult;
It is worth mentioning here that the registered driver is placed in the static code block, so as to avoid repeated calls while ensuring calls;

CRUD operation

In fact, the most commonly used operation for database is adding, deleting, modifying and querying, but it is complex to rewrite the code every time, so you can also encapsulate an operation of adding, deleting, modifying and querying by yourself; The previous test02 database is still used here. Here is the operation on the bank table; The bank table is shown in the figure below:

Next is an addition, deletion, modification and query operation code encapsulated by myself (DBUtil tool class is used here):

package jdbctest01.mytest;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

// Attempt to encapsulate the add, delete, modify query operation for a table
public class CRUD {
    /**
     * Add data to the bank table
     * @param num Primary key value
     * @param user user name
     * @param money Number of deposits
     * @return The return value is the number of sql executions
     * @throws SQLException DBUtil.getConnection()Exception handling for
     */
    public static int add(int num, String user, int money) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        // Register drivers and get links
        connection = DBUtil.getConnection();
        // Get data operation object
        String sql = "insert into bank(num, user, money)values(?, ?, ?)";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, num); // First placeholder? Is num
        preparedStatement.setString(2, user); // Second placeholder? For user
        preparedStatement.setInt(3, money); // Third placeholder? For money
        // Execute sql statement
        int count = preparedStatement.executeUpdate(); // Execute insert statement
        // Release resources
        DBUtil.close(connection, preparedStatement, null);
        return count;
    }

    /**
     * Delete the bank table
     * @param num Primary key value
     * @return Returns the number of sql executions
     * @throws SQLException DBUtil.getConnection()Exception handling for
     */
    public static int delete(int num) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        connection = DBUtil.getConnection();
        String sql = "delete from bank where num = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, num);
        int count = preparedStatement.executeUpdate(); // Execute delete statement
        DBUtil.close(connection, preparedStatement, null);
        return count;
    }

    /**
     * Update the specified data of the bank
     * @param num Primary key value
     * @param user user name
     * @param money Number of deposits
     * @return Returns the number of sql executions
     * @throws SQLException DBUtil.getConnection()Exception handling for
     */
    public static int update(int num, String user, int money) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        connection = DBUtil.getConnection();
        String sql = "update bank set user = ?, money = ? where num = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, user);
        preparedStatement.setInt(2, money);
        preparedStatement.setInt(3, num);
        int count = preparedStatement.executeUpdate(); // Execute the update statement
        DBUtil.close(connection, preparedStatement, null);
        return count;
    }

    /**
     * Realize the query of the corresponding data in the bank table
     * @param num Primary key value
     * @return Returns the ResultSet value and the corresponding query result
     * @throws SQLException DBUtil.getConnection()Exception handling for
     */
    @Deprecated
    public static ResultSet select(int num) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        connection = DBUtil.getConnection();
        String sql = "select * from bank where num = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, num);
        resultSet = preparedStatement.executeQuery(); // Execute the select statement
        if (resultSet.next()) {
            System.out.println("num:" + resultSet.getInt("num") +
            " user:" + resultSet.getString("user") + " money:" + resultSet.getInt("money"));
        }
        DBUtil.close(connection, preparedStatement, resultSet);
        return resultSet;
    }

    /**
     * Realize the query of the corresponding data in the bank table
     * @param num Primary key value
     * @return Returns an object of type Bank
     * @throws SQLException DBUtil.getConnection()Exception handling for
     */
    public static Bank selectElem(int num) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Bank bank = new Bank();
        connection = DBUtil.getConnection();
        String sql = "select * from bank where num = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, num);
        resultSet = preparedStatement.executeQuery(); // Execute the select statement
        if (resultSet.next()) {
            bank.setNum(resultSet.getInt("num"));
            bank.setUser(resultSet.getString("user"));
            bank.setMoney(resultSet.getInt("money"));
        }
        DBUtil.close(connection, preparedStatement, resultSet);
        return bank;
    }
}

It should also be noted that when executing the select query statement, the executeQuery() method is used, and the executeUpdate() method is used for addition, deletion and modification;

summary

JDBC doesn't have many things. In fact, it's just a few steps. It may be a little confused as soon as you get in touch. Just get used to it;
I still want to mention here: the code is just a reference. I hope you can understand the operation method through my code and try to create a database yourself;
Of course, if you have questions or want to comment on relevant resources, you can leave a message. I hope this article can help you!!!

Welcome your comments!

Tags: Java MySQL JDBC

Posted on Sat, 27 Nov 2021 18:07:09 -0500 by heyjohnlim