Database connection pool & DBUtils

Database connection pool & dbutils

1. Database connection pool

1.1 introduction to connection pool

1) What is a connection pool

  • In the actual development, "getting a Connection" or "releasing resources" are two processes that consume system resources very much. In order to solve such performance problems, we usually use the Connection pool technology to share the Connection. In this way, we don't need to create and release connections every time. These operations are handed over to the Connection pool

2) Benefits of connection pooling

  • Use a pool to manage connections so that connections can be reused. When Connection is used, the close() method that calls Connection will not really turn off Connection, but return the Connection to the pool.

1.2 JDBC mode and connection pool mode

  • Normal JDBC mode
  • Connection pool mode

1.3 how to use database connection pool

Java provides a common interface for database connection pool: javax.sql.DataSource. Each manufacturer needs to make its own connection pool implement this interface.
In this way, the application can easily switch the connection pools of different manufacturers!

Common connection pools include DBCP connection pool, C3P0 connection pool and Druid connection pool, which we will learn in detail next

1.4 data preparation

#Create database
CREATE DATABASE db5 CHARACTER SET utf8;
#Use database
USE db5;
#Create employee table
CREATE TABLE employee (
eid INT PRIMARY KEY AUTO_INCREMENT ,
ename VARCHAR (20), -- Employee name
age INT , -- Employee age
sex VARCHAR (6), -- Employee gender
salary DOUBLE , -- salary
empdate DATE -- Entry date
);
#insert data
INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'Li Qing
 mirror',22,'female',4000,'2018-11-12');
INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'Lin Dai
 Jade',20,'female',5000,'2019-03-14');
INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'Du
 Just',40,'male',6000,'2020-01-01');
INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'Lee
white',25,'male',3000,'2017-10-01');

1.5 DBCP connection pool

DBCP is also an open source connection pool. It is a member of Apache and is also common in enterprise development. tomcat has a built-in connection pool.

1.5.1 create project import jar package

1) Add the two jar packages to the myJar folder (the jar package is in the software folder in the resources)

2) Add the myJar library to the dependencies of the project

1.5.2 writing tools

  • The tool class for connecting database tables is completed by DBCP connection pool
    • Java provides a rule interface for connection pool: DataSource, which is the connection pool provided in Java
    • The DBCP package provides the implementation class of the DataSource interface. We need to use the specific connection pool BasicDataSource class

Code example

