原创声明:所有内容均由所学知识书写。
文章目录
前言
本文详细介绍如何利用druid工具包和dbutils工具包来简化dao工厂并连接数据库以及进行一系列的增删查改操作
git项目地址
一、环境准备
idea开发工具
mysql数据库8.0.17
准备好新建一个数据库
新建数据库代码以及表参考:
show databases;
create database if not exists demo;
use demo;
create table if not exists stuEmp(
`id` int(255) NOT NULL AUTO_INCREMENT,
`stu` varchar(255) not null,
`age` int(100) not null,
PRIMARY KEY(`id`)
);
这里我新建的数据库名叫做demo,新建在此库下新建了一个stuEmp表,里面的各种详细参数有id,stu(名字),age(年龄)。
二、工具包下载
工具包都可以百度搜索,找到官网下载。我这里提供我所使用的工具包下载链接:
提取码:6666
三、项目框架
四、具体细节
1. DaoFactory.class
package dao;
import java.io.IOException;
import java.util.Properties;
public class DaoFactory {
private static Properties prop = new Properties();
static {
try {
prop.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("properties/dao.properties"));
} catch (IOException e) {
System.err.println("在classpath下未找到dao.properties文件,请检查!");
e.printStackTrace();
}
}
public static Object getInstance(String daoName){
Object obj = null;
if(null != daoName && daoName.length()>0){
try {
obj = Class.forName(prop.getProperty(daoName)).newInstance();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
}else{
System.out.println("未输入指定Dao名称");
}
return obj;
}
}
2. StuDao.class
package dao;
import java.util.List;
import object.Stu;
public interface StuDao {
//提供一些常用的功能,例如数据库的增删查改
public void addElement(Stu stu);
public void delete(int id);//根据id删除元素
public List<Stu> findAll();//列出所有成员
public void update(Stu stu);//改
}
3. StuDaoImpl.class
package dao;
import static java.lang.Integer.parseInt;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import object.Stu;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import tool.ConnectionTool;
public class StuDaoImpl implements StuDao{
@Override
public void addElement(Stu stu) {
/*
insert into `stuEmp`(`stu`,`age`) values ('娄黔子',22);
*/
Connection conn = ConnectionTool.getConnectionByDataSource();
String sql = "INSERT INTO stuEmp(`stu`,`age`) VALUES (?,?)";
QueryRunner qr = new QueryRunner();
try {
conn.setAutoCommit(false);
String ro = qr.insert(conn, sql,new ScalarHandler<>(),stu.getStu(), stu.getAge()).toString();
int row = parseInt(ro);
System.out.println("新增了id为"+row+"的数据");
DbUtils.commitAndCloseQuietly(conn);
} catch (SQLException e) {
DbUtils.rollbackAndCloseQuietly(conn);
e.printStackTrace();
}
}
@Override
public void delete(int id) {
/*
delete from stuEmp where id = ?
*/
Connection conn = ConnectionTool.getConnectionByDataSource();
QueryRunner qr = new QueryRunner();
String sql = "delete from stuEmp where id = ?";
try {
conn.setAutoCommit(false);
int row = qr.update(conn, sql, id);
System.out.println("删除了id为"+id+"的数据");
DbUtils.commitAndCloseQuietly(conn);
} catch (SQLException e) {
DbUtils.rollbackAndCloseQuietly(conn);
e.printStackTrace();
}
}
@Override
public List<Stu> findAll() {
/*
select * from stuEmp;
*/
List<Stu> stuList = new ArrayList<>();
QueryRunner qr = new QueryRunner();
Connection conn = ConnectionTool.getConnectionByDataSource();
String sql = "select * from stuEmp;";
try {
stuList=qr.query(conn,sql,new BeanListHandler<>(Stu.class));
DbUtils.commitAndCloseQuietly(conn);
} catch (SQLException e) {
e.printStackTrace();
}
return stuList;
}
@Override
public void update(Stu stu) {
/*
update stuEmp set stu=?,age=? where id=?
*/
QueryRunner qr = new QueryRunner();
Connection conn = ConnectionTool.getConnectionByDataSource();
String sql = "update stuEmp set stu=?,age=? where id=?";
try {
conn.setAutoCommit(false);
int row = qr.update(conn, sql, stu.getStu(), stu.getAge(), stu.getId());
System.out.println("更新了"+row+"行数据.");
DbUtils.commitAndCloseQuietly(conn);
} catch (SQLException e) {
DbUtils.rollbackAndCloseQuietly(conn);
e.printStackTrace();
}
}
}
4. Stu.class
package object;
public class Stu {
private int id;
private String stu;
private int age;
public Stu() {
}
public Stu(int id, String stu, int age) {
this.id = id;
this.stu = stu;
this.age = age;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getStu() {
return stu;
}
public void setStu(String stu) {
this.stu = stu;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Stu{" +
"id=" + id +
", stu='" + stu + '\'' +
", age=" + age +
'}';
}
}
5. dao.properties
daoname=dao.StuDaoImpl
6. druid.properties
driverClass=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/demo?useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true&characterEncoding=UTF8&serverTimezone=Asia/Shanghai
username=root
password=123456
#druid配置初始化大小、最小、最大
initialSize=1
minIdle=10
maxActive=20
#配置获取连接等待超时的时间
maxWait=60000
#配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 'x'
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
#配置监控统计拦截的filters
filters=stat
#其他配置
maxOpenPreparedStatements=20
removeAbandoned=true
removeAbandonedTimeout=1800
logAbandoned=true
————————————————
版权声明:本文为CSDN博主「娄黔子」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/herojeo/article/details/114573893
7. ConnectionTool.class
package tool;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
public class ConnectionTool {
private static Properties prop = new Properties();
private static DruidDataSource dds =null;
static {
try {
prop.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("properties/druid.properties"));
dds = (DruidDataSource)DruidDataSourceFactory.createDataSource(prop);
dds.setUsername(prop.getProperty("username"));
dds.setPassword(prop.getProperty("password"));
dds.setUrl(prop.getProperty("url"));
dds.setDriverClassName(prop.getProperty("driverClass"));
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnectionByDataSource(){
Connection conn = null;
try {
conn = dds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
五、运行结果
1. run.class
import dao.DaoFactory;
import dao.StuDaoImpl;
import object.Stu;
public class Run {
public static void main(String[] args) {
StuDaoImpl st = (StuDaoImpl) DaoFactory.getInstance("daoname");
// st.addElement(new Stu(1,"王五",35));//增
// st.delete(6);//删
st.findAll().forEach(System.out::println);//查
// st.update(new Stu(1,"王五",35));//改
}
}
2. idea控制台模拟运行结果
这里只用了一行查的代码
后续可根据业务需求添加新的功能
该处使用的url网络请求的数据。