JDBC

JDBC(Java Database connectivity) is a Java API for executing SQL statements
 Can provide unified access to a variety of relational databases
 Java database connection specification (a set of interfaces)
There are four core classes of JDBC:
    DriverManager create connection
    Connection connection class
    Statement is used to execute sql statements
    ResultSet result set
 JDBC connection steps:
    1. Registration driver
    2. Get Connection
    3. Get the execution object Statement of sql Statement
    4. Execute sql statement to return result set
    5. Processing result set
    6. Close resources

JDBC connection

    // 1. Registration driver
    /*  This is equivalent to registering twice
        Driver The static code block inside the class has been registered once
        DriverManager.registerDriver(new Driver());
    */
    // Directly load the class into memory. The parameter is the fully qualified class name (package name + class name)
    Class.forName("com.mysql.jdbc.Driver");

    // 2. Get connection object
    // url is the address to access the database connection
    String url = "jdbc:mysql://localhost:3306/myjdbc";

    // How to get a connection
    Connection connection = DriverManager.getConnection(url, "root", "123456");

    // How to get the connection
    Properties info = new Properties();
    // Add user name and password
    info.setProperty("user", "root");
    info.setProperty("password", "123456");
    Connection connection = DriverManager.getConnection(url, info);

    // The third way to get the connection is to use a get request
    // Access connection with parameters
    String url2 = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456";
    Connection connection = DriverManager.getConnection(url2);

    // 3. Get the Statement of the object executing the sql Statement
    Statement statement = connection.createStatement();

    // 4. Execute sql statement to return result set
    String sql = "select * from users";
    ResultSet resultSet = statement.executeQuery(sql);

    // 5. Processing result set
    // Loop through result set output result
    // The next() method with records returns true and vice versa
    while (resultSet.next()) {
        // print data
        // Note: the index starts from 1 when querying the database
        // The Index added in the result set should correspond to the field in the query statement
        System.out.println(resultSet.getObject(1));
        System.out.println(resultSet.getObject(2));
        System.out.println(resultSet.getObject(3));
        System.out.println(resultSet.getObject(4));
        System.out.println(resultSet.getObject(5));
    }

    // 6. Close resources
    resultSet.close();
    statement.close();
    connection.close();

Add, delete, modify and query JDBC

@TestAnnotations are used to test methods
//Note: to usepublicDecorated method with no return value

    // Insertion method
    @Test
    public void testInsert() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/myjdbc";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        Statement statement = connection.createStatement();
        String sql = "insert into users values(5,'ab','123','123@qq.com','1997-06-23')";
        // executeUpdate addition, deletion and modification
        // row represents the number of rows affected
        int row = statement.executeUpdate(sql);
        if (row>0) {
            System.out.println("Insert successfully"+ row +"That's ok");
        }
        connection.close();
        statement.close();
    }

    // Update method
    @Test
    public void testUpdate() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/myjdbc";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        Statement statement = connection.createStatement();
        String sql = "update users set name='ac' where name='ab'";
        int row = statement.executeUpdate(sql);
        if (row>0) {
            System.out.println("Successful update"+ row +"That's ok");
        }
        connection.close();
        statement.close();
    }

    // Deletion method
    @Test
    public void testDelete() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/myjdbc";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        Statement statement = connection.createStatement();
        String sql = "delete from users where id=5";
        // Additions and deletions
        int row = statement.executeUpdate(sql);
        if (row >0) {
            System.out.println("Deleted successfully"+ row +"That's ok");
        }
        connection.close();
        statement.close();
    }

    // Query method
    @Test
    public void testSelect() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/myjdbc";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        Statement statement = connection.createStatement();
        // query
        String sql = "select id,name,email from users";
        ResultSet resultSet = statement.executeQuery(sql);
        // Process result set
        while (resultSet.next()) {
            // You can directly fill in the field name
            System.out.println(resultSet.getObject("id"));
            System.out.println(resultSet.getObject("name"));
            System.out.println(resultSet.getObject("email"));
        }
        resultSet.close();
        connection.close();
        statement.close();
    }

