jdbc实现的orm工具类代码教程

17-11-14 来源：[db:作者]

收藏我要投稿

新公司的ORM框架使用了hibernate，但是我并不会，刚来项目老板催的紧，而且项目还是我独立开发，所以就自己用JDBC完成功能，但是你懂的，jdbc代码的冗余，操作的复杂都是我们初学就很烦的事儿了，所以花了半天时间写了一个BaseDao出来，代码如下：

import java.lang.reflect.ParameterizedType;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.uqiauto.util.ConnectionUtils;


/**
 * dao层父类
 * 封装了大部分的增删改查代码，但是关键实现都设置为抽象，继承此类需要提供具体实现
 * 因为父类try了SQLException，如果声明其他异常可能会导致程序终止
 * 所以如果需要限制访问权限，可以在子类实现中声明SQLException或其子类异常
 * 例：限制用户添加 throw new SQLException("This table is not allowed to be added");
 */
public abstract class BaseDao{

    private final Class entityClass;
    private final String entityClassName;

    @SuppressWarnings("unchecked")
    public BaseDao() {
        // 通过范型反射，获取在子类中定义的entityClass.
        this.entityClass = (Class) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
        entityClassName = entityClass.getSimpleName();
    }

    /**
     * 获取实体类对象
     */
    public Class getEntityClass() {
        return entityClass;
    }

    /**
     * 获取实体类名（不包括包结构）
     */
    public String getEntityClassName() {
        return entityClassName;
    }

    /**
     * 调用子类方法的入口，子类中只需要提供toObject方法的实现
     * 查询无参实现
     * @param sql
     * @param values
     * @return
     */
    public T queryOne(String sql){
        T data = null;
        try {
            data = this.entityClass.newInstance();
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            try {
                Connection conn = ConnectionUtils.getConnection();
                pstmt = conn.prepareStatement(sql);
                rs = pstmt.executeQuery();
                toObject(rs, data);
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                ConnectionUtils.closeAll(rs, pstmt, Boolean.TRUE);
            }
        } catch (InstantiationException | IllegalAccessException e1) {
            e1.printStackTrace();
        }
        return data;
    }

    /**
     * 由子类继承，父类中不提供任何实现
     * @param rs    
     *      查询返回的结果集
     * @param data
     *      查询后保存的对象
     * @return
     *      参数data
     * @throws SQLException
     *      必须处理异常
     */
    protected abstract void toObject(ResultSet rs, T entity) throws SQLException;

    /**
     * 查询结果数量
     * @param sql
     * @return
     */
    public int queryCount(String sql){
        int count = 0;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = ConnectionUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if(rs.next()){
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            ConnectionUtils.closeAll(rs, pstmt, Boolean.TRUE);
        }
        return count;
    }

