Concept: Java DataBase Connectivity, java database connection, java language operation database.

Essence: it is actually an officially defined set of rules for operating all relational databases, namely interfaces. Each database manufacturer implements this set of interface and provides the database jar package. We can use this set of interface (JDBC) for programming. The real executed code is to drive the implementation classes in the jar package, mainly using polymorphic calling methods

Interface type object name = new Database type()；

quick get start

Steps:

1. Import driver package
   • Copy mysql-connector-java-5.1 37-bin. Jar to the libs directory of the project
   • Right click Add As Library
2. Register driver (you don't need to register after mysql5.0)
3. Get database Connection object Connection
4. Define sql statements
5. Gets the Statement object that executes the sql Statement
6. Execute sql and accept the returned results
7. Processing results
8. Release resources

//        1. Import driver package
//          First copy the jar package to the libs folder and right-click Add As Library
//        2. Register driver
//            mysql8.* The registered driver after the version is "com.mysql.cj.jdbc.Driver", and the previously registered driver is "com.mysql.jdbc.Driver"
        Class.forName("com.mysql.cj.jdbc.Driver");
//        3. Get database Connection object Connection
//            The version after 8.0 needs to be added after the path of the url? serverTimezone=GMT%2B8, which means to set the time to Beijing time. After the port number is the selected database, and here is the xscj database
        Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/xscj?serverTimezone=GMT%2B8", "root", "root");
//        4. Define sql statements
        String sql = "select * from kc";
//        5. Gets the Statement object that executes the sql Statement
        Statement stmt = conn.createStatement();
//        6. Execute sql and accept the returned results
        ResultSet resultSet = stmt.executeQuery(sql);
//        7. Processing results
        System.out.println(resultSet);
//        8. Release resources
        stmt.close();
        conn.close();

Explain each object in detail

• DriverManager driver management object

   1. Register driver: tell the program which database driver to use jar
   static void registerDriver(Driver driver) :Register with the given driver DriverManager .  
   Write code using:  Class.forName("com.mysql.jdbc.Driver");
   By viewing the source code, it is found that: com.mysql.jdbc.Driver Static code block in class
      static {
         try {
             java.sql.DriverManager.registerDriver(new Driver());
             } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
            }
          }

be careful: mysql5 Subsequent drive jar The package can omit the step of registering the driver.
   2. Get database connection:
      * method: static Connection getConnection(String url, String user, String password) 
      * Parameters:
         * url: Specify the path of the connection
         * Syntax: jdbc:mysql://ip address (domain name): port number / database name
         * example: jdbc:mysql://localhost:3306/db3
         * Details: if the connection is local mysql Server, and mysql The default service port is 3306, then url It can be abbreviated as: jdbc:mysql:///Database name
         * user: user name
         * password: password 

• Connection database connection object

	Function:
	1. Get execution sql Object of
        *  Statement createStatement()
       	*  PreparedStatement prepareStatement(String sql)  
     2. Management services:
       	* Open transaction: setAutoCommit(boolean autoCommit) : Call this method to set the parameter to false，The transaction is started
        * Commit transaction: commit() 
        * Rollback transaction: rollback() 

• Statement object to execute sql

1. implement sql
    1. boolean execute(String sql) : Can perform any sql sentence 
    2. int executeUpdate(String sql) : implement DML(insert,update,delete)Statement DDL(create，alter,drop)sentence
        * Return value: the number of affected rows, which can be judged by the number of affected rows DML The return value of whether the statement was executed successfully>0 If yes, the execution is successful, otherwise, it fails.
    3. ResultSet executeQuery(String sql)  : implement DQL(select)sentence
//        Exercise: int executeUpdate(String sql) and ResultSet executeQuery(String sql) of Statement
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql:///girls?serverTimezone=GMT%2B8", "root", "root");
            stmt = conn.createStatement();
            String zen = "insert into boys set id = 5, boyName = 'ryx', userCP = 20";
//            int i1 = stmt.executeUpdate(zen);
//            System.out.println(i1);
            String shan = "delete from boys where id = 5";
//            int i2 = stmt.executeUpdate(shan);
//            System.out.println(i2);
            String gai = "update boys set boyName = 'pyh' where id = 5";
