DaoUtils(Object类型方法)
在Dao层的DaoImpl类中,对数据表进行的增、删、改、查操作方法中存在很多相同的代码,可以将这些相同的代码抽取出来封装到一个类中,形成DaoUtils工具类,实现复用。以查询teacher表为例。
commonsUpdae方法
该方法是对数据表进行增、删、改的公共方法。
// 增、删、改三个方法封装成一个方法
public int commonsUpdate(String sql, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
// 获取数据库连接对象
connection = DBUtils.getConnection();
try {
// 获取SQL语句发送对象
preparedStatement = connection.prepareStatement(sql);
if (args != null) {
// 绑定参数
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
}
// 执行SQL语句
int result = preparedStatement.executeUpdate();
return result;
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(null, preparedStatement, null);
}
return 0;
}
commonsSelect方法
该方法是对数据表进行查询的公共方法,可以查询一条数据,也可以查询多条数据。不管是查询一条数据还是查询多条数据,都将查询到的数据封装到Object类型的集合中。
public List<Object> commonsSelect(String sql,RowMapper rowMapper, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<Object> objectList = new ArrayList<Object>();
// 获取数据库连接对象
connection = DBUtils.getConnection();
try {
// 获取SQL语句发送对象
preparedStatement = connection.prepareStatement(sql);
if (args != null) {
// 绑定参数
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
}
// 执行SQL语句
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
Object object = rowMapper.getRow(resultSet);
objectList.add(object);
}
return objectList;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(null, preparedStatement, resultSet);
}
return null;
}
RowMapper类封装resultSet结果集
使用查询方法从表中查询出来的结果存储在ResultSet结果集中,需要从ResultSet结果集中遍历出数据封装成对应对象,然后将封装出来的对象返回给DaoImpl类使用。这个封装的过程由RowMapper类完成。
package com.cxyzxc.www.examples01;
/**
* 数据封装类,专门用来处理查询返回的resultSet结果集
*
* 针对每一个实体类,封装一个RowMapper类
*/
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class RowMapper {
public static Object getRow(ResultSet resultSet){
Teacher teacher = null;
try {
//返回的结果集中有哪些字段,RowMapper类是知道的,因为他是实体类的封装类
int tid = resultSet.getInt("tid");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
Date bornDate = resultSet.getDate("bornDate");
String email = resultSet.getString("email");
String address = resultSet.getString("address");
teacher = new Teacher(tid, name, age, bornDate, email, address);
return teacher;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
综合案例
综合案例完全按照三层架构实现,创建不同的包存放不同的接口、实现类、工具类、测试类等代码。使用数据库中的teacher表来完成综合案例。
com.cxyzxc.www.utils包
该包用来存放项目开发所需要的数据库连接工具类DBUtils类、日期时间转换工具类DateUtils类,数据库操作工具类DaoUtils类
DaoUtils工具类完整代码
package com.cxyzxc.www.utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.cxyzxc.www.advanced.RowMapper;
public class DaoUtils {
// 增、删、改三个方法封装成一个方法
public int commonsUpdate(String sql, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
// 获取数据库连接对象
connection = DBUtils.getConnection();
try {
// 获取SQL语句发送对象
preparedStatement = connection.prepareStatement(sql);
if (args != null) {
// 绑定参数
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
}
// 执行SQL语句
int result = preparedStatement.executeUpdate();
return result;
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(null, preparedStatement, null);
}
return 0;
}
public List<Object> commonsSelect(String sql,RowMapper rowMapper, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<Object> objectList = new ArrayList<Object>();
// 获取数据库连接对象
connection = DBUtils.getConnection();
try {
// 获取SQL语句发送对象
preparedStatement = connection.prepareStatement(sql);
if (args != null) {
// 绑定参数
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
}
// 执行SQL语句
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
Object object = rowMapper.getRow(resultSet);
objectList.add(object);
}
return objectList;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(null, preparedStatement, resultSet);
}
return null;
}
}
DBUtils工具类完整代码
package com.cxyzxc.www.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* 跨平台方案 注册驱动 获取连接 释放资源
*
* @author 程序员张小厨
*
*/
public class DBUtils {
// 读取配置文件的Map
private static final Properties PROPERTIES = new Properties();
// 声明一个ThreadLocal<Connection>对象用来存储数据库连接对象
private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
static {
// 通过复用本类自带流,读取配置文件中的数据
InputStream is = DBUtils.class.getResourceAsStream("/db.properties");
try {
// 通过prop对象将流中的配置信息分隔成键值对,将配置文件内容加载到properties集合
PROPERTIES.load(is);
// 注册驱动,通过driverName的键获取对应的值(com.mysql.jdbc.Driver)
Class.forName(PROPERTIES.getProperty("driver"));
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 获取连接对象
public static Connection getConnection() {
// 将当前线程中绑定的Connection对象赋值给connection变量
Connection connection = threadLocal.get();
try {
// 如果连接对象为null,则创建一个连接对象
if (connection == null) {
connection = DriverManager.getConnection(
PROPERTIES.getProperty("url"),
PROPERTIES.getProperty("username"),
PROPERTIES.getProperty("password"));
// 将创建的连接对象存储到当前线程中共享
threadLocal.set(connection);
}
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
// 释放所有资源
public static void closeAll(Connection connection, Statement statement,
ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
// 将connection从threadLocal中移除
threadLocal.remove();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 3、开启事务
public static void startTransaction() {
Connection connection = null;
try {
connection = getConnection();
connection.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 4、提交事务
public static void commitTransaction() {
Connection connection = getConnection();
try {
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(connection, null, null);
}
}
// 5、回滚事务
public static void rollbackTransaction() {
Connection connection = getConnection();
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(connection, null, null);
}
}
}
DateUtils工具类完整代码
package com.cxyzxc.www.utils;
import java.text.ParseException;
import java.text.SimpleDateFormat;
/**
* 日期时间工具类DateUtils
*
* @author 程序员张小厨
*
*/
public class DateUtils {
private static final SimpleDateFormat SIMPLEDATEFORMAT = new SimpleDateFormat("yyyy-MM-dd");
// 字符串转换为java.util.Date类型日期时间
public static java.util.Date strDateToUtilDate(String strDate) {
try {
return SIMPLEDATEFORMAT.parse(strDate);
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
// java.util.Date类型日期时间转换为java.sql.Date类型日期时间
public static java.sql.Date utilDateToSqlDate(java.util.Date date) {
// long date.getTime():返回自 1970 年 1 月 1 日 00:00:00 GMT以来此 Date对象表示的毫秒数
return new java.sql.Date(date.getTime());
}
// java.util.Date类转换为字符串类型
public static String utilDateToString(java.util.Date date) {
return SIMPLEDATEFORMAT.format(date);
}
}
com.cxyzxc.www.entity包
该包用来存储实体类,根据teacher表创建实体类Teacher类
package com.cxyzxc.www.entity;
import java.util.Date;
public class Teacher {
/** 教师编号 */
private int tid;
/** 姓名 */
private String name;
/** 年龄 */
private int age;
/** 出生日期 */
private Date bornDate;
/** 邮箱 */
private String email;
/** 住址 */
private String address;
// 无参构造方法
public Teacher() {
super();
}
// 有参构造方法
public Teacher(String name, int age, Date bornDate, String email,
String address) {
super();
this.name = name;
this.age = age;
this.bornDate = bornDate;
this.email = email;
this.address = address;
}
public Teacher(int tid, String name, int age, Date bornDate, String email,
String address) {
super();
this.tid = tid;
this.name = name;
this.age = age;
this.bornDate = bornDate;
this.email = email;
this.address = address;
}
// getXxx()/setXxx()方法
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
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 Date getBornDate() {
return bornDate;
}
public void setBornDate(Date bornDate) {
this.bornDate = bornDate;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
// 重写Object类中的toString()方法
@Override
public String toString() {
return "tid=" + tid + ", name=" + name + ", age=" + age + ", bornDate="
+ bornDate + ", email=" + email + ", address=" + address;
}
}
com.cxyzxc.www.advanced包
该包中存放RowMapper接口,用来约束封装对象的ORM
package com.cxyzxc.www.advanced;
import java.sql.ResultSet;
/**
* 该接口用来约束封装对象的ORM
*
* @author 程序员张小厨
*
*/
public interface RowMapper {
Object getRow(ResultSet resultSet);
}
com.cxyzxc.www.advanced.impl包
package com.cxyzxc.www.advanced.impl;
/**
* 数据封装类,专门用来处理查询返回的resultSet结果集
*
* 针对每一个实体类,封装一个RowMapper类
*/
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import com.cxyzxc.www.advanced.RowMapper;
import com.cxyzxc.www.entity.Teacher;
public class TeacherRowMapper implements RowMapper {
@Override
public Object getRow(ResultSet resultSet) {
Teacher teacher = null;
try {
// 返回的结果集中有哪些字段,RowMapper类是知道的,因为他是实体类的封装类
int tid = resultSet.getInt("tid");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
Date bornDate = resultSet.getDate("bornDate");
String email = resultSet.getString("email");
String address = resultSet.getString("address");
teacher = new Teacher(tid, name, age, bornDate, email, address);
return teacher;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
com.cxyzxc.www.dao包
该包用来存放操作数据库的接口
package com.cxyzxc.www.dao;
import java.util.List;
import com.cxyzxc.www.entity.Teacher;
public interface TeacherDao {
//增
int insert(Teacher teacher);
//删
int delete(int tid);
//改
int update(Teacher teacher);
//查单个
Teacher selectOne(int tid);
//查所有
List<Teacher> selectAll();
}
com.cxyzxc.www.dao.impl包
该包用来存放数据库操作接口的实现类
package com.cxyzxc.www.dao.impl;
import java.util.ArrayList;
import java.util.List;
import com.cxyzxc.www.advanced.RowMapper;
import com.cxyzxc.www.advanced.impl.TeacherRowMapper;
import com.cxyzxc.www.dao.TeacherDao;
import com.cxyzxc.www.entity.Teacher;
import com.cxyzxc.www.utils.DaoUtils;
/**
*
* @author 程序员张小厨
*
*/
public class TeacherDaoImpl implements TeacherDao {
/*
* (1)该类中提供对teacher表进行增、删、改、查单个、查所有5个方法。
*
* (2)该类中的代码只做数据库访问操作,不做任何业务逻辑操作。
*
* (3)该类只对数据库一张表进行操作,从而实现复用
*/
DaoUtils daoUtils = new DaoUtils();
RowMapper rowMapper = new TeacherRowMapper();
// 新增:向teacher表中插入一条数据(一条数据对应一个Teacher对象),插入成功,返回一个受影响行数值(int类型)
@Override
public int insert(Teacher teacher) {
String sql = "insert into `teacher`(name,age,bornDate,email,address) values(?,?,?,?,?);";
// 赋值
Object[] args = { teacher.getName(), teacher.getAge(),
teacher.getBornDate(), teacher.getEmail(), teacher.getAddress() };
return daoUtils.commonsUpdate(sql, args);
}
// 删除:根据教师tid删除一条数据,删除成功,返回一个受影响行数值(int类型)
@Override
public int delete(int tid) {
String sql = "delete from teacher where tid = ?;";
return daoUtils.commonsUpdate(sql, tid);
}
// 修改:修改teacher表中的数据,可能对任意的一个字段进行修改,所以方法中直接传递一个对象进行修改,修改成功,返回一个受影响行数值(int类型)
@Override
public int update(Teacher teacher) {
String sql = "update teacher set name = ?,age = ?,bornDate = ?,email = ?,address = ? where tid = ?;";
Object[] args = { teacher.getName(), teacher.getAge(),
teacher.getBornDate(), teacher.getEmail(),
teacher.getAddress(), teacher.getTid() };
return daoUtils.commonsUpdate(sql, args);
}
// 查询单个:根据教师tid查询一条数据,查询成功返回一个结果集(ResultSet类型),从结果集中取出元素,封装成一个Teacher对象,将该对象返回
@Override
public Teacher selectOne(int tid) {
String sql = "select * from teacher where tid = ?;";
List<Object> objectList = daoUtils.commonsSelect(sql, rowMapper, tid);
if (objectList.size() == 1) {
Teacher teacher = (Teacher) objectList.get(0);
return teacher;
} else {
return null;
}
}
// 查询所有:将teacher表中的所有数据全部查询出来,查询成功返回一个结果集(ResultSet类型),从结果集中取出元素,封装成多个Teacher对象,将多个对象存储在集合中,返回这个集合
@Override
public List<Teacher> selectAll() {
List<Teacher> teacherList = new ArrayList<Teacher>();
String sql = "select * from teacher;";
DaoUtils daoUtils = new DaoUtils();
Object[] args = null;
List<Object> objectList = daoUtils.commonsSelect(sql, rowMapper, args);
for (int i = 0; i < objectList.size(); i++) {
teacherList.add((Teacher) objectList.get(i));
}
return teacherList;
}
}
com.cxyzxc.www.service包
该包用来存储业务层接口代码
package com.cxyzxc.www.service;
import java.util.List;
import com.cxyzxc.www.entity.Teacher;
public interface TeacherService {
//添加老师
int addTeacher(Teacher teacher);
//删除老师
int deleteTeacher(int tid);
//修改老师
int updateTeacher(Teacher teacher);
//查询一个老师
Teacher selectOne(int tid);
//查询所有老师
List<Teacher> selectAll();
}
com.cxyzxc.www.service.impl包
该包用来存储service层实现类代码
package com.cxyzxc.www.service.impl;
import java.util.List;
import com.cxyzxc.www.dao.impl.TeacherDaoImpl;
import com.cxyzxc.www.entity.Teacher;
import com.cxyzxc.www.service.TeacherService;
public class TeacherServiceImpl implements TeacherService {
@Override
public int addTeacher(Teacher teacher) {
TeacherDaoImpl teacherDaoImpl = new TeacherDaoImpl();
Teacher teacher2 = teacherDaoImpl.selectOne(teacher.getTid());
if (teacher2 == null) {
return teacherDaoImpl.insert(teacher);
} else {
System.out.println("老师已存在");
}
return 0;
}
@Override
public int deleteTeacher(int tid) {
TeacherDaoImpl teacherDaoImpl = new TeacherDaoImpl();
Teacher teacher2 = teacherDaoImpl.selectOne(tid);
if (teacher2 == null) {
System.out.println("你要删除的老师不存在,无法删除");
} else {
return teacherDaoImpl.delete(tid);
}
return 0;
}
@Override
public int updateTeacher(Teacher teacher) {
TeacherDaoImpl teacherDaoImpl = new TeacherDaoImpl();
Teacher teacher2 = teacherDaoImpl.selectOne(teacher.getTid());
if (teacher2 == null) {
System.out.println("你要修改的老师不存在,无法修改");
} else {
return teacherDaoImpl.update(teacher);
}
return 0;
}
@Override
public Teacher selectOne(int tid){
TeacherDaoImpl teacherDaoImpl = new TeacherDaoImpl();
Teacher teacher =teacherDaoImpl.selectOne(tid);
return teacher;
}
@Override
public List<Teacher> selectAll(){
TeacherDaoImpl teacherDaoImpl = new TeacherDaoImpl();
return teacherDaoImpl.selectAll();
}
}
com.cxy.zxc.view包
该包用来存储测试类
测试插入数据
package com.cxyzxc.www.view;
import com.cxyzxc.www.entity.Teacher;
import com.cxyzxc.www.service.TeacherService;
import com.cxyzxc.www.service.impl.TeacherServiceImpl;
import com.cxyzxc.www.utils.DateUtils;
public class TestTeacherServiceImpl01Insert {
public static void main(String[] args) {
TeacherService teacherService = new TeacherServiceImpl();
Teacher teacher = new Teacher(1004, "王五", 30, DateUtils.strDateToUtilDate("1992-10-07"), "13645678912@qq.com", "安徽合肥庐阳区");
int result =teacherService.addTeacher(teacher);
if(result!=0){
System.out.println("老师添加成功");
}
}
}
测试删除数据
package com.cxyzxc.www.view;
import com.cxyzxc.www.service.TeacherService;
import com.cxyzxc.www.service.impl.TeacherServiceImpl;
public class TestTeacherServiceImpl02Delete {
public static void main(String[] args) {
TeacherService teacherService = new TeacherServiceImpl();
int result =teacherService.deleteTeacher(1004);
if(result!=0){
System.out.println("删除成功");
}
}
}
测试修改数据
package com.cxyzxc.www.view;
import com.cxyzxc.www.entity.Teacher;
import com.cxyzxc.www.service.TeacherService;
import com.cxyzxc.www.service.impl.TeacherServiceImpl;
import com.cxyzxc.www.utils.DateUtils;
public class TestTeacherServiceImpl03Update {
public static void main(String[] args) {
TeacherService teacherService = new TeacherServiceImpl();
Teacher teacher = new Teacher(1003, "王五", 30,
DateUtils.strDateToUtilDate("1992-08-07"),
"13677881122@qq.com", "安徽合肥政务区");
int result = teacherService.updateTeacher(teacher);
if(result!=0){
System.out.println("修改成功");
}
}
}
测试查询一条数据
package com.cxyzxc.www.view;
import com.cxyzxc.www.entity.Teacher;
import com.cxyzxc.www.service.TeacherService;
import com.cxyzxc.www.service.impl.TeacherServiceImpl;
public class TestTeacherServiceImpl04SelectOne {
public static void main(String[] args) {
TeacherService teacherService = new TeacherServiceImpl();
Teacher teacher = teacherService.selectOne(1002);
if (teacher != null) {
System.out.println(teacher);
} else {
System.out.println("你查询的老师不存在");
}
}
}
测试查询所有数据
package com.cxyzxc.www.view;
import java.util.List;
import com.cxyzxc.www.entity.Teacher;
import com.cxyzxc.www.service.TeacherService;
import com.cxyzxc.www.service.impl.TeacherServiceImpl;
public class TestTeacherServiceImpl05SelectAll {
public static void main(String[] args) {
TeacherService teacherService = new TeacherServiceImpl();
List<Teacher> listTeacher = teacherService.selectAll();
for (int i = 0; i < listTeacher.size(); i++) {
System.out.println(listTeacher.get(i));
}
}
}
DaoUtils(泛型类型方法)
commonsUpdate方法
该方法是对数据表进行增、删、改的公共方法。与17.1小节中的代码一样,不再赘述。
commonsSelect方法
该方法是对数据表进行查询的公共方法,可以查询一条数据,也可以查询多条数据。不管是查询一条数据还是查询多条数据,都将查询到的数据封装到泛型类型的集合中。
public List<T> commonsSelect(String sql,RowMapper<T> rowMapper, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<T> list = new ArrayList<T>();
// 获取数据库连接对象
connection = DBUtils.getConnection();
try {
// 获取SQL语句发送对象
preparedStatement = connection.prepareStatement(sql);
if (args != null) {
// 绑定参数
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
}
// 执行SQL语句
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
T t = rowMapper.getRow(resultSet);
list.add(t);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(null, preparedStatement, resultSet);
}
return null;
}
RowMapper类封装resultSet结果集
RowMapper接口:
package com.cxyzxc.www.advanced;
import java.sql.ResultSet;
/**
* 该接口用来约束封装对象的ORM
*
* @author 程序员张小厨
* @param <T>
*
*/
public interface RowMapper<T> {
T getRow(ResultSet resultSet);
}
RowMapper接口实现类:
package com.cxyzxc.www.advanced.impl;
/**
* 数据封装类,专门用来处理查询返回的resultSet结果集
*
* 针对每一个实体类,封装一个RowMapper类
*/
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import com.cxyzxc.www.advanced.RowMapper;
import com.cxyzxc.www.entity.Teacher;
public class TeacherRowMapper implements RowMapper<Teacher> {
@Override
public Teacher getRow(ResultSet resultSet) {
Teacher teacher = null;
try {
// 返回的结果集中有哪些字段,RowMapper类是知道的,因为他是实体类的封装类
int tid = resultSet.getInt("tid");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
Date bornDate = resultSet.getDate("bornDate");
String email = resultSet.getString("email");
String address = resultSet.getString("address");
teacher = new Teacher(tid, name, age, bornDate, email, address);
return teacher;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
综合案例
综合案例完全按照三层架构实现,创建不同的包存放不同的接口、实现类、工具类、测试类等代码。使用数据库中的teacher表来完成综合案例。
com.cxyzxc.www.utils包
该包用来存放项目开发所需要的数据库连接工具类DBUtils类、日期时间转换工具类DateUtils类,数据库操作工具类DaoUtils类
DaoUtils工具类完整代码
package com.cxyzxc.www.utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.cxyzxc.www.advanced.RowMapper;
public class DaoUtils<T> {
// 增、删、改三个方法封装成一个方法
public int commonsUpdate(String sql, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
// 获取数据库连接对象
connection = DBUtils.getConnection();
try {
// 获取SQL语句发送对象
preparedStatement = connection.prepareStatement(sql);
if (args != null) {
// 绑定参数
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
}
// 执行SQL语句
int result = preparedStatement.executeUpdate();
return result;
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(null, preparedStatement, null);
}
return 0;
}
public List<T> commonsSelect(String sql,RowMapper<T> rowMapper, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<T> list = new ArrayList<T>();
// 获取数据库连接对象
connection = DBUtils.getConnection();
try {
// 获取SQL语句发送对象
preparedStatement = connection.prepareStatement(sql);
if (args != null) {
// 绑定参数
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
}
// 执行SQL语句
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
T t = rowMapper.getRow(resultSet);
list.add(t);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(null, preparedStatement, resultSet);
}
return null;
}
}
重复代码
DBUtils工具类完整代码
DateUtils工具类完整代码
com.cxyzxc.www.entity包
com.cxyzxc.www.advanced包
该包中存放RowMapper接口,用来约束封装对象的ORM
package com.cxyzxc.www.advanced;
import java.sql.ResultSet;
/**
* 该接口用来约束封装对象的ORM
*
* @author 程序员张小厨
* @param <T>
*
*/
public interface RowMapper<T> {
T getRow(ResultSet resultSet);
}
com.cxyzxc.www.advanced.impl包
package com.cxyzxc.www.advanced.impl;
/**
* 数据封装类,专门用来处理查询返回的resultSet结果集
*
* 针对每一个实体类,封装一个RowMapper类
*/
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import com.cxyzxc.www.advanced.RowMapper;
import com.cxyzxc.www.entity.Teacher;
public class TeacherRowMapper implements RowMapper<Teacher> {
@Override
public Teacher getRow(ResultSet resultSet) {
Teacher teacher = null;
try {
// 返回的结果集中有哪些字段,RowMapper类是知道的,因为他是实体类的封装类
int tid = resultSet.getInt("tid");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
Date bornDate = resultSet.getDate("bornDate");
String email = resultSet.getString("email");
String address = resultSet.getString("address");
teacher = new Teacher(tid, name, age, bornDate, email, address);
return teacher;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
com.cxyzxc.www.dao包
该包用来存放操作数据库的接口
package com.cxyzxc.www.dao;
import java.util.List;
import com.cxyzxc.www.entity.Teacher;
public interface TeacherDao {
//增
int insert(Teacher teacher);
//删
int delete(int tid);
//改
int update(Teacher teacher);
//查单个
Teacher selectOne(int tid);
//查所有
List<Teacher> selectAll();
}
com.cxyzxc.www.dao.impl包
该包用来存放数据库操作接口的实现类
package com.cxyzxc.www.dao.impl;
import java.util.ArrayList;
import java.util.List;
import com.cxyzxc.www.advanced.RowMapper;
import com.cxyzxc.www.advanced.impl.TeacherRowMapper;
import com.cxyzxc.www.dao.TeacherDao;
import com.cxyzxc.www.entity.Teacher;
import com.cxyzxc.www.utils.DaoUtils;
/**
*
* @author 程序员张小厨
*
*/
public class TeacherDaoImpl implements TeacherDao {
/*
* (1)该类中提供对teacher表进行增、删、改、查单个、查所有5个方法。
*
* (2)该类中的代码只做数据库访问操作,不做任何业务逻辑操作。
*
* (3)该类只对数据库一张表进行操作,从而实现复用
*/
DaoUtils<Teacher> daoUtils = new DaoUtils<Teacher>();
RowMapper<Teacher> rowMapper = new TeacherRowMapper();
// 新增:向teacher表中插入一条数据(一条数据对应一个Teacher对象),插入成功,返回一个受影响行数值(int类型)
@Override
public int insert(Teacher teacher) {
String sql = "insert into `teacher`(name,age,bornDate,email,address) values(?,?,?,?,?);";
// 赋值
Object[] args = { teacher.getName(), teacher.getAge(),
teacher.getBornDate(), teacher.getEmail(), teacher.getAddress() };
return daoUtils.commonsUpdate(sql, args);
}
// 删除:根据教师tid删除一条数据,删除成功,返回一个受影响行数值(int类型)
@Override
public int delete(int tid) {
String sql = "delete from teacher where tid = ?;";
return daoUtils.commonsUpdate(sql, tid);
}
// 修改:修改teacher表中的数据,可能对任意的一个字段进行修改,所以方法中直接传递一个对象进行修改,修改成功,返回一个受影响行数值(int类型)
@Override
public int update(Teacher teacher) {
String sql = "update teacher set name = ?,age = ?,bornDate = ?,email = ?,address = ? where tid = ?;";
Object[] args = { teacher.getName(), teacher.getAge(),
teacher.getBornDate(), teacher.getEmail(),
teacher.getAddress(), teacher.getTid() };
return daoUtils.commonsUpdate(sql, args);
}
// 查询单个:根据教师tid查询一条数据,查询成功返回一个结果集(ResultSet类型),从结果集中取出元素,封装成一个Teacher对象,将该对象返回
@Override
public Teacher selectOne(int tid) {
String sql = "select * from teacher where tid = ?;";
List<Teacher> objectList = daoUtils.commonsSelect(sql, rowMapper, tid);
if (objectList.size() == 1) {
Teacher teacher = (Teacher) objectList.get(0);
return teacher;
} else {
return null;
}
}
// 查询所有:将teacher表中的所有数据全部查询出来,查询成功返回一个结果集(ResultSet类型),从结果集中取出元素,封装成多个Teacher对象,将多个对象存储在集合中,返回这个集合
@Override
public List<Teacher> selectAll() {
List<Teacher> teacherList = new ArrayList<Teacher>();
String sql = "select * from teacher;";
Object[] args = null;
List<Teacher> list = daoUtils.commonsSelect(sql, rowMapper, args);
for (int i = 0; i < list.size(); i++) {
teacherList.add(list.get(i));
}
return teacherList;
}
}
重复包或代码
com.cxyzxc.www.service包
com.cxyzxc.www.service.impl包
com.cxy.zxc.view包
测试插入数据
测试删除数据
测试修改数据
测试查询一条数据
测试查询所有数据
Druid连接池
连接池思想
在程序初始化时,提前创建好指定数量的数据库连接对象存储在“池子”中(这个池子称为“连接池”),当需要连接数据库的时候,从这个“池子”中取出一个连接对象使用,使用完毕后,不会将这个连接对象关闭,而是将这个连接对象放回“池子”中,实现复用,节省资源。
Druid连接池使用步骤
引入相关jar包
在lib文件夹中引入druid-1.1.5.jar文件和mysql-connector-java-5.1.0-bin.jar文件,并将两个jar文件配置到项目中。
创建database.properties配置文件
在src文件夹下创建database.properties配置文件,配置文件中内容如下:
# 连接设置
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcdatabase
username=root
password=123456
# 初始化连接,连接池连接对象数量
initialSize=10
#最大连接数
maxActive=30
#最小空闲连接
maxIdle=5
#超时等待时间(毫秒为单位)
maxWait=3000
编写连接池工具类
package com.cxyzxc.www.utils;
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;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class DBUtils {
// 声明一个连接池对象
private static DruidDataSource druidDataSource;
static {
// 实例化配置文件对象
Properties properties = new Properties();
try {
// 加载配置文件内容
InputStream is = DBUtils.class
.getResourceAsStream("/database.properties");
properties.load(is);
// 创建连接池
druidDataSource = (DruidDataSource) DruidDataSourceFactory
.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接对象
public static Connection getConnection() {
try {
// 通过连接池获得连接对象
return druidDataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
// 释放资源,将连接对象放入连接池中
public static void closeAll(Connection connection, Statement statement,
ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
// 使用完连接对象后,将连接对象还给连接池,这里的close()方法是DruidPooledConnection实现类里的close()方法,将connection连接对象还给连接池
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Druid连接池测试
package com.cxyzxc.www.utils;
import java.sql.Connection;
public class Test {
public static void main(String[] args) {
// 获取20个连接对象,输出连接对象,地址值不同
for (int i = 1; i <= 20; i++) {
Connection connection = DBUtils.getConnection();
System.out.println(connection);
// 调用关闭连接对象的方法后,发现获取的20个连接对象地址值是同一个,说明每次从连接池中取出的连接对象是同一个
// DBUtils.closeAll(connection, null, null);
}
}
}
Apache的DBUtils使用
前面的DaoUtils工具类是我们经过千难万阻自己封装的,也有一些组织给我们封装DBUtils工具类,比如Apache组织提供了一个对JDBC进行简单封装的开源工具类库Commons DbUtils类,使用它能够简化JDBC应用程序的开发,同时也不影响程序的性能。
Apache DBUtils介绍
Apache DBUtils特征
Apache DBUtils是java编程中的数据库操作实用工具,小巧简单实用,主要特征有: 1)对于数据表的读操作,他可以把结果转换成List,Array,Set等java集合,便于程序员操作; 2)对于数据表的写操作,也变得很简单(只需写sql语句) 3)可以使用数据源,使用JNDI,数据库连接池等技术来优化性能--重用已经构建好的数据库连接对象,而不像php,asp那样,费时费力的不断重复的构建和析构这样的对象。
Apache DbUtils主要组成
1)ResultSetHandler接口:转换类型接口
-
BeanHandler类:实现类,把一条记录转换成对象。
-
BeanListHandler类:实现类,把多条记录转换成List集合。
-
ScalarHandler类:实现类,适合获取一行一列的数据。
2)QueryRunner类:执行SQL语句的类
-
update()方法:增、删、改
-
query()方法:查询
Apache DbUtils使用步骤
1)创建lib文件夹,导入需要的jar包,并将其配置到项目中
-
mysql-connector-java-5.1.0-bin.jar
-
druid-1.1.5.jar
-
commons-dbutils-1.7.jar
2)在src文件夹下面创建database.properties配置文件
# 连接设置
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcdatabase
username=root
password=123456
# 初始化连接,连接池连接对象数量
initialSize=10
#最大连接数
maxActive=30
#最小空闲连接
maxIdle=5
#超时等待时间(毫秒为单位)
maxWait=3000
编写DBUtils连接池工具类
package com.cxyzxc.www.utils;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class DBUtils {
// 声明一个连接池对象
private static DruidDataSource druidDataSource;
static {
// 实例化配置文件对象
Properties properties = new Properties();
try {
// 加载配置文件内容
InputStream is = DBUtils.class
.getResourceAsStream("/database.properties");
properties.load(is);
// 创建连接池
druidDataSource = (DruidDataSource) DruidDataSourceFactory
.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//返回一个数据源
public static DataSource getDataSource(){
return druidDataSource;
}
}
综合案例
创建product表
CREATE TABLE IF NOT EXISTS `product` (
`pid` INT PRIMARY KEY AUTO_INCREMENT COMMENT '产品编号',
`pname` VARCHAR(20) NOT NULL COMMENT '产品名称',
`price` DOUBLE NOT NULL COMMENT '产品价格',
`birthday` DATE NOT NULL COMMENT '产品生产日期'
);
INSERT INTO `product`(`pid`,`pname`,`price`,`birthday`)VALUES(1001,'虎皮凤爪',20.5,'2022-06-12');
INSERT INTO `product`(`pid`,`pname`,`price`,`birthday`)VALUES(1002,'卧龙锅巴',18.5,'2022-09-22');
创建实体类Product
package com.cxyzxc.www.entity;
import java.util.Date;
public class Product {
private int pid;
private String pname;
private double price;
private Date birthday;
public Product() {
super();
}
public Product(String pname, double price, Date birthday) {
super();
this.pname = pname;
this.price = price;
this.birthday = birthday;
}
public Product(int pid, String pname, double price, Date birthday) {
super();
this.pid = pid;
this.pname = pname;
this.price = price;
this.birthday = birthday;
}
public int getPid() {
return pid;
}
public void setPid(int pid) {
this.pid = pid;
}
public String getPname() {
return pname;
}
public void setPname(String pname) {
this.pname = pname;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "Product [pid=" + pid + ", pname=" + pname + ", price=" + price
+ ", birthday=" + birthday + "]";
}
}
创建ProductDao接口
package com.cxyzxc.www.dao;
import java.util.List;
import com.cxyzxc.www.entity.Product;
public interface ProductDao {
//添加
int insert(Product product);
//删除
int delete(int pid);
//修改
int update(Product product);
//查询单个
Product selectOne(int pid);
//查询所有
List<Product> selectAll();
}
创建ProductDaoImpl实现类
package com.cxyzxc.www.dao.impl;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.cxyzxc.www.dao.ProductDao;
import com.cxyzxc.www.entity.Product;
import com.cxyzxc.www.utils.DBUtils;
import com.cxyzxc.www.utils.DateUtils;
public class ProductDaoImpl implements ProductDao {
// 创建QueryRunner对象,并传递一个数据源对象
private QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());
@Override
public int insert(Product product) {
String sql = "INSERT INTO `product`(`pname`,`price`,`birthday`)VALUES(?,?,?);";
Object[] args = { product.getPname(), product.getPrice(),
DateUtils.utilDateToSqlDate(product.getBirthday()) };
try {
return queryRunner.update(sql, args);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
@Override
public int delete(int pid) {
String sql = "DELETE FROM `product` WHERE `pid` = ?;";
try {
return queryRunner.update(sql, pid);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
@Override
public int update(Product product) {
String sql = "UPDATE `product` SET `pname` = ?,`price`=?,`birthday`=? WHERE `pid`=?;";
Object[] args = { product.getPname(), product.getPrice(),
DateUtils.utilDateToSqlDate(product.getBirthday()),
product.getPid() };
try {
return queryRunner.update(sql, args);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
@Override
public Product selectOne(int pid) {
// 查询一个数据,使用BeanHandler将记录转换为对象
BeanHandler<Product> product = new BeanHandler<Product>(Product.class);
String sql = "SELECT * FROM `product` WHERE `pid`=?;";
try {
return queryRunner.query(sql, product, pid);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
@Override
public List<Product> selectAll() {
// 查询一个数据,使用BeanHandler将记录转换为对象
BeanListHandler<Product> productList = new BeanListHandler<Product>(Product.class);
String sql = "SELECT * FROM `product`;";
try {
return queryRunner.query(sql, productList);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
创建ProductService接口
package com.cxyzxc.service;
import java.util.List;
import com.cxyzxc.www.entity.Product;
public interface ProductService {
//增加
int addProduct(Product product);
//删除
int deleteProduct(int pid);
//修改
int updateProduct(Product product);
//查询单个
Product selectOneProduct(int pid);
//查询所有
List<Product> selectAllProduct();
}
创建ProductServiceImpl实现类
package com.cxyzxc.service.impl;
import java.util.List;
import com.cxyzxc.service.ProductService;
import com.cxyzxc.www.dao.ProductDao;
import com.cxyzxc.www.dao.impl.ProductDaoImpl;
import com.cxyzxc.www.entity.Product;
public class ProductServiceImpl implements ProductService {
ProductDao productDao = new ProductDaoImpl();
@Override
public int addProduct(Product product) {
// 查询添加的商品是否存在
Product pd = productDao.selectOne(product.getPid());
if (pd == null) {
return productDao.insert(product);
} else {
System.out.println("商品已经存在,不能重复添加");
}
return 0;
}
@Override
public int deleteProduct(int pid) {
// 查询添加的商品是否存在
Product pd = productDao.selectOne(pid);
if (pd != null) {
return productDao.delete(pid);
} else {
System.out.println("商品不存在,不能删除");
}
return 0;
}
@Override
public int updateProduct(Product product) {
// 查询添加的商品是否存在
Product pd = productDao.selectOne(product.getPid());
if (pd!= null) {
return productDao.update(product);
} else {
System.out.println("商品不存在,不能修改");
}
return 0;
}
@Override
public Product selectOneProduct(int pid) {
Product product =productDao.selectOne(pid);
if(product!=null){
return product;
}else{
System.out.println("没有你要查找产品,查找失败");
}
return null;
}
@Override
public List<Product> selectAllProduct() {
List<Product> productList = productDao.selectAll();
if(productList.size()!=0){
return productList;
}else{
System.out.println("数据库为空,没有产品");
}
return null;
}
}
创建测试类
测试插入数据
package com.cxyzxc.www.view;
import com.cxyzxc.service.ProductService;
import com.cxyzxc.service.impl.ProductServiceImpl;
import com.cxyzxc.www.entity.Product;
import com.cxyzxc.www.utils.DateUtils;
public class Test01InsertProduct {
public static void main(String[] args) {
//创建ProductService引用,指向ProductServiceImpl实现类
ProductService productService = new ProductServiceImpl();
//增加产品
Product product = new Product(1003,"流心威化饼干", 13.5, DateUtils.strDateToUtilDate("2022-11-10"));
int result = productService.addProduct(product);
String str = result==1?"商品添加成功":"商品添加失败";
System.out.println(str);
}
}
测试删除数据
package com.cxyzxc.www.view;
import com.cxyzxc.service.ProductService;
import com.cxyzxc.service.impl.ProductServiceImpl;
public class Test02DeleteProduct {
public static void main(String[] args) {
// 创建ProductService引用,指向ProductServiceImpl实现类
ProductService productService = new ProductServiceImpl();
int result = productService.deleteProduct(1003);
String str = result == 1 ? "删除成功" : "删除失败";
System.out.println(str);
}
}
测试修改数据
package com.cxyzxc.www.view;
import com.cxyzxc.service.ProductService;
import com.cxyzxc.service.impl.ProductServiceImpl;
import com.cxyzxc.www.entity.Product;
import com.cxyzxc.www.utils.DateUtils;
public class Test03UpdateProduct {
public static void main(String[] args) {
// 创建ProductService引用,指向ProductServiceImpl实现类
ProductService productService = new ProductServiceImpl();
// 增加产品
Product product = new Product(1002, "流心威化饼干", 13.5,
DateUtils.strDateToUtilDate("2022-11-10"));
int result = productService.updateProduct(product);
String str = result == 1 ? "修改成功" : "修改失败";
System.out.println(str);
}
}
测试查询一条数据
package com.cxyzxc.www.view;
import com.cxyzxc.service.ProductService;
import com.cxyzxc.service.impl.ProductServiceImpl;
import com.cxyzxc.www.entity.Product;
public class Test04SelectOneProduct {
public static void main(String[] args) {
// 创建ProductService引用,指向ProductServiceImpl实现类
ProductService productService = new ProductServiceImpl();
Product product = productService.selectOneProduct(1003);
if (product != null) {
System.out.println(product);
} else {
System.out.println("你要查询的商品不存在");
}
}
}
测试查询所有数据
package com.cxyzxc.www.view;
import java.util.List;
import com.cxyzxc.service.ProductService;
import com.cxyzxc.service.impl.ProductServiceImpl;
import com.cxyzxc.www.entity.Product;
public class Test05SelectAllProduct {
public static void main(String[] args) {
// 创建ProductService引用,指向ProductServiceImpl实现类
ProductService productService = new ProductServiceImpl();
List<Product> productList = productService.selectAllProduct();
for (int i = 0; i < productList.size(); i++) {
System.out.println(productList.get(i));
}
}
}