JDBC and DBUtils tools

Keywords: Java Database MySQL

JDBC and DBUtils tools

What is JDBC?

In Web development, it is inevitable to use database to store and manage data. In order to provide support for database access in Java language, SUN company provided a set of standard Java class library for database access, namely JDBC, in 1996

The full name of JDBC is Java Database Connectivity, which is a set of Java API s for executing SQL statements. The application program can connect to the relational database through this set of APIs, and use SQL statements to complete the operations of querying, updating, adding and deleting the data in the database.

Different kinds of databases (such as mysql, Oracle, etc.) process data in different ways. If the database is operated directly using the access interface provided by the database manufacturer, the portability of the application will become very poor. For example, the user currently uses the interface provided by MySQL in the program to operate the database. If you change to Oracle database, you need to reuse the interface provided by Oracle database, so the amount of code changes will be very large. With JDBC, this situation no longer exists, because it requires various database manufacturers to provide database drivers according to unified specifications, and JDBC is connected with specific database drivers in the program, so users do not have to interact directly with the underlying database, which makes the code more universal.

Before using JDBC, you should first download the corresponding driver jar package from the corresponding official website and configure it into the project according to the database manufacturer you use.

JDBC is a set of interfaces provided by sun company. All interfaces have callers and implementers. Interface oriented calling and interface oriented writing implementation classes belong to interface oriented programming.

Why interface oriented programming: in order to understand the coupling, reduce the coupling degree of the program and improve the expansion force of the program.

The way the application uses JDBC to access the database is shown in the following figure.

As can be seen from the above figure, JDBC acts as a bridge between applications and databases. When applications use JDBC to access a specific database, they need to connect with different databases through different database drivers. After connecting, they can operate the database accordingly.

JDBC common API s

Driver interface

Driver interface is an interface that all JDBC drivers must implement. This interface is specially provided for database manufacturers. It should be noted that when writing a JDBC program, you must load the database driver or class library used into the classpath of the project (MySQL driver JAR package here).

DriverManager class

The DriverManager class is used to load the JDBC driver and create a connection to the database. In the DriverManager class, two important static methods are defined, as shown in the following table.

Connection interface

The Connection interface represents the Connection between the Java program and the database. Only after obtaining the Connection object can you access the database and operate the data table. In the Connection interface, a series of methods are defined, and their common methods are shown in the following table.

Statement interface

The Statement interface is used to execute static SQL statements and return a result object. The object of the interface is obtained through the createStatement() method of the Connection instance. Use this object to send static SQL statements to the database for compilation and execution, and then return the processing results of the database. In the Statement interface, three common methods for executing SQL statements are provided, as shown in the following table.

PreparedStatement interface

The Statement interface encapsulates the method of JDBC executing SQL statements, which can complete the operation of Java programs executing SQL statements. However, in the actual development process, it is often necessary to take the variables in the program as the query conditions of SQL statements, and using the Statement interface to operate these SQL statements will be too cumbersome and have security problems. To solve this problem, an extended PreparedStatement interface is provided in the JDBC API.

PreparedStatement is a sub interface of Statement, which is used to execute precompiled SQL statements. This interface extends the execution of SQL statements with parameters. The SQL statements in this interface can use the placeholder "?" to replace their parameters, and then assign values to the parameters of the SQL statements through the setXxx() method. In the PreparedStatement interface, some common methods are provided, as follows

It should be noted that the setDate() method in the table can set the Date content, but the type of the parameter Date is java.sql.Date, not java.util.Date.

When assigning a value to a parameter in an SQL statement through the setXxx() method, you can set multiple types of input parameters through a method that is compatible with the defined SQL type of the input parameter (for example, if the parameter has an SQL type of Integer, you should use the setInt method), or through the setObject() method. The details are as follows:

ResultSet interface

The resultset interface is used to save the result set returned when JDBC executes the query, which is encapsulated in a logical table. Inside the ResultSet interface, there is a cursor or pointer to the table data row. When the ResultSet object is initialized, the cursor moves the cursor to the next row before calling the next() method before the first row of the form. If there is no data in the next row, false is returned. In applications, the next() method is often used as a condition of the while loop to iterate over the resultset result set.

ResultSet is mainly used to store the result set. You can obtain the data in the result set one by one from the front to the back through the next() method.

The common methods in the ResultSet interface are shown in the following table.

As can be seen from the table, a large number of getXxx() methods are defined in the ResultSet interface, and which getXxx() method to use depends on the data type of the field. The program can obtain the specified data either by the name of the field or by the index of the field. The index of the field is numbered from 1. For example, if the field name of the first column of the data table is id and the field type is int, you can obtain the value of the column by using getInt(1) field index or getInt("id") field name.

Steps to implement a JDBC program

Before using JDBC, go to the official website of the corresponding manufacturer to download the driver jar package.

JDBC can generally be used in the following six steps.

(1) Load and register the database driver. (registered driver)

That is to tell the Java program which brand of database to connect to. I use mysql here.

Although the registration can also be completed by using the DriverManager.registerDriver(new com.mysql.jdbc.Driver()) method, the database driver will be registered twice. This is because the database driver has been registered in the static code block in the source code of the driver class. Therefore, in order to avoid repeated registration of the database driver, you only need to use the Class.forName() method to load the driver class in the program.

//Step 1: load and register the driver. There are two ways.
//First kind
Driver driver = new Driver();
DriverManager.registerDriver(driver);

