通用的数据库操作以及针对某个表的JDBC操作优化版

2022-12-02 10:22:53

package GoodDAO;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;

/**
 * @className: BaseDAO
 * @description: 将通用的数据库操作进行封装（优化版）
 * @author: CCQ
 * @date: 2021/9/25
 **/
public abstract class BaseDAO<S> {
    private Class<S> clazz =null;

    //获取当前BaseDAO的子类继承父类中的泛型
    {
        //获取父类的Class对象，Type是一个接口，被Class类实现，在这里是接口回调
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        //将这么对象转换成getActualTypeArguments类型，相当于把Type换成ParameterizedType类型，ParameterizedType是type的子接口
        ParameterizedType paramType =(ParameterizedType) genericSuperclass;
        //利用ParameterizedType类型的Class对象调用getActualTypeArguments方法，此方法是ParameterizedType接口的，用于获取对象的泛型Class对象
        Type[] actualTypeArguments = paramType.getActualTypeArguments();
        //将获得的泛型对象取一个然后强转为Class类型（因为之前是Type类型）
        clazz = (Class<S>) actualTypeArguments[0];
    }

    //通用的修改操作
    public int update(Connection connection,String sql,Object...args){
        PreparedStatement preparedStatement =null;
        try {
            preparedStatement =connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }

            int i = preparedStatement.executeUpdate();
            return i;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return 0;
    }


    //通用的查询操作,用于返回数据库表里的一条数据，考虑事务
    public S select(Connection connection,String sql,Object...args){
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;
        try {
            preparedStatement =connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            if (resultSet.next()){
                S s = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    Object value = resultSet.getObject(i+1);
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Field declaredField = clazz.getDeclaredField(columnLabel);
                    declaredField.setAccessible(true);
                    declaredField.set(s,value);
                }
                return s;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }



    //通用的查询操作,用于返回数据库表里的多条数据组成的集合，考虑事务
    public  ArrayList<S> selectAll(Connection connection, String sql, Object...args){
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;
        ArrayList<S> arrayList =null;
        try {
            preparedStatement =connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            arrayList = new ArrayList<>();
            while (resultSet.next()){
                S s = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    Object value = resultSet.getObject(i+1);
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Field declaredField = clazz.getDeclaredField(columnLabel);
                    declaredField.setAccessible(true);
                    declaredField.set(s,value);
                }
                arrayList.add(s);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return arrayList;
    }

    //用于查询特殊值的通用方法
    public <E> E getvalue(Connection connection,String sql,Object...args){
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                Object value = resultSet.getObject(1);
                return (E) value;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
}

package GoodDAO;

import Bean.T;

import java.sql.Connection;
import java.util.List;

/**
 * @className: tDAO
 * @description: 此接口用于规范对于t表的常用操作
 * @author: CCQ
 * @date: 2021/9/25
 **/
public interface TDAO {
    //将t对象添加到数据库中
    void insert(Connection connection, T t);

    //根据指定的id删除表中的记录
    void delete(Connection connection,int id);

    //修改，把指定id的数据修改成新的对象
    void update(Connection connection,T t);

    //根据指定id查询得到对于的T对象
    T getTByid(Connection connection,int id);

    //查询表中所有的记录组成的集合
    List<T> getAll(Connection connection);

    //返回数据表中的数据条数
    Long getCount(Connection connection);

    //返回数据表中生日最晚的
    String getMAXBirthday(Connection connection);
}

package GoodDAO;

import Bean.T;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

/**
 * @className: TDAOImpl
 * @description: 实现TDAO接口的实现类
 * @author: CCQ
 * @date: 2021/9/25
 **/
public class TDAOImpl extends BaseDAO<T> implements TDAO {

    @Override
    public void insert(Connection connection, T t) {
        String sql ="insert into t(NAME,sex,birthday,zhicheng,yuanxi) values(?,?,?,?,?)";
        update(connection,sql,t.getNAME(),t.getSex(),t.getBirthday(),t.getZhicheng(),t.getYuanxi());
    }

    @Override
    public void delete(Connection connection, int id) {
        String sql ="delete from t where id =?";
        update(connection,sql,id);
    }

    @Override
    public void update(Connection connection, T t) {
        String sql ="update t set NAME =?,sex =?,birthday =?,zhicheng =?,yuanxi =? where id =?";
        update(connection,sql,t.getNAME(),t.getSex(),t.getBirthday(),t.getZhicheng(),t.getYuanxi(),t.getBid());
    }

    @Override
    public T getTByid(Connection connection, int id) {
        String sql ="select id,NAME,sex,birthday,zhicheng,yuanxi from t where id =?";
        T t = select(connection, sql, id);
        return t;
    }

    @Override
    public List<T> getAll(Connection connection) {
        String sql ="select * from t";
        ArrayList<T> ts = selectAll(connection, sql);
        return ts;
    }

    @Override
    public Long getCount(Connection connection) {
        String sql ="select count(*) from t";
        return getvalue(connection, sql);
    }

    @Override
    public String getMAXBirthday(Connection connection) {
        String sql ="select max(birthday) from t";
        return getvalue(connection, sql);
    }
}

码农公寓

相关文章