JDBC 提供操作数据表的BaseDAO,实现类及单元测试
2019-12-21 本文已影响0人
一亩三分甜
BaseDAO
/**
* @program: jdbc_2
* @description: DAO:data(base) access object封装了针对于数据表的通用的操作
* @author: fish
* @create: 2019-12-19 11:09
**/
public abstract class BaseDAO {
//通用的增删改操作 -----version 2.0(考虑上事务)
public int update(Connection conn, String sql, Object ...args){//sql中占位符的个数与可变形参的个数相同
PreparedStatement ps = null;
try{
//1.预编译sql语句,返回PreparedStatement的实例
ps = conn.prepareStatement(sql);
//2.填充占位符
for (int i = 0;i < args.length;i++)
{
ps.setObject(i+1,args[i]);
}
//3.执行
return ps.executeUpdate();
}catch (Exception e){
e.printStackTrace();
}finally {
//4.资源的关闭
JDBCUtils.closeResource(null,ps);
System.out.println("执行完毕！");
}
return 0;
}
//通用的查询操作，用于返回数据表中的一条记录(version 2.0:考虑上事务)
public <T> T getInstance(Connection conn,Class<T> clazz, String sql,Object... args){
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
//获取结果集的元数据:ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()){
T t = clazz.newInstance();
//处理结果集一行数据中的每一个列
for (int i = 0;i< columnCount;i++){
//获取列值
Object columnValue = rs.getObject(i+1);
//获取每个列的列名
// String columnName = rsmd.getColumnName(i+1);
String columnLabelName = rsmd.getColumnLabel(i+1);
//给t对象指定columnName属性，赋值columnValue，通过反射
Field field = clazz.getDeclaredField(columnLabelName);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
/**
* @Description: 针对于不同的表的通用的查询操作，返回表中的多条记录(Version2.0,考虑上事务)
* @Param:
* @return:
* @Author: fish
* @Date: 1:40 AM 2019/12/12
*/
public <T> List<T> getForList(Connection conn,Class<T> clazz, String sql, Object... args){
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
//获取结果集的元数据:ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
ArrayList<T> arrayList = new ArrayList<T>();
while(rs.next()){
T t = clazz.newInstance();
//处理结果集一行数据中的每一个列
for (int i = 0;i< columnCount;i++){
//获取列值
Object columnValue = rs.getObject(i+1);
//获取每个列的列名
// String columnName = rsmd.getColumnName(i+1);
String columnLabelName = rsmd.getColumnLabel(i+1);
//给t对象指定columnName属性，赋值columnValue，通过反射
Field field = clazz.getDeclaredField(columnLabelName);
field.setAccessible(true);
field.set(t,columnValue);
}
arrayList.add(t);
}
return arrayList;
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
//用于查询特殊值的通用的方法
public <E> E getValue(Connection conn, String sql, Object...args){
PreparedStatement ps = null;
ResultSet rs = null;
try{
ps = conn.prepareStatement(sql);
for (int i = 0;i < args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
if (rs.next()){
Object result = rs.getObject(1);
//过滤Timestamp类型，若为Timestamp类型,则转化为java.sql.Date类型
if (result instanceof Timestamp){
Timestamp timestamp = (Timestamp) result;
return (E) new Date(timestamp.getTime());
}
return (E) rs.getObject(1);
}
}catch (SQLException e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
//用于查询特殊值的通用的方法
public <E> E getValue(Connection conn, String sql, Class<E> clz, Object...args){
PreparedStatement ps = null;
ResultSet rs = null;
try{
ps = conn.prepareStatement(sql);
for (int i = 0;i < args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
if (rs.next()){
return (E) rs.getObject(1, clz);
}
}catch (SQLException e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
}
针对于具体的表的DAO
public interface CustomerDAO {
/**
* @Description: 将cust对象添加到数据库中
* @Param:
* @return:
* @Author: fish
* @Date: 1:40 PM 2019/12/19
*/
void insert(Connection conn, Customer cust);
/**
* @Description: 针对指定的id,删除表中的一条记录
* @Param:
* @return:
* @Author: fish
* @Date: 1:45 PM 2019/12/19
*/
void deleteById(Connection conn,int id);
/**
* @Description:针对内存中的cust对象，去修改数据表中指定的记录
* @Param:
* @return:
* @Author: fish
* @Date: 1:58 PM 2019/12/19
*/
void update(Connection conn, Customer cust);
/**
* @Description: 根据指定的id查询得到对象的Customer对象
* @Param:
* @return:
* @Author: fish
* @Date: 2:02 PM 2019/12/19
*/
Customer getCustomerById(Connection conn,int id);
/**
* @Description: 查询表中所有记录构成的集合
* @Param:
* @return:
* @Author: fish
* @Date: 2:05 PM 2019/12/19
*/
List<Customer> getAll(Connection conn);
/**
* @Description:返回数据表中的数据的条目数
* @Param:
* @return:
* @Author: fish
* @Date: 2:12 PM 2019/12/19
*/
Long getCount(Connection conn);
/**
* @Description:返回数据表中最大的生日
* @Param:
* @return:
* @Author: fish
* @Date: 2:14 PM 2019/12/19
*/
Date getMaxBirth(Connection conn);
}
针对于具体的表的DAO的实现
public class CustomerDAOImpl extends BaseDAO implements CustomerDAO {
@Override
public void insert(Connection conn, Customer cust) {
String sql = "insert into customers (name,email,birth) values (?,?,?)";
update(conn, sql, cust.getName(), cust.getEmail(), cust.getBirth());
}
@Override
public void deleteById(Connection conn, int id) {
String sql = "delete from customers where id = ?";
update(conn, sql, id);
}
@Override
public void update(Connection conn, Customer cust) {
String sql = "update customers set name = ?,email = ?,birth = ? where id = ?";
update(conn, sql, cust.getName(), cust.getEmail(), cust.getBirth(), cust.getId());
}
@Override
public Customer getCustomerById(Connection conn, int id) {
String sql = "select id,name,email,birth from customers where id = ?";
Customer customer = getInstance(conn, Customer.class, sql, id);
return customer;
}
@Override
public List<Customer> getAll(Connection conn) {
String sql = "select id,name,email,birth from customers";
return getForList(conn, Customer.class, sql);
}
@Override
public Long getCount(Connection conn) {
String sql = "select count(*) from customers";
return getValue(conn, sql);
// return getValue(conn, sql, Long.class);
}
@Override
public Date getMaxBirth(Connection conn) {
String sql = "select max(birth) from customers";
return getValue(conn, sql);
// return getValue(conn, sql, Date.class);
}
}
单元测试类
public class CustomerDAOImplTest {
private CustomerDAOImpl dao = new CustomerDAOImpl();
@Test
public void insert() {
Connection conn = null;
try{
conn = JDBCUtils.getConnection();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = sdf.parse("2019-12-19");
Customer cust = new Customer(1,"卡卡","kaka@126.com",new Date(date.getTime()));
dao.insert(conn,cust);
System.out.println("添加成功");
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
@Test
public void deleteById(){
Connection conn = null;
try{
conn = JDBCUtils.getConnection();
dao.deleteById(conn,6);
System.out.println("删除成功");
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
@Test
public void update() {
Connection conn = null;
try{
conn = JDBCUtils.getConnection();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = sdf.parse("1876-12-19");
Customer customer = new Customer(13,"贝多芬","beiduofen@126.com",new Date(date.getTime()));
dao.update(conn,customer);
System.out.println("修改成功");
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
@Test
public void getCustomerById(){
Connection conn = null;
try{
conn = JDBCUtils.getConnection();
Customer customer = dao.getCustomerById(conn,8);
System.out.println(customer);
System.out.println("查询成功");
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
@Test
public void getAll(){
Connection conn = null;
try{
conn = JDBCUtils.getConnection();
List<Customer> list = dao.getAll(conn);
Iterator it = list.iterator();//获取迭代器，用于取出集合中的元素。
while(it.hasNext())
{
System.out.println(it.next());
}
System.out.println("获取成功");
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
@Test
public void getCount() {
Connection conn = null;
try{
conn = JDBCUtils.getConnection();
long lon = dao.getCount(conn);
System.out.println(lon);
System.out.println("获取成功");
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
@Test
public void getMaxBirth(){
Connection conn = null;
try{
conn = JDBCUtils.getConnection();
Date maxBirth = dao.getMaxBirth(conn);
System.out.println(maxBirth);
System.out.println("最大的生日为:"+maxBirth);
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
}
注意当MySql中存储的数据类型为datetime时，java代码中接收到的原始类型为TimeStamp。如果要用java.sql.Date接收，则需要转型。new Date(timestamp.getTime())，或直接用java.util.Date(是java.util.TimeStamp和java.sql.Date的父类)类型接收。若直接用java.sql.Date类型接收会报错。
java.lang.ClassCastException: java.sql.Timestamp cannot be cast to java.sql.Date
1.若不改接收的类型，仍旧用java.sql.Date接收，则在调用getObject()方法获取到TimeStamp类型是进行过滤,再通过new Date(timestamp.getTime())转成java.sql.Date类型。
//用于查询特殊值的通用的方法
public <E> E getValue(Connection conn, String sql, Object...args){
PreparedStatement ps = null;
ResultSet rs = null;
try{
ps = conn.prepareStatement(sql);
for (int i = 0;i < args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
if (rs.next()){
Object result = rs.getObject(1);
//过滤Timestamp类型，若为Timestamp类型,则转化为java.sql.Date类型
if (result instanceof Timestamp){
Timestamp timestamp = (Timestamp) result;
return (E) new Date(timestamp.getTime());
}
return (E) rs.getObject(1);
}
}catch (SQLException e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
2.通过将要获取到的接收类型(java.sql.Date.class)java.sql.Date传入getObject方法中，对应获取到的类型即为传入的类型java.sql.Date(的对象)。
//用于查询特殊值的通用的方法
public <E> E getValue(Connection conn, String sql, Class<E> clz, Object...args){
PreparedStatement ps = null;
ResultSet rs = null;
try{
ps = conn.prepareStatement(sql);
for (int i = 0;i < args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
if (rs.next()){
return (E) rs.getObject(1, clz);
}
}catch (SQLException e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
升级以后的DAO,在非静态代码块中实现获取当前BaseDAO的子类继承的父类中的泛型。
{
//获取当前BaseDAO的子类继承的父类中的泛型
Type genericSuperclass = this.getClass().getGenericSuperclass();
ParameterizedType paramType = (ParameterizedType)genericSuperclass;
//获取了父类的泛型参数
Type[] typeArguments = paramType.getActualTypeArguments();
//获取泛型的第一个参数
clazz = (Class<T>) typeArguments[0];
}
升级后的抽象类BaseDAO
public abstract class BaseDAO<T> {
private Class<T> clazz = null;
// public BaseDAO(){
//
// }
{
//获取当前BaseDAO的子类继承的父类中的泛型
Type genericSuperclass = this.getClass().getGenericSuperclass();
ParameterizedType paramType = (ParameterizedType) genericSuperclass;
//获取了父类的泛型参数
Type[] typeArguments = paramType.getActualTypeArguments();
//获取泛型的第一个参数
clazz = (Class<T>) typeArguments[0];
}
//通用的增删改操作 -----version 2.0(考虑上事务)
public int update(Connection conn, String sql, Object ...args){//sql中占位符的个数与可变形参的个数相同
PreparedStatement ps = null;
try{
//1.预编译sql语句,返回PreparedStatement的实例
ps = conn.prepareStatement(sql);
//2.填充占位符
for (int i = 0;i < args.length;i++)
{
ps.setObject(i+1,args[i]);
}
//3.执行
return ps.executeUpdate();
}catch (Exception e){
e.printStackTrace();
}finally {
//4.资源的关闭
JDBCUtils.closeResource(null,ps);
System.out.println("执行完毕！");
}
return 0;
}
//通用的查询操作，用于返回数据表中的一条记录(version 2.0:考虑上事务)
public T getInstance(Connection conn, String sql,Object... args){
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
//获取结果集的元数据:ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()){
T t = clazz.newInstance();
//处理结果集一行数据中的每一个列
for (int i = 0;i< columnCount;i++){
//获取列值
Object columnValue = rs.getObject(i+1);
//获取每个列的列名
// String columnName = rsmd.getColumnName(i+1);
String columnLabelName = rsmd.getColumnLabel(i+1);
//给t对象指定columnName属性，赋值columnValue，通过反射
Field field = clazz.getDeclaredField(columnLabelName);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
/**
* @Description: 针对于不同的表的通用的查询操作，返回表中的多条记录(Version2.0,考虑上事务)
* @Param:
* @return:
* @Author: fish
* @Date: 1:40 AM 2019/12/12
*/
public List<T> getForList(Connection conn, String sql, Object... args){
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
//获取结果集的元数据:ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
ArrayList<T> arrayList = new ArrayList<T>();
while(rs.next()){
T t = clazz.newInstance();
//处理结果集一行数据中的每一个列
for (int i = 0;i< columnCount;i++){
//获取列值
Object columnValue = rs.getObject(i+1);
//获取每个列的列名
// String columnName = rsmd.getColumnName(i+1);
String columnLabelName = rsmd.getColumnLabel(i+1);
//给t对象指定columnName属性，赋值columnValue，通过反射
Field field = clazz.getDeclaredField(columnLabelName);
field.setAccessible(true);
field.set(t,columnValue);
}
arrayList.add(t);
}
return arrayList;
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
//用于查询特殊值的通用的方法
public <E> E getValue(Connection conn, String sql, Object...args){
PreparedStatement ps = null;
ResultSet rs = null;
try{
ps = conn.prepareStatement(sql);
for (int i = 0;i < args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
if (rs.next()){
Object result = rs.getObject(1);
//过滤Timestamp类型，若为Timestamp类型,则转化为java.sql.Date类型
if (result instanceof Timestamp){
Timestamp timestamp = (Timestamp) result;
return (E) new Date(timestamp.getTime());
}
return (E) rs.getObject(1);
}
}catch (SQLException e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
//用于查询特殊值的通用的方法
public <E> E getValue(Connection conn, String sql, Class<E> clz, Object...args){
PreparedStatement ps = null;
ResultSet rs = null;
try{
ps = conn.prepareStatement(sql);
for (int i = 0;i < args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
if (rs.next()){
return (E) rs.getObject(1, clz);
}
}catch (SQLException e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
}
在子类中带上父类的泛型Customer，查询方法中不用再传入Customer.class了。
public class CustomerDAOImpl extends BaseDAO<Customer> implements CustomerDAO {
@Override
public Customer getCustomerById(Connection conn, int id) {
String sql = "select id,name,email,birth from customers where id = ?";
Customer customer = getInstance(conn,sql, id);
return customer;
}
@Override
public List<Customer> getAll(Connection conn) {
String sql = "select id,name,email,birth from customers";
return getForList(conn, sql);
}
}
