连接池
连接池就是存储我们数据库连接对象的容器,系统初始化后,会去申请一些连接对象,
用户访问数据库时,会去容器中获取数据库连接对象,使用完毕后会归还给容器。
好处:
1.提高资源利用率
2.提高访问速度
DataSource
Java提供的接口,DataSource接口由框架编写者实现
方法
Connection getConnection()
获取连接
close()
归还连接对象,如果调用的是连接池中的Connection对象的close()方法,就不会关闭连接,
会将该连接归还给连接池
实现DataSource
一般情况下不需要我们实现此接口
1.C3P0
2.Driud 阿里巴巴
package demo01_连接池;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class D3P0Demo {
public static void main(String[] args) throws SQLException {
/*
1.导入jar包
2.添加配置文件
配置文件格式为xml格式
默认名称为c3p0-config.xml
3.获取数据源对象DataSource
4.通过DataSource获取数据库连接Connection
5.通过连接对象获取PreparedStatement,执行sql语句
6.遍历结果
7.关闭
*/
//3.获取数据源对象DataSource
//ctrl + shift + f
//ComboPooledDataSource会去项目scr目录下读取一个文件名为c3p0-config.xml文件
//默认大于配置
//读取myApp节点的配置
DataSource ds = new ComboPooledDataSource();
//读取<default-config>节点的配置
Connection conn = ds.getConnection();
String sql = "SELECT * FROM user";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()){
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
String email = rs.getString("email");
System.out.println(id + " "+username+ " "+password+ " "+email);
}
rs.close();
ps.close();
conn.close();
}
}
类介绍
jar包:使用时只需要导入到项目中
将驱动进行编译,驱动文件右键 -- ADD as Library
Driver接口
java.sql.Driver java提供的接口,所有的数据库厂商会实现此接口
com.mysql.jdbc.Driver 实现了java.sql.Driver接口
该接口是所有JDBC程序必须实现的接口,该接口专门提供给数据库厂商使用
Druid使用
package demo02_连接池;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;
public class DruidDemo {
public static void main(String[] args) throws Exception {
/*
1.导入jar包
2.添加配置文件
3.加载配置文件
4.获取DataSource
5.获取Connection
6.操作数据库
7.释放资源
*/
Properties pro = new Properties();
pro.load(DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties"));
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
Connection conn = ds.getConnection();
String sql = "inser into user(id,username,password,email) values(null,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,"wangwu1");
ps.setString(2,"123");
ps.setString(3,"123@qq.com");
int count = ps.executeUpdate();
System.out.println(count);
ps.close();
conn.close();
}
}
提取工具类
package demo02_连接池;
import org.junit.Test;
import utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DruidDemo2 {
@Test
public void t1() throws SQLException {
Connection conn = JDBCUtils.getConnection();
//查询user表
String sql = "SELECT * FROM user";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()){
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
String email = rs.getString("email");
System.out.println(id + " "+username+ " "+password+ " "+email);
}
JDBCUtils.close(rs,ps,conn);
}
}
package utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
*
* 1.获取数据连接
* 2.关闭资源
* 3.获取数据源对象
*
* 实现
*1.加载配置文件
*2.获取DateSource对象
*
*/
public class JDBCUtils {
static DataSource ds;
static {
Properties pro = new Properties();
try {
pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接对象的方法
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
//获取数据源对象
public static DataSource getDataSource(){
return ds;
}
//关闭资源
public static void close(ResultSet rs, Statement st,Connection conn){
if(rs !=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st !=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn !=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
在Java编译器中调用数据库并处理数据
package demo03_springjdbc;
import domain.User;
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.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
public class Demo01 {
JdbcTemplate jt = new JdbcTemplate(JDBCUtiuls.getDataSource());
@Test
public void t1(){
/*
0.准备数据源(Druid)
1.导入jar包
2.创建JDBCTemplate
3.执行sql语句
4.处理结果
*/
String sql = "update user set username = ? where id = ?";
int count = jt.update(sql,"zhaoliu",5);
System.out.println(count);
t2();
}
/**
* 2.添加数据
*/
@Test
public void t2(){
String sql = "inser into user(username,password,email)values(?,?,?);";
int count = jt.update(sql,"王五","123","123@qq.com");
System.out.println(count);
}
//删除数据
@Test
public void t3(){
String sql = "DELETE FROM USER WHERE ID = ?";
int count = jt.update(sql,2);
System.out.println(count);
}
/*
查询id为2的数据,并将数据封装为map集合
key = 列名
value = 列值
id 2
username laowang
password 123
email laowang@163.com
queryForMap 把查询到的数据转化为Map集合
*/
@Test
public void t4(){
String sql = "select * from user where id = ?";
Map<String, Object> map = jt.queryForMap(sql,2);
System.out.println(map);
}
/**
* 查询多行数据
* 将每一行数据封装成map集合
* 然后将每一行的map集合添加到list集合中
*/
@Test
public void t5(){
String sql = "select * from user where id = ?";
List<Map<String, Object>> list = jt.queryForList(sql);
System.out.println(list);
}
/**
* 将查询到的数据封装成User对象,并将其添加到list集合中
*/
@Test
public void t6(){
String sql = "select * from user";
//结果集
List<User> users = jt.query(sql, new RowMapper<User>() {
@Override
public User mapRow(ResultSet rs, int RowNum) throws SQLException {
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
String email = rs.getString("email");
User user = new User();
user.setId(id);
user.setUsername(username);
user.setPassword(password);
user.setEmail(email);
return user;
}
});
System.out.println(users);
}
/**
*将查询到的数据封装成User对象,并将其添加到list集合中
*/
@Test
public void t7(){
String sql = "select * from user";
//将列的值自动注入到User属性中(列名必须和属性名一致)
//通过反射获取所有属性名
//根据类的属性名去mao集合中获取数据
List<User> users = jt.query(sql,new BeanPropertyRowMapper<User>(User.class));
for (User user : users) {
System.out.println(user);
}
}
public void t8(){
String sql = "SELECT COUNT(*) FROM user";
Long count = jt.queryForObject(sql,Long.class);
System.out.println(count);
}
}
User类
package domain;
public class User {
private Integer id;
private String username;
private String password;
private String email;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", email='" + email + '\'' +
'}';
}
}
druid.properties:
driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql:///ls username=root password=root # 初始化连接数量 initialSize=5 # 最大连接数 maxActive=10 # 最大等待时间 maxWait=3000