DBPC:
Packages needed: commons-dbcp2-2.10.0.jar, commons-pool2-2.12.0.jar, commons-logging-1.2.jar
accomplish:
Create a new file dbcpconfig.properties
#Connection settings driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true &characterEncoding=utf8 &useSSL=true username=root password=123456 #<!-- Initialize connection --> initialSize=10 #Maximum number of connections maxActive=50 #<!-- Maximum idle connection --> maxIdle=20 #<!-- Minimum idle connection --> minIdle=5 #<!-- The timeout waiting time is in milliseconds. 6000 milliseconds/1000 is equal to 60 seconds --> maxWait=60000 #The format of the connection attribute attached to the JDBC driver when establishing a connection must be as follows: [property name=property;] #Note: The two attributes "user" and "password" will be passed explicitly, so there is no need to include them here. connectionProperties=useUnicode=true;characterEncoding=UTF8 #Specify the auto-commit status of the connection created by the connection pool. defaultAutoCommit=true #driver default specifies the read-only status of connections created by the connection pool. #If this value is not set, the "setReadOnly" method will not be called. (Some drivers do not support read-only mode, such as: Informix) defaultReadOnly= #driver default specifies the transaction level (TransactionIsolation) of the connection created by the connection pool. #Available values are one of the following: (See javadoc for details.) NONE, READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE defaultTransactionIsolation=READ_UNCOMMITTED
Tool class JdbcUtils_DPCP
package com.wen.lesson05.utils; import org.apache.commons.dbcp2.BasicDataSource; import org.apache.commons.dbcp2.BasicDataSourceFactory; import javax.sql.DataSource; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class JdbcUtils_DPCP {<!-- --> private static DataSource dataSource = null; static {<!-- --> try{<!-- --> InputStream in = JdbcUtils_DPCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties"); Properties properties = new Properties(); properties.load(in); //Create data source factory mode-->Create object dataSource = BasicDataSourceFactory.createDataSource(properties); } catch (Exception e) {<!-- --> e.printStackTrace(); } } //Get connection public static Connection getConnection() throws SQLException {<!-- --> return dataSource.getConnection(); //Get the connection from the data source } //Release link resources public static void release(Connection conn, Statement st, ResultSet rs){<!-- --> if (rs!=null){<!-- --> try {<!-- --> rs.close(); } catch (SQLException e) {<!-- --> e.printStackTrace(); } } if (st!=null){<!-- --> try {<!-- --> st.close(); } catch (SQLException e) {<!-- --> e.printStackTrace(); } } if (conn!=null){<!-- --> try {<!-- --> conn.close(); } catch (SQLException e) {<!-- --> e.printStackTrace(); } } } }
Insert data using DBPC
package com.wen.lesson05; import com.wen.lesson02.utils.JdbcUtils; import com.wen.lesson05.utils.JdbcUtils_DPCP; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Date; public class TestDBCP {<!-- --> public static void main(String[] args) {<!-- --> Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try {<!-- --> conn = JdbcUtils_DPCP.getConnection(); //the difference //use? Placeholders instead of parameters String sql = "INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)"; st = conn.prepareStatement(sql); //Precompile sql, write sql first, and then do not execute it //Manually assign values to parameters st.setInt(1,4); //id st.setString(2, "xiaoming"); //name st.setString(3, "123456"); //password st.setString(4, "[email protected]"); //email //Note: sql.Date database java.sql.Date // util.Date Java new Date().getTime() gets the timestamp st.setDate(5, new java.sql.Date(new Date().getTime())); //birthday int i = st.executeUpdate(); if (i>0){<!-- --> System.out.println("Insertion successful"); } } catch (SQLException e) {<!-- --> throw new RuntimeException(e); } finally {<!-- --> JdbcUtils_DPCP.release(conn, st, rs); } } }
C3P0
Packages needed: c3p0-0.9.5.5.jar, mchange-commons-java-0.2.20.jar
accomplish:
Create a new file c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <!-- Default (default) configuration for c3p0 If "ComboPooledDataSource ds=new ComboPooledDataSource();" is written in the code, it means that the default (default) of c3p0 is used --> <default-config> <property name="driverClass">com.mysql.cj.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true & amp;amp;characterEncoding=utf8 & amp;amp;useSSL=false</property> <property name="user">root</property> <property name="password">123456</property> <property name="acquiredIncrement">5</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">5</property> <property name="maxPoolSize">20</property> </default-config> <!-- Named configuration for c3p0 If "ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");" is written in the code, it means that the default of mysql is used (default) --> <named-config name="MySQL"> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true & amp;amp;characterEncoding=utf8 & amp;amp;useSSL=false</property> <property name="user">root</property> <property name="password">123456</property> <property name="acquiredIncrement">5</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">5</property> <property name="maxPoolSize">20</property> </named-config> </c3p0-config>
Tool class JdbcUtils_C3P0
package com.wen.lesson05.utils; import com.mchange.v2.c3p0.ComboPooledDataSource; import org.apache.commons.dbcp2.BasicDataSourceFactory; import javax.sql.CommonDataSource; import javax.sql.DataSource; import java.io.InputStream; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class JdbcUtils_C3P0 {<!-- --> private static ComboPooledDataSource dataSource = null; static {<!-- --> try{<!-- --> //Code configuration writing method //dataSource = new ComboPooledDataSource(); //dataSource.setDriverClass(); //dataSource.setUser(); //dataSource.setPassword(); //dataSource.setJdbcUrl(); //dataSource.setMaxPoolSize(); //dataSource.setMinPoolSize(); //Create data source factory mode-->Create object dataSource = new ComboPooledDataSource("MySQL"); //How to write the configuration file } catch (Exception e) {<!-- --> e.printStackTrace(); } } //Get connection public static Connection getConnection() throws SQLException {<!-- --> return dataSource.getConnection(); //Get the connection from the data source } //Release link resources public static void release(Connection conn, Statement st, ResultSet rs){<!-- --> if (rs!=null){<!-- --> try {<!-- --> rs.close(); } catch (SQLException e) {<!-- --> e.printStackTrace(); } } if (st!=null){<!-- --> try {<!-- --> st.close(); } catch (SQLException e) {<!-- --> e.printStackTrace(); } } if (conn!=null){<!-- --> try {<!-- --> conn.close(); } catch (SQLException e) {<!-- --> e.printStackTrace(); } } } }
Use C3P0 to insert data
package com.wen.lesson05; import com.wen.lesson05.utils.JdbcUtils_C3P0; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Date; public class TestC3P0 {<!-- --> public static void main(String[] args) {<!-- --> Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try {<!-- --> conn = JdbcUtils_C3P0.getConnection(); //the difference //use? Placeholders instead of parameters String sql = "INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)"; st = conn.prepareStatement(sql); //Precompile sql, write sql first, and then do not execute it //Manually assign values to parameters st.setInt(1,5); //id st.setString(2, "xiaohong"); //name st.setString(3, "123456"); //password st.setString(4, "[email protected]"); //email //Note: sql.Date database java.sql.Date // util.Date Java new Date().getTime() gets the timestamp st.setDate(5, new java.sql.Date(new Date().getTime())); //birthday int i = st.executeUpdate(); if (i>0){<!-- --> System.out.println("Insertion successful"); } } catch (SQLException e) {<!-- --> throw new RuntimeException(e); } finally {<!-- --> JdbcUtils_C3P0.release(conn, st, rs); } } }