基于JDBC的数据库DAO封装

2016-7-29 15:47| 发布者: zhangjf| 查看: 691| 评论: 0

摘要: 基于JDBC的数据库DAO封装， MYSQL 版本。可与下面的代码配合比较：基于Apache Dbutils 的数据库DAO封装地址： http://www.oschina.net/code/snippet_2688496_55658 import java.sql.Connectio ...

基于JDBC的数据库DAO封装， MYSQL 版本。
可与下面的代码配合比较：
基于Apache Dbutils 的数据库DAO封装
地址： http://www.oschina.net/code/snippet_2688496_55658

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;
}
}

复制代码

收藏邀请

上一篇：字符串操作2下一篇：url正则匹配

		自动登录	找回密码
密码			立即注册

基于JDBC的数据库DAO封装

最新评论

相关分类