Java的JDBC封装

数据库连接操作类，该类提供创建数据库连接getConnection和release释放资源

package com.demo.JDBC;

import java.sql.*;

public class BaseDate {
    /**
     * MySQL驱动全路径
     */
    private static final String DRIDER = "com.mysql.cj.jdbc.Driver";
    /**
     * MySQL连接URL
     */
    private static final String URL = "jdbc:mysql://localhost:3306/avue";
    /**
     * 数据库用户名
     */
    private static final String USER = "root";
    /**
     * 数据库密码
     */
    private static final String PASSWORD = "123456";

    public static Connection getConnection() {
        try {
            //注册MySQL驱动
            Class.forName(DRIDER);
            //创建与数据库的连接
            return DriverManager.getConnection(URL,USER,PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("找不到驱动包");
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("创建数据库连接失败");
        }
    }

    /**
     * 释放JDBC的资源
     * @param conn
     * @param ps
     * @param rs
     */
    public static void release(Connection conn, PreparedStatement ps, ResultSet rs){
        if (rs != null) {
            try {
                rs.close();
                rs = null;
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException("结果集资源释放失败");
            }
        }
        if (ps != null) {
            try {
                ps.close();
                ps = null;
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException("SQL会话资源释放失败");
            }
        }
        if (conn != null) {
            try {
                conn.close();
                conn=null;
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException("数据库连接资源释放失败");
            }
        }
    }
}

数据库的操作类

package com.demo.JDBC;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @author 伍英杰
 * @param <T>
 */
public class CrudDao<T> {
    /**
     * 数据库连接对象
     */
    private Connection conn;
    /**
     * 结果集对象
     */
    private ResultSet rs;
    /**
     * SQl执行对象，使用Statement的子接口PreparedStatement有效防止SQL注入攻击
     */
    private PreparedStatement ps;
    /**
     * 实体类的Class对象，需要将结果集封装成指定实体类的Class对象，执行查询SQL必加
     */
    private Class cs;

    public CrudDao(Class cs) {
        this.cs = cs;
    }

