JDBC仿Mybatis的封装简化使用

JDBC的使用繁琐复杂，这里提供两个工具类仿照Mybatis对JDBC进行快速调用

1.创建一个resource包，添加jdbc.properties配置文件

输入你的数据库名和账号密码，在your_db替换为你的数据库名称

jdbc.url=jdbc:mysql://localhost:3306/your_db?serverTimezone=UTC
jdbc.username=root
jdbc.password=123456

2.创建包装JDBC类

注意，现在JDBC连接时不需要进行Class.forName操作，直接连接就行:

执行查询的结果将会得到一个ResultSet

public class JdbcWrapper {
    private static Connection connection;
    private static Properties properties;
    private static Statement statement;
    private static PreparedStatement preparedStatement;
    
    //数据库连接
    public static void connect() throws SQLException, IOException {
        properties = new Properties();
        //在这里填入你的jdbc配置文件路径
        FileInputStream fis = new     
        FileInputStream("jdbc.properties");
        properties.load(fis);

        String url = properties.getProperty("jdbc.url");
        String username = properties.getProperty("jdbc.username");
        String password = properties.getProperty("jdbc.password");
        connection = DriverManager.getConnection(url, username, password);
    }
    
    //不传入参数的sql
    public static ResultSet executeQuery(String sql) throws SQLException {
        statement = connection.createStatement();
        return statement.executeQuery(sql);
    }
    
    //执行查询语句
    public static ResultSet executeQuery(String sql, Object... params) throws SQLException {
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            preparedStatement.setObject(i + 1, params[i]);
        }
        return preparedStatement.executeQuery();
    }
    
    //执行更新语句
    public static int executeUpdate(String sql, Object... params) throws SQLException {
        PreparedStatement statement = connection.prepareStatement(sql);

        for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
        }

        return statement.executeUpdate();
    }


    //关闭所有JDBC连接
    public static void close() {
        try {
            if (statement != null) {
                statement.close();
            }
            if (preparedStatement != null){
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3.创建映射工具类ResultSetMapper

//指定返回的类型泛型
public class ResultSetMapper<T> {
    private final Class<T> clazz;
    
    public ResultSetMapper(Class<T> clazz) {
        this.clazz = clazz;
    }
    
    //返回单个查询结果
    public T mapSingleResult(ResultSet resultSet) throws SQLException {
        if (resultSet.next()) {
            return mapRow(resultSet);
        }
        return null;
    }
    
    //返回集合查询结果
    public List<T> mapResultList(ResultSet resultSet) throws SQLException {
        List<T> resultList = new ArrayList<>();
        while (resultSet.next()) {
            T object = mapRow(resultSet);
            resultList.add(object);
        }
        return resultList;
    }
    
    //对类进行反射操作来映射
    private T mapRow(ResultSet resultSet) throws SQLException {
        try {
            //通过构造器new出类型
            T object = clazz.getDeclaredConstructor().newInstance();
            //获取MetaData中的所有数据,也就是查询出的键和值
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            
            //对这些进行对属性的一一映射
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object columnValue = resultSet.getObject(i);
                Field field = clazz.getDeclaredField(columnName);
                field.setAccessible(true);
                field.set(object, columnValue);
            }

            return object;
        } catch (Exception e) {
            throw new SQLException("映射类失败: " + clazz.getSimpleName(), e);
        }
    }
}

4.使用方法:

假设当前MySQL中存在表user,字段name,password

创建类:User,注意进行实现序列化的接口

public class User implements Serializable{
    private static final long serialVersionUID = 52028138999216232L;
    private String name;
    private String password;
    //你的get和set方法
}

对不需要传参的sql语句:这里用查询所有user数据举例

public List<User> getAllUser(){
        ResultSet resultSet = null;
        List<User> users = null;
        try {
            //输入sql语句,得到resultSet
            resultSet = JdbcWrapper.executeQuery("select * from user");
            //对resultSet进行映射,指定泛型
            users  = newsMapper.mapResultList(resultSet);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return users;
    }

结果返回一个User的List，这里的newsMapper是外面new的ResultSetMappet类。

对需要传参的语句:这里以用用户名删除用户举例

public boolean delUser(String name){
        int done = -1;
        try {
            //执行语句
            done = JdbcWrapper.executeUpdate("DELETE FROM user WHERE name = ?", name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return done!=-1;
    }

此处?为占位符，将会和后面传入的参数进行拼接再执行，例如，若传入的name值为admin.

则执行的sql语句为delete from user where name = admin

其他语句同理，现在你可以试着去写一个demo了