一、数据库连接池
- 连接池(容器、集合)中申请一些连接对象,当用户访问数据库时,从容器中获取连接对象,用户访问完后,会将连接对象归还给容器。
C3P0 :数据库连接池技术
(jar包相关资源)链接:https://pan.baidu.com/s/1IBVzY0LGh0dV9K0JFPifTA
提取码:a1hu
- c3p0-config.xml文件中一些参数的含义:
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
/* C3P0的演示*/
public class C3P0 {
public static void main(String[] args) throws SQLException {
//1.获取DataSource,使用默认配置(无参)
/*DataSource ds=new ComboPooledDataSource();*/
//1.获取DataSource,使用默认配置(有参)
DataSource ds=new ComboPooledDataSource("otherc3p0");
//3.验证最大连接数量
for(int i=1;i<=10;i++){
//2.获取连接对象
Connection con= ds.getConnection();
System.out.println(i+" "+con);
}
}
}
druid :数据库连接池实现技术
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
/*
Druid的演示
*/
public class Druid {
public static void main(String[] args) throws Exception {
//1.加载配置文件(需要手动加载)
Properties pro=new Properties();
InputStream is = Druid.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(is);
//2.获取连接池对象
DataSource ds= DruidDataSourceFactory.createDataSource(pro);
//3.获取连接对象
Connection con=ds.getConnection();
System.out.println(con);
}
}
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.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/*
Druid连接池的工具类
*/
public class Druid_Utils {
private static DataSource ds=null;
static{
try {
//1.加载配置文件
Properties pro=new Properties();
InputStream is= Druid_Utils.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(is);
//2.获取DataSource
ds= DruidDataSourceFactory.createDataSource(pro);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取连接
*/
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
/**
* 释放资源,归还连接
*/
public static void close(Statement sta, Connection con){
close(null,sta,con);
}
public static void close(ResultSet rs,Statement sta, Connection con){
if(rs!=null){
try {
rs.close(); //归还连接
} catch (SQLException e) {
e.printStackTrace();
}
}
if(sta!=null){
try {
sta.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获取连接池
*/
public static DataSource getDataSource(){
return ds;
}
}
- 使用Druid的工具类来给表插入数据
Spring JDBC: Spring框架对JDBC的简单封装
练习:
import DataSource.Druid_Utils;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
/*
JDBCTemplate的相关练习:
1.修改1号记录的salary为10000
2.添加一条记录
3.删除刚刚添加的记录
4.查询id为1的数据,将其封装为map集合
5.查询所有数据,将其封装为list集合
6.查询所有数据,将其封装为emp对象的list集合
7.查询总记录数
*/
class Emp{
private String name;
private int age;
private double salary;
private int id;
public Emp() {
}
public Emp(String name, int age, double salary, int id) {
this.name = name;
this.age = age;
this.salary = salary;
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@Override
public String toString() {
return "Emp{" +
"name='" + name + '\'' +
", age=" + age +
", salary=" + salary +
", id=" + id +
'}';
}
}
public class Test1 {
//Junit单元测试,可以让方法独立运行
//1.获取JDBCTemplate对象,放在外面不用每次都获取
private DataSource ds= Druid_Utils.getDataSource();
private JdbcTemplate jt=new JdbcTemplate(ds);
/**
* 1.修改1号记录的salary为10000
*/
@Test
public void test1(){
//2.定义sql语句
String sql="update emp set salary=10000 where id=1";
//3.执行sql语句
int count= jt.update(sql);
System.out.println(count);
}
/**
* 2.添加一条记录
*/
@Test
public void test2(){
String sql="insert into emp(name,id,age,salary) value(?,?,?,?)";
int count= jt.update(sql, "小雪", 4, 20, 3200);
System.out.println(count);
}
/**
* 3.删除刚刚添加的记录
*/
@Test
public void test3(){
String sql="delete from emp where id=?";
int count= jt.update(sql,4);
System.out.println(count);
}
/**
* 4.查询id为1的数据,将其封装为map集合
* 注意:查询的结果集长度只能为1
*/
@Test
public void test4(){
String sql="select * from emp where id=?";
//封装为map集合
Map<String, Object> map= jt.queryForMap(sql, 1);
System.out.println(map);
}
/**
* 5.查询所有数据,将其封装为list集合
*/
@Test
public void test5(){
String sql="select * from emp";
List<Map<String, Object>> list= jt.queryForList(sql);
System.out.println(list);
}
/**
* 6.查询所有数据,将其封装为emp对象的list集合
* 第一种方法:自己实现RowMapper<T>接口
*/
@Test
public void test6(){
String sql="select * from emp";
List<Emp> list= jt.query(sql, new RowMapper<Emp>() {
@Override
public Emp mapRow(ResultSet rs, int i) throws SQLException {
Emp emp = new Emp();
String name = rs.getString("name");
int id = rs.getInt("id");
int age = rs.getInt("age");
double salary = rs.getDouble("salary");
emp.setName(name);
emp.setId(id);
emp.setAge(age);
emp.setSalary(salary);
return emp;
}
});
for(Emp emp:list){
System.out.println(emp);
}
}
/**
* 6.查询所有数据,将其封装为emp对象的list集合
* 第二种方法:用封装好的实现类 BeanPropertyRowMapper
*/
@Test
public void test7(){
String sql="select * from emp";
List<Emp> list= jt.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
for(Emp emp:list){
System.out.println(emp);
}
}
/**
* 7.查询总记录数
*/
@Test
public void test8(){
String sql="select count(name) from emp";
Long total= jt.queryForObject(sql, Long.class);
System.out.println(total);
}
}