封装JdbcUtil工具类，实现自动封装为实体类

本文参考自封装JdbcUtil工具类，实现自动封装为实体类

查看原文: 原文地址

根据今天所学，自己写的JdbcUtil工具类。

其中update操作包含增删改，使用事务处理

查询操作分为两种：

1、需要传入sql语句（使用预编译，用？占位）、参数、需要包装的实体类的class路径；返回实体类的list（可以为单个）

2、传入sql、参数。适用于查询基本数据类型组成的list （可以为单个）

jdbcUtil.java代码：

import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;

/**
 * Created by srg
 *
 * @date 2018/7/14
 */
public class JdbcUtil {

    private static String url = null;
    private static String username = null;
    private static String password = null;
    private static String driverClass = null;

    private static Connection conn = null;
    private static PreparedStatement pstm = null;
    private static ResultSet rs = null;

    static {
        try {
            Properties prop = new Properties();
            InputStream in = JdbcUtil.class.getResourceAsStream("/database.properties");
            prop.load(in);
            url = prop.getProperty("url");
            username = prop.getProperty("user");
            password = prop.getProperty("password");
            driverClass = prop.getProperty("driverClass");
            Class.forName(driverClass);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库链接
     * @return
     */
    public static Connection getConnection(){
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 流的关闭
     */
    public static void close(){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(pstm != null){
            try {
                pstm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 执行插入操作
     * @param sql       sql语句
     * @param param     需要的参数
     * @return
     */
    public static boolean executeUpdate(String sql, ArrayList param){
        boolean flag = false;
        conn = getConnection();
        try {
            //设置为手动提交
            conn.setAutoCommit(false);
            pstm = conn.prepareStatement(sql);
            for (int i = 1;i < param.size();i++){
                pstm.setObject(i + 1, param.get(i));
            }
            pstm.executeUpdate();
            //如果执行成功了，就提交
            conn.commit();
            flag = true;
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            return flag;
        }
        return flag;
    }

    /**
     * 执行查询操作
     * @param sql       sql语句
     * @param param     需要的参数
     * @param classPath      根据传入的path,反射为实体类,封装到list中
     * @return          将封装好的list返回
     */
    public static ArrayList executeQuery(String sql, ArrayList param, String classPath){
        ArrayList result = new ArrayList();
        conn = getConnection();
        try {
            pstm = conn.prepareStatement(sql);
            for (int i = 0;i < param.size();i++){
                pstm.setObject(i + 1, param.get(i));
            }
            rs = pstm.executeQuery();

            Class c = Class.forName(classPath);
            while(rs.next()){
                Object obj = c.newInstance();
                Field[] fields = c.getDeclaredFields();
                for (Field field : fields){
                    String ftype = field.getType().getSimpleName();
                    String fname = field.getName();

                    //如果不存在，继续
                    if(!isExists(rs, fname)){
                        continue;
                    }

                    Object value = null;
                    if("string".equalsIgnoreCase(ftype)){
                        value = rs.getString(fname);
                    } else if("int".equalsIgnoreCase(ftype) || "integer".equalsIgnoreCase(ftype)){
                        value = rs.getInt(fname);
                    } else if("float".equalsIgnoreCase(ftype)){
                        value = rs.getFloat(fname);
                    } else if("double".equalsIgnoreCase(ftype)){
                        value = rs.getDouble(fname);
                    } else if("boolean".equalsIgnoreCase(ftype)){
                        value = rs.getBoolean(fname);
                    } else if("date".equalsIgnoreCase(ftype)){
                        value = rs.getTimestamp(fname);
                    }

                    //使用setXXX方法
                    fname = fname.substring(0,1).toUpperCase() + fname.substring(1);
                    String mName = "set" + fname;
                    Method m = c.getDeclaredMethod(mName, field.getType());
                    m.invoke(obj, value);
                }
                //将封装好的数据依次加入result
                result.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
            result = new ArrayList();
            return result;
        } finally {
            close();
        }
        return result;
    }

    /**
     * 查询单个数据
     * @param sql       sql语句
     * @param param     需要的参数
     * @return          返回值为基本类型或者String的list
     */
    public static ArrayList executeQuery(String sql, ArrayList param){
        ArrayList result = null;
        conn = getConnection();
        try{
            pstm = conn.prepareStatement(sql);
            for (int i = 0;i < param.size();i++){
                pstm.setObject(i + 1, param.get(i));
            }
            rs = pstm.executeQuery();

            while (rs.next()){
                Object object = rs.getObject(1);
                result.add(object);
            }
        } catch (Exception e){
            e.printStackTrace();
            return new ArrayList();
        } finally {
            close();
        }
        return result;
    }

    public static boolean isExists(ResultSet rs,String column)
    {
        try {
            rs.findColumn(column);
        } catch (SQLException e) {
            return false;
        }
        return true;
    }
}

其中database.properties中保存数据库的信息

url=jdbc:mysql://localhost:3306/gs
user=root
password=
driverClass=com.mysql.jdbc.Driver

由于使用到了反射，所以bean（实体类）里面的set get函数必须标准命名。

例如:

user.java(仅作为例子）

public class UserBean {
    
    private String username;
    private String password;

    public UserBean() {
    }

    public UserBean(String username, String password) {
        this.username = username;
        this.password = password;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

使用实例：（我还没验证。。。瞎写一下）

public User login(String username, String password){
        String sql = "select * from user where username=? and password=?";

        ArrayList<String> param = new ArrayList<>();
        param.add(username);
        param.add(password);

        String path = "com.gs.bean.UserBean";
        ArrayList userList = JdbcUtil.executeQuery(sql,param,path);
        User user = new User();
        if(userList.size() > 0) {
            user = (User) userList.get(0);
        }
        return user;
    }

本文参考自封装JdbcUtil工具类，实现自动封装为实体类

查看原文: 原文地址