//Second (more commonly used)
Class.forName("com.mysql.jdbc.Driver")

(2) Get database connection through DriverManager. (get connection)

It means that the process channel between the jvm and the database is open, which belongs to the communication between processes. It must be closed after use.

//Step 2: get database connection through DriverManager

String url = "jdbc:mysql://127.0.0.1:3306/databaseName";
String userName = "root";
String password = "123456";

Connection connection = DriverManager.getConnection(url,userName,password);

As can be seen from the above code, there are three parameters in the getConnection() method, which respectively represent the URL address to connect to the database, the user name and password to log in to the database. Taking MySQL database as an example, the writing format of its URL address is as follows:

In the above code, jdbc:mysql: is a fixed writing method, and MySQL refers to MySQL database. Hostname refers to the name of the host (if the database is on the local machine, hostname can be localhost or 127.0.0.1; if the database to be connected is on another computer, hostname is the IP of the computer to be connected), port refers to the port number to connect to the database (MySQL port number is 3306 by default), and databasename refers to the name of the corresponding database in MySQL.

Here is a note. If the mysql database version is 8, the url should be added after it

? Usessl = false & servertimezone = UTC, i.e

jdbc:mysql://127.0.0.1:3306/databaseName?useSSL=FALSE&serverTimezone=UTC

If not, the program will report an abnormal error.

(3) Obtain the Statement object through the Connection object. (obtain the database operation object (the object that executes the sql Statement))

Connection creates a Statement in the following three ways:

  • createStatement(): creates a basic Statement object.
  • prepareStatement(): creates a PreparedStatement object.
  • prepareCall(): creates a CallableStatement object.

Take creating a basic Statement object as an example. The creation method is as follows:

//Step 3: get the Statement object through the Connection object
Statement statement = connection.createStatement();

(4) Execute SQL Statement using Statement. (execute SQL Statement)

All statements have the following three methods to execute SQL statements:

  • execute(): any SQL statement can be executed.
  • executeQuery(): usually execute a query statement and return the ResultSet object representing the result set after execution.
  • executeUpdate(): mainly used to execute DML and DDL statements. When executing DML statements, such as INSERT, UPDATE or DELETE, the number of rows affected by SQL statements is returned, and 0 is returned when executing DDL statements.

Take the executeQuery() method as an example. Its usage is as follows:

//Step 4: execute the SQL statement to obtain the result set ResultSet
ResultSet resultSet = statement.executeQuery(sql);

(5) Process the ResultSet query result set. (step 5 is only available when the select statement is executed in step 4)

If the executed SQL statement is a query statement, the execution result will return a ResultSet object in which the query results of the SQL statement are saved. The program can extract the query results by operating the ResultSet object.

(6) Close the connection and release resources. (java program and database are the communication between processes, and must be closed after use)

After each database operation, close the database Connection and release resources, including resources such as ResultSet, Statement and Connection.

Because the database resources are very valuable and the number of concurrent access connections allowed by the database is limited, you must remember to release the resources after the database resources are used. In order to ensure the release of resources, in Java programs, the final operations that must be performed should be placed in the finally code block.

example01:

database

create database mydb;
use mydb;

create table users(
	id int primary key auto_increment,
    name varchar(40),
    password varchar(40),
    email varchar(60),
    birthday date
)character set utf8 collate utf8_general_ci;


insert into users(name,password,email,birthday) values ('zs','123456','zs@sina.com','1978-12-12');
insert into users(name,password,email,birthday) values ('lisi','123456','lisi@sina.com','1943-03-12');
insert into users(name,password,email,birthday) values ('wangwu','123456','wangwu@sina.com','1999-09-12');

JDBC program

package com.mcxfate.study.jdbc;


import java.sql.*;

/**
 * @Author: Mcxfate
 * 
 */
public class MyJDBC  {

   public static void main(String args[]) {

      Statement statement = null;
      ResultSet resultSet = null;
      Connection connection = null;

      //Step 1: load and register the driver. There are two ways.
      //First kind
      //Driver driver = new Driver();
      //DriverManager.registerDriver(driver);


      try {
         //The second is more commonly used
         Class.forName("com.mysql.jdbc.Driver");


         //Step 2: get database connection through DriverManager
         String url = "jdbc:mysql://127.0.0.1:3306/mydb?useSSL=FALSE&serverTimezone=UTC";
         String userName = "root";
         String password = "root";
         connection = DriverManager.getConnection(url,userName,password);

         //Step 3: get the Statement object through the Connection object
         statement = connection.createStatement();

         //Step 4: execute the SQL statement to obtain the result set ResultSet
         String sql = "select * from users";
         resultSet = statement.executeQuery(sql);

         //Step 5: process the Result result set
         System.out.println("id |  name  |   password  |  email   | birthday");
         while (resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String pwd = resultSet.getString("password");
            String email = resultSet.getString("email");
            Date birthday = resultSet.getDate("birthday");
            System.out.println(id +"  |  "+name+"    | " + pwd  +" | " + email  + "  | " + birthday);
         }

      } catch (ClassNotFoundException | SQLException e) {

         e.printStackTrace();

      }finally {

         //Step 6: close the connection and release resources
         if(resultSet != null){
            try {
               resultSet.close();
            } catch (SQLException e) {
               e.printStackTrace();
            }
            resultSet = null;
         }

         if (statement != null) {
            try {
               statement.close();
            } catch (SQLException e) {
               e.printStackTrace();
            }
            statement = null;
         }

         if (connection != null) {
            try {
               connection.close();
            } catch (SQLException e) {
               e.printStackTrace();
            }
            connection = null;
         }

      }


   }



}

