I jdbc connection pool
- The connection between the program and the database is the underlying technology. Each connection is time-consuming, and after it is used up, the connection must be closed to release resources. In fact, it is a waste of time to connect and release repeatedly. Therefore, a connection pool technology is introduced to manage these connection objects. After the connection object is used, it will be returned to the connection pool for allocation to the next use instead of destruction.
1. It's a container
2. The container contains connection objects
3. These connection objects can be used repeatedly after creation and will not be destroyed
4. They are shared
1. Common connection pool
c3p0 hibernate traditional orm framework
druid domestic Ali
2. Method of connection pool
1. ds.getConnection(); Get connection object -- Traditional way DriverManager.getConnection(); 2. con.close(); Indicates that the connection object is returned to the connection pool (premise: con (obtained from connection pool)
3. Use of c3p0 connection pool
- preparation
- Import jar package - two connection pools, one database driver, three in total
- Profile introduction
1. Import two c3p0 jar packages + mysql driver jar packages, 3 in total
2.c3p0-config. Incorrect writing of XML: C3PO config xml c3p0config. xml c3p0_ config. xml
*The file must be named correctly
*The path must be in the src direct directory, not in its subdirectory
Basic use
<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
<!--Default configuration-->
<default-config>
<!--Connection parameters-->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/db1</property>
<property name="user">root</property>
<property name="password">123456</property>
<!--Connection pool parameters-->
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">10</property>
<property name="checkoutTimeout">3000</property>
</default-config>
<!--Custom configuration-->
<name-config name="myc3p0">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/db1</property>
<property name="user">root</property>
<property name="password">123456</property>
<!--Connection pool parameters-->
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">10</property>
<property name="checkoutTimeout">3000</property>
</name-config>
</c3p0-config>
-Precautions:
- c3p0-config. The XML file name must be named this way
- c3p0-config. The XML file must be placed in the src directory, not in a subdirectory
1.maxPoolSize maximum number of connected objects
2.checkoutTimeout maximum error reporting time
3. Named config specifies the configuration name
*If new CombopooledDataSource("abc")
-
Something that could go wrong
- Writing error in configuration file
- Wrong file name or path
- The four parameters of database connection are written incorrectly, or more spaces are added
- Forget to import the driver jar package
@Test //Test connection pool parameters
public void test2() throws SQLException {
//Create connection pool data source object
ComboPooledDataSource ds = new ComboPooledDataSource();
//Get linked object
Connection con = ds.getConnection();
//The address value of the output link object indicates that the connection is normal
System.out.println(con);
}
4. druid connection pool
4.1 basic use
-
Import jar package -- 1
-
Define the profile Druid properties
-
Code demonstration
public class Test2 {
@Test //No profile
public void test1() throws SQLException {
//Create connection pool object
DruidDataSource ds = new DruidDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost:3306/db1");
ds.setUsername("root");
ds.setPassword("123456");
//Get object from connection pool
System.out.println(ds.getConnection());
}
@Test //Use profile
public void test2() throws Exception {
Properties p = new Properties();
p.load(new FileInputStream("src/druid.properties"));
DataSource ds = DruidDataSourceFactory.createDataSource(p);
System.out.println(ds.getConnection());
}
}
4.2 tools
public class JdbcUtil {
private static DataSource ds;
/**
* You only need to read the file once to get these values. Using static code blocks
*/
static{
//Read the resource file and get the value.
try {
FileInputStream is = new FileInputStream("E:\\Javaweb\\day06-web\\src\\druid.properties");
Properties p = new Properties();
p.load(is);
//Create a data source and assign a value to the member property ds
ds = DruidDataSourceFactory.createDataSource(p);
//System.out.println(p);
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
System.out.println(getDs());
}
/**
* Get connection
* @return Connection object
*/
public static Connection getConnection() {
try {
return ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//Get connection pool object ds
public static DataSource getDs(){
return ds;
}
/**
* Release resources
* @param stmt
* @param conn
*/
public static void close(ResultSet rs, Statement stmt, Connection conn){
if( rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if( stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if( conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
4.3 tool test
@Test //The revised amount is 5000
public void test8(){
//Create a JDBC template object
JdbcTemplate template = new JdbcTemplate(com.JdbcUtil.getDs());
//Define sql
int update = template.update("update account set balance=5000 where id=?",1);
System.out.println(update);
}
II jdbcTemplate
1. Introduction
- The Spring framework is a simple encapsulation of jdbc and provides a JdbcTemplate object
- Some methods of JDBC template
- update performs dml addition, deletion, and modification
- queryForMap() query results, encapsulating the result set as a map set
- queryForList() query results, encapsulating the result set into a list set
- queryForObject() queries the result set and encapsulates the result set into a data type
- query() queries the result set and encapsulates the result set as a java bean object
1. 5 jar packages of spring + mysql driver + druid connection pool package 1 = 7
2. Use the JDBC template object. The creation of this object requires the connection pool object ds
JdbcTemplate jdbcTemplate=new JdbcTemplate(DruidUtil.getDataSource());
jdbcTemplate.update();//dml
jdbcTemplate.query();
jdbcTemplate.queryForMap();
jdbcTemplate.queryForList();
jdbcTemplate.queryForObject();
2. Getting started
@Test //The revised amount is 5000
public void test8(){
//Create a JDBC template object
JdbcTemplate template = new JdbcTemplate(com.JdbcUtil.getDs());
//Define sql
int update = template.update("update account set balance=5000 where id=?",1);
System.out.println(update);
}
3. Comprehensive exercise of JDBC template
public class TestJdbcTemplate {
@Test //The revised amount is 5000
public void test8(){
//Create a JDBC template object
JdbcTemplate template = new JdbcTemplate(com.JdbcUtil.getDs());
//Define sql
int update = template.update("update account set balance=5000 where id=?",1);
System.out.println(update);
}
@Test
public void update(){
//Create simplified action templates
JdbcTemplate template = new JdbcTemplate(JdbcUtil.getDs());
//Call update
int i = template.update("update dept set name=? where id=?", "Teaching Department", 5);
System.out.println(i);//Number of rows affected i
}
@Test //Query all and encapsulate them into a List. The generic type is Map
public void test2(){
JdbcTemplate template = new JdbcTemplate(com.JdbcUtil.getDs());
List<Map<String, Object>> list = template.queryForList("select * from emp");
System.out.println(list);
}
@Test //newly added
public void test3(){
JdbcTemplate template = new JdbcTemplate(com.JdbcUtil.getDs());
int c = template.update("insert users values (null,?,?)", "Cao Cao", "123123");
System.out.println(c);
}
@Test //modify
public void test4(){
JdbcTemplate template = new JdbcTemplate(com.JdbcUtil.getDs());
int d = template.update("update users set NAME=? where id=?", "army officer's hat ornaments", 3);
System.out.println(d);
}
@Test //delete
public void test5(){
JdbcTemplate template = new JdbcTemplate(com.JdbcUtil.getDs());
int d = template.update("delete from users where id=?", 3);
System.out.println(d);
}
@Test //Query an encapsulated map
public void test6(){
JdbcTemplate template = new JdbcTemplate(com.JdbcUtil.getDs());
Map<String, Object> map = template.queryForMap("select * from users where id=?", 2);
System.out.println(map);
}
@Test //Query an encapsulated javaBean
public void test7(){
JdbcTemplate template = new JdbcTemplate(com.JdbcUtil.getDs());
Users users = template.queryForObject("select * from users where id=?", new BeanPropertyRowMapper<Users>(Users.class), 1);
System.out.println(users);
}
}