//            int i3 = stmt.executeUpdate(gai);
//            System.out.println(i3);
            String cha = "select * from boys";
            ResultSet i4 = stmt.executeQuery(cha);
            while (i4.next()){
                int id = i4.getInt(1);
                String name = i4.getString(2);
                int cp = i4.getInt(3);
                System.out.println("id + name + cp = " + id + name + cp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
//            If the try is not executed successfully, you need to judge whether stmt and conn are null. If they are not close d directly, an error will be reported
            if (stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

• ResultSet is a result set object that encapsulates query results

* boolean next(): The cursor moves down one line to determine whether the current line is the end of the last line(Is there data)，If yes, return false，If not, return true
            * getXxx(parameter):get data
                * Xxx: Representative data types, such as: int getInt() ,    String getString()
                * Parameters:
                    1. int: Represents the number of the column,Starting from 1, such as: getString(1)
                    2. String: Represents the column name. For example: getDouble("balance")
            * be careful:
                * Use steps:
                    1. Move cursor down one line
                    2. Determine whether there is data
                    3. get data

    //Loop to determine whether the cursor is at the end of the last line.
        	while(rs.next()){
             	//get data
     	          int id = rs.getInt(1);
                 String name = rs.getString("name");
                 double balance = rs.getDouble(3);
                 System.out.println(id + "---" + name + "---" + balance);
             }
	//Note: the result set is also a resource and needs to be closed using close()
			res.close();

• PreparedStatement execute sql object

		1. SQL Injection problem: in splicing sql When, there are some sql Special keywords participate in string splicing. It will cause security problems
			1). Enter the user name and password: a' or 'a' = 'a
			2). sql: select * from user where username = 'fhdsjkf' and password = 'a' or 'a' = 'a' 
			Because user input'a' = 'a'It's an identity, and it's used or Keywords, so this time sql Statement is select * from user where true;Will cause bug
		2. solve sql Injection problem: Using PreparedStatement Object to solve
3. Precompiled SQL: Parameter use?As placeholder
		4. Steps:
				1). Import driver jar package mysql-connector-java-5.1.37-bin.jar
				2). Register driver
				3). Get database connection object Connection
				4). definition sql
					* be careful: sql Parameter usage? As a placeholder. For example: select * from user where username = ? and password = ?;
				5). Get execution sql Statement object PreparedStatement  call Connection.prepareStatement(String sql)Method, and put sql Statement passed
				6). Here? Assignment:
					* method: setXxx(Parameter 1,Parameter 2)
							    Xxx Different data types
						* Parameter 1:? The position number of starts from 1
						* Parameter 2:? Value of
				7). implement sql，Accept the returned result without passing sql sentence
				8). Processing results
				9). Release resources
		5. Note: it will be used later PreparedStatement To complete all operations of adding, deleting, modifying and querying
			1. Can prevent SQL injection
			2. More efficient

            conn = JDBCUtils.getConnection();
//            Create sql statements through placeholders
            String sql = "select * from user where username = ? and password = ?";
//            Get the prepareStatement object and pass in the sql statement with placeholders
            pstmt = conn.prepareStatement(sql);
//            Set placeholder data
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            res = pstmt.executeQuery();

Extract JDBC tool class

Every time you use JDBC, you need to create many objects and release them. There will be a lot of redundant code

1. Registered drivers are also extracted
2. Extract a method to get the connection object
   • Requirements: do not want to pass parameters (trouble), but also ensure the universality of tool classes.
   • Resolution: configuration file JDBC properties

   		url=
   		user=
   		password=
   

3. Extract a method to release resources
   import java.io.FileReader;
   import java.io.IOException;
   import java.net.URL;
   import java.sql.*;
   import java.util.Properties;