The execution results are shown in the figure below:

PreparedStatement object

In the above JDBC program, the execution of SQL statements is realized through the Statement object. The Statement object compiles the SQL Statement every time it executes. When the same SQL Statement is executed multiple times, the Statement object will make the database compile the same SQL Statement frequently, thus reducing the access efficiency of the database.

To solve the above problems, Statement provides a subclass PreparedStatement. The PreparedStatement object can precompile SQL statements, and the precompiled information will be stored in the PreparedStatement object. When the same SQL Statement is executed again, the program will use the data in the PreparedStatement object without recompiling the SQL Statement to query the database This greatly improves the data access efficiency.

example02: 

package com.mcxfate.study.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @Author: Mcxfate
 * 
 */
public class Example02 {

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;


        try {
            //Load database driver
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/mydb?useSSL=FALSE&serverTimezone=UTC";
            String userName = "root";
            String password = "root";
            //Get connection
            connection = DriverManager.getConnection(url,userName,password);

            //Get operation object
            String sql = "insert into users (name,password,email,birthday) " + " values (?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);

            //Assign values to parameters in SQL statements
            preparedStatement.setString(1,"xiaoming");
            preparedStatement.setString(2,"123456");
            preparedStatement.setString(3,"xiaoyao@qq.com");
            preparedStatement.setString(4,"2021-05-12");

            //Execute SQL
            int result = preparedStatement.executeUpdate();
            System.out.println(result);

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            //Release resources
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                preparedStatement = null;
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                    connection = null;
            }


        }

    }

}

ResultSet object

The ResultSet is mainly used to store the result set. You can get the data in the result set from front to back one by one through the next() method. If you want to get the data at any position in the result set, you need to set the constants defined by the two resultsets when creating the Statement object. The specific setting methods are as follows:

In the above methods, the constant "result.type_scroll_intrinsic" indicates that the result set can be scrolled, and the constant "ResultSet.CONCUR_READ_ONLY" indicates that the result set is opened in read-only form.

example03:

Data in database tables

package com.mcxfate.study.jdbc;

import java.sql.*;

/**
 * @Author: Mcxfate
 * 
 */
public class Example03 {

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/mydb?useSSL=FALSE&serverTimezone=UTC";
            String userName = "root";
            String password = "root";
            connection = DriverManager.getConnection(url,userName,password);

            String sql = "select * from users";
            //Create a Statement object and set constants
            statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            //Execute SQL statement
            ResultSet resultSet = statement.executeQuery(sql);

            System.out.println("Article 2 of data name Value is:");
            resultSet.absolute(2);     //Position the pointer to the second row of data in the result set
            System.out.println(resultSet.getString("name"));
            System.out.println("Article 1 of data name The value is:");
            resultSet.beforeFirst();        //Position the pointer before the first row of data in the result set
            resultSet.next();               //Scroll the pointer back
            System.out.println(resultSet.getString("name"));
            System.out.println("Article 4 of data name The value is:");
            resultSet.afterLast();          //Position the pointer after the last data in the result set
            resultSet.previous();           //Scroll the pointer forward
            System.out.println(resultSet.getString("name"));


        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            //Release resources

            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                statement = null;
            }


            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                connection = null;
            }

        }
    }

}

Operation results

Example 1: JDBC completes data addition, deletion, modification and query

1. Create JavaBean

package com.mcxfate.study.example;import java.util.Date;/** * @Author: Mcxfate *  *///User class public class user {private int ID; private string username; private string password; private string email; private date birthday; public int getid() {return ID;} public void setid (int ID) {this. Id = ID;} public string getusername() {return username;} public void setusername (String username) {        this.username = username;    }    public String getPassword() {        return password;    }    public void setPassword(String password) {        this.password = password;    }    public String getEmail() {        return email;    }    public void setEmail(String email) {        this.email = email;    }     public Date getBirthday() {        return birthday;    }    public void setBirthday(Date birthday) {        this.birthday = birthday;    }}

2. Create a tool class

package com.mcxfate.study.example;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;/** * @Author: Mcxfate *  *///Tool classes for database related operations public class JDBC utils {public static connection getconnection() throws classnotfoundexception, sqlexception {/ / register the database driver class. Forname ("com. Mysql. JDBC. Driver"); string url = "JDBC: mysql://localhost:3306/mydb?useSSL=FALSE&serverTimezone=UTC ";        String username = "root" ; string password = "root"; / / get the connection object connection connection = drivermanager.getconnection (URL, username, password); return connection;} / / close the database connection and release the resource public static void release (connection, connection, statement) {if (connection! = null) {try {                connection.close();            } catch (SQLException e) {                e.printStackTrace();            }            connection = null;        }        if (statement != null) {            try {                statement.close();            } catch (SQLException e) {                e.printStackTrace();            } Statement = null;}} / / overloaded release method public static void release (connection connection, statement statement, resultset) {if (resultset! = null) {try {resultset. Close();} catch (sqlexception E) {e.printstacktrace();}             resultSet = null;        }        release(connection,statement);    }}

