JDBC connection database and improvement

catalogue

1, Native jdbc connection database

Use the original method to connect to the database. The steps are as follows:

  1. Register driver
  2. Get connection object
  3. Get the platform object that executes the statement
  4. Execute sql statement
  5. Processing results
  6. Release connection
//1. register driver
Class.forName("com.mysql.cj.jdbc.Driver");	//The database version is 8.0.15
//2. obtain the connection object, url, database user name, and database password
Connection conn =
    DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=UTC",
                                "root", "root");
//3. obtain the platform object for executing the statement
PreparedStatement ps = conn.prepareStatement("insert into stu values (5,'wolf')");
//4. execute sql statement
int i = ps.executeUpdate();
//5. processing results
if (i > 0) {
    System.out.println("Successfully added!");
} else {
    System.out.println("Add failed!");
}
//Release connection
if (ps != null) {
    ps.close();
}
if (conn != null) {
    conn.close();
}

2, Encapsulate native jdbc into DBUtil

Packaged as DBUtil

public class DBUtil {
    //Declare url,user,password
    private static String url = "jdbc:mysql:///test?serverTimezone=UTC";
    private static String user = "root";
    private static String password = "root";

    //1. register driver
    static{
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //2. get database connection
    public static Connection getConnection(){
        Connection connection=null;
        try {
            connection = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //3. release resources. Pass in the parameter rs: result set object. If not, pass in null. PS: platform object for executing sql statements. conn: connection object
    public static void close(ResultSet rs, PreparedStatement  ps,Connection conn){
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Call DBUtil externally.

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = connection.prepareStatement("select * from stu");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            int sid = rs.getInt("sid");
            String sname = rs.getString("sname");
            System.out.println("Student ID:" + sid + "  Name:" + sname);
        }
        DBUtil.close(rs, ps, connection);
    }
}

3, Using DBUtils provided by Apache

Further improve native jdbc

DbUtils(org.apache.commons.dbutils.DbUtils) is Apache A pair of JDBC Open source tool class library for simple encapsulation, which can simplify JDBC Application development will not affect the performance of the program. The DbUtils class is mainly responsible for the general work of loading the drive and closing the connection.

Steps:

  1. Register the driver.
  2. Get the Connection object.
  3. Get the QueryRunner object that executes the sql statement.
  4. Execute the sql statement.
  5. Processing results.
  6. Free resources.

The ResultSetHandler interface (org.apache.commons.dbutils.ResultSethandler) handles a result set object, transforms and processes data into any form for use by other applications. The implementation classes are as follows:

There are only three common result set objects to remember:

  • BeanHandler: encapsulates the first record in the query result set into the specified JavaBean. This result set object is used when the query condition in the sql statement can only obtain a unique record!
  • BeanListHandler encapsulates each record in the query result set into JavaBeans, and puts these JavaBean objects into the List collection
  • ScalarHandler is specially used to encapsulate the result obtained by the aggregate function, that is, the query result is a single data.
	@Test
    public void test1() throws ClassNotFoundException, SQLException {
        //1. register driver
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2. obtain the connection object
        Connection conn =
                DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=UTC",
                        "root", "root");
        //3. get the QueryRunner object that executes the sql statement
        QueryRunner runner=new QueryRunner();
        String sql = "select * from stu";
        //4. execute sql statement
        List<Student> studentList = runner.query(conn,sql, new BeanListHandler<Student>(Student.class));
        //5. processing results
        for (Student student : studentList) {
            System.out.println(student.toString());
        }
        //6. release resources
        DbUtils.close(conn);
    }

4, Use DBUtils + Druid (recommended)

There are three common database connection pool technologies in java programs: c3p0, dbcp, Druid (ALI)

Personal suggestion: from the perspective of code performance, druid or dbcp is recommended

From the point of view of code conciseness, c3p0 is recommended, but it has a drawback of low efficiency

1. Import package for connection pool Druid-1.0.9 Jar

2. Place the connection information in druid In the properties file

druid.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC
username=root
password=root

DruidUtils.java

public class DruidUtils {
    //Declare data source object
    private static DataSource dataSource=null;
    //Initializing a data source in a static code block
    static {
        try {
            //Specify a property file object
            Properties properties=new Properties();
            //Get input file stream through reflection
            InputStream is = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            //Read Druid from input stream Connection information in the properties configuration file
            //Load the read information into the properties file object
            properties.load(is);
            //Get connection pool object from property file object
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static DataSource getDataSource() {
        return dataSource;
    }
}

Test:

	@Test
    public void test2() throws SQLException {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from stu";
        List<Student> studentList = runner.query(sql, new BeanListHandler<Student>(Student.class));
        for (Student student : studentList) {
            System.out.println(student.toString());
        }
    }

Tags: Java

Posted by atkman on Mon, 30 May 2022 06:03:56 +0530