Exception handling of JDBC connection database

    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbc", "root", "123456");
        statement = connection.createStatement();
        String sql = "select * from users";
        resultSet = statement.executeQuery(sql);

        // Process result set (encapsulate database records into objects)
        // Save objects to a collection
        ArrayList<User> list = new ArrayList<>();
        while (resultSet.next()) {
            // Create user object
            User user = new User();
            user.setId(resultSet.getInt("id"));
            user.setName(resultSet.getString("name"));
            user.setPassword(resultSet.getString("password"));
            user.setEmail(resultSet.getString("email"));
            user.setBirthday(resultSet.getDate("birthday"));
            // Put into collection
            list.add(user);
        }
        // Traversal printing
        for (User user : list) {
            System.out.println(user);
        }
    } catch (ClassNotFoundException e) {
        // Stop procedure
        throw new RuntimeException("Driver load failed");
    } catch (SQLException e) {
        throw new RuntimeException("Failed to get connection");
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException("Shutdown failure");
            }
            // Speed up system recovery
            resultSet = null;
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException("Shutdown failure");
            }
            statement = null;
        }
        if (connection != null) {
            try {
                connection.close(); 
            } catch (SQLException e) {
                throw new RuntimeException("Shutdown failure");
            }
            connection = null;
        }       
    }

JDBC tool class

JDBC util tool class

public class JDBCUtil {
    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;

    // Use static code block to load driver and read configuration file (let driver class load only once)
    static {
        // Use system classes to read configuration files
        ResourceBundle rb = ResourceBundle.getBundle("dbinfo");
        // Get data from file
        driverClass = rb.getString("driverClass");
        url = rb.getString("url");
        user = rb.getString("user");
        password = rb.getString("password");
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        /*
        // Using collections to read files
        Properties properties = new Properties();
        try {
            FileInputStream fis = new FileInputStream("src/dbinfo.properties");
            properties.load(fis);
            // read file
            driverClass = properties.getProperty("driverClass");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
        } catch (Exception e) {
        }
        */
    }

    // How to get database connection
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        return DriverManager.getConnection(url, user, password);
    }
    // Method to close the database if no result set needs to be closed, directly send null
    public static void closeAll(ResultSet resultSet, Statement statement, Connection connection) {
        // Non null judgment before closing resources to prevent null pointer
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException("Shutdown failure");
            }
            // Speed up system recovery
            resultSet = null;
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException("Shutdown failure");
            }
            statement = null;
        }
        if (connection != null) {
            try {
                connection.close(); 
            } catch (SQLException e) {
                throw new RuntimeException("Shutdown failure");
            }
            connection = null;
        }   
    }
}

TestJDBCUtil test class

public class TestJDBCUtil {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    @Test
    public void testSelect() {
        try {
            // Get connection
            connection = JDBCUtil.getConnection();
            statement = connection.createStatement();
            String sql = "select * from users";
            resultSet = statement.executeQuery(sql);
            ArrayList<User> list = new ArrayList<>();
            while (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setEmail(resultSet.getString("email"));
                user.setBirthday(resultSet.getDate("birthday"));
                list.add(user);
            }
            for (User user : list) {
                System.out.println(user);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // close resource
            JDBCUtil.closeAll(resultSet, statement, connection);
        }
    }
}

JDBC realizes user login

User login

public class Login {
    public static void main(String[] args) {
        // Receive the account and password entered by the user
        System.out.println("Please enter your account number:");
        Scanner scanner = new Scanner(System.in);
        String name = scanner.nextLine();
        System.out.println("Please input a password:");
        String password = scanner.nextLine();
        // Call query method
        DoLogin dl = new DoLogin();
        User user = dl.findUser(name, password);
        if (user != null) {
            System.out.println(user.getName()+"Login successfully");
        } else {
            System.out.println("Login failed");
        }
    }
}

Process the query operation of login

public class DoLogin {
    public User findUser(String name,String password) {
        User user = null;
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        String sql = "select * from users where name='"+name+"' and password='"+password+"'";
        // query data base
        try {
            connection = JDBCUtil.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            if (resultSet.next()) {
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setEmail(resultSet.getString("email"));
                user.setBirthday(resultSet.getDate("birthday"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(resultSet, statement, connection);
        }
        return user;
    }
}

sql statement injection problem (a constant condition is added)
Solution

public class DoLogin {
    public User findUser(String name,String password) {
        User user = null;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        // placeholder
        String sql = "select * from users where name=? and password=?";
        // query data base
        try {
            connection = JDBCUtil.getConnection();
            // Precompile sql statements
            statement = connection.prepareStatement(sql);
            // Assign values to placeholders of sql statements
            // Parameter 1: fill in the index of the question mark in the sql statement
            statement.setString(1, name);
            statement.setString(2, password);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setEmail(resultSet.getString("email"));
                user.setBirthday(resultSet.getDate("birthday"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(resultSet, statement, connection);
        }
        return user;
    }