准备:
- 创建项目
- 导入jar包 工具类 配置文件
commons-dbutils-1.6.jar
druid-1.1.5.jar
DruidUtils.java工具类
database.properties配置文件
项目结构:
Druid工具类:
package afternoon.utils;
import com.alibaba.druid.pool.DruidDataSource;
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.SQLException;
import java.util.Properties;
/*
*2019/08/28
*/
public class DataSourceUtile {
static DruidDataSource ds;
static {
try {
Properties properties = new Properties();
InputStream is = DataSourceUtile.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(is);
ds = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static DataSource getConnection(){
try {
return ds;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("获取连接失败",e);
}
}
}
druid.properties配置文件如下:
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mystudent?useSSL=true&characterEncoding=utf8
username=root
password=root
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=5000
Emp实体类
package afternoon.domain;
import java.math.BigDecimal;
import java.util.Date;
/*
*2019/08/28
*/
public class Emp {
private int empno;
private String ename;
private String job;
private int mgr;
private Date hiredate;
private BigDecimal sal;
private BigDecimal comm;
private int deptno;
public Emp() {
}
public Emp(int empno, String ename, String job, int mgr, Date hiredate, BigDecimal sal, BigDecimal comm, int deptno) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public BigDecimal getSal() {
return sal;
}
public void setSal(BigDecimal sal) {
this.sal = sal;
}
public BigDecimal getComm() {
return comm;
}
public void setComm(BigDecimal comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
@Override
public String toString() {
return "Emp{" +
"empno=" + empno +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", mgr=" + mgr +
", hiredate=" + hiredate +
", sal=" + sal +
", comm=" + comm +
", deptno=" + deptno +
'}';
}
}
EmpDao接口编写:
package afternoon.Dao;
import afternoon.domain.Emp;
import java.util.List;
/*
*2019/08/28
*/
public interface EmpDao {
public List<Emp> findAll();
public Object findById(int empno);
public List<String> findNames();
public long getCounts();
public void add(Emp e);
public void update(Emp e);
public void delete(int empno);
}
DbUtils类:启动类
ResultSetHandler接口:转换类型接口
--ArrayHandler类:实现类,把记录转化成数组
--ArrayListHandler类:把记录转化成数组,并放入集合中
--ColumnListHandler类:取某一列的数据。封装到List中。
–ScalarHandle类:适合获取一行一列数据。
–BeanHandler类:实现类,把记录转成对象。
–BeanListHandler类:实现类,把记录转化成List,使记录为JavaBean类型的对象
QueryRunner类:执行SQL语句的类
EmpDaoImpl实现类的编写:
package afternoon.Dao.impl;
import afternoon.Dao.EmpDao;
import afternoon.domain.Emp;
import afternoon.utils.DataSourceUtile;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/*
*2019/08/28
*/
public class EmpDaoImpl implements EmpDao {
@Override
public List<Emp> findAll() {
//List<Emp> list = new ArrayList<>();
//创建查询执行器,并连接连接池
QueryRunner qr = new QueryRunner(DataSourceUtile.getConnection());
try {
return qr.query("select * from emp", new BeanListHandler<Emp>(Emp.class));
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("查询所员工信息失败",e);
}
}
@Override
public Object findById(int empno) {
//创建查询执行器,并连接连接池
QueryRunner qr = new QueryRunner(DataSourceUtile.getConnection());
//执行sql语句
try {
return qr.query("select * from emp where empno=?", new BeanHandler(Emp.class), empno);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("查询失败",e);
}
}
@Override
public List<String> findNames() {
//创建查询执行器,并连接连接池
QueryRunner qr = new QueryRunner(DataSourceUtile.getConnection());
//执行sql语句
try {
return qr.query("select * from emp",new ColumnListHandler<String>("ename"));
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("查询失败",e);
}
}
@Override
public long getCounts() {
//创建查询执行器,并获取连接池
QueryRunner qr = new QueryRunner(DataSourceUtile.getConnection());
//执行查询语句
try {
return qr.query("select Count(*) from emp", new ScalarHandler<>());
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("查询失败",e);
}
}
@Override
public void add(Emp e) {
//创建查询执行器,并指定连接池
QueryRunner qr = new QueryRunner(DataSourceUtile.getConnection());
Object[] params = {e.getEmpno(),e.getEname(),e.getJob(),e.getMgr(),e.getHiredate(),e.getSal(),e.getComm(),e.getDeptno()};
try {
qr.update("insert into emp(empno,ename,job,Mgr,Hiredate,sal,comm,deptno) values (?,?,?,?,?,?,?,?)",params);
} catch (SQLException ex) {
ex.printStackTrace();
System.out.println("添加失败");
}
}
@Override
public void update(Emp e) {
//创建查询执行器,并连接连接池
QueryRunner qr = new QueryRunner(DataSourceUtile.getConnection());
Object[] params = {e.getEname(),e.getJob(),e.getMgr(),e.getHiredate(),e.getSal(),e.getComm(),e.getDeptno(),e.getEmpno()};
//执行更新
try {
qr.update("update emp set ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? where empno=?;",params);
} catch (SQLException ex) {
ex.printStackTrace();
System.out.println("更新失败");
}
}
@Override
public void delete(int empno) {
//创建查询执行器,并连接池
QueryRunner qr = new QueryRunner(DataSourceUtile.getConnection());
//执行sql语句
try {
qr.update("delete from emp where empno=?",empno);
} catch (SQLException e) {
e.printStackTrace();
System.out.println("删除失败");
}
}
}
最后编写测试类test:
package afternoon.Test;
import afternoon.Dao.EmpDao;
import afternoon.Dao.impl.EmpDaoImpl;
import afternoon.domain.Emp;
import org.junit.Test;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
/*
*2019/08/28
*/
public class test {
@Test
public void testAdd(){
EmpDao empDao = new EmpDaoImpl();
Emp emp = new Emp(6080,"一行","拍点他",10,new Date(),new BigDecimal(800),new BigDecimal(800),10);
empDao.add(emp);
}
@Test
public void testUpdete(){
EmpDao empDao = new EmpDaoImpl();
Emp emp = new Emp(6080,"一行","拍电影",10,new Date(),new BigDecimal(800),new BigDecimal(800),10);
empDao.update(emp);
}
@Test
public void testDelete(){
EmpDao empDao = new EmpDaoImpl();
empDao.delete(6080);
System.out.println("删除成功");
}
@Test
public void testFindAll(){
EmpDao empDao = new EmpDaoImpl();
List<Emp> all = empDao.findAll();
for (Emp emp : all) {
System.out.println(emp.toString());
}
}
@Test
public void testFindById(){
EmpDao empDao = new EmpDaoImpl();
Object emp = empDao.findById(7369);
System.out.println(emp);
}
@Test
public void testFindNames(){
EmpDao empDao = new EmpDaoImpl();
List<String> names=empDao.findNames();
for (String name : names) {
System.out.println(name);
}
}
@Test
public void testGetCounts(){
EmpDao empDao = new EmpDaoImpl();
long count = empDao.getCounts();
System.out.println(count);
}
}
数据库emp表创建语句:
CREATE TABLE emp(
empno int,
ename varchar(50),
job varchar(50),
mgr int,
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno int
);
数据库添加语句
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);