Today's Content
1. Database Connection Pool
2. Spring JDBC : JDBC Template
Database Connection Pool
1. Concept: It is actually a container (collection) that holds database connections.
When the system is initialized, the container is created and some connection objects are requested in the container. When the user accesses the database, the connection objects are retrieved from the container and returned to the container after the user has accessed them.
2. Benefits:
1. Save resources
2. User access is efficient
3. Implementation:
1. Standard interface: DataSourceJavax.sqlUnpackaged
1. Method:
*Get a connection: getConnection()
*Return connection:Connection.close().If the connection object Connection is obtained from the connection pool, then callConnection.close() method, the connection is no longer closed.Instead, return the connection
2. Normally we don't want to implement it, but there are database vendors to implement it.
1. C3P0: Database Connection Pool Technology
2. Druid: database connection pool implementation technology, provided by Alibaba
4. C3P0: Database Connection Pool Technology
*Step:
1. Import the jar package (two) c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar,
*Don't forget to import database-driven jar packages
2. Define the profile:
*Name: c3p0.properties or c3p0-config.xml
*Path: Place the file directly in the src directory.
3. Create core object database connection pool object ComboPooledDataSource
4. Get connected: getConnection
*Code:
package dataSource.c3p0;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
/**
* c3p0 Demonstration
*/
public class C3p0Demo01 {
public static void main(String[] args) {
//1. Create a database connection pool object
DataSource ds= new ComboPooledDataSource();
//2. Get the database connection object
Connection conn=null;
try {
conn = ds.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
System.out.println(conn);
}
}
- Demonstration of whether c3p0 has parameters
package dataSource.c3p0;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import sun.management.snmp.jvmmib.JVM_MANAGEMENT_MIBOidTable;
import javax.sql.DataSource;
import javax.xml.crypto.Data;
import java.sql.Connection;
import java.sql.SQLException;
/**
* c3p0 Demonstration
*/
public class C3p0Demo02 {
public static void main(String[] args) throws SQLException {
/* //Get DataSource No Parameters Use Default Configuration
DataSource ds=new ComboPooledDataSource();
//Get Connections
for (int i=1;i<=11;i++){
Connection conn=ds.getConnection();
System.out.println(i+"::"+conn);
if(i==5)
conn.close();*/
getOtherName();
}
public static void getOtherName() throws SQLException {
DataSource das=new ComboPooledDataSource("otherc3p0"); //Using a c3p0 configuration file with parameters
for (int i=1;i<=10;i++){
Connection conn=das.getConnection();
System.out.println(i+"::"+conn); //
}
}
}
- C3p0-Config.xmlFile Configuration
<c3p0-config>
<!-- Read Connection Pool Objects with Default Configuration -->
<default-config>
<!-- Connection parameters -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/db4</property>
<property name="user">root</property>
<property name="password">19990508</property>
<!-- Connection pool parameters -->
<!-- Number of connections initiated for request-->
<property name="initialPoolSize">5</property>
<!-- Maximum number of connections-->
<property name="maxPoolSize">10</property>
<!-- timeout-->
<property name="checkoutTimeout">3000</property>
</default-config>
<named-config name="otherc3p0">
<!-- Connection parameters -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="user">root</property>
<property name="password">19990508</property>
<!-- Connection pool parameters -->
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">8</property>
<property name="checkoutTimeout">1000</property>
</named-config>
</c3p0-config>
5. Druid: Database connection pool implementation technology, provided by Alibaba
1. Steps:
1. Import jar package druid-1.0.9.jar
2. Define the profile:
* is in the form of properties
* Can be any name, can be placed in any directory
3. Load the configuration file.Properties
4. Get the database connection pool object: Get DruidDataSourceFactory from the factory
5. Get connected: getConnection
*Code:
package dataSource.druid;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
public class DruidDemo01 {
public static void main(String[] args) throws Exception {
//1. Importing jar packages
//2. Define the configuration file
//3. Load Profile
Properties prop=new Properties();
ClassLoader classLoader = DruidDemo01.class.getClassLoader();
InputStream is = classLoader.getResourceAsStream("druid.properties");
prop.load(is);
//4. Get Connection Pool Objects
DataSource ds = DruidDataSourceFactory.createDataSource(prop);
//5. Get connected
Connection conn = ds.getConnection();
System.out.println(conn);
}
}
2. Define Tool Classes
1. Define a class JDBCUtils
2. Provide static code blocks to load configuration files and initialize connection pool objects
3. Provide methods
1. Get connections: Get connections through the database connection pool
2. Release resources
3. How to get the connection pool
*Code:
public class JDBCUtils {
//1. Define member variable DataSource
private static DataSource ds ;
static{
try {
//1. Load Profile
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 Connections
*/
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;
}
}
package dataSource.druid;
import utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* Using the new tool class
*/
/*
Complete the add operation: Add a record to the bank table
*/
public class DruidDemo02 {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement pret=null;
try {
//Get Database Connection Object
conn = JDBCUtils.getConnection();
//Define sql
String sql="insert into bank values(null,?,?)";
//Get the object that operates on sql
pret = conn.prepareStatement(sql);
//Here?Set Value
pret.setString(1,"hubeihua");
pret.setInt(2,6000);
//Execute sql statement
int i = pret.executeUpdate();
if(i>0){
System.out.println("Added Successfully");
}else {
System.out.println("Failed to add");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
//Release Resources
JDBCUtils.close(conn,pret);
}
}
}
- Druid.propertiesConfiguration of files
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test
username=root
password=19990508
# Number of Initialized Connections
initialSize=5
# maximum connection
maxActive=10
# Maximum Wait Time
maxWait=3000
package domain;
import java.util.Date;
public class Emp {
private Integer id;
private String ename;
private Integer job_id;
private Integer mgr;
private Date joindate;
private Double salary;
private Double bonus;
private Integer dept_id;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public Integer getJob_id() {
return job_id;
}
public void setJob_id(Integer job_id) {
this.job_id = job_id;
}
public Integer getMgr() {
return mgr;
}
public void setMgr(Integer mgr) {
this.mgr = mgr;
}
public Date getJoindate() {
return joindate;
}
public void setJoindate(Date joindate) {
this.joindate = joindate;
}
public Double getSalary() {
return salary;
}
public void setSalary(Double salary) {
this.salary = salary;
}
public Double getBonus() {
return bonus;
}
public void setBonus(Double bonus) {
this.bonus = bonus;
}
public Integer getDept_id() {
return dept_id;
}
public void setDept_id(Integer dept_id) {
this.dept_id = dept_id;
}
@Override
public String toString() {
return "Emp{" +
"id=" + id +
", ename='" + ename + ''' +
", job_id=" + job_id +
", mgr=" + mgr +
", joindate=" + joindate +
", salary=" + salary +
", bonus=" + bonus +
", dept_id=" + dept_id +
'}';
}
}
Spring JDBC
* Simple encapsulation of JDBC by the Spring framework.Provides a JDBCTemplate object to simplify JDBC development
*Step:
1. Importing jar packages
2. Create a JdbcTemplate object.Dependent on data source DataSource
* JdbcTemplate template = new JdbcTemplate(ds);
3. Call JdbcTemplate's method to complete CRUD operation
* update(): Execute the DML statement.Add, delete, change sentences
* queryForMap(): The query result encapsulates the result set as a map collection, the column name as a key, and the value as a value to encapsulate this record as a map collection
*Note: The result set length of this method query can only be 1
* queryForList(): Query results encapsulate the result set as a list set
*Note: Encapsulate each record as a Map collection and load the Map collection into the List collection
* query(): Query results, encapsulate the results as JavaBean objects
* Quey parameter: RowMapper
*Typically, we use BeanPropertyRowMapper to implement classes.Automatic data-to-JavaBean encapsulation can be accomplished
* new BeanPropertyRowMapper <type> (type.class)
* queryForObject: Query results, encapsulate results as objects
*Queries typically used for aggregate functions
package jdbcTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import utils.JDBCUtils;
/**
* jdbcTemplate
*/
public class JdbcTemplateDemo01 {
public static void main(String[] args) {
//Import Package
//Create JDBCTemplate object
JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDateSource());
//Call Method
String sql="update bank set balance=5000 where id=?";
int count = template.update(sql, 1);
System.out.println(count);
}
}
4. Exercise:
*Requirements:
1. The salary for modifying Data No. 1 is 10000
2. Add a record
3. Delete the record you just added
4. Query a record with id 1 and encapsulate it as a Map collection
5. Query all records and encapsulate them as List s
6. Query all records and encapsulate them as a List collection of Emp objects
7. Total number of records queried
*Code:
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 JdbcTemplateDemo2 {
//Junit unit tests, which allow methods to execute independently
//1. Get the JDBCTemplate object
private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
/**
* 1. Modify salary of data 1 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 you 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 a record with id 1001 and encapsulate it as a Map collection
* Note: The result set length 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 List s
*/
@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 number of records queried
*/
@Test
public void test7(){
String sql = "select count(id) from emp";
Long total = template.queryForObject(sql, Long.class);
System.out.println(total);
}
}