基于JDBC的数据库DAO封装代码,Java代码库,德仔网

代码语言
代码分类
【Java】基于JDBC的数据库DAO封装
作者:明鑫 / 发布于2016/7/18/ 936
基于JDBC的数据库DAO封装， MYSQL 版本。可与下面的代码配合比较：基于Apache Dbutils 的数据库DAO封装
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.concurrent.ExecutionException;
 
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.builder.ToStringBuilder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
 
/**
 * 
 * 带事务支持的数据库操作DAO实现类
 * 
 * @author 00fly
 * @version [版本号, 2016-3-5]
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
public class BaseDAOImpl
{
    Logger log = LoggerFactory.getLogger(getClass());
     
    // 使用ThreadLocal存储当前线程中的Connection对象
    private ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
     
    private static MysqlDataSource dataSource = new MysqlDataSource();
     
    // 静态初始化 DataSource
    static
    {
        ResourceBundle config = ResourceBundle.getBundle("jdbc");
        dataSource.setUrl(config.getString("jdbc.url"));
        dataSource.setUser(config.getString("jdbc.username"));
        dataSource.setPassword(config.getString("jdbc.password"));
    }
     
    /**
     * 获取数据库连接
     * 
     * @return
     * @throws ExecutionException
     * @throws InterruptedException
     * @see [类、类#方法、类#成员]
     */
    protected Connection getConnection()
    {
        Connection connection = threadLocal.get();
        try
        {
            if (connection == null)
            {
                // 把 connection绑定到当前线程上
                connection = dataSource.getConnection();
                threadLocal.set(connection);
            }
        }
        catch (Exception e)
        {
            log.error(e.getMessage());
            throw new RuntimeException("Failed to get Mysql connection");
        }
        return connection;
    }
     
    /**
     * SQl查询,返回执行结果
     * 
     * @param sql 查询sql
     * @return
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    public List<Map<String, Object>> querySql(String sql)
        throws SQLException
    {
        return querySql(sql, null);
    }
     
    /**
     * 带可变参数查询,返回执行结果
     * 
     * @param sql 查询sql
     * @param para 可变参数
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> querySql(String sql, Object[] para)
        throws SQLException
    {
        log.info("querySql: {}, para: {}", sql, ToStringBuilder.reflectionToString(para));
        Connection conn = null;
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try
        {
            conn = getConnection();
            if (conn != null)
            {
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet rs = null;
                if (null != para && para.length > 0)
                {
                    for (int i = 0; i < para.length; i++)
                    {
                        ps.setObject(i + 1, para[i]);
                    }
                }
                rs = ps.executeQuery();
                ResultSetMetaData md = rs.getMetaData();
                int columnCount = md.getColumnCount(); // Map rowData
                while (rs.next())
                {
                    Map<String, Object> rowData = new LinkedHashMap<String, Object>();
                    for (int i = 1; i <= columnCount; i++)
                    {
                        rowData.put(md.getColumnName(i).toLowerCase(), rs.getObject(i));
                    }
                    list.add(rowData);
                }
                ps.close();
                rs.close();
            }
        }
        catch (SQLException e)
        {
            log.error("--------- QuerySql--" + e.getMessage());
            throw e;
        }
        finally
        {
            if (conn != null && conn.getAutoCommit() == true)
            {
                freeConnection();
            }
            log.info("BaseDAOImpl querySql end ");
        }
        return list;
    }
     
    /**
     * 带可变参数条件的分页查询
     * 
     * @param sql 查询sql
     * @param pageNo 页号
     * @param pageSize 每页记录数
     * @param para 可变参数
     * @return
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    public PaginationSupport queryForPagination(String sql, int pageNo, int pageSize, Object[] para)
        throws SQLException
    {
        // 保证正整数
        pageNo = Math.max(pageNo, 1);
        pageSize = Math.max(pageSize, 1);
         
        // 查询记录总条数
        int index = sql.toLowerCase().indexOf(" from ");
        String countSql = "select count(1)" + StringUtils.substring(sql, index);
        long total = queryForLong(countSql, para);
         
        // 查询当前页数据
        StringBuffer sbSql =
            new StringBuffer(sql).append(" limit ").append(pageSize * (pageNo - 1)).append(", ").append(pageSize);
        List<Map<String, Object>> list = querySql(sbSql.toString(), para);
         
        // 封装返回分页对象
        PaginationSupport page = new PaginationSupport(total, pageNo, pageSize);
        page.setItems(list);
        return page;
    }
     
    public Long queryCountSql(String countSql)
        throws SQLException
    {
        return queryCountSql(countSql, null);
    }
     
    /**
     * 带可变参数查询，返回记录条数
     * 
     * @param countSql 查询记录条数的sql
     * @param para 可变参数
     * @return
     * @throws SQLException
     */
    public Long queryCountSql(String countSql, Object[] para)
        throws SQLException
    {
        log.info("queryCountSql: {}, para: {}", countSql, ToStringBuilder.reflectionToString(para));
        Long count = null;
        Connection conn = null;
        try
        {
            conn = getConnection();
            if (conn != null)
            {
                PreparedStatement ps = conn.prepareStatement(countSql);
                ResultSet rs = null;
                if (null != para && para.length > 0)
                {
                    for (int i = 0; i < para.length; i++)
                    {
                        ps.setObject(i + 1, para[i]);
                    }
                }
                rs = ps.executeQuery();
                while (rs.next())
                {
                    count = rs.getLong(1);
                }
                ps.close();
                rs.close();
            }
        }
        catch (SQLException e)
        {
            log.error("--------- queryCountSql--" + e.getMessage());
            throw e;
        }
        finally
        {
            if (conn != null && conn.getAutoCommit() == true)
            {
                freeConnection();
            }
            log.info("BaseDAOImpl queryCountSql end ");
        }
        return count;
    }
     
    /**
     * 带可变参数查询，返回long类型数据
     * 
     * @param sql 查询sql
     * @param para 可变参数
     * @return
     * @throws SQLException
     */
    public Long queryForLong(String sql, Object[] para)
        throws SQLException
    {
        return queryCountSql(sql, para);
    }
     
    /**
     * 带可变参数查询,返回首条执行结果
     * 
     * @param sql 查询sql
     * @param para 可变参数
     * @return
     * @throws SQLException
     */
    public Map<String, Object> queryFirst(String sql, Object[] para)
        throws SQLException
    {
        if (!sql.contains(" limit ")) // 前后有空格
        {
            sql = sql + " limit 1";
        }
        List<Map<String, Object>> list = querySql(sql, para);
        if (list.isEmpty())
        {
            return Collections.emptyMap();
        }
        return list.get(0);
    }
     
    /**
     * 带可变参数, 执行sql插入，返回新增记录的自增主键<BR>
     * 注意： 若插入的表无自增主键则返回 0，异常的话则返回 null
     * 
     * @param sql
     * @param para
     * @return
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    public Long insertSql(String sql, Object[] para)
        throws SQLException
    {
        log.info("InsertSql: {}, para: {}", sql, ToStringBuilder.reflectionToString(para));
        Connection conn = null;
        Long id = null;
        try
        {
            conn = getConnection();
            if (conn != null)
            {
                // step1: 执行插入操作
                PreparedStatement ps = conn.prepareStatement(sql);
                if (null != para && para.length > 0)
                {
                    for (int i = 0; i < para.length; i++)
                    {
                        ps.setObject(i + 1, para[i]);
                    }
                }
                ps.executeUpdate();
                 
                // step2: 查询新增记录的自增主键
                ps = conn.prepareStatement("SELECT @@IDENTITY");
                ResultSet rs = ps.executeQuery();
                while (rs.next())
                {
                    id = rs.getLong(1);
                }
                ps.close();
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
            log.error("--------- execSql--" + e.getMessage());
        }
        finally
        {
            if (conn != null && conn.getAutoCommit() == true)
            {
                freeConnection();
            }
            log.info("BaseDAOImpl InsertSql end ");
        }
        return id;
    }
     
    /**
     * 带可变参数, 执行sql，返回执行结果
     * 
     * @param sql 执行的sql 语句
     * @param para 可变参数
     * @return
     * @throws SQLException
     */
    public int execSql(String sql, Object[] para)
        throws SQLException
    {
        log.info("execSql: {}, para: {}", sql, ToStringBuilder.reflectionToString(para));
        Connection conn = null;
        int rs = 0;
        try
        {
            conn = getConnection();
            if (conn != null)
            {
                PreparedStatement ps = conn.prepareStatement(sql);
                if (null != para && para.length > 0)
                {
                    for (int i = 0; i < para.length; i++)
                    {
                        ps.setObject(i + 1, para[i]);
                    }
                }
                rs = ps.executeUpdate();
                ps.close();
            }
        }
        catch (SQLException e)
        {
            log.error("--------- execSql--" + e.getMessage());
            throw e;
        }
        finally
        {
            if (conn != null && conn.getAutoCommit() == true)
            {
                freeConnection();
            }
            log.info("BaseDAOImpl execSql end ");
        }
        return rs;
    }
     
    /**
     * 批量更新
     * 
     * @param sql 需执行的sql
     * @param params 二维参数数组
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    public void executeBatch(String sql, Object[][] params)
        throws SQLException
    {
        log.info("executeBatch: {}, params:{}", sql, ToStringBuilder.reflectionToString(params));
        Connection conn = null;
        try
        {
            conn = getConnection();
            if (conn != null)
            {
                PreparedStatement ps = conn.prepareStatement(sql);
                if (null != params && params.length > 0)
                {
                    for (Object[] para : params)
                    {
                        for (int i = 0; i < para.length; i++)
                        {
                            ps.setObject(i + 1, para[i]);
                        }
                        ps.addBatch();
                    }
                }
                ps.executeBatch();
                ps.close();
            }
        }
        catch (Exception e)
        {
            log.error("--------- executeBatch Error:" + e.getMessage());
            throw new SQLException(e);
        }
        finally
        {
            if (conn != null && conn.getAutoCommit() == true)
            {
                freeConnection();
            }
            log.info("BaseDAOImpl executeBatch end ");
        }
         
    }
     
    /**
     * 批量更新
     * 
     * @param sql 需执行的sql
     * @param params List参数组
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    public void executeBatch(String sql, List<Object[]> params)
        throws SQLException
    {
        log.info("executeBatch: {}, params:{}", sql, ToStringBuilder.reflectionToString(params));
        Connection conn = null;
        try
        {
            conn = getConnection();
            if (conn != null)
            {
                PreparedStatement ps = conn.prepareStatement(sql);
                if (null != params && params.size() > 0)
                {
                    for (Object[] para : params)
                    {
                        for (int i = 0; i < para.length; i++)
                        {
                            ps.setObject(i + 1, para[i]);
                        }
                        ps.addBatch();
                    }
                }
                ps.executeBatch();
                ps.close();
            }
        }
        catch (Exception e)
        {
            log.error("--------- executeBatch Error:" + e.getMessage());
            throw new SQLException(e);
        }
        finally
        {
            if (conn != null && conn.getAutoCommit() == true)
            {
                freeConnection();
            }
            log.info("BaseDAOImpl executeBatch end ");
        }
    }
     
    /**
     * 释放数据库连接
     * 
     * @see [类、类#方法、类#成员]
     */
    public void freeConnection()
    {
        log.info("------释放数据库连接-------");
        try
        {
            Connection conn = threadLocal.get();
            if (conn != null)
            {
                conn.close();
                threadLocal.remove(); // 解除当前线程上绑定conn
            }
        }
        catch (Exception e)
        {
            log.error(e.getMessage());
            throw new RuntimeException(e);
        }
    }
     
    // ************** 事务操作 **************
     
    /**
     * 开启事务
     * 
     * @param connection
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    public void startTransaction()
    {
        log.info("------开启事务-------");
        try
        {
            Connection conn = threadLocal.get();
            if (conn == null)
            {
                conn = getConnection();
                threadLocal.set(conn);
            }
            conn.setAutoCommit(false); // 开启事务
        }
        catch (Exception e)
        {
            log.error(e.getMessage());
        }
    }
     
    /**
     * 提交事务
     * 
     * @see [类、类#方法、类#成员]
     */
    public void commit()
    {
        log.info("------提交事务-------");
        try
        {
            Connection conn = threadLocal.get();
            if (conn != null)
            {
                conn.commit();
            }
        }
        catch (Exception e)
        {
            log.error(e.getMessage());
        }
    }
     
    /**
     * 回滚事务
     * 
     * @see [类、类#方法、类#成员]
     */
    public void rollback()
    {
        log.info("------ 系统异常，回滚事务-------");
        try
        {
            Connection conn = threadLocal.get();
            if (conn != null)
            {
                conn.rollback();
            }
        }
        catch (Exception e)
        {
            log.error(e.getMessage());
        }
    }
}



