JDBC

JDBC(Java Database Connectivity)

Java database connection specification (a set of interfaces) provided by Sun company

JDBC core class

DriverManager create connection

Connection connection class

Statement execute sql statement

ResultSet result set

JDBC connection steps

1. Registration driver

2. Get the connection

3. Get the execution object Statement of sql Statement

4. execute sql statement to return ResultSet

5. Processing result set

6. Close resources

Registration driven
 //It should be noted that this registration method is equivalent to two times of registration
 //Because the static code block inside the Driver 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");

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

//Connection mode 1:
// root database user name
// 123456 database password
Connection connection = DriverManager.getConnection(url, "root", "123456");
//Connection mode 2:
Properties info = new Properties();
// Add user name and password
// Note: don't misspell the key value
info.setProperty("user", "root");
info.setProperty("password", "123456);
Connection connection = DriverManage.getConnection(url, info);
//Connection mode 3: it is equivalent to using a get request to access the link with parameters
String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456";
Connection connection = DriverManager.getConnection(url);

// Get the Statement of the object executing the sql Statement
Statement statement = connection.createStatement();
// Execute sql statement and return result set
// The Index added in the result set should correspond to the field in the query statement
String sql = "select * from users";
ResultSet resultSet = statement.executeQuery(sql);
// Process result set
// Loop through result set, output result
// The next() method with record returns true, but not false
while(resultSet.next()) {
    // print data
    // Note: when querying a database, the index starts at 1
    System.out.print(resultSet.getObject(1));
    System.out.print(resultSet.getObject(2));
    System.out.print(resultSet.getObject(3));
    System.out.print(resultSet.getObject(4));
    System.out.println(resultSet.getObject(5));
}
// close resource
resultSet.close();
statement.close();
connection.close();

Test addition, deletion, modification and query

@Test 
public void testInsert() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456";
    Connection connection = DriverManager.getConnection(url);
    Statement statement = connection.createStatement();
    String sql = "insert into users values(4, 'zs', '123', '123@163.com', '2018-01-01
    int row = statement.executeUpdate(sql);
    System.out.println(row);
    if (row > 0) {
        System.out.println("Insert success");
    }
    statement.close();
    connection.close();
}

@Test 
public void testUpdate() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456";
    Connection connection = DriverManager.getConnection(url);
    Statement statement = connection.createStatement();
    String sql = "update users set name='zs";
    int row = statement.executeUpdate(sql);
    System.out.println(row);
    if (row > 0) {
        System.out.println("Update success");
    }
    statement.close();
    connection.close();
}

@Test 
public void testDelete() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456";
    Connection connection = DriverManager.getConnection(url);
    Statement statement = connection.createStatement();
    String sql = "delete from users where id=5";
    int row = statement.executeUpdate(sql);
    System.out.println(row);
    if (row > 0) {
        System.out.println("Delete successful");
    }
    statement.close();
    connection.close();
}   

@Test 
public void testSelect() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456";
    Connection connection = DriverManager.getConnection(url);
    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.print(resultSet.getObject("id") + " ");
        System.out.print(resultSet.getObject("name") + " ");
        System.out.println(resultSet.getObject("email") + " ");
    }
    resultSet.close();
    statement.close();
    connection.close();
}

Exception handling when connecting to database

public static void main(String[] args) {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/myjdbc";
        connection = DriverManager.getConnection(url, "root", "123456");
        statement = connection.createStatement();
        String sql = "select * from users";
        resultSet = statement.executeQuery(sql);
        // Processing result set (encapsulating records of data into objects)
        ArrayList<User> arrayList = 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
            arrayList.add(user);
        }
        // Traversal view
        for (User user : arrayList) {
            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 {
        // Before closing a resource, make a non null judgment to prevent null pointer exceptions
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException("Resource shutdown failed");
            }
            // Speed up system recovery
            resultSet = null;
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException("Resource shutdown failed");
            }
            // Speed up system recovery
            statement = null;
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException("Resource shutdown failed");
            }
            // Speed up system recovery
            connection = null;
        }
    }
}

Create the dbinfo.properties file as the configuration file

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myjdbc
user=root
password=123456

take JDBC Encapsulation as a tool class method




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
    static {
        // The first way to read a configuration file: using a collection to read a file
        Properties properties = new Properties();
        FileInputStream fileInputStream;
        try {
            fileInputStream = new FileInputStream("src/dbinfo.properties");
            properties.load(fileInputStream);
            // read file
            driverClass = properties.getProperty("driverClass");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
        } catch (Exception e) {
            e.printStackTrace();
        }
        // The second way to read the configuration file: use the system class to read the configuration file
        ResourceBundle resourceBundle = ResourceBundle.getBundle("dbinfo");
        // Get data from file
        driverClass = resourceBundle.getString("driverClass");
        url = resourceBundle.getString("url");
        user = resourceBundle.getString("user");
        password = resourceBundle.getString("password");


        // Let the driver class load only once
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    // How to get database connection
    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        return DriverManager.getConnection(url, user, password);
    }
    // To close the database, if there is no result set to close, just empty it
    public static void closeAll(ResultSet resultSet, Statement statement, Connection connection) {
        // Before closing a resource, make a non null judgment to prevent null pointer exceptions
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException("Resource shutdown failed");
            }
            // Speed up system recovery
            resultSet = null;
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException("Resource shutdown failed");
            }
            // Speed up system recovery
            statement = null;
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException("Resource shutdown failed");
            }
            // Speed up system recovery
            connection = null;
        }
    }
}

test JDBC Tool class

public class TestJDBCUtil {
    @Test
    public void testSelect() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            // Get connection
            connection = JDBCUtil.getConnection();
            statement = connection.createStatement();
            String sql = "select * from users";
            resultSet = statement.executeQuery(sql);
            // Processing result set (encapsulating records of data into objects)
            ArrayList<User> arrayList = 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
                arrayList.add(user);
            }
            // Traversal view
            for (User user : arrayList) {
                System.out.println(user);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(resultSet, statement, connection);
        }
    }
}

SQL statement injection problem

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

public class DoLogin {
    // Find users by user name and password
    public User findUser(String name, String password) {
        String sql = "select * from users where name = '" + name + "' and password = '" + password + "'";
        // query data base
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        User user = null;
        try {
            connection = JDBCUtil.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            // Return only one piece of data
            System.out.println(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,Add the condition of permanent establishment
//The result is that the input sql statement is equivalent to select * from users;

Solution

public User findUser(String name, String password) {
    // Placeholder?
    String sql = "select * from users where name = ? and password = ?";
    // query data base
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    User user = null;
    try {
        connection = JDBCUtil.getConnection();
        // Precompile sql statements
        preparedStatement = connection.prepareStatement(sql);
        // Assign values to placeholders of sql statements
        // Parameter 1 fills in the index, and the question mark index in the sql statement,
        preparedStatement.setString(1, name);
        preparedStatement.setString(2, password);
        resultSet = preparedStatement.executeQuery();
        // Return only one piece of data
        System.out.println(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, preparedStatement, connection);
    }
    return user;
}