    /**
     * 调用子类方法的入口，子类中只需要提供toObjectOfList方法的实现
     * 查询不带参数实现
     * @param sql
     * @param values
     * @return
     */
    public List queryList(String sql){
        List data = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = ConnectionUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            toObjectOfList(rs, data);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            ConnectionUtils.closeAll(rs, pstmt, Boolean.TRUE);
        }
        return data;
    }

    /**
     * 分页查询，注意调用者必须验证返回值中的success函数
     * @param sql
     *      查询的SQL语句，不需要limit
     * @param curPage
     *      当前页码
     * @param pageSize
     *      每页显示条数
     * @return
     *      保存分页相关参数的map集合
     *      totalRows：数据条数
     *      page：页数
     *      data：返回数据
     *      success：验证分页 true->成功  false->失败
     */
    public Map queryListByPage(String sql, int curPage, int pageSize){
        Map map = new HashMap<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = ConnectionUtils.getConnection();

            String countSql = sql.replace("*", "count(*)");
            pstmt = conn.prepareStatement(countSql);
            rs = pstmt.executeQuery();
            int count = rs.next() ? rs.getInt(1) : 0;
            int pageCount = count%pageSize == 0 ? count/pageSize : count/pageSize+1;
            map.put("totalRows", count);
            map.put("page", pageCount);

            List data = new ArrayList<>();
            int begin = (curPage-1)*pageSize+1;
            sql = sql.contains("where") ? sql + " limit " : sql + " where 1=1 limit ";
            sql = sql + begin + "," + pageSize;
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            toObjectOfList(rs, data);
            map.put("data", data);
            map.put("success", true);
        } catch (SQLException e) {
            map.put("success", false);
            e.printStackTrace();
        }finally {
            ConnectionUtils.closeAll(rs, pstmt, Boolean.TRUE);
        }
        return map;
    }

    /**
     * 可以由子类继承，父类中所提供默认实现的内部是由子类实现的toObject方法
     * @param rs    
     *      查询返回的结果集
     * @param data
     *      查询后保存的集合
     * @return
     *      参数data
     * @throws SQLException
     *      必须处理异常
     */
    protected List toObjectOfList(ResultSet rs, List data) throws SQLException{
        if(rs != null && data != null){
            int rowCount = rs.last() ? rs.getRow() : 0;
            rs.beforeFirst();
            try {
                T t;
                for(int i=0; i< rowCount; i++){
                    t = this.entityClass.newInstance();
                    toObject(rs, t);
                    data.add(t);
                }
            } catch (InstantiationException | IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        return data;
    }

    /**
     * 添加一条数据方法，参数放到SQL中直接执行
     * @param sql 执行SQL
     * @return 验证操作成功
     */
    public boolean insert(String sql){
        PreparedStatement pstmt = null;
        Connection conn =  null;
        try {
            conn = ConnectionUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.executeUpdate();
            conn.commit();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            return false;
        }finally{
            ConnectionUtils.closeAll(null, pstmt, Boolean.TRUE);
        }
    }

    /**
     * 添加一条数据方法，参数的传递使用实体的值和实体对应dao层封装的赋值方法
     * @param sql 执行SQL
     * @param t  带参数的实体对象
     * @return 验证操作成功
     */
    public boolean insert(String sql, T entity){
        PreparedStatement pstmt = null;
        Connection conn =  null;
        try {
            conn = ConnectionUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            doInsertSetPstmt(pstmt, entity);
            pstmt.executeUpdate();
            conn.commit();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            return false;
        }finally{
            ConnectionUtils.closeAll(null, pstmt, Boolean.TRUE);
        }
    }

    /**
     * 批量添加数据，参数的传递使用实体的值和实体对应dao层封装的赋值方法
     * @param sql 执行SQL
     * @param dataList  保存带参数实体对象的集合
     * @return 验证操作成功
     */
    public boolean batchInsert(String sql, List dataList){
        PreparedStatement pstmt = null;
        Connection conn =  null;
        try {
            conn = ConnectionUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            int count = 0;
            for (T t : dataList) {
                doInsertSetPstmt(pstmt, t);
                pstmt.addBatch();
                if(++count == 500){
                    pstmt.executeBatch();
                    count = 0;
                }
            }
            pstmt.executeBatch();
            conn.commit();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            return false;
        }finally{
            ConnectionUtils.closeAll(null, pstmt, Boolean.TRUE);
        }
    }

    /**
     * 由子类继承，父类中不提供任何实现
     * @param pstmt
     * @param entity
     * @throws SQLException 
     */
    protected abstract void doInsertSetPstmt(PreparedStatement pstmt, T entity) throws SQLException;

    /**
     * 删除SQL中的数据
     * @param sql
     * @return
     */
    public boolean delete(String sql){
        PreparedStatement pstmt = null;
        Connection conn =  null;
        try {
            conn = ConnectionUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.executeUpdate();
            doDelete(conn);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            return false;
        }finally{
            ConnectionUtils.closeAll(null, pstmt, Boolean.TRUE);
        }
    }

    /**
     * 根据id集合批量删除
     * @param sql
     * @param ids
     * @return
     */
    public boolean batchDelete(String sql, List ids){
        PreparedStatement pstmt = null;
        Connection conn =  null;
        try {
            conn = ConnectionUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            int count = 0;
            for (int i : ids) {
                pstmt.setInt(1, i);
                pstmt.addBatch();
                if(++count == 500){
                    pstmt.executeBatch();
                    count = 0;
                }
            }
            pstmt.executeBatch();
            doDelete(conn);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            return false;
        }finally{
            ConnectionUtils.closeAll(null, pstmt, Boolean.TRUE);
        }
    }
    protected abstract void doDelete(Connection conn) throws SQLException;

    /**
     * 修改SQL中的数据
     * @param sql
     * @return
     */
    public boolean update(String sql){
        PreparedStatement pstmt = null;
        Connection conn =  null;
        try {
            conn = ConnectionUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.executeUpdate();
            conn.commit();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            return false;
        }finally{
            ConnectionUtils.closeAll(null, pstmt, Boolean.TRUE);
        }
    }

    /**
     * 批量修改集合中的所有数据
     * @param sql
     * @param dataList
     * @return
     */
    public boolean batchUpdate(String sql, List dataList){
        PreparedStatement pstmt = null;
        Connection conn =  null;
        try {
            conn = ConnectionUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            int count = 0;
            for (T t : dataList) {
                doUpdateSetPstmt(pstmt, t);
                pstmt.addBatch();
                if(++count == 500){
                    pstmt.executeBatch();
                    count = 0;
                }
            }
            pstmt.executeBatch();
            conn.commit();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            return false;
        }finally{
            ConnectionUtils.closeAll(null, pstmt, Boolean.TRUE);
        }
    }

    protected abstract void doUpdateSetPstmt(PreparedStatement pstmt, T entity) throws SQLException;

    public java.sql.Date toSqlDate(Date date){
        java.sql.Date sqlDate = new java.sql.Date(new Date().getTime());
        if(date != null)
            sqlDate = new java.sql.Date(date.getTime());
        return sqlDate;
    }

    public Date toUtilDate(java.sql.Date sqlDate){
        Date date = new Date();
        if(sqlDate != null)
            date = new Date(sqlDate.getTime());
        return date;
    }

}

连接MySQL数据库的工具类：

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class ConnectionUtils {

    // 线程单例

    private static ThreadLocal tl = new ThreadLocal();

    private static String url;

    private static String username;

    private static String password;

    static {
        // 装载驱动参数
        try {
            ClassLoader classLoader = ConnectionUtils.class.getClassLoader();
            InputStream is = classLoader.getResourceAsStream("standard.properties");
            Properties props = new Properties();
            props.load(is);
            url = props.getProperty("url");
            username = props.getProperty("username");
            password = props.getProperty("password");
            // 注册驱动
            Class.forName(props.getProperty("jdbc.driverName"));
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException{
        Connection con = tl.get();
        if (con == null || con.isClosed()) {
            con = DriverManager.getConnection(url, username, password);
            tl.set(con);
        }
        return con;
    }

    public static void closeConnection() {
        Connection conn = tl.get();
        if (conn == null)
            return;
        try {
            if (!conn.isClosed()) {
                //关闭数据库连接
                conn.close();
            }
        } catch (SQLException e) {
            System.err.println("#ERROR# :关闭数据库连接发生异常，请检查！\n" + e.getMessage());
        }
    }

    public static void closeAll(ResultSet rs, Statement stmt, boolean closeConn) {
        try {
            if (rs != null)
                rs.close();
            if (stmt != null)
                stmt.close();
            if(closeConn)
                closeConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

实体类如下：

public class Area{
    //----------- object properties
    private Integer id;
    private String name;
    private Integer parent_id;
    private Byte sort;
    private Byte deep;
    private String city_code;
    private String region;
    private Integer status;
    private Integer ad_code;

    //------------ database columns
    public static final String ID = "AREA_ID";
    public static final String NAME = "AREA_NAME";
    public static final String PARENT_ID = "AREA_PARENT_ID";
    public static final String SORT = "AREA_SORT";
    public static final String DEEP = "AREA_DEEP";
    public static final String CITY_CODE = "CITY_CODE";
    public static final String REGION = "AREA_REGION";
    public static final String STATUS = "AREA_STATUS";
    public static final String AD_CODE = "AD_CODE";
    //------------ get or set ...

dao实现类如下：

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

import com.entity.Area;

public class AreaDao extends BaseDao<Area>{

    @Override
    protected void toObject(ResultSet rs, Area data) throws SQLException{
        if(rs.next()){
            data.setId(rs.getInt(Area.ID));
            data.setName(rs.getString(Area.NAME));
            data.setParent_id(rs.getInt(Area.PARENT_ID));
            data.setSort(rs.getByte(Area.SORT));
            data.setDeep(rs.getByte(Area.DEEP));
            data.setCity_code(rs.getString(Area.CITY_CODE));
            data.setRegion(rs.getString(Area.REGION));
            data.setStatus(rs.getInt(Area.STATUS));
            data.setAd_code(rs.getInt(Area.AD_CODE));
        }
    }

    @Override
    protected void doInsertSetPstmt(PreparedStatement pstmt, Area t) throws SQLException {
        throw new SQLException("This table is not allowed to be added");
    }

    @Override
    protected void doUpdateSetPstmt(PreparedStatement pstmt, Area t) throws SQLException {
        throw new SQLException("This form is not allowed to be amended");
    }
    @Override
    protected void doDelete(Connection conn) throws SQLException {
        throw new SQLException("This form is not allowed to be deleted");
    }

}

我这里只是封装了一些冗余代码，并没有完成属性映射，一开始想着是用反射实现，后来发现时间不够。。。然后就先写到这里了，写的过程想到我们可以用自定义注解来配置实体和表的映射，后面有时间会研究研究。

然后突然发现ORM框架其实不难（说这句话的时候我还没有研究过人家框架的源码，也许并不像我想的那么简单），也许以后有时间会自己完成一个ORM框架吧，先到这里，去赶项目了。

点击复制链接与好友分享!回本站首页