web实操日记11.19，Mysql-jdbc：关于可以通过不同的对象与实例管理数据库中不同的表，

关于上一篇jdbc中，不难发现，我的curd只针对userinfo表中的数据进行操作，这具有比较大的不灵活性，利用泛型和反射的特点，我们可以对数据库中不同的表定义不同的对象，并根据对象字段名，类名，对sql进行拼接，这样，对每一个curd操作，我们都能映射到不同的表中

下面展示curd代码

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import dbtools.GetConnection;
//泛型curd
public class BaseDAO {
    public <T> void save(T t) throws IllegalArgumentException, IllegalAccessException, SQLException ,ClassNotFoundException{
        String tableName = t.getClass().getSimpleName().toLowerCase();
        String sql = "insert into "+tableName;
        String colName = "";
        String colValue = "";
        List valueList = new ArrayList();
        Field[] fields = t.getClass().getDeclaredFields();
        for(int i=0;i<fields.length;i++){
            String eachFieldName = fields[i].getName();
            if(eachFieldName.equals("id")){
                colName = colName+","+eachFieldName;
                colValue = colValue+",?";
                fields[i].setAccessible(true);
                Object valueObject = fields[i].get(t);
                valueList.add(valueObject);
            }
        }
        colName = colName.substring(1);
        colValue = colValue.substring(1);
        sql = sql+"("+colName+","+colValue+")";
        System.out.println(sql);
        List<Map> listMap = new ArrayList<>();
        Connection connection = GetConnection.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        for(int i=0;i<valueList.size();i++){
            ps.setObject(i+1, valueList.get(i));
        }
        ps.executeUpdate();
        ps.close();
        connection.close();
    }
    public <T> void delete1(Class<T> classObject, int deleteId) throws SQLException, ClassNotFoundException{
        String tableName = classObject.getSimpleName();
        String sql = "delete from "+tableName+" where id = ?";
        Connection conn = GetConnection.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,deleteId);
        ps.executeUpdate();
        ps.close();
        conn.close();
    }
    public <T> void delete2(T t) throws IllegalArgumentException
            ,IllegalAccessException,ClassNotFoundException,SQLException{
        String tableName = t.getClass().getSimpleName();
        String sql = "delete from "+tableName+" where id = ?";
        long deleteId = 0;
        Field[] fields = t.getClass().getDeclaredFields();
        for(int i=0;i<fields.length;i++){
            String eachFieldName = fields[i].getName().toLowerCase();
            if(eachFieldName.equals("id")){
                fields[i].setAccessible(true);
                deleteId = fields[i].getLong(t);
                Long.parseLong(fields[i].get(t).toString());
                break;
            }
        }
        Connection conn = GetConnection.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,deleteId);
        ps.executeUpdate();
        ps.close();
        conn.close();
    }
    public <T> void delete3(T t) throws IllegalArgumentException, IllegalAccessException, ClassNotFoundException, NoSuchFieldException, SQLException {
        String tableName = t.getClass().getSimpleName();
        String sql = "delete from "+tableName +" where id = ?";
        Field field = t.getClass().getDeclaredField("id");
        Long deleteId = Long.parseLong(field.get(t).toString());
        Connection conn = GetConnection.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,deleteId);
        ps.executeUpdate();
        ps.close();
        conn.close();
    }
    public <T> void update(T t) throws IllegalAccessException, SQLException {
        String tableName = t.getClass().getSimpleName();
        String sql = "update " + tableName;
        String whereSQL = "where id = ?";
        String colName = "";
        List valueList = new ArrayList();
        Field[] fields = t.getClass().getDeclaredFields();
        long updateId = 0;
        for(int i=0;i<fields.length;i++){
            Field eachField = fields[i];
            eachField.setAccessible(true);
            String eachColName = eachField.getName();
            if(eachColName.equals("id")){
                updateId = Long.parseLong(eachField.get(t).toString());
            }
            else{
                colName = colName+","+eachColName+"=？";
                valueList.add(eachField.get(t));
            }
        }
        valueList.add(updateId);
        colName = colName.substring(1);
        sql = sql+" set "+colName+" "+whereSQL;
        System.out.println(sql);
        Connection connection = GetConnection.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        for(int i=0;i<valueList.size();i++){
            ps.setObject(i+1, valueList.get(i));
        }
        ps.executeUpdate();
        ps.close();
        connection.close();
    }
    public <T> T get(Class<T> t, long id) throws InstantiationException, IllegalAccessException, SQLException {
        T returnT = t.newInstance();
        String tableName = t.getClass().getSimpleName();
        String sql = "select * from "+ tableName;
        String whereSQL = "where id = ?";
        sql = sql+" "+whereSQL;

        Connection conn = GetConnection.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,id);
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            Field[] fields = t.getDeclaredFields();
            for(int i=0;i<fields.length;i++){
                Field eachField = fields[i];
                eachField.setAccessible(true);
                String colName = fields[i].getName();
                Object valueObject = rs.getObject(colName);
                eachField.set(returnT,colName);
            }
        }
        rs.close();
        ps.close();
        conn.close();
        return returnT;
    }
    public <T> List<T> getAll(Class<T> classObject) throws SQLException, InstantiationException, IllegalAccessException {
        List<T> returnList = new ArrayList<>();
        String tableName = classObject.getSimpleName();
        String sql = "select * from "+ tableName;
        Connection conn = GetConnection.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            T returnT = classObject.newInstance();
            Field[] fields = classObject.getDeclaredFields();
            for(int i=0;i<fields.length;i++){
                Field eachField = fields[i];
                eachField.setAccessible(true);
                String colName = eachField.getName();
                Object valueObject = rs.getObject(colName);
                eachField.set(returnT,colName);
            }
        }
        rs.close();
        ps.close();
        conn.close();

        return returnList;
    }
}

通过收集对象的字段，拼接sql

附mysql的连接注册方法

package dbtools;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class GetConnection {

    private static final String URL = "jdbc:mysql://localhost:3306/jdbc_mysql";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123456";

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("Failed to load database driver");
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }
    public static void main(String[]args) throws SQLException {
        Connection conn = GetConnection.getConnection();
    }

}