3. Create DAO

package com.mcxfate.study.example;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.text.SimpleDateFormat;import java.util.ArrayList;/** * @Author: Mcxfate *  *///Dao class that encapsulates the operations of adding, querying, deleting, and updating the users table public class UserDao {/ / operations of adding users public Boolean insert (user) {connection connection = null; statement statement = null; / / resultset = null; try {/ / get the connection object connection = jdbcutils. Getconnection(); / / get the operation object statement = connection. Createstatement(); / / convert the time object into a string simpledateformat simpledateformat = new simpledateformat ("yyyy MM DD"); string birthday = simpledateformat.format (user. Getbirthday()); / / send SQL statement string SQL = "insert into users (ID, name, password, email, birthday)" + "values (" + user. Getid() + ", '" + user. Getusername() + ",'" + user. Getpassword() + ", '" + user. Getemail() + ",'" + ", '" + user. Getemail() + ",'" + birthday + "')" ;            System.out.println(sql);            int num = statement.executeUpdate(sql);            if(num > 0 ){                return true;            }            return false;        } catch (Exception e) {            e.printStackTrace();        }finally {            JDBCUtils.release(connection,statement);        }        return false;    } //Query all user objects public ArrayList < user > findall() {connection connection = null; statement statement = null; resultset = null; ArrayList < user > List = new ArrayList < user > (); try {connection = JDBC utils. Getconnection(); statement = connection. Createstatement() ; string SQL = "select * from users"; resultset = statement.executequery (SQL); / / process the result set while (resultset. Next()) {user user = new user(); user.setid (resultset.getint ("Id")); user.setusername (resultset.getstring ("name")) ;                user.setPassword(resultSet.getString("password"));                user.setEmail(resultSet.getString("email"));                user.setBirthday(resultSet.getDate("birthday"));                list.add(user);            }            return list;        } catch (ClassNotFoundException e) {            e.printStackTrace();        } Catch (sqlexception E) {e.printstacktrace();} finally {JDBC utils. Release (connection, statement, resultset);} return null;} / / find the specified user public user find (int ID) by ID {        Connection connection = null;        Statement statement = null;        ResultSet resultSet = null;        try {            connection = JDBCUtils.getConnection();            statement = connection.createStatement();            String sql = "select * from users where id = "+id;            resultSet = statement.executeQuery(sql) ; / / process the result set while (resultset. Next()) {user user = new user(); user.setid (resultset.getint ("Id")); user.setusername (resultset.getstring ("name")); user.setpassword (resultset.getstring ("password")); user.setemail (resultset.getstring ("email")) ;                user.setBirthday(resultSet.getDate("birthday"));                return  user;            }            return null;        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        } finally {            JDBCUtils.release (connection, statement, resultset);} return null;} / / delete user public Boolean delete (int ID) {connection = null; statement statement = null; / / resultset = null; try {connection = jdbcutils. Getconnection(); statement = connection. Createstatement()) ;            String sql = "delete from users where id = "+ id;            int num = statement.executeUpdate(sql);            if(num > 0){                return true;            }            return false;        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace() ;} finally {jdbcutils. Release (connection, statement);} return false;} / / modify user public Boolean update (user user) {connection = null; statement statement = null; / / resultset = null; try {connection = jdbcutils. Getconnection() ;            statement = connection.createStatement();            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");            String birthday = sdf.format(user.getBirthday());            String sql = "update users set name = '" + user.getUsername()+"',password = '"+                    user.getPassword()+"',email = '"+user.getEmail()+ "',birthday = '"+                    birthday+"' where id = "+user.getId();            System.out.println(sql);            int num = statement.executeUpdate(sql);            if(num > 0){                return true;            }            return false;        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch  (SQLException e) {            e.printStackTrace();        } finally {            JDBCUtils.release(connection,statement);        }        return false;    }}

4. Create test class

