1. C3P0 database connection pool
- Information: link: https://pan.baidu.com/s/19jWfIQ_OlZ29pPPnxNm5og
Extraction code: hgha
1.1 concept
- In fact, it is a container (Collection) for storing database connections.
- After the system is initialized, the container is created, and some connection objects will be applied in the container. When the user accesses the database, the connection objects will be obtained from the container. After the user accesses, the connection objects will be returned to the container.
1.2 benefits
- save resources
- Efficient user access
1.3 realization:
-
Standard interface: under DataSource javax.sql package
method:
-Get connection: getConnection()
-Return Connection: Connection.close(). If the Connection object Connection is obtained from the Connection pool, the Connection will not be closed by calling the Connection.close() method. Instead, return the Connection -
Generally, we do not implement it, but database manufacturers do
C3P0: database connection pool technology
Druid: database connection pool implementation technology, provided by Alibaba
1.4 C3P0 database connection pool technology
-
Steps:
1. Import the jar package (two) c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar,
*Don't forget to import the database driver jar package
2. Define profile:
*Name: c3p0.properties or c3p0-config.xml
*Path: directly put the file in the src directory.3. Create core database connection pool object ComboPooledDataSource 4. Get connection: getConnection * code: //1. Create database connection pool object DataSource ds = new ComboPooledDataSource(); //2. Get the connection object Connection conn = ds.getConnection();
2. Druid database connection pool
- Information: link: https://pan.baidu.com/s/1ehW_hnrbMRRITdrmDUZ3oA
Extraction code: 7i1v
2.1 steps
-
Import the jar package druid-1.0.9.jar
-
Define profile:
*It is in the form of properties
*You can call any name and put it in any directory -
Load the configuration file. Properties
-
Get database connection pool object: get DruidDataSourceFactory through the factory
-
Get connection: getConnection
//1. Import the jar package druid-1.0.9.jar //2. Define profile //3. Load configuration file Properties pro = new Properties(); InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties"); pro.load(is); //4. Get connection pool object DataSource ds = DruidDataSourceFactory.createDataSource(pro); //5. Get connection Connection conn = ds.getConnection();
2.2 defining tool classes
-
Define a class JDBC utils
-
Provide static code blocks, load configuration files, and initialize connection pool objects
-
Provide method
- Get connection method: get connection through database connection pool
- Release resources
- Method to get connection pool
public class JDBCUtils { //1. Define member variable DataSource private static DataSource ds ; static{ try { //1. Load configuration file Properties pro = new Properties(); pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties")); //2. Get DataSource ds = DruidDataSourceFactory.createDataSource(pro); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } /** * Get connection */ public static Connection getConnection() throws SQLException { return ds.getConnection(); } /** * Release resources */ public static void close(Statement stmt,Connection conn){ /* if(stmt != null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close();//Return connection } catch (SQLException e) { e.printStackTrace(); } }*/ close(null,stmt,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();//Return connection } catch (SQLException e) { e.printStackTrace(); } } } /** * Get connection pool method */ public static DataSource getDataSource(){ return ds; } }
Use of tool classes:
public static void main(String[] args) {
/*
* Complete the add operation: add a record to the account table
*/
Connection conn = null;
PreparedStatement pstmt = null;
try {
//1. Get connection
conn = JdbcUtils.getConnection();
//2. Define sql
String sql = "insert into dept values(null,?)";
//3. Get pstmt object
pstmt = conn.prepareStatement(sql);
//4. Here? assignment
pstmt.setString(1,"Zhao 412 Liu");
//5. Execute sql
int count = pstmt.executeUpdate();
System.out.println(count);
} catch (SQLException e) {
e.printStackTrace();
}finally {
//6. Release resources
JdbcUtils.close(pstmt,conn);
}
}
3. Spring JDBC
- Spring framework's simple encapsulation of JDBC. Provides a JDBC template object to simplify JDBC development
Steps:
-
Import jar package
-
Create a JdbcTemplate object. Dependent on DataSource datasource
* JdbcTemplate template = new JdbcTemplate(ds); -
Call the method of JdbcTemplate to complete CRUD operation
-
update(): execute DML statement. Add, delete and modify statements
-
queryForMap(): the query result encapsulates the result set as a map set, the column name as the key, the value as the value, and the record as a map set
*Note: the length of the result set of this method query can only be 1
-
queryForList(): the query result encapsulates the result set into a list set
*Note: encapsulate each record into a Map set, and then load the Map set into the List set -
query(): query results, which are encapsulated as JavaBean objects
*Parameter of query: RowMapper
*Generally, we use the BeanPropertyRowMapper implementation class. It can complete the automatic encapsulation of data to JavaBean s
*New beanpropertyrowmapper < type > (type. class) -
queryForObject: the query result is encapsulated as an object
*It is generally used for queries of aggregate functions
import cn.itcast.domain.Emp;
import cn.itcast.utils.JDBCUtils;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
public class JdbcTemplateDemo {
//Junit unit tests allow methods to execute independently
//1. Get JDBC template object
private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
/**
* 1. Modify the salary of No. 1 data to 10000
*/
@Test
public void test1(){
//2. Define sql
String sql = "update emp set salary = 10000 where id = 1001";
//3. Execute sql
int count = template.update(sql);
System.out.println(count);
}
/**
* 2. Add a record
*/
@Test
public void test2(){
String sql = "insert into emp(id,ename,dept_id) values(?,?,?)";
int count = template.update(sql, 1015, "Guo Jing", 10);
System.out.println(count);
}
/**
* 3.Delete the record just added
*/
@Test
public void test3(){
String sql = "delete from emp where id = ?";
int count = template.update(sql, 1015);
System.out.println(count);
}
/**
* 4.Query the record with id 1001 and encapsulate it as a Map set
* Note: the length of the result set of this method query can only be 1
*/
@Test
public void test4(){
String sql = "select * from emp where id = ? or id = ?";
Map<String, Object> map = template.queryForMap(sql, 1001,1002);
System.out.println(map);
//{id=1001, ename = Monkey King, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=20}
}
/**
* 5. Query all records and encapsulate them as a List
*/
@Test
public void test5(){
String sql = "select * from emp";
List<Map<String, Object>> list = template.queryForList(sql);
for (Map<String, Object> stringObjectMap : list) {
System.out.println(stringObjectMap);
}
}
/**
* 6. Query all records and encapsulate them as a List collection of Emp objects
*/
@Test
public void test6(){
String sql = "select * from emp";
List<Emp> list = template.query(sql, new RowMapper<Emp>() {
@Override
public Emp mapRow(ResultSet rs, int i) throws SQLException {
Emp emp = new Emp();
int id = rs.getInt("id");
String ename = rs.getString("ename");
int job_id = rs.getInt("job_id");
int mgr = rs.getInt("mgr");
Date joindate = rs.getDate("joindate");
double salary = rs.getDouble("salary");
double bonus = rs.getDouble("bonus");
int dept_id = rs.getInt("dept_id");
emp.setId(id);
emp.setEname(ename);
emp.setJob_id(job_id);
emp.setMgr(mgr);
emp.setJoindate(joindate);
emp.setSalary(salary);
emp.setBonus(bonus);
emp.setDept_id(dept_id);
return emp;
}
});
for (Emp emp : list) {
System.out.println(emp);
}
}
/**
* 6. Query all records and encapsulate them as a List collection of Emp objects
*/
@Test
public void test6_2(){
String sql = "select * from emp";
List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
for (Emp emp : list) {
System.out.println(emp);
}
}
/**
* 7. Total records queried
*/
@Test
public void test7(){
String sql = "select count(id) from emp";
Long total = template.queryForObject(sql, Long.class);
System.out.println(total);
}
}