/*
* JDBC Tool class
* */
public class JDBCUtils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;
    static{
        //Read the resource file and get the value.

        try {
            //1. Create the Properties collection class.
            Properties pro = new Properties();
            //How to get the file under src path -- > classloader classloader
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
//            Through classloader Get JDBC. XML by getresource (file name) Properties, provided that the file is in the src folder
            URL res  = classLoader.getResource("jdbc.properties");
            String path = res.getPath();
            //2. Load file
            pro.load(new FileReader(path));
            //3. Obtain data and assign values
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            //4. Register driver
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    /**
     * Get connection
     * @return Return connection
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * Release resources
     * @param stmt
     * @param conn
     */
    public static void Close(Statement stmt, Connection conn){
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * Release resources
     * @param res
     * @param stmt
     * @param conn
     */
    public static void Close(ResultSet res, Statement stmt, Connection conn){
        if (res != null) {
            try {
                res.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

JDBC management transactions

JDBC management transactions are managed using Connection objects

        * Open transaction: setAutoCommit(boolean autoCommit) : Call this method to set the parameter to false，The transaction is started
            * In execution sql Open transaction before
        * Commit transaction: commit() 
            * When all sql All commit transactions have been completed
        * Rollback transaction: rollback() 
            * stay catch Rollback transaction in

The way to start a transaction is to directly use conn.setAutoCommit(false) after creating the database connection object (conn), and then commit the transaction to conn.commit(); after the sql statement is executed and before releasing resources;, If an exception occurs during transaction processing, the transaction must be rolled back conn.rollback(), and the transaction rollback is written in catch

    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;
        try {
            conn = JDBCUtils.getConnection();
//            Open transaction
            conn.setAutoCommit(false);
            String sql1 = "update user set password = ? where id = ?";
            String sql2 = "update user set password = ? where id = ?";
            pstmt1 = conn.prepareStatement(sql1);
            pstmt2 = conn.prepareStatement(sql2);
            pstmt1.setString(1, "qwe");
            pstmt1.setInt(2, 1);
            pstmt2.setString(1, "qwer");
            pstmt2.setInt(2, 2);
            pstmt1.executeUpdate();
//            Human error, causing transaction rollback
            int c = 3 / 0;
            pstmt2.executeUpdate();
//            Commit transaction
            conn.commit();
        } catch (SQLException e) {
//            If an error is reported, the transaction is rolled back
            if (conn != null) {
                conn.rollback();
            }
            e.printStackTrace();
        } finally {
            if (pstmt2 != null) {
                pstmt2.close();
            }
            if (pstmt1 != null) {
                pstmt1.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }

Database connection pool

1. Concept: in fact, it is a container (Collection) for storing database connections.
   After the system is initialized, the container is created, and some connection objects will be applied in the container. When the user accesses the database, the connection objects will be obtained from the container. After the user accesses, the connection objects will be returned to the container.

2. Benefits:

   1. save resources
   2. Efficient user access
      

realization

1. Standard interface: datasource javax SQL package

   1. method:
      • Get connection: getConnection()
      • Return Connection: Connection close(). If the Connection object Connection is obtained from the Connection pool, call Connection Close () method, the Connection will not be closed again. Instead, return the Connection

2. Generally, we do not implement it, but database manufacturers do

   1. C3P0: database connection pool technology
   2. Druid: database connection pool implementation technology, provided by Alibaba

C3P0

• Steps:

1. Import jar package (two) c3p0-0.9 5.2. jar mchange-commons-java-0.2. 12.jar
   • Don't forget to import the database driver jar package
2. Define profile:
   • Name: c3p0 Properties or c3p0 config XML (you must choose one of these two names)
   • Path: directly put the file in the src directory.
3. Create the core object database connection pool object ComboPooledDataSource
4. Get connection: getConnection

• code:

		 //1. Create database connection pool object
        DataSource ds  = new ComboPooledDataSource();
        //2. Get the connection object
        Connection conn = ds.getConnection();
		//3. Return the connection
		conn.close();
//        Use default configuration
//        DataSource ds = new ComboPooledDataSource();
//        Configure with the specified name
        DataSource ds = new ComboPooledDataSource("otherc3p0");
//        Get connection
        for (int i = 0; i < 9; i++) {
            Connection conn = ds.getConnection();
            System.out.println(i + ":" + conn);
            if (i == 5){
//                Return connection
                conn.close();
            }
        }

Druid

Steps:

1. Import the jar package druid-1.0 9.jar
2. Define profile:
   • It is in the form of properties
   • It can be called any name and placed in any directory. In the case, it is Druid properties
3. Load the configuration file. Properties

Properties pro = new Properties();

//			The whole sentence means to create a stream called is. The path of this stream is to find the configuration file Druid. In the case of this class properties
InputStream is = Class name.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(is);

//			Chain programming can also be used
pro.load(Objects.requireNonNull(Class name.class.getClassLoader().getResourceAsStream("druid.properties")));

1. Get database connection pool object: get DruidDataSourceFactory through the factory
2. Get connection: getConnection

//3. Load configuration file
Properties pro = new Properties();
InputStream is = DruidDemo01.class.getClassLoader().getResourceAsStream("druid.properties");
if (is != null) {
    pro.load(is);
}
//4. Get connection pool object
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
//5. Get connection
Connection conn = ds.getConnection();
System.out.println(conn);

Define tool classes

1. Define a class JDBC utils
2. Provide static code blocks, load configuration files, and initialize connection pool objects
3. Provide method
   1. Get connection method: get connection through database connection pool
   2. Release resources
   3. Method to get connection pool

package Utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import javax.xml.crypto.Data;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Objects;
import java.util.Properties;

public class JDBCDruidUtil {
    //    1. Define member variable DataSource
    private static DataSource ds;

    static {
        try {
//        1.1. Loading configuration file
            Properties pro = new Properties();
//        1.2. Get configuration file path
            pro.load(Objects.requireNonNull(JDBCUtil.class.getClassLoader().getResourceAsStream("druid.properties")));
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /*
        Get connection
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
    /*
        Release resources
     */
    public static void close(Statement stmt, Connection conn){
        if (stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
//                Return connection
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(ResultSet res, Statement stmt, Connection conn){
        if (res != null){
            try {
                res.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        close(stmt, conn);
    }
    /*
        Get connection pool method
     */
    public static DataSource getDataSource(){
            return ds;
    }
}

Spring JDBC

Spring framework's simple encapsulation of JDBC. Provides a JDBC template object to simplify JDBC development

Steps:

1. Import jar package
   

2. Create a JdbcTemplate object. Dependent on DataSource datasource
   JdbcTemplate template = new JdbcTemplate(ds);

3. Call the method of JdbcTemplate to complete the operation of adding, deleting, modifying and querying (Statement object is not required)

• int update(): execute DML statement. Add, delete and modify statements
  • There are two parameters. The first parameter is the sql statement, and the second and third... Parameters are the values represented by the placeholders in the sql statement in the PreparedStatement object. In this way, there is no need to use setXxx to pass parameters, and the number of affected rows will be returned

//        1. Import jar package
//        2. Create a JDBC template object
        JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCDruidUtil.getDataSource());
//        3. Call method
//        Define sql statements
        String sql = "INSERT INTO USER VALUES(NULL, ?, ?)";
//        Use the update method in the template to pass in the sql statement, fill the placeholders one by one, and return the number of affected rows
        int num = jdbcTemplate.update(sql, "wangwu", "324");

• queryForMap(): the query result encapsulates the result set as a map set, takes the column name as the key, takes the value as the value, and encapsulates this record as a map set. The length of the result set of this method query can only be 1

        String sql = "select * from emp where id = ?";
//        The map collection can only receive one return record
        Map<String, Object> map = jdbcTemplate.queryForMap(sql, 1);
        System.out.println(map);//{id=1, NAME = Monkey King, gender = male, salary = 10000.0, join_date = February 24, 2013, dept_id = 1}

• queryForList(): the query result encapsulates the result set into a list set, encapsulates each record into a Map set, and then loads the Map set into the list set

        String sql = "select * from emp";
//        Encapsulate each record into a Map set, and then load the Map set into the List set
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        System.out.println(mapList);

• query(): query results, which are encapsulated as JavaBean objects
  query parameters: sql statement, RowMapper

    /*
        6. Query all records and encapsulate them as a List collection of Emp objects
 */
    @Test
    public void test6_1(){
//        Use your own RowMapper class to operate
        String sql = "select * from emp";
        List<Emp> query = jdbcTemplate.query(sql, new RowMapper<Emp>() {
            // The RowMapper collection will traverse the ResultSet collection, operate one by one, and finally add it to the List collection
            @Override
            public Emp mapRow(ResultSet resultSet, int i) throws SQLException {
                Emp emp = new Emp();
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String gender = resultSet.getString("gender");
                double salary = resultSet.getDouble("salary");
                Date join_date = resultSet.getDate("join_date");
                int dept_id = resultSet.getInt("dept_id");
                emp.setId(id);
                emp.setName(name);
                emp.setGender(gender);
                emp.setSalary(salary);
                emp.setJoin_date(join_date);
                emp.setDept_id(dept_id);
                return emp;
            }
        });
        for (Emp emp : query) {
            System.out.println(emp);
        }
    }

• Although this can be used, it does not simplify our writing code, so we generally use the BeanPropertyRowMapper implementation class. It can complete the automatic encapsulation of data to JavaBean s

//							You can also write generics directly without writing generics class
list query(sql, new BeanPropertyRowMapper<generic paradigm>(generic paradigm.class));

    /*
    6. Query all records and encapsulate them as a List collection of Emp objects
*/
    @Test
    public void test6_2(){
//        Use the BeanPropertyRowMapper implementation class provided by Spring to operate
        String sql = "select * from emp";
        List<Emp> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Emp.class));
        for (Emp emp : list) {
            System.out.println(emp.toString());
        }
    }

• queryForObject: the query result is encapsulated as an object, which is generally used for the query of aggregate functions
  Parameter: (sql statement, return value type. class)

    /*
        7. Total records queried
    */
    @Test
    public void test7(){
//        Use the BeanPropertyRowMapper implementation class provided by Spring to operate
        String sql = "select count(id) from emp";
        Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println(integer);
    }