public class DBCPUtils {
  //1.Define constants to hold information about database connections
  public static final String DRIVERNAME = "com.mysql.jdbc.Driver";
  public static final String URL = "jdbc:mysql://localhost:3306/db5?characterEncoding=UTF-8";
  public static final String USERNAME = "root";
  public static final String PASSWORD = "123456";
  //2.Create connection pool object (have DBCP Implementation classes provided)
  public static BasicDataSource dataSource = new BasicDataSource();
  //3.Configuration using static code blocks
  static{
    dataSource.setDriverClassName(DRIVERNAME);
    dataSource.setUrl(URL);
    dataSource.setUsername(USERNAME);
    dataSource.setPassword(PASSWORD);
  }
  //4.Method of obtaining connection
  public static Connection getConnection() throws SQLException {
    //Get connection from connection pool
    Connection connection = dataSource.getConnection();
    return connection;
  }
  //5.Resource release method
  public static void close(Connection con, Statement statement){
    if(con != null && statement != null){
      try {
        statement.close();
        //Return connection
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
  public static void close(Connection con, Statement statement, ResultSet resultSet){
    if(con != null && statement != null && resultSet != null){
      try {
        resultSet.close();
        statement.close();
        //Return connection
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

1.5.3 test tools

Requirement: query the names of all employees

public class TestDBCP {
  /*
  * Test DBCP connection pool
  * */
  public static void main(String[] args) throws SQLException {
    //1.from DBCP Get connections from the connection pool
    Connection con = DBCPUtils.getConnection();
    //2.obtain Statement object
    Statement statement = con.createStatement();
    //3.Query the names of all employees
    String sql = "select ename from employee";
    ResultSet resultSet = statement.executeQuery(sql);
    //4.Processing result set
    while(resultSet.next()){
      String ename = resultSet.getString("ename");
      System.out.println("Employee name: " + ename);
    }
    //5.Release resources
    DBCPUtils.close(con,statement,resultSet);
  }
}

1.5.4 common configuration items

1.6 C3P0 connection pool

C3P0 is an open source JDBC connection pool, which supports the JDBC 3 specification and the standard extension of JDBC 2. At present, the open source projects using it include Hibernate
Spring et al.

1.6.1 import jar package and configuration file

1) Copy the jar package to the myJar folder, and the IDEA will be imported automatically

2) Import the configuration file c3p0-config.xml

  • The c3p0-config.xml file name cannot be changed
  • Directly under src or in the resource folder
<c3p0-config>
  <!--Default configuration-->
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/db5?characterEncoding=UTF-8</property>
    <property name="user">root</property>
    <property name="password">123456</property>
    <!-- initialPoolSize: Get three connections during initialization,
    The value should be minPoolSize And maxPoolSize between. -->
    <property name="initialPoolSize">3</property>
    <!-- maxIdleTime: Maximum idle time,60 If not used within seconds, the connection is discarded. If it is 0, it will never be discarded.-->
    <property name="maxIdleTime">60</property>
    <!-- maxPoolSize: Maximum number of connections reserved in the connection pool -->
    <property name="maxPoolSize">100</property>
    <!-- minPoolSize: Minimum number of connections reserved in the connection pool -->
    <property name="minPoolSize">10</property>
  </default-config>
  <!--Configure connection pool mysql-->
  <named-config name="mysql">
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/db5</property>
    <property name="user">root</property>
    <property name="password">123456</property>
    <property name="initialPoolSize">10</property>
    <property name="maxIdleTime">30</property>
    <property name="maxPoolSize">100</property>
    <property name="minPoolSize">10</property>
  </named-config>
  <!--Configure connection pool 2,Multiple can be configured-->
</c3p0-config>

 

3) Create a resource folder under the project (dedicated to storing resource files)

4) Select a folder and right-click to specify the resource folder as the resource folder

5) Just put the file in the resource directory. The configuration file will be loaded when creating the connection pool object

1.6.2 compiling C3P0 tools

  • C3P0 provides a core tool class, ComboPooledDataSource. If you want to use connection pool, you must create an object of this class
    • new ComboPooledDataSource(); Use default configuration
    • new ComboPooledDataSource("mysql"); Use named configuration
public class C3P0Utils {
  //1.Create connection pool object C3P0 yes DataSource Implementation class of interface
  //The configuration used is the default configuration in the configuration file
  //public static ComboPooledDataSource dataSource = new ComboPooledDataSource();
  //Use the specified configuration
  public static ComboPooledDataSource dataSource = new ComboPooledDataSource("mysql");
  //Method of obtaining connection
  public static Connection getConnection() throws SQLException {
    return dataSource.getConnection();
  }
  //Release resources
  public static void close(Connection con, Statement statement){
    if(con != null && statement != null){
      try {
        statement.close();
        //Return connection
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
  public static void close(Connection con, Statement statement, ResultSet resultSet){
    if(con != null && statement != null && resultSet != null){
      try {
        resultSet.close();
        statement.close();
        //Return connection
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

1.6.3 test tools

Demand: query the employee information whose name is Li Bai

public class TestC3P0 {
  //Request to query the record whose name is Li Bai
  public static void main(String[] args) throws SQLException {
    //1.Get connection
    Connection con = C3P0Utils.getConnection();
    //2.Get preprocessing object
    String sql = "select * from employee where ename = ?";
    PreparedStatement ps = con.prepareStatement(sql);
    //3.Set the value of the placeholder
    ps.setString(1,"Li Bai");
    ResultSet resultSet = ps.executeQuery();
    //4.Processing result set
    while(resultSet.next()){
      int eid = resultSet.getInt("eid");
      String ename = resultSet.getString("ename");
      int age = resultSet.getInt("age");
      String sex = resultSet.getString("sex");
      double salary = resultSet.getDouble("salary");
      Date date = resultSet.getDate("empdate");
      System.out.println(eid +" " + ename + " " + age +" " + sex +" " + salary +" "
      +date);
    }
    //5.Release resources
    C3P0Utils.close(con,ps,resultSet);
  }
}

1.6.4 common configurations

1.7 Druid connection pool

Druid is a database connection pool called monitoring developed by Alibaba. Druid is the best database connection pool at present. In work
In terms of performance, performance and scalability, it surpasses other database connection pools. At the same time, log monitoring is added, which can well monitor DB pool connections and SQL execution
situation.

1.7.1 import jar package and configuration file

1) Import jar package

2) Import profile

  • It is in the form of properties
  • You can call any name and put it in any directory. We put it in the resources directory

 

 

 

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/db5?characterEncoding=UTF-8
username=root
password=123456
initialSize=5
maxActive=10
maxWait=3000

1.7.2 writing Druid tool class

  • Get database connection pool object
    • Get the createDataSource method of the DruidDataSourceFactory class through the factory
    • The createDataSource(Properties p) method parameter can be a property set object
public class DruidUtils {
  //1.Define member variables
  public static DataSource dataSource;
  //2.Static code block
  static{
    try {
      //3.Create property set object
      Properties p = new Properties();
      //4.Load profile Druid The connection pool cannot actively load the configuration file ,File needs to be specified
      InputStream inputStream =
      DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
      //5. use Properties Object load Method reads configuration information from the byte stream
      p.load(inputStream);
      //6. Get the connection pool object through the factory class
      dataSource = DruidDataSourceFactory.createDataSource(p);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  //Method of obtaining connection
  public static Connection getConnection(){
    try {
      return dataSource.getConnection();
    } catch (SQLException e) {
      e.printStackTrace();
      return null;
    }
  }
  //Release resources
  public static void close(Connection con, Statement statement){
    if(con != null && statement != null){
      try {
        statement.close();
        //Return connection
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
  public static void close(Connection con, Statement statement, ResultSet resultSet){
    if(con != null && statement != null && resultSet != null){
      try {
        resultSet.close();
        statement.close();
        //Return connection
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

1.7.3 test tools

Demand: query the name of an employee whose salary is between 3000 and 5000 yuan

public class TestDruid {
  // You need to query the names of employees whose salary is between 3000 and 5000
  public static void main(String[] args) throws SQLException {
    //1.Get connection
    Connection con = DruidUtils.getConnection();
    //2.obtain Statement object
    Statement statement = con.createStatement();
    //3.Execute query
    ResultSet resultSet = statement.executeQuery("select ename from employee where salary
    between 3000 and 5000");
    //4.Processing result set
    while(resultSet.next()){
      String ename = resultSet.getString("ename");
      System.out.println(ename);
    }
    //5.Release resources
    DruidUtils.close(con,statement,resultSet);
  }
}

2.DBUtils tool class

2.1 introduction to dbutils

Using JDBC, we found too much redundant code. In order to simplify development, we chose to use DbUtils

Commons DbUtils is an open source tool class library provided by Apache organization to simply encapsulate JDBC. Using it can simplify JDBC application
The development of the program will not affect the performance of the program.

  • Usage:
    • DBUtils is a simplified development kit for JDBC. The project needs to import commons-dbutils-1.6.jar.

2.1.1 introduction to the core functions of dbutils

  • 1. Query runner provides API for sql statement operation
  • 2. ResultSetHandler interface, used to define how to encapsulate the result set after the select operation
  • 3. DbUtils class, which is a tool class, defines methods related to closing resources and transaction processing

2.2 case related knowledge

2.2.1 relationship between tables and classes

  • The whole table can be regarded as a class
  • A row of records in the table corresponds to an instance (object) of a class
  • A column in a table that corresponds to a member attribute in a class

2.2.2 JavaBean components

1) JavaBean is a class, which is usually used to encapsulate data in development. It has the following characteristics

  • 1. The Serializable interface needs to be implemented (it can be omitted temporarily)
  • 2. Provide private field: private type variable name;
  • 3. Provide getter and setter
  • 4. Provide null parameter structure

2) Create an employee class corresponding to the employee table of the database

  • We can create an entity package to store JavaBean classes

 

 

public class Employee implements Serializable {
  private int eid;
  private String ename;
  private int age;
  private String sex;
  private double salary;
  private Date empdate;
  //Empty parameter getter setter ellipsis
}

2.3 completion of CRUD by dbutils

2.3.1 QueryRunner core class

  • Construction method
    • QueryRunner()
    • QueryRunner(DataSource ds) provides data sources (connection pools), and the underlying DBUtils automatically maintains connections
  • common method
    • update(Connection conn, String sql, Object... params) is used to add, delete and update table data
    • Query (connection Conn, string SQL, resultsethandler < T > RSH, object... Params) is used to query table data

2.3.2 creation of queryrunner

  • Manual mode
//Create manually QueryRunner object
QueryRunner qr = new QueryRunner();
  • Automatic mode
//Automatically create incoming database connection pool objects
QueryRunner qr2 = new QueryRunner(DruidUtils.getDataSource());
  • Automatic mode requires an incoming connection pool object
//Get connection pool object
public static DataSource getDataSource(){
  return dataSource;
}

2.3.3 QueryRunner implements add, delete and modify operations

Core method

update(Connection conn, String sql, Object... params)

step

  • 1. Create queryrunner (manual or automatic)
  • 2. Write SQL in placeholder mode
  • 3. Set placeholder parameters
  • 4. Implementation
2.3.3.1 add
@Test
public void testInsert() throws SQLException {
  //1.establish QueryRunner Manual mode creation
  QueryRunner qr = new QueryRunner();
  //2.How to write placeholders SQL
  String sql = "insert into employee values(?,?,?,?,?,?)";
  //3.Set parameters for placeholders
  Object[] param = {null,"Zhang million",20,"female",10000,"1990-12-26"};
  //4.implement update method
  Connection con = DruidUtils.getConnection();
  int i = qr.update(con, sql, param);
  //5.Release resources
  DbUtils.closeQuietly(con);
}
2.3.3.2 modification
//Modify: modify the salary of an employee whose name is Zhang million
@Test
public void testUpdate() throws SQLException {
  //1.establish QueryRunner Object automatic mode,Incoming database connection pool
  QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
  //2.to write SQL
  String sql = "update employee set salary = ? where ename = ?";
  //3.Set placeholder parameters
  Object[] param = {0,"Zhang million"};
  //4.implement update, No incoming connection object is required
  qr.update(sql,param);
}
2.3.3.3 deletion
//Delete operation delete id Data of 1
@Test
public void testDelete() throws SQLException {
  QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
  String sql = "delete from employee where eid = ?";
  //Only one parameter,You do not need to create an array
  qr.update(sql,1);
}

2.3.4 QueryRunner implements query operations

2.3.4.1 introduction to resultsethandler interface
  • ResultSetHandler can process the queried ResultSet result set to meet some business requirements.
2.3.4.2 ResultSetHandler result set processing class

This example shows how to use several common implementation classes of the ResultSetHandler interface to add, delete, modify and query the database, which can greatly reduce the amount of code and optimize
Procedure.
Each implementation class represents a way to process the query result set

2.3.4.3 common implementation class test of resultsethandler
  • Query method of QueryRunner
  • The return values of query methods are generic. The specific return value types will change according to the processing method of the result set

  • Create a test class to test several common implementation classes of ResultSetHandler interface
    • Query the record with id 5 and encapsulate it into an array
    • Query all data and encapsulate it into the List collection
    • Query the record with id 5 and encapsulate it into the specified JavaBean
    • Query the information of employees whose salary is greater than 3000, package it into JavaBean, and then package it into List collection
    • The query name is the employee information of millions, and the results are encapsulated in the Map collection
    • Query payroll of all employees

1) Query the record with id 5 and encapsulate it into an array

/*
* Query the record with id 5 and encapsulate it into an array
* ArrayHandler Encapsulate the first data of the result set into an array
* */
@Test
public void testFindById() throws SQLException {
  //1.establish QueryRunner
  QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
  //2.to write SQL
  String sql = "select * from employee where eid = ?";
  //3.Execute query
  Object[] query = qr.query(sql, new ArrayHandler(), 5);
  //4.get data
  System.out.println(Arrays.toString(query));
}

2) Query all data and encapsulate it into the List collection

/**
* Query all data and encapsulate it into the List collection
* ArrayListHandler You can encapsulate each piece of data into an array and then an array into a collection
*
*/
@Test
public void testFindAll() throws SQLException {
  //1.establish QueryRunner
  QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
  //2.to write SQL
  String sql = "select * from employee";
  //3.Execute query
  List<Object[]> query = qr.query(sql, new ArrayListHandler());
  //4.Traverse the collection to get data
  for (Object[] objects : query) {
    System.out.println(Arrays.toString(objects));
  }
}

3) According to the ID query, it is encapsulated into the specified JavaBean

/**
* Query the record with id 3 and encapsulate it into the specified JavaBean
* BeanHandler Encapsulate the first data of the result set into a javaBean
*
4) Query the information of employees whose salary is greater than 3000, package it into JavaBean, and then package it into List collection
5) The query name is the employee information of millions, and the results are encapsulated in the Map collection
**/
@Test
public void testFindByIdJavaBean() throws SQLException {
  QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
  String sql = "select * from employee where eid = ?";
  Employee employee = qr.query(sql, new BeanHandler<Employee>(Employee.class), 3);
  System.out.println(employee);
}

4) Query the information of employees whose salary is greater than 3000, package it into JavaBean, and then package it into List collection

/*
* Query the information of employees whose salary is greater than 3000, package it into JavaBean, and then package it into List collection
* BeanListHandler Encapsulate each item and data of the result set into a JavaBean, and then put the JavaBean into the list collection
* */
@Test
public void testFindBySalary() throws SQLException {
  QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
  String sql = "select * from employee where salary > ?";
  List<Employee> list = qr.query(sql, new BeanListHandler<Employee>(Employee.class),
  3000);
  for (Employee employee : list) {
    System.out.println(employee);
  }
}

5) The query name is the employee information of millions, and the results are encapsulated in the Map collection

/*
* The query name is the employee information of millions, and the results are encapsulated in the Map collection
* MapHandler Encapsulate the first record of the result set into map < string, Object >
* key Corresponding to the column name, value corresponds to the value of the column
* */
@Test
public void testFindByName() throws SQLException {
  QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
  String sql = "select * from employee where ename = ?";
  Map<String, Object> map = qr.query(sql, new MapHandler(), "Zhang million");
  Set<Map.Entry<String, Object>> entries = map.entrySet();
  for (Map.Entry<String, Object> entry : entries) {
    //Print results
    System.out.println(entry.getKey() +" = " +entry.getValue());
  }
}

6) Query payroll of all employees

/*
* Query payroll of all employees
* ScalarHandler Used to encapsulate a single piece of data
* */
@Test
public void testGetSum() throws SQLException {
  QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
  String sql = "select sum(salary) from employee";
  Double sum = (Double)qr.query(sql, new ScalarHandler<>());
  System.out.println("Total employee salary: " + sum);
}

3. Database batch processing

3.1 what is batch processing

  • Batch operation database
    • Batch processing refers to the execution of multiple SQL statements in one operation. The efficiency of batch processing will be much higher than that of one execution at a time.
    • Batch processing is required when adding a large amount of data to the database.
  • Example: the work of a delivery man:
    • When batch processing is not used, the deliveryman can only deliver one piece of goods to the merchant at a time;
    • In batch processing, the deliveryman takes all the goods to be transported to the distribution office and sends them to the customer.

3.2 batch processing

Both Statement and PreparedStatement support batch processing. Here we introduce the batch processing method of PreparedStatement:

1) Methods to be used

2) mysql batch processing is closed by default, so you need to add a parameter to open mysql database batch processing and add it in the url

rewriteBatchedStatements=true
 for example: url=jdbc:mysql://127.0.0.1:3306/db5?characterEncoding=UTF-8&rewriteBatchedStatements=true

3) Create a table

CREATE TABLE testBatch (
  id INT PRIMARY KEY AUTO_INCREMENT,
  uname VARCHAR(50)
)

4) Insert 10000 pieces of data into the test table

public class TestBatch {
  //Using batch processing,Add 10000 pieces of data to the table
  public static void main(String[] args) {
    try {
      //1.Get connection
      Connection con = DruidUtils.getConnection();
      //2.Get preprocessing object
      String sql ="insert into testBatch(uname) values(?)";
      PreparedStatement ps = con.prepareStatement(sql);
      //3.establish for Loop to set placeholder parameters
      for (int i = 0; i < 10000 ; i++) {
        ps.setString(1,"cockroach"+i);
        //take SQL Add to batch list
        ps.addBatch();
      }
      //Add timestamp to test execution efficiency
      long start = System.currentTimeMillis();
      //Unified batch execution
      ps.executeBatch();
      long end = System.currentTimeMillis();
      System.out.println("Insert 10000 pieces of data for use: " +(end - start) +" millisecond!");
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

4.MySql metadata

4.1 what is metadata

  • Data other than tables are metadata and can be divided into three categories
    • Query result information: the number of records affected by UPDATE or DELETE statements.
    • Database and data table information: contains the structure information of database and data table.
    • MySQL server information: contains the current status and version number of the database server.

4.2 common commands

-- Introduction to metadata related commands
-- 1.View the current status of the server
-- 2.see MySQl Version information for
-- 3.Details in query table
-- 4.Displays detailed index information for the data table
-- 5.List all databases
-- 6.Displays all tables in the current database
-- 7.Gets the current database name
  • select version(); Get the version information of mysql server
  • show status; View server status information
  • show columns from table_name; Display the field information of the table, and desc table_ Same as name
  • show index from table_name; Displays detailed index information of the data table, including PRIMARY KEY
  • show databases: list all databases
  • show tables: displays all tables in the current database
  • select database(): get the current database name

4.3 obtaining metadata using JDBC

Metadata, such as database related information, can also be obtained through JDBC, or when we use programs to query an unfamiliar table, we can
Get the element data information to understand how many fields, field names and field types are in the table

4.3.1 introduction to common classes

  • Classes describing metadata in JDBC
  • Method to get metadata object: getMetaData()
    • connection connects the object, calls the getMetaData () method, and obtains the DatabaseMetaData database metadata object
    • PrepareStatement preprocessing object calls getMetaData() to obtain ResultSetMetaData and result set metadata object
  • Common methods of DatabaseMetaData
  • Common methods of ResultSetMetaData

4.3.2 code example

public class TestMetaData {
  //1.Get metadata information related to the database DatabaseMetaData
  @Test
  public void testDataBaseMetaData() throws SQLException {
    //1.Get database connection object connection
    Connection connection = DruidUtils.getConnection();
    //2.Gets the metadata object representing the database DatabaseMetaData
    DatabaseMetaData metaData = connection.getMetaData();
    //3.Get metadata information related to the database
    String url = metaData.getURL();
    System.out.println("database URL: " + url);
    String userName = metaData.getUserName();
    System.out.println("Current user: " + userName );
    String productName = metaData.getDatabaseProductName();
    System.out.println("Database product name: " + productName);
    String version = metaData.getDatabaseProductVersion();
    System.out.println("Database version: " + version);
    String driverName = metaData.getDriverName();
    System.out.println("Drive name: " + driverName);
    //Judge whether only read-only is allowed in the current database
    boolean b = metaData.isReadOnly(); //If it is true Read only
    if(b){
      System.out.println("Only read operations are allowed in the current database!");
    }else{
      System.out.println("Not a read-only database");
    }
    connection.close();
  }
  //Get metadata information in result set
  @Test
  public void testResultSetMetaData() throws SQLException {
    //1.Get connection
    Connection con = DruidUtils.getConnection();
    //2.Get preprocessing object
    PreparedStatement ps = con.prepareStatement("select * from employee");
    ResultSet resultSet = ps.executeQuery();
    //3.Get result set element object
    ResultSetMetaData metaData = ps.getMetaData();
    //1.Gets the total number of columns in the current result set
    int count = metaData.getColumnCount();
    System.out.println("Common in the current result set: " + count + " column");
    //2.Gets the name and type of the column in the result set
    for (int i = 1; i <= count; i++) {
      String columnName = metaData.getColumnName(i);
      System.out.println("Listing: "+ columnName);
      String columnTypeName = metaData.getColumnTypeName(i);
      System.out.println("type: " +columnTypeName);
    }
    //Release resources
    DruidUtils.close(con,ps,resultSet);
  }
}

 

Posted on Fri, 03 Dec 2021 11:15:23 -0500 by Brokenhope