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

package Bean;


public class T {
    private int id;
    private String NAME;
    private String sex;
    private String birthday;
    private String zhicheng;
    private String yuanxi;

    public T() {
    }

    public T(int id, String NAME, String sex, String birthday, String zhicheng, String yuanxi) {
        this.id = id;
        this.NAME = NAME;
        this.sex = sex;
        this.birthday = birthday;
        this.zhicheng = zhicheng;
        this.yuanxi = yuanxi;
    }

    public int getBid() {
        return id;
    }

    public void setBid(int id) {
        this.id = id;
    }

    public String getNAME() {
        return NAME;
    }

    public void setNAME(String NAME) {
        this.NAME = NAME;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getBirthday() {
        return birthday;
    }

    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }

    public String getZhicheng() {
        return zhicheng;
    }

    public void setZhicheng(String zhicheng) {
        this.zhicheng = zhicheng;
    }

    public String getYuanxi() {
        return yuanxi;
    }

    public void setYuanxi(String yuanxi) {
        this.yuanxi = yuanxi;
    }

    @Override
    public String toString() {
        return "t{" +
                "id=" + id +
                ", NAME='" + NAME + ''' +
                ", sex='" + sex + ''' +
                ", birthday='" + birthday + ''' +
                ", zhicheng='" + zhicheng + ''' +
                ", yuanxi='" + yuanxi + ''' +
                '}';
    }
}

package DAO;

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


public abstract class baseDAO {
    //通用的修改操作
    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  E select(Connection connection,Class clazz,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()){
                E e = 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(e,value);
                }
                return e;
            }
        } 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 selectAll(Connection connection, Class clazz, String sql, Object...args){
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;
        ArrayList 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()){
                E e = 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(e,value);
                }
                arrayList.add(e);
            }
        } 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 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 DAO;

import Bean.T;

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


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 getAll(Connection connection);

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

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

package DAO;

import Bean.T;

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


public class TDAOImpl extends baseDAO 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, T.class, sql, id);
        return t;
    }

    @Override
    public List getAll(Connection connection) {
        String sql ="select * from t";
        ArrayList ts = selectAll(connection, T.class, 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);
    }
}