最近在看各种持久层 发现每一个框架提供的关于JDBC的封装都有限 我就按照易于扩展、灵活、简单方便的思路写了一个关于数据库持久层的操作 BaseDao
思路是这样的:
BaseDao要有如下的功能
1 对于简单的增删改查采用hibernate提供的HibernateDaoSupport
2 对于复杂的链接查询或者统计可以自己写sql语句
于是就写了如下的一个BaseDao 带分页功能的。
package com.sod.dao.base;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import com.sod.dao.dialect.MysqlDialect;
import com.sod.entity.sys.SodUser;
public class BaseDao<T> extends HibernateDaoSupport {
private JdbcTemplate jdbcTemplate;
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
@Autowired
public void setJT(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Autowired
public void setHT(HibernateTemplate hibernateTemplate) {
super.setHibernateTemplate(hibernateTemplate);
}
public Page<T> paginate(String sql,int pageNumber, int pageSize,Class<T> clazz,Object... paras) {
if (pageNumber < 1) {
pageNumber = 1;
}
if (pageSize < 1) {
pageSize = Page.DEFAULT_PAGE_SIZE;
}
long totalRow = 0;
int totalPage = 0;
//获取记录总数
totalRow = getJdbcTemplate().queryForLong(getSQLCount(sql));
if (totalRow == 0) {
return new Page<T>(new ArrayList<T>(0), pageNumber, pageSize, 0, 0); // totalRow = 0;
}
totalPage = (int) (totalRow / pageSize);
if (totalRow % pageSize != 0) {
totalPage++;
}
//获取mysql的分页sql
String paginateSql = MysqlDialect.getInstance().bulidPaginateSql(sql, pageNumber, pageSize);
List<Map<String, Object>> list = getJdbcTemplate().queryForList(paginateSql.toString());
List<T> tList = getList(list,clazz);
return new Page<T>(tList, pageNumber, pageSize, totalPage, totalRow);
}
public String getSQLCount(String sql){
String sqlBak = sql.toLowerCase();
String searchValue = " from ";
String sqlCount = "select count(*) from "+ sql.substring(sqlBak.indexOf(searchValue)+searchValue.length(), sqlBak.length());
return sqlCount;
}
public List<T> getList(List<Map<String, Object>> list,
Class<T> clazz){
List<T> listRes = new ArrayList<T>();
for (Map<String, Object> item : list) {
T obj;
try {
obj = clazz.newInstance();
Iterator<Entry<String, Object>> iter = item.entrySet()
.iterator();
while (iter.hasNext()) {
Map.Entry<String, Object> entry = (Map.Entry<String, Object>) iter
.next();
Object objKey = entry.getKey();
Object val = entry.getValue();
String key = treat(objKey.toString());
Field fieldItem = getDeclaredField(clazz,key);
if (fieldItem != null) {
fieldItem.setAccessible(true);
fieldItem.set(obj, val);
}
}
listRes.add(obj);
// log(item.toString()+":");
for (Object it : item.values()) {
if (it != null) {
it.toString();
// log(it.toString());
}
}
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return listRes;
}
public Field getDeclaredField(Class<?> clazz, String fieldName) {
Field field = null;
for (; clazz != Object.class; clazz = clazz.getSuperclass()) {
try {
field = clazz.getDeclaredField(fieldName);
return field;
} catch (Exception e) {
}
}
return field;
}
private String treat(String objKey) {
String str = objKey;
if (objKey.indexOf("_") != -1) {
String[] strArr = objKey.split("_");
String strRes = strArr[0];
for (int i = 1; i < strArr.length; i++) {
strRes = strRes + strArr[i].substring(0, 1).toUpperCase()
+ strArr[i].substring(1);
}
str = strRes;
}
return str;
}
public static void main(String[] args) throws IllegalArgumentException, IllegalAccessException {
BaseDao<SodUser> bd = new BaseDao<SodUser>();
Field f = bd.getDeclaredField(SodUser.class, "id1");
SodUser user = new SodUser();
f.setAccessible(true);
f.set(user, new Integer(100));
System.out.println(user.getId());
}
}
分页Page类
package com.sod.dao.base;
import java.io.Serializable;
import java.util.List;
/**
* @author zhaobb
* @version 创建时间:2012-12-16 上午10:13:20
* 类说明 :
*/
public class Page<T> implements Serializable{
private static final long serialVersionUID = 1L;
public static final int DEFAULT_PAGE_SIZE = 3;
private List<T> list; // list result of this page
private int pageNumber; // page number
private int pageSize; // result amount of this page
private int totalPage; // total page
private long totalRow; // total row
public Page(List<T> list, int pageNumber, int pageSize, int totalPage, long totalRow) {
this.list = list;
this.pageNumber = pageNumber;
this.pageSize = pageSize;
this.totalPage = totalPage;
this.totalRow = totalRow;
}
/**
* Return list of this page.
*/
public List<T> getList() {
return list;
}
/**
* Return page number.
*/
public int getPageNumber() {
return pageNumber;
}
/**
* Return page size.
*/
public int getPageSize() {
return pageSize;
}
/**
* Return total page.
*/
public int getTotalPage() {
return totalPage;
}
/**
* Return total row.
*/
public long getTotalRow() {
return totalRow;
}
}
写了一个Mysql 方言分页处理类MysqlDialect 这个是看了JFinal源码想到的
/**
* Copyright (c) 2011-2012, James Zhan 詹波 (jfinal@126.com).
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.sod.dao.dialect;
/**
* MysqlDialect.
*/
public class MysqlDialect {
static MysqlDialect mydialect = new MysqlDialect();
private MysqlDialect(){
}
public static MysqlDialect getInstance(){
if (mydialect == null) {
mydialect = new MysqlDialect();
}
return mydialect;
}
public String forTableInfoBuilderDoBuildTableInfo(String tableName) {
return "select * from `" + tableName + "` where 1 = 2";
}
public String forDbFindById(String tableName, String primaryKey, String columns) {
StringBuilder sql = new StringBuilder("select ");
if (columns.trim().equals("*")) {
sql.append(columns);
}
else {
String[] columnsArray = columns.split(",");
for (int i=0; i<columnsArray.length; i++) {
if (i > 0)
sql.append(", ");
sql.append("`").append(columnsArray[i].trim()).append("`");
}
}
sql.append(" from `");
sql.append(tableName.trim());
sql.append("` where `").append(primaryKey).append("` = ?");
return sql.toString();
}
public String forDbDeleteById(String tableName, String primaryKey) {
StringBuilder sql = new StringBuilder("delete from `");
sql.append(tableName.trim());
sql.append("` where `").append(primaryKey).append("` = ?");
return sql.toString();
}
public String bulidPaginateSql(String sql ,int pageNumber, int pageSize) {
StringBuilder paginationSQL = new StringBuilder();
int offset = pageSize * (pageNumber - 1);
paginationSQL.append(sql);
paginationSQL.append(" limit ").append(offset).append(", ").append(pageSize); // limit can use one or two '?' to pass paras
return paginationSQL.toString();
}
public boolean isSupportAutoIncrementKey() {
return true;
}
}
下面是BaseDao的一个应用SodUserDao类
package com.sod.dao.sys;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import com.sod.dao.base.BaseDao;
import com.sod.dao.base.Page;
import com.sod.entity.sys.SodUser;
/**
* @author zhaobb
* @version 创建时间:2012-12-15 上午10:56:49
* 类说明 :
*/
@Repository
public class SodUserDao extends BaseDao<SodUser>{
public Integer save(SodUser user){
Integer id = (Integer)getHibernateTemplate().save(user);
return id;
}
public SodUser getById(int id){
return getHibernateTemplate().get(SodUser.class, id);
}
public Page<SodUser> findUserByPage(int pageSize, int pageNumber){
String sql = "select * from sod_user";
Page<SodUser> page = paginate(sql, pageNumber, pageSize,SodUser.class);
return page;
}
}
上面的 SodUserDao中分页功能是自己写的一个sql语句 ,没有采用hibernate的分页功能。
基本就这些了,希望专家指正。哈哈