学习目标:
了解JDBC连接池及JDBCTemplate相关知识
学习内容:
JDBC连接池
1.概念: 其实就是一个存放数据库连接的容器。当用户来访问数据库时,从该容器获得连接对象;用户访问完成后,会将连接对象返回容器。
2.目的:节约资源 效率高
3.方法:
* 获取连接: getConnection()方法
*返回资源: close() 若连接对象是从连接池中获取的,则close方法不是关闭连接,而是归还连接。
4.两个jar包:c3p0和druid
学习产出:
1.C3P0:
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class C3P0 {
public static void main(String[] args) throws SQLException {
DataSource ds = new ComboPooledDataSource("otherc3p0");
for (int i = 0; i < 100; i++) {
Connection connection = ds.getConnection();
System.out.println(connection);
if (i ==5){
connection.close();
}
}
}
}
2.Durid
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.util.Properties;
public class Druid {
public static void main(String[] args) throws Exception {
Properties properties = new Properties();
properties.load(Druid.class.getClassLoader().getResourceAsStream("druid.properties"));
DataSource ds = DruidDataSourceFactory.createDataSource(properties);
for (int i = 0; i < 20; i++) {
Connection connection = ds.getConnection();
System.out.println(connection);
if (i ==3){
connection.close();
}
}
}
}
3.定义工具类JDBCUtils
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtils {
/**
* 提供静态代码块加载配置文件,初始化连接池对象
*/
private static DataSource dataSource;
static{
Properties properties = new Properties();
try {
properties.load(new FileReader("src/druid.properties"));
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
*获取连接方法:通过数据库连接池获取连接
* @return connection对象
* @throws SQLException sql exception
*/
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
/**
* 释放资源
* @param rs 结果集
* @param connection connection对象
* @param statement statement对象
*/
public static void close(ResultSet rs, Connection connection , Statement statement){
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
}catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
/**
* 获取连接池的方法
* @return 连接池
*/
public static DataSource getDataSource(){
return dataSource;
}
}
4.用Spring框架对JDBC简单封装,简化JDBC开发
1. 调用JdbcTemplate的方法来完成CRUD的操作
* update():执行DML语句。增、删、改语句
* queryForMap():查询结果将结果集封装为map集合,将列名作为key,将值作为value 将这条记录封装为一个map集合
* 注意:这个方法查询的结果集长度只能是1
* queryForList():查询结果将结果集封装为list集合
* 注意:将每一条记录封装为一个Map集合,再将Map集合装载到List集合中
* query():查询结果,将结果封装为JavaBean对象
* query的参数:RowMapper
* 一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装
* new BeanPropertyRowMapper<类型>(类型.class)
* queryForObject:查询结果,将结果封装为对象
* 一般用于聚合函数的查询
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.math.BigDecimal;
import java.sql.Date;
import java.util.List;
public class SpringJDBC {
public static void main(String[] args) {
JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
String sql =" select * from emp;";
List<Emp> emps = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Emp.class));
for (Emp e : emps) {
System.out.println(e);
}
}
@Test
public void test1(){
JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
String sql = "delete from emp where id = ?;";
int update = jdbcTemplate.update(sql, 6);
System.out.println(update);
}
@Test
public void test2(){
JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
String sql = "select * from emp;";
List<Emp> emps = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Emp.class));
for (Emp e : emps) {
System.out.println(e);
}
}
}
public class Emp {
private int id;
private String name;
private int job_id;
private String mgr;
private Date joindate;
private BigDecimal salary;
private BigDecimal bonus;
private int dept_id;
public Emp() {
}
public Emp(int id, String name, int job_id, String mgr, Date joindate, BigDecimal salary, BigDecimal bonus, int dept_id) {
this.id = id;
this.name = name;
this.job_id = job_id;
this.mgr = mgr;
this.joindate = joindate;
this.salary = salary;
this.bonus = bonus;
this.dept_id = dept_id;
}
/**
* 获取
* @return id
*/
public int getId() {
return id;
}
/**
* 设置
* @param id
*/
public void setId(int id) {
this.id = id;
}
/**
* 获取
* @return name
*/
public String getName() {
return name;
}
/**
* 设置
* @param name
*/
public void setName(String name) {
this.name = name;
}
/**
* 获取
* @return job_id
*/
public int getJob_id() {
return job_id;
}
/**
* 设置
* @param job_id
*/
public void setJob_id(int job_id) {
this.job_id = job_id;
}
/**
* 获取
* @return mgr
*/
public String getMgr() {
return mgr;
}
/**
* 设置
* @param mgr
*/
public void setMgr(String mgr) {
this.mgr = mgr;
}
/**
* 获取
* @return joindate
*/
public Date getJoindate() {
return joindate;
}
/**
* 设置
* @param joindate
*/
public void setJoindate(Date joindate) {
this.joindate = joindate;
}
/**
* 获取
* @return salary
*/
public BigDecimal getSalary() {
return salary;
}
/**
* 设置
* @param salary
*/
public void setSalary(BigDecimal salary) {
this.salary = salary;
}
/**
* 获取
* @return bonus
*/
public BigDecimal getBonus() {
return bonus;
}
/**
* 设置
* @param bonus
*/
public void setBonus(BigDecimal bonus) {
this.bonus = bonus;
}
/**
* 获取
* @return dept_id
*/
public int getDept_id() {
return dept_id;
}
/**
* 设置
* @param dept_id
*/
public void setDept_id(int dept_id) {
this.dept_id = dept_id;
}
public String toString() {
return "Emp{id = " + id + ", name = " + name + ", job_id = " + job_id + ", mgr = " + mgr + ", joindate = " + joindate + ", salary = " + salary + ", bonus = " + bonus + ", dept_id = " + dept_id + "}";
}
}
总结
JDBC连接池及JDBCTemplate技术都需要导入外部jar包来实现。在操作上极大的简化了代码,将初始化类及获取连接对象简化并引入配置文件。既节约了资源,又使用户访问效率提高。