package com.mcxfate.study.example;import java.util.ArrayList;import java.util.Date;/** * @Author: Mcxfate *  */public class DemoTest {    public static void main(String[] args) {        //Create a data operation Dao object UserDao userDao = new UserDao()// Insert a user's information into the users table user = new user()// user.setId(10);//         user.setUsername("test01");//         user.setPassword("test01");//         user.setEmail(" test01@qq.com ");//         user.setBirthday(new Date());         boolean result = userDao.insert(user);//         If (result) {/ / system. Out. Println ("insert succeeded!"); / /} else {/ / system. Out. Println ("insert failed!"); / /} / / query all user objects ArrayList < user > List = userdao. Findall(); For (user users: list) {system.out.println (users. Getid() + "-" + users. Getusername() + "-" + users. Getpassword() + "-" + users. Getemail() + "-" + users. Getbirthday());} / / query the specified user information through id, user = userdao.find (1); System.out.println(user.getId()+"-"+user.getUsername()+"-"+user.getPassword()+"-"+user.getEmail()+"-"+user.getBirthday());        // Modify user's data user.setid (1); user.setUsername("test02");         user.setPassword("test02");         user.setEmail(" test02@qq.com ");         user.setBirthday(new Date());         result = userDao.update(user);         If (result) {system. Out. Println ("modification succeeded!");} else {system. Out. Println ("modification failed!");} / / delete user result = userdao. Delete (3); If (result) {system.out.println ("deletion succeeded!");} else {system.out.println ("deletion failed!");}}}

Insert test results:

Query all user test results:

Find the test chart of the specified user by id:

Modify user data test chart:

Delete user test chart:

Database connection pool

What is a database connection pool?

In JDBC programming, each time a Connection object is created and disconnected, it consumes a certain amount of time and IO resources. This is because when establishing a Connection between a Java program and a database Connection, the database side needs to verify the user name and password, and allocate resources for the Connection. The program needs to load the java.sql.Connection object representing the Connection into memory. Therefore, it is very expensive to establish a database Connection, especially when there are a large number of concurrent accesses. Frequent creation and disconnection of database connections will inevitably affect the access efficiency of the database and even lead to database crash.

In order to avoid frequent creation of database connections, database connection pool technology came into being. The database connection pool is responsible for allocating, managing, and releasing database connections. It allows applications to reuse existing database connections rather than re-establish them.

As can be seen from the above figure, the database Connection pool will create a certain number of database Connection objects into the Connection pool during initialization. When the application accesses the database, it does not directly create a Connection, but "requests" a Connection object from the Connection pool. If there is an idle Connection object in the Connection pool, it returns; otherwise, a new Connection object is created. After use, the Connection pool will recycle the Connection and deliver it to other threads to use, so as to reduce the times of creating and disconnecting database connections and improve the data access of the database.

DataSource interface

In order to obtain the database Connection object (Connection), JDBC provides the javax.sql.DataSource interface, which is responsible for establishing a Connection with the database, and defines the method whose return value is the Connection object, as follows.

  • Connection getConnection()
  • Connection getConnection(String username, String password)

Both of the above overloaded methods can be used to obtain the Connection object.

The difference is that the first method is to establish a connection with the database by means of no parameters, and the second method is to establish a connection with the database by passing in login information.

Interfaces usually have their implementation classes, and the javax salDatasource interface is no exception. The classes that implement the javax.sal DataSource interface are usually called data sources. As the name suggests, data sources are the sources of data. All database connection information is stored in the data source. Just as the file can be found in the file system by specifying the file name, the corresponding database connection can also be found by providing the correct data source name.

The data source contains the database connection pool. If the data is water, the database is the reservoir, the data source is the pipeline connected to the reservoir, and the data set seen by the end user is the water flowing out of the pipeline. Some open source organizations provide independent implementation of data sources, including DBCP data source and C3P0 data source.

DBCP data source

**DBCP is the abbreviation of DataBase Connection Pool) * *. It is an open source connection pool implementation under Apache organization and a connection pool component used by Tomcat server. When using DBCP data source alone, you need to import two JAR packages in the application, as follows.

  • ① commons-dbcp.jar package

    commons-dbcp.jiar package is the implementation package of DBCP data source. It contains all methods for operating database connection information and database connection pool initialization information, and implements the getConnection() method of DataSource interface.

  • ② commons-pool.jar package
    Commons pooljar package is the dependent package of DBCP database connection pool implementation package, which is in commons-dbcp.jar package
    It can be said that without the dependency package, many methods in the commons-dbcp.jar package will not be available
    Method implementation.

    The two JAR packages can be found on the Apache official website“ http://commons.apache.org/proper/ ”Common-dbcp.JAR contains two core classes: Basic Data SourceFactory and
    BasicDataSource, which all contain methods to obtain DBCP data source objects.

    BasicDataSource is an implementation class of the Datasource interface, which mainly includes methods for setting data source objects
    Common methods are shown in the table.

    Method nameFunction description
    void setDriverClassName(String driverClassName)Set the name of the driver connecting to the database
    void setUrl(String url)Set the path to connect to the database
    void setUsername(String username)Set the login account of the database
    void setPassword(String password)Set the login password for the database
    void setInitialSize(int initialSize)Sets the connection item for database connection pool initialization
    void setMaxActive(int maxldle)Sets the maximum number of active connections in the database connection pool
    void setMinldle(int minldle)Set the minimum number of idle connections in the database connection pool
    Connection getConnection()Get a database connection from the connection pool

In the table, common methods of BasicDetasource object are listed. setDriverClassName, setUrl, setUsername, setPassword and other methods are used to set database connection information. setInitialSize, setMaxActive, setMinldle and other methods are used to set the initialization value of database connection pool, The getConnection method means to obtain a database connection from the DBCP data source.

BasicDataSourceFactory is a factory class that creates a BasicDataSource object. It contains a method createDataSource () whose return value is the BasicDataSource object. This method generates a data source by reading the information in the configuration file
Object and returns it to the caller. This method extracts the connection information of the database and the initialization information of the data source and writes it into the configuration file
Way, so that the code looks more concise and the idea is clearer. When using a DBCP data source, you must first create a data source object. There are two ways to create a data source object, as follows.

1. Create a data source object directly through the BasicDatasource class
When using the BasicDataSource class to create a data source object, you need to manually set the property value of the data source object, and then obtain the database connection object.

example04:
package com.mcxfate.study.jdbc;import org.apache.commons.dbcp2.BasicDataSource;import javax.sql.DataSource;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.SQLException;/** * @Author: Mcxfate *  */public class Example04 {    public static DataSource dataSource = null;    //Static {/ / get the DBCP data source implementation class object BasicDataSource basicDataSource = new BasicDataSource(); / / set the configuration information required to connect to the database basicdatasource.setdriverclassname ("com. Mysql. JDBC. Driver"); basicdatasource.seturl( "jdbc: mysql://localhost:3306/mydb?useSSL=FALSE&serverTimezone=UTC "); basicdatasource.setusername (" root "); basicdatasource.setpassword (" root "); / / set the connection pool parameters basicdatasource.setinitialsize (5); basicdatasource.setmaxtotal (5); datasource = basicdatasource;} public static void main (string []) Args) throws sqlexception {/ / get the database connection object Connection connection = dataSource.getConnection(); / / get the database connection information DatabaseMetaData metaData = connection.getMetaData(); / / print the database connection information system.out.println (metadata. Geturl() + "" + "username =" + metadata. Getusername())+“     "+ metaData.getDriverName());    }}

The operation results are shown in the figure below:

2. Create a data source object by reading the configuration file

In addition to using BasicDataSource to directly create a data source object, you can also use the BasicDataSourceFactory factory class to read the configuration file, create a data source object, and then obtain a database connection object.

example05: 
#configuration file src/dbcpconfig.properties#connections setting up driverClassName=com.mysql.jdbc.Driverurl=jdbc:mysql://Localhost: 3306 / mydb? Usessl = false & servertimezone = utcusername = rootpassword = root # initialize connections initialSize=5 # Max connections maxActive=10 # Max idle = 10

package com.mcxfate.study.jdbc;import org.apache.commons.dbcp2.BasicDataSourceFactory;import javax.sql.DataSource;import java.io.InputStream;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.SQLException;import java.util.Properties;/** * @Author: Mcxfate * */public class Example08 {    public static DataSource ds = null;    //Static {/ / create a configuration file object properties prop = new properties(); try {/ / find the file path through the class loader and read the configuration file InputStream in = new Example08().getClass().getClassLoader().getResourceAsStream("dbcpconfig.properties") ; / / load the file into the configuration object as an input stream. prop.load(in); / / create the data source object DS = basicdatasourcefactory. Createdatasource (prop);} catch (exception E) {e.printstacktrace();}} public static void main (string [] args) throws sqlexception {/ / get the database connection object Connection conn = ds.getConnection(); / / get the database connection information databasemetadata metadata = conn.getmetadata(); / / print the database connection information system.out.println (metadata. Geturl() + "Username:" + metadata. Getusername() + "password =" + metadata. Getdrivername());}}

C3P0 data source

C3P0 is one of the most popular open source database connection pools at present. It implements the DataSource data source interface and supports the standard specifications of JDBC 2 and JDBC 3. It is easy to expand and has superior performance. The famous open source frameworks Hibernate and Spring support this data source. When developing with C3P0 data source, you need to understand the implementation class of the DataSource interface in C3P0, ComboPooledDataSource, which is C3P0 The core class of provides methods related to data source objects. The common methods of this class are described in the following table.

When using C3P0 data source, you first need to create a data source object. To create a data source object, you can use the combo pooleddatasource class, which has two construction methods: combo pooleddatasource () and combo pooleddatasource (String configName).

1. Create a data source object through the ComboPooledDataSource() construction method

example06: 
package com.mcxfate.study.jdbc;import com.mchange.v2.c3p0.ComboPooledDataSource;import javax.sql.DataSource;import java.sql.SQLException;/** * @Author: Mcxfate *  */public class Example09 {    public static DataSource ds = null;    //Initialize C3P0 data source static {combopooleddatasource CPDs = new combopooleddatasource(); / / set the configuration information required to connect to the database try {cpds.setdriverclass ("com. Mysql. JDBC. Driver"); cpds.setjdbcurl ("JDBC: mysql://localhost:3306/mydb?useSSL=FALSE&serverTimezone=UTC ");            cpds.setUser("root") ; cpds.setpassword ("root"); / / set the connection pool parameters cpds.setinitialpoolsize (5); cpds.setmaxpoolsize (15); DS = CPDs;} catch (exception E) {throw new exceptionininitializererror (E);}} public static void main (string [] args) throws sqlexception {/ / get the database connection object System.out.println(ds.getConnection());}}

2. Create a data source object by reading the configuration file

Use the combo pooleddatasource (String configName) construction method to read the c3p0-config.xml configuration file to create a data source object, and then obtain the database connection object.

example07:

c3p0-config.xml file configuration

<?xml version="1.0" encoding="UTF-8" ?><c3p0-config>    <!-- Read the connection pool object using the default configuration -->    <default-config>        <!--  Connection parameters -->        <property name="driverClass">com.mysql.jdbc.Driver</property>        <property name="jdbcUrl">jdbc:mysql://localhost:3306/mydb</property>        <property name="user">root</property>        <property name="password">root</property>        <!--  Connection pool parameters -- > <-- Number of connections applied for initialization -- > < property name = "initialpoolsize" > 5 < / property > <-- Maximum number of connections -- > < property name = "maxpoolsize" > 100 < / property > < property name = "minpoolsize" > 10 < / property > <-- Connection timeout -- > < property name = "checkouttimeout" > 3000 < / property > < property name = "MaxIdleTime" > 30 < / property > < property name = "maxstatements" > 200 < / property > < / default config > <-- Custom configuration file -- > < named config name = "otherc3p0" > <-- Connection parameters -- > < property name = "driverclass" > com. Mysql. JDBC. Driver < / property > < property name = "jdbcurl" > JDBC: mysql://localhost:3306/mydb </property>        <property name="user">root</property>        <property name="password">root</property>        <!--  Connection pool parameters -- > < property name = "initialpoolsize" > 5 < / property > < property name = "maxpoolsize" > 8 < / property > < property name = "checkouttimeout" > 1000 < / property > < / named config > < / c3p0 config >

package com.mcxfate.study.jdbc;import com.mchange.v2.c3p0.ComboPooledDataSource;import javax.sql.DataSource;import java.sql.SQLException;/** * @Author: Mcxfate *   */public class Example10 {    public static DataSource ds = null;    static {        //Use the value of combopooleddatasource CPDs = new combopooleddatasource ("otherc3p0") of the name config node in the src/c3p0-config.xml configuration file; ds = cpds;    }     Public static void main (string [] args) throws sqlexception {/ / print connection object information System.out.println(ds.getConnection());}}

DBUtils tool

In order to use JDBC more simply, Apache provides a DBUtils tool, which is a component of operating the database, realizes the simple encapsulation of JDBC, and can greatly simplify the coding workload of JDBC without affecting the performance.

The DBUtils tool can be used in“ http://commons.apache.org/proper/commons-dbutils/index.html "Download to.

The core of the DBUtils tool is the org.apache.commons.dbutils.QueryRunner class and the org.apache.commons.dbutils.ResultSetHandler interface.

QueryRunner class

QueryRunner class simplifies the code for executing SQL statements. Combined with ResultSetHandler, it can complete most database operations, greatly reducing the amount of coding.

The QueryRunner class provides a constructor with one parameter, which passes javax.sql.DataSource as a parameter to the constructor of QueryRunner to obtain the Connection object. QueryRunner class provides several common methods for different database operations, as follows.

  • query (String sql, ResultSetHandler rsh, Object... params) method. This method is used to perform query operations. It can obtain connections from the DataSource provided to the construction method or the setDataSource () method used.
  • Update (string, SQL, object... params) method, which is used to perform insert, update or delete operations. The parameter params represents the replacement parameter in the SQL statement.
  • update(String sql) method, which is used to perform insert, update or delete operations. It does not need to replace parameters.

ResultSetHandler interface

The ResultSetHandler interface is used to process the ResultSet result set. It can convert the data in the result set into different forms. According to different data types in the result set, ResultSetHandler provides several common implementation classes, as follows:

  • BeanHandler: encapsulates the first row of data in the result set into a corresponding JavaBean instance.
  • BeanListHandler: encapsulate each row of data in the result set into a corresponding JavaBean instance and store it in the List.
  • ScalarHandler: stores the data of a column of a record in the result set as an Object object.

In the ResultSetHandler interface, a separate method handle (Java. Sal. Resultist RS) is provided. If the above implementation class does not provide the desired functions, you can customize a class that implements the ResultSetHandler interface, and then override the handle() method to realize the processing of the result set.

ResultSetHandler implementation class

1.BeanHandler and BeanListHandler

BeanHandler and BeanListHandler implementation classes encapsulate the data in the result set into the corresponding JavaBean instance, which is also the most commonly used result set processing method in actual development.

example08:

(1) Create a data table user in the database

use mydb;create table user(	id int primary key auto_increment,    name varchar(20) not null,    password varchar(20) not null);insert into user(name,password) values ('zhangsan','123456');insert into user(name,password) values ('lisi','123456');insert into user(name,password) values ('wangwu','123456');

(2) Add the downloaded JAR package of the DBUtils tool to the lib directory of the project.

commons-dbutils-1.6.jar

(3) Writing test classes

package com.mcxfate.study.jdbc;import java.sql.*;import com.mcxfate.study.example.JDBCUtils;import org.apache.commons.dbutils.ResultSetHandler;public class BaseDao {    // Optimize query public static object query (string SQL, resultsethandler <? > RSH, object... Params) throws sqlexception {connection conn = null; Preparedstatement pstmt = null; resultset rs = null; try {/ / use the JDBCUtils tool class to obtain the connection conn = JDBCUtils.getConnection(); / / precompile SQL pstmt = conn.preparestatement (SQL); / / set the parameters in for (int i = 0; params! = null & & I < params. Length; I + +) {pstmt.setobject (I + 1, params [i]) ;} / / send SQL rs = pstmt. Executequery(); / / let the caller process the result set object obj = RSH. Handle (RS); return obj;} catch (exception E) {e.printstacktrace();} finally {/ / release the resource JDBCUtils. Release (Conn, pstmt, RS);} return RS;}}

(4) Create entity class User

package com.mcxfate.study.entity;/** * @Author: Mcxfate *  */public class User {    private  int id;    private String name;    private String password;    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public String getPassword() {        return password;    }    public void setPassword(String password) {        this.password = password;    }}

(5) Create test class

Test BeanHandler class

package com.mcxfate.study.utils;import com.mcxfate.study.entity.User;import com.mcxfate.study.jdbc.BaseDao;import org.apache.commons.dbutils.handlers.BeanHandler;import java.sql.SQLException;/** * @Author: Mcxfate *  */public class ResultSetTest1 {    public static void testBeanHandler() throws SQLException {        BaseDao baseDao = new BaseDao();        String sql = "select * from user where id = ?";        User user = (User) baseDao.query(sql,new BeanHandler(User.class),2);        System.out.println("id For 2 User Object name The value is:"+user.getName());		System.out.println(user);    }    public static void main(String[] args) throws SQLException {        testBeanHandler();    }}

Test BeanListHandler class

package com.mcxfate.study.utils;import com.mcxfate.study.entity.User;import com.mcxfate.study.jdbc.BaseDao;import org.apache.commons.dbutils.handlers.BeanListHandler;import java.sql.SQLException;import java.util.ArrayList;/** * @Author: Mcxfate *  */public class ResultSetTest2 {    public static void testBeanListHandler() throws SQLException {        BaseDao baseDao = new BaseDao();        String sql = "select * from user ";        ArrayList<User> list = (ArrayList<User>) baseDao.query(sql,new BeanListHandler(User.class));        for (int i = 0; i < list.size(); i++) {            System.out.println("The first"+(i+1)+"Of data username The value is:"+list.get(i).getName());        }    }    public static void main(String[] args) throws SQLException {        testBeanListHandler();    }}

2.ScalarHandler

When using DBUtils to operate the database, if you need to output the specified field value of a row of data in the result set, you can use the ScalarHandler class.

example09: 
package com.mcxfate.study.utils;import com.mcxfate.study.jdbc.BaseDao;import org.apache.commons.dbutils.handlers.ScalarHandler;import java.sql.SQLException;/** * @Author: Mcxfate *  */public class ResultSetTest03 {    public static void testScalarHandler() throws SQLException {        BaseDao baseDao = new BaseDao();        String sql = "select * from user where id = ?";        Object arr = (Object) baseDao.query(sql,new ScalarHandler<>("name"),2);        System.out.println(arr);    }    public static void main(String[] args) throws SQLException {        testScalarHandler();    }}

Example 2: add, delete, change and query using DBUtils

1. Create C3p0Utils class

package com.mcxfate.study.utils;import com.mchange.v2.c3p0.ComboPooledDataSource;import javax.sql.DataSource;/** * @Author: Mcxfate *  */public class C3p0Utils {    private static DataSource ds;    static {        ds = new ComboPooledDataSource();    }    public static DataSource getDataSource(){        return ds;    }}

2. Create DBUtilsDao class

package com.mcxfate.study.utils;import com.mcxfate.study.entity.User;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.BeanHandler;import org.apache.commons.dbutils.handlers.BeanListHandler;import java.sql.SQLException;import java.util.List;/** * @Author: Mcxfate *  */public class DBUtilsDao {    //Query all and return the list collection public List findAll() throws SQLException {/ / create the QueryRunner object QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource()); / / write the SQL statement String sql = "select * from user"; / / call the method list = runner.query (SQL, new beanlisthandler < > (user. Class)) ; return list;} / / for a single query, return the object public User find(int id) throws SQLException {/ / create a QueryRunner object QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource()); / / write an SQL statement String sql = "select * from user where id =?"; / / call the method user = (user) Runner.query (SQL, new beanhandler < > (user. Class), new object [] {ID}); return user;} / / add user public Boolean insert(User user) throws SQLException {/ / create QueryRunner object QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource()); / / SQL statement string SQL = "insert into user" (name, password) values (?,?) "; / / call the method int num = runner.update (SQL, new object [] {user. Getname(), user. Getpassword()}); if (Num > 0) {return true;} return false;} / / modify the user's operation public boolean update(User user) throws SQLException {/ / create the QueryRunner object QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource()); / / write the SQL statement string SQL = "update user set name =?, password =? Where id =?"; / / call the method int num = runner.update (SQL, new object [] {user. Getname(), user. Getpassword(), user. Getid()}); if (Num > 0) Return true; return false;} / / delete the user's operation public boolean delete(int id) throws SQLException {/ / create a QueryRunner object QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource()); / / write an SQL statement String sql = "delete from user where id =?" ; / / call the method int num = runner. Update (SQL, ID); if (Num > 0) return true; return false;}}

In the above code, the BeanHandler class that can process one row of data is used to query one piece of data, and the BeanListHandler class that can process all rows of data is used to query all data.

3. Test the addition, deletion, modification and query operations in DBUtilsDao class

package com.mcxfate.study.utils;import com.mcxfate.study.entity.User;import java.sql.SQLException;/** * @Author: Mcxfate *  */public class DBUtilsDaoTest01 {    private static DBUtilsDao dao = new DBUtilsDao();    public static void testInsert() throws SQLException {        User user = new User();        user.setName("test111");        user.setPassword("666666");        boolean result = dao.insert(user);    }    public static void testUpdate() throws SQLException {        User user = new User();        user.setName("test0002");        user.setPassword("200000");        user.setId(1);        boolean result = dao.update(user);        if(result){            System.out.println("Modified successfully!!");        }else{            System.out.println("Modification failed!!");        }    }    public static void testDelete() throws SQLException {        boolean result = dao.delete(1);        if(result){            System.out.println("Delete succeeded!!");        }else{            System.out.println("Deletion failed!!");        }    }    public static void testFind() throws SQLException {        User user = dao.find(2);        System.out.println(user.getId()+"---"+user.getName()+"----"+user.getPassword());    }    public static void main(String args[]) throws SQLException {        //testInsert();        //testUpdate();        //testDelete();        testFind();    }}

Posted by Kathy on Thu, 28 Oct 2021 12:38:58 -0700

Hot Keywords

©2024 Programmer Group