1: DBCP data source
DBCP: short for database connection pool, it is the implementation of open source connection pool under Apache organization and the 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:
1.commons-dbcp.jar package
commons-dbcp.jar package is the implementation package of DBCP data source, which contains all methods to operate database connection information and database connection pool initialization information, and implements the getConnection() method of DataSource interface.
2. commons-pool.jar package
commons-pool.jar package is the dependent package of DBCP database connection pool implementation package, which is commons DBCP The methods in the jar package provide support. It can be said that there is no dependent package, common DBCP Many methods in the jar package cannot be implemented.
2: In Chapter 13 Create an Example01 class under example:
package chapter13.example;
import java.awt.image.DataBufferShort;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import java.sql.DatabaseMetaData;
import java.sql.Connection;
public class example01 {
public static DataSource ds = null;
static {
BasicDataSource bds = new BasicDataSource();
bds.setDriverClassName("com.mysql.cj.jdbc.Driver");
bds.setUrl( "jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC");
bds.setUsername("root");
bds.setPassword("123456");
bds.setInitialSize(5);
bds.setMaxActive(5);
ds = bds;
}
public static void main(String[] args) throws SQLException{
Connection conn =ds.getConnection();
DatabaseMetaData metaData = conn.getMetaData();
System.out.println(metaData.getURL()+"UserName="+metaData.getUserName()+","+metaData.getDriverName());
}
}
result
3: Create an example02 class, which uses the connection information of the database and the initialization information of the data source obtained from the configuration file. The specific code is as follows:
package chapter13.example;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class Example02 {
public static DataSource ds = null;
static {
// Create a new profile object
Properties prop = new Properties();
try {
// Find the file path through the classloader and read the configuration file
InputStream in = new Example02().getClass().getClassLoader()
.getResourceAsStream("dbcpconfig.properties");
// Load the file into the configuration object as an input stream
prop.load(in);
// Create data source object
ds = BasicDataSourceFactory.createDataSource(prop);
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static void main(String[] args) throws SQLException {
// Get database connection object
Connection conn = ds.getConnection();
//Get database connection information
DatabaseMetaData metaData = conn.getMetaData();
//Print database connection information
System.out.println(metaData.getURL()
+",UserName="+metaData.getUserName()
+","+metaData.getDriverName());
}
}
Create an example03 class, which uses the C3P0 data source manual code to obtain the Connection object
package chapter13.example;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class Example03 {
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/jdbc");
cpds.setUser("root");
cpds.setPassword("itcast");
// Set 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 database connection object
System.out.println(ds.getConnection());
}
}
Create a data source object by reading the configuration file
Use the ComboPooledDataSource (String configName) constructor to read c3p0 config XML configuration file to create the data source object, and then obtain the database connection object. Create a c3p0 - config. In the src root directory XML file, which is used to set the connection information of the database and the initialization information of the data source
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">
jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC
</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="checkoutTimeout">30000</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config>
<named-config name="dashuju">
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">
jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC
</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">15</property>
</named-config>
</c3p0-config>
Create an Example04 class, which uses the C3P0 data source to obtain the Connection object from the configuration file:
package chapter13.example;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class example04 {
public static DataSource ds = null;
static {
ComboPooledDataSource cpds = new ComboPooledDataSource("dashuju");
ds = cpds;
}
public static void main(String[] args) throws SQLException{
System.out.println(ds.getConnection());
}
}
result
4: DBUtils tool
1. Introduction to 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 core of the DBUtils tool is org apache. commons. DBUtils. Queryrunner class and org apache. commons. DBUtils. Resultsethandler interface
2. Create the data table user in the database named jdbc. The creation statement is as follows.
USE jdbc; CREATE TABLE user( id INT(3) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, password VARCHAR(20) NOT NULL );
Insert three pieces of data into the user table. The specific statements are as follows
INSERT INTO user (name, password) VALUES ('zhangsan','123456');
INSERT INTO user (name, password) VALUES ('lisi','123456');
INSERT INTO user (name, password) VALUES ('wangwu','123456');
result
3. Create a class named BaseDao, in which a general query method is written
package chapter13.example;
import java.sql.SQLException;
import org.apache.commons.dbutils.ResultSetHandler;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;
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;
Object obj = null;
try {
conn = JDBCUtils.getConnection();
pstmt = conn.prepareStatement(sql);
for (int i = 0; params !=null && i<params.length; i++) {
pstmt.setObject(i+1, params[i]);
}
rs = pstmt.executeQuery();
obj = rsh.handle(rs);
} catch (Exception e) {
// TODO: handle exception
return new Exception(e.getMessage());
}finally {
JDBCUtils.release(rs,pstmt,conn);
}
return obj;
}
}
Create an entity class User, which is used to encapsulate the User object
package chapter13.example;
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;
}
}
Create a class ResultSetTest1, which is used to demonstrate the processing of the result set by the BeanHandler class
package chapter13.example;
import java.sql.SQLException;
import org.apache.commons.dbutils.handlers.BeanHandler;
public class ResultSetTest1 {
public static void testBeanHandler() throws SQLException{
basedao basedao = new basedao();
String sql = "select * from user where id =?";
Object object = basedao.query(sql, new BeanHandler(User.class), 1);
if (object!=null&& object instanceof User) {
User user = (User) object;
System.out.println("id Is 1 User Object name Value is:"+user.getName());
}else {
System.out.println("The query result is empty:"+object);
}
}
public static void main(String[] args) throws SQLException{
testBeanHandler();
}
}
result
Create a class ResultSetTest2, which is used to demonstrate the processing of the result set by the BeanListHandler class
package chapter13.example;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.commons.dbutils.handlers.BeanListHandler;
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 userna Value is:"+list.get(i).getName());
}
}
public static void main(String[] args) throws SQLException{
testBeanListHandler();
}
}
result
5.ScalarHandler
This class is used to create the settesthandler class
package chapter13.example;
import java.sql.SQLException;
import org.apache.commons.dbutils.handlers.ScalarHandler;
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"), 1);
System.out.println(arr);
}
public static void main(String[] args) throws SQLException{
testScalarHandler();
}
}
result
5: Using DBUtils to add, delete, modify and query
Create C3p0Utils class, which is used to create data sources
package chapter13.example;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3p0Utils {
public static DataSource ds;
static {
ds = new ComboPooledDataSource();
}
public static DataSource getDataSource() {
return ds;
}
}
Create DBUtilsDao class
package chapter13.example;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
public class DBUtilsDao {
public List findAll() throws SQLException{
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "select * from user ";
List list = (List) runner.query(sql, new BeanListHandler(User.class));
return list;
}
public User find(int id) throws SQLException{
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "select * from user where id=?";
User user = (User) runner.query(sql, new BeanHandler(User.class),new Object[] {id});
return user;
}
public Boolean insert(User user) throws SQLException{
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "insert into user (name,password) values (?,?)";
int num =runner.update(sql,new Object[] {user.getName(),user.getPassword()});
if (num>0)
return true;
return false;
}
public Boolean update(User user) throws SQLException{
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "update user set name=?,password=? where id=?";
int num = runner.update(sql,new Object[] {user.getName(),user.getPassword(),user.getId()});
if (num>0)
return true;
return false;
}
public Boolean delete(int id) throws SQLException{
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "delete from user where id=?";
int num = runner.update(sql,id);
if (num>0)
return true;
return false;
}
}
Test the addition, deletion, modification and query operations in DBUtilsDao class
Create class DBUtilsDaoTest1 to test the addition operation:
package chapter13.example;
import java.sql.SQLException;
public class DBUtilsDaoTest1 {
private static DBUtilsDao dao = new DBUtilsDao();
public static void testInsert() throws SQLException{
User user = new User();
user.setName("zhaoliu");
user.setPassword("666666");
boolean b =dao.insert(user);
System.out.println(b);
}
public static void main(String[] args) throws SQLException {
testInsert();
}
}
result
Create test class DBUtilsDaoTest2:
package chapter13.example;
import java.sql.SQLException;
public class DBUtilsDaoTest2 {
private static DBUtilsDao dao = new DBUtilsDao();
public static void testUpdate() throws SQLException{
User user = new User();
user.setName("zhaoliu");
user.setPassword("666777");
user.setId(4);
boolean b = dao.update(user);
System.out.println(b);
}
public static void main(String[] args) throws SQLException {
testUpdate();
}
}
result
