JDBC封装查询单个和查询多个

Mybatis在转化时候可以将数据库任意类型全转字符串是没有问题的

下面封装存在一个问题就是需要数据库字段类型与实体类字段类型一致

实体类

//String columnName = metaData.getColumnName(i + 1); 这个方法返回实际列名
String columnLabel = metaData.getColumnLabel(i + 1);//该方法返回别名,没有别名就返回列名

因此用metaData.getColumnLabel(i + 1);比较合适

工具类

package com.utils;
 
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;
 
/**
 * @author hrui
 * @date 2023/10/13 13:49
 */
public class DBUtils {
    private static ResourceBundle bundle=ResourceBundle.getBundle("jdbc");
    private static String driver=bundle.getString("jdbc.driver");
    private static String url=bundle.getString("jdbc.url");
    private static String username=bundle.getString("jdbc.username");
    private static String password=bundle.getString("jdbc.password");
 
    static{
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
 
    //通用查询多个
    public static <T> List<T> selectList(Class<T> clazz, String sql, Object...args){
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
 
        try {
            conn=DBUtils.getConnection();
            ps=conn.prepareStatement(sql);
 
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<T> list=new ArrayList<>();
            while(rs.next()){
                T t = clazz.newInstance();
                for(int i=0;i<columnCount;i++){
                    Object object = rs.getObject(i + 1);
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    if(columnLabel.contains("_")){
                        int index = columnLabel.indexOf("_");
                        String replace = columnLabel.replace("_", "");
                        char c = Character.toUpperCase(replace.charAt(index));
                        columnLabel=replace.substring(0, index)+c+replace.substring(index+1);
                    }
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,object);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
 
        }finally {
            DBUtils.closed(conn,ps,rs);
        }
        return null;
    }
 
    //通用查询单个
    public static <T> T selectOne(Class<T> clazz,String sql,Object...args){
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
 
        try {
            conn=DBUtils.getConnection();
            ps=conn.prepareStatement(sql);
 
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            if(rs.next()){
                T t = clazz.newInstance();
                for(int i=0;i<columnCount;i++){
                    Object object = rs.getObject(i + 1);
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    if(columnLabel.contains("_")){
                        int index = columnLabel.indexOf("_");
                        String replace = columnLabel.replace("_", "");
                        char c = Character.toUpperCase(replace.charAt(index));
                        columnLabel=replace.substring(0, index)+c+replace.substring(index+1);
                    }
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,object);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
 
        }finally {
            DBUtils.closed(conn,ps,rs);
        }
        return null;
    }
 
 
 
 
    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(url, username, password);
        return connection;
    }
    //通用增删改方法
    public static int update(String sql,Object...args){
        Connection conn =null;
        PreparedStatement ps=null;
        int count=0;
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1, args[i]);
            }
            count = ps.executeUpdate();
            //ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closed(conn,ps,null);
        }
 
        return count;
    }
 
 
    public static void closed(Connection conn, Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
}

jdbc.properties

测试