import java.util.LinkedList;
import java.util.List;
 
/**
 * 分页对象
 * 
 * @author 00fly
 * @version [版本号, 2016-3-6]
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
public class PaginationSupport
{
     
    private long currentPage; // 当前页
     
    private long total; // 总记录数
     
    private int pageSize; // 每页大小
     
    private long totalPage; // 总页数
     
    private long previous; // 上一页
     
    private long next; // 下一页
     
    private List<?> items; // 当前页的数据
     
    @SuppressWarnings("rawtypes")
    public PaginationSupport(long total, int pageNo, int pageSize)
    {
        items = new LinkedList();
        this.pageSize = pageSize;
        currentPage = pageNo;
        setTotal(total);
    }
     
    public long getCurrentPage()
    {
        return currentPage;
    }
     
    public void setCurrentPage(int currentPage)
    {
        this.currentPage = currentPage;
    }
     
    public long getTotal()
    {
        return total;
    }
     
    public void setTotal(long totalcount)
    {
        total = totalcount;
        if (totalcount == 0)
        {
            totalPage = 0;
        }
        else
        {
            totalPage = 1 + (totalcount - 1) / pageSize;
        }
        if (currentPage < 1)
        {
            currentPage = 1;
        }
        else if (currentPage > totalPage)
        {
            currentPage = totalPage;
        }
        if (1 == currentPage)
        {
            previous = 1;
        }
        else
        {
            previous = currentPage - 1;
        }
        if (totalPage == currentPage)
        {
            next = currentPage;
        }
        else
        {
            next = currentPage + 1;
        }
    }
     
    public int getPageSize()
    {
        return pageSize;
    }
     
    public void setPageSize(int pageSize)
    {
        this.pageSize = pageSize;
    }
     
    public long getTotalPage()
    {
        return totalPage;
    }
     
    public void setTotalPage(long totalPage)
    {
        this.totalPage = totalPage;
    }
     
    public long getPrevious()
    {
        return previous;
    }
     
    public void setPrevious(long previous)
    {
        this.previous = previous;
    }
     
    public long getNext()
    {
        return next;
    }
     
    public void setNext(long next)
    {
        this.next = next;
    }
     
    public List<?> getItems()
    {
        return items;
    }
     
    public void setItems(List<?> items)
    {
        this.items = items;
    }
     
}
试试其它关键字
　基于JDBC　　数据库DAO　
同语言下
可能有用的
明鑫贡献的其它代码(17)