文章目录
1.连接池原理
1.创建一个集合，用于存放连接
2.驱动加载
3.指定数量的连接对象，存入集合
4.从集合中拿出连接来使用，将该连接对象从集合中移除
5.连接释放：本质是将该连接对象放回集合
具体实现如下：（这里是使用了配置文件来存储配置的变量，所以多了一个加载配置文件的步骤）
2.简单连接池的实现
SQL语句建表：
create table department
(
id int not null primary key auto_increment comment'部门编号',
name varchar(30) not null comment'部门名',
description varchar(50) not null comment'部门描述',
state int not null default 1 comment'1为启用，0为禁用,默认为1'
);
1.配置文件：db.properties;
#这里是你的数据库驱动
jdbc_driver=com.mysql.cj.jdbc.Driver
#你的数据库地址和名称
jdbc_url=jdbc:mysql://localhost:3306/empdb?useSSL=false&serverTimezone=GMT&allowMultiQueries=true
#你的用户名，一般为root,也可以用其他有操作权限的用户
jdbc_user=root
#你的密码
jdbc_password=123
#连接池数量
pool_size=30
2.数据库连接池：CommPool
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Vector;
/**
* @Description:自己写一个数据库连接池
* @Author one world
* @Date 2020/8/12 0012 17:04
*/
public final class CommPool {
//创建Vector集合，用于存储连接对象
private static Vector<Connection> pool = null;
//本类的连接池对象
private static CommPool commPool = null;
//连接池大小
private static int pool_size;
//驱动名
private static String driver_name;
//数据库连接地址
private static String url;
//数据库用户
private static String user;
//用户密码
private static String password;
static {
load();
try {
//加载驱动
Class.forName(driver_name);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//实例化本类对象
commPool = new CommPool();
}
private CommPool(){
pool = new Vector<>(pool_size);
init();
}
/**
* 用于加载配置文件
*/
private static void load(){
//读取配置文件方式1
//这里有个问题，我用一下方式加载文件时会报空指针异常，但是第二天相同代码就又可以了
InputStream in =CommPool.class.getResourceAsStream("db.properties");
// 第一天我修改为了
//InputStream in =CommPool.class.getClassLoader().getResourceAsStream("db.properties");
//这就可以了，建议使用报错时，可以试试清除缓存试试
try {
//读取配置文件方式2
//InputStream in = new FileInputStream("db1.properties");
Properties props = new Properties();//创建一个Proterties对象,解析文件
props.load(in);
//从配置中读取配置
pool_size = Integer.parseInt(props.getProperty("pool_size"));
driver_name = props.getProperty("jdbc_driver");
url = props.getProperty("jdbc_url");
user = props.getProperty("jdbc_user");
password = props.getProperty("jdbc_password");
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 初始化连接池
*/
private void init(){
//创建连接对象，并存放入集合
for(int i=0;i<pool_size;i++){
try {
Connection connection = DriverManager.getConnection(url, user, password);
// System.out.println(connection);
pool.add(connection);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获取本类实例
*/
public static CommPool getInstance(){
return commPool;
}
/**
* 从数据库连接池获取连接
*/
public synchronized Connection getConnection(){
Connection conn = null;
//当连接池里还有连接对象时，从连接池中拿出
if(pool.size()>0){
conn = pool.firstElement();
pool.remove(0);
}else{
//当连接池里没有时，创建新的连接给调用者使用
try {
conn = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
}
return conn;
}
/**
* 连接对象用完以后放入连接池
*/
public synchronized void close(Connection conn){
pool.add(conn);
}
}
3.基于数据库连接池写SQL查询和修改的封装
/**
* @Description:封装更新和结果集返回
* @Author one world
* @Date 2020/8/12 0012 19:37
*/
public class JDBCUtil {
//获取连接池实例
static CommPool pool = CommPool.getInstance();
//封装的增删改方法 params为对象数组，可变参数可以有一个或多个，也可以没有
public static boolean executeUpdate(String sql,Object...params){
//从连接池获取连接
Connection conn = pool.getConnection();
try {
//获取执行对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//判断有无参数，有参数时将pstmt对象中的占位符赋值
if(params.length>0){
for(int i=0;i<params.length;i++){
pstmt.setObject(i+1, params[i]);
}
}
//是否执行成功
return pstmt.executeUpdate()>0;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
//查询结果集的返回，返回给Dao来进行具体对象的封装
public static ResultSet executeQuery(String sql,Object...params){
//声明结果集对象
ResultSet rs = null;
//从连接池获取连接
Connection conn = pool.getConnection();
try {
//创建SQL执行对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//判断传入的参数个数
if(params.length>0){
for(int i=0;i<params.length;i++){
//赋值
pstmt.setObject(i+1, params[i]);
}
}
//执行，得到结果集
rs = pstmt.executeQuery();
}catch (SQLException e) {
e.printStackTrace();
}
//结果集返回
return rs;
}
}
下面以部门类为例：
1.部门类：
/**
* @Description:
* @Author one world
* @Date 2020/8/11 0011 17:12
*/
public class Department {
//属性
private int id;
private String name;
private String description;
private int state;
//构造方法
public Department() {
super();
}
//构造方法
public Department(int id, String name, int state) {
super();
this.id = id;
this.name = name;
this.state = state;
}
//重写父类中的方法
public String toString(){
String result = null;
result = ""+id+" "+name+" "+state;
return result;
}
//getter & setter
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public int getState() {
return state;
}
public void setState(int state) {
this.state = state;
}
}
2.部门DAO接口
/**
* @Description:部门操作接口
* @Author one world
* @Date 2020/8/11 0011 17:35
*/
public interface DepartmentDao {
/**
* 查询所有部门
* @return 部门集合
*/
public List<Department> findAllDepartments();
/**
* 添加部门
* @param department
* @return true/false
*/
public boolean addDepartment(Department department);
/**
* 更新部门信息
* @param department
* @return
*/
public boolean updateDepartment(Department department);
/**
* 删除部门
* @param id
* @return
*/
public boolean deleteDepartmentById(int id);
/**
* 查找部门
* @param id
* @return
*/
public Department findDepartmentById(int id);
}
3.部门接口实现类：
public class EmployeeDaoImpl2 implements EmployeeDao {
JDBCUtil jdbcutil = new JDBCUtil();
@Override
public List<Employee> findAllEmployees() {
List<Employee> list =new ArrayList<>();
String sql = "select * from employee";
ResultSet resultSet = JDBCUtil.executeQuery(sql);
try {
while(resultSet.next()) {
Employee emp = new Employee();
emp.setId(resultSet.getInt("id"));
emp.setPhone(resultSet.getString("phone"));
emp.setAddress(resultSet.getString("address"));
emp.setBirth(resultSet.getTimestamp("birth"));
Department department = new Department();
department.setId(resultSet.getInt("depid"));
emp.setDepartment(department);
emp.setName(resultSet.getString("name"));
emp.setEmail(resultSet.getString("email"));
list.add(emp);
}
}catch(SQLException e){
}
return list;
}
@Override
public Employee findEmployeeById(int id) {
Employee emp = new Employee();
try {
String sql = "select * from employee where id = ?";
ResultSet resultSet = JDBCUtil.executeQuery(sql,id);
Department department = new Department();
while(resultSet.next()){
emp.setId(resultSet.getInt("id"));
emp.setPhone(resultSet.getString("phone"));
emp.setAddress(resultSet.getString("address"));
emp.setBirth(resultSet.getTimestamp("birth"));
department.setId(resultSet.getInt("depid"));
emp.setDepartment(department);
emp.setName(resultSet.getString("name"));
emp.setEmail(resultSet.getString("email"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return emp;
}
@Override
public List<Employee> findEmployeesByDepartmentId(int departmentId) {
List<Employee> list = null;
String sql = "select * from employee where depid = ?";
ResultSet resultSet = JDBCUtil.executeQuery(sql, departmentId);
try {
while(resultSet.next()) {
Employee emp = new Employee();
emp.setId(resultSet.getInt("id"));
emp.setPhone(resultSet.getString("phone"));
emp.setAddress(resultSet.getString("address"));
emp.setBirth(resultSet.getTimestamp("birth"));
Department department = new Department();
department.setId(resultSet.getInt("depid"));
emp.setDepartment(department);
emp.setName(resultSet.getString("name"));
emp.setEmail(resultSet.getString("email"));
list.add(emp);
}
}catch(SQLException e){
}
return list;
}
@Override
public boolean addEmployee(Employee employee) {
String sql = "insert into employee values(default,?,?,?,?,?,?)";
Object[] params = new Object[]{employee.getName(),employee.getBirth(),employee.getPhone(),employee.getEmail(),employee.getAddress(), employee.getDepartment().getId()};
return JDBCUtil.executeUpdate(sql, params);
}
@Override
public boolean updateEmployee(Employee employee) {
String sql = "update employee set name=?,birth=?,phone=?,email=?,address=?,depid=? where id =?";
Object[] params = new Object[]{employee.getName(),employee.getBirth(),employee.getPhone(),employee.getEmail(),employee.getAddress(), employee.getDepartment().getId(),employee.getId()};
return JDBCUtil.executeUpdate(sql, params);
}
@Override
public boolean deleteEmployeeById(int id) {
String sql = "delete from employee where id=?";
return JDBCUtil.executeUpdate(sql, id);
}
}
4.测试类的编写：
public class TestDepartmentDao {
public static void main(String[] args) {
DepartmentDao depdao = new DepartmentDaoImpl2();
//测试数据
Department department = new Department();
department.setName("人文部");
department.setDescription("人员招聘");
System.out.println("1.查询所有部门");
System.out.println(depdao.findAllDepartments());
System.out.println("2.添加部门");
System.out.println(depdao.addDepartment(department));
System.out.println("3.删除部门");
System.out.println(depdao.deleteDepartmentById(9));
System.out.println("4.查找指定部门：");
System.out.println(depdao.findDepartmentById(5));
Department newDep = new Department();
newDep.setName("发展部");
newDep.setId(6);
newDep.setDescription("企业未来发展规划");
System.out.println("5.更新部门信息：");
System.out.println(depdao.updateDepartment(newDep));
}
}
4.druid的使用
1.创建druid.properties，存储具体配置信息
#druid.properties文件的配置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/empdb?useUnicode=true&characterEncoding=utf-8&serverTimezone=Hongkong
username=root
password=123
#初始化连接数量
initialSize=5
#最大连接数
maxActive=10
#最大超时时间
maxWait=3000
2.读取并设置属性
/**
* @Description:
* @Author one world
* @Date 2020/8/13 0013 10:58
*/
public class Druid {
//阿里提供的druiddatasource类
private static DruidDataSource druid = new DruidDataSource();
//驱动名
private static String driverClassName;
//数据库url
private static String url;
//数据库用户名
private static String userName;
//数据库密码
private static String password;
static {
load();
}
//加载配置文件并给本类设置属性
private static void load(){
//读取配置文件 读取文件的方式可以有很多种
InputStream in = Druid.class.getResourceAsStream("/druid.properties");
Properties props = new Properties();
try {
props.load(in);
driverClassName = props.getProperty("driverClassName");
url = props.getProperty("url");
userName = props.getProperty("username");
password = props.getProperty("password");
//调用DruidDatascorce的方法给druid赋值
druid.setDriverClassName(driverClassName);
druid.setUrl(url);
druid.setUsername(userName);
druid.setPassword(password);
} catch (IOException e) {
e.printStackTrace();
}
}
public static DruidDataSource getDruid(){
return druid;
}
}
3测试：(还是用的上面的数据库)
/**
* @Description:
* @Author one world
* @Date 2020/8/13 0013 11:09
*/
public class TestDruid {
public static void main(String[] args) {
try {
List<Department> deps = new ArrayList<>();
Connection conn = Druid.getDruid().getConnection();
String sql = "select * from department ";
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()){
Department dep = new Department();
dep.setId(rs.getInt("id"));
dep.setName(rs.getString("name"));
dep.setDescription(rs.getString("description"));
dep.setState(rs.getInt("state"));
deps.add(dep);
}
System.out.println(deps);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
4.效果及源码：
扫一扫
690
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