    /**
     * 执行更新SQL
     * @param sql
     * @param ob
     */
    public int executeUpdate(String sql, Object...ob) {
        //获取数据库连接对象
        conn = BaseDate.getConnection();
        int colums = 0;
        try {
            if (conn != null) {
                //关闭自动提交事务，方便使用事务
                conn.setAutoCommit(false);
                ps = conn.prepareStatement(sql);
                bind(ob);
                colums =  ps.executeUpdate();
                //事务提交
                conn.commit();
            } else {
                //事务回滚
                conn.rollback();
                throw new RuntimeException("没找到连接对象");
            }
            return colums;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("执行SQL出错");
        }
    }
    /**
     * 执行查询SQL
     * @param sql
     * @param ob
     */
    public List<T> executeQuery(String sql, Object...ob) {
        //获取数据库连接对象
        conn = BaseDate.getConnection();
        List<T> list = new ArrayList<>();
        try {
            if (conn != null) {
                //使用数据库连接对象创建SQL执行对象
                ps = conn.prepareStatement(sql);
                //调用参数绑定方法
                bind(ob);
                //执行查询方法，并接受返回值
                rs = ps.executeQuery();
                while (rs.next()){
                    //调用结果集封装方法，将返回值添加到List集合当中
                    list.add(toBean());
                }
                return list;
            } else {
                throw new RuntimeException("没找到连接对象");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("执行SQL出错");
        } finally {
            //释放数据库连接资源
            BaseDate.release(conn,ps,rs);
        }
    }

    /**
     * 执行多条查询SQL
     * LinkedHashMap的Key是SQL语句（注意不能重复），value是查询条件参数
     * 参数为何使用LinkedHashMap集合而不是Map接口，为了保证数据的有序性
     * @param map
     * @return
     */
    public List<List<T>> executeQueryLists(LinkedHashMap<String,Object[]> map) {
        //获取数据库连接对象
        conn = BaseDate.getConnection();
        List<List<T>> lists = new ArrayList<>();
        try {
            if (conn != null) {
                //遍历Map集合
                Set<Map.Entry<String, Object[]>> entries =
                        map.entrySet();
                for (Map.Entry<String, Object[]> entry:entries) {
                    List<T> list = new ArrayList<>();
                    //创建执行对象
                    ps = conn.prepareStatement(entry.getKey());
                    //绑定参数
                    bind(entry.getValue());
                    //执行查询方法
                    rs = ps.executeQuery();
                    //遍历结果集，并调用结果集封装方法
                    while (rs.next()) {
                        list.add(toBean());
                    }
                    //添加到集合
                    lists.add(list);
                }
                return lists;
            } else {
                throw new RuntimeException("没找到连接对象");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("执行SQL出错");
        } finally {
            //释放资源
            BaseDate.release(conn,ps,rs);
        }
    }

    /**
     * 执行多条更新SQL语句
     * @param map
     */
    public int executeUpdates(Map<String,Object[]> map) {
        conn = BaseDate.getConnection();
        //影响行数
        int colums = 0;
        if (conn != null) {
            try {
                //关闭自动提交事务，方便使用事务
                conn.setAutoCommit(false);
                Set<Map.Entry<String, Object[]>> entries =
                        map.entrySet();
                for (Map.Entry<String, Object[]> entry:entries) {
                    ps = conn.prepareStatement(entry.getKey());
                    bind(entry.getValue());
                    colums+=ps.executeUpdate();
                }
                //提交事务
                conn.commit();
            } catch (SQLException e) {
                try {
                    //事务回滚
                    conn.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                    throw new RuntimeException("事务回滚失败");
                }
                e.printStackTrace();
                throw new RuntimeException("执行多条SQL出错");
            }
            return colums;
        } else {
            throw new RuntimeException("没找到数据库连接对象");
        }
    }
    /**
     * ORM映射
     * @return
     */
    private T toBean() {
        try {
            //创建实例对象
            T t = (T) cs.newInstance();
            //获取该Class对象的声明的所有方法
            Field[] fields = cs.getDeclaredFields();
            for (Field field:fields) {
                //开启私有访问权限
                field.setAccessible(true);
                //@TableField是一个自定义注解，是用来指定属性在数据库中相对应的字段名
                TableField tableField = field.getAnnotation(TableField.class);
                Object value = null;
                if (tableField!=null) {
                    //使用属性的自定义注解的value值从结果集当中取值
                    value = rs.getObject(tableField.value());
                } else {
                    //使用属性名从结果集当中取值
                    value = rs.getObject(field.getName());
                }
                //添加到实例当中去
                field.set(t,value);
            }
            return t;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("结果集封装失败");
        }
    }


    /**
     * 绑定SQL参数
     * @param ob
     */
    private void bind(Object[] ob) {
        if (ob!=null&&ob.length>0) {
            try {
                for (int i = 0,k = ob.length; i < k; i++) {
                    //绑定参数，下标从1开始
                    ps.setObject((i+1),ob[i]);
                }
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException("绑定SQL参数出错");
            }
        }
    }

}

测试类

package com.demo;

import com.demo.JDBC.CrudDao;
import com.demo.JDBC.User;

import java.util.*;

/**
 * @author 伍英杰
 */
public class Demo {

    public static void main(String[] args) {
        CrudDao<User> dao = new CrudDao<>(User.class);
        LinkedHashMap<String,Object[]> map = new LinkedHashMap<>();
        map.put("select * from user",new Object[]{});
        map.put("select * from user u where u.id = ?",new Object[]{3});
        List<List<User>> lists = dao.executeQueryLists(map);
        for (List<User> list:lists) {
            for (User user: list) {
                System.out.println(user);
            }
            System.out.println("---------------------------------------");
        }
    }
}

自定义注解

package com.demo.JDBC;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface TableField {
    public String value();
}

测试所用实体类

package com.demo.JDBC;

import java.io.Serializable;
import java.sql.Timestamp;

/**
 * <p>
 *
 * </p>
 *
 * @author psj
 * @since 2019-03-05
 */
public class User implements Serializable {

    /**
     * ID
     */
    private Long id;
    /**
     * 创建日期
     */
    @TableField("create_time")
    private Timestamp createTime;
    /**
     * 邮箱
     */
    private String email;

    /**
     * 状态：1启用、0禁用
     */
    private Boolean enabled;
    /**
     * 手机号码
     */
    @TableField("mobile_phone")
    private String mobilePhone;
    /**
     * 姓名
     */
    private String name;
    /**
     * 密码
     */
    private String password;

    /**
     * 用户名
     */
    private String username;

    /**
     * 最后修改密码的日期
     */
    @TableField("last_password_reset_time")
    private Timestamp lastPasswordResetTime;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Timestamp getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Timestamp createTime) {
        this.createTime = createTime;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Boolean getEnabled() {
        return enabled;
    }

    public void setEnabled(Boolean enabled) {
        this.enabled = enabled;
    }

    public String getMobilePhone() {
        return mobilePhone;
    }

    public void setMobilePhone(String mobilePhone) {
        this.mobilePhone = mobilePhone;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

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

    public String getUsername() {
        return username;
    }

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

    public Timestamp getLastPasswordResetTime() {
        return lastPasswordResetTime;
    }

    public void setLastPasswordResetTime(Timestamp lastPasswordResetTime) {
        this.lastPasswordResetTime = lastPasswordResetTime;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", createTime=" + createTime +
                ", email='" + email + '\'' +
                ", enabled=" + enabled +
                ", mobilePhone='" + mobilePhone + '\'' +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", username='" + username + '\'' +
                ", lastPasswordResetTime=" + lastPasswordResetTime +
                '}';
    }
}