使用ibatis操作数据库的封装
Posted on使用ibatis操作数据库的封装
近期刚进入公司,也是本人的第一份正式工作,公司使用的ORM框架是ibatis,下面代码是对batis dao的一个封装,主要继承自spring的SqlMapClientDaoSupport,负责为单个Entity 提供CRUD操作的IBatis DAO基类,使用该基类,可以减少不少代码量。
view plaincopy to clipboardprint?
package com.nfschina.utils.dao.ibatis
import java.io.Serializable;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.util.Assert;
import com.ibatis.sqlmap.engine.impl.SqlMapClientImpl;
import com.ibatis.sqlmap.engine.mapping.sql.stat.StaticSql;
import com.ibatis.sqlmap.engine.mapping.statement.MappedStatement;
importcom.nfschina.utils.BaseException;
import com.nfschina.utils.DataPage;
import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport
///
/* IBatis Dao的泛型基类.
/* 继承于Spring的SqlMapClientDaoSupport,提供分页函数和若干便捷查询方法,并对返回值作了泛型类型转换.
/*/
@SuppressWarnings("unchecked")
public class IBatisGenericDao extends SqlMapClientDaoSupport {
public static final String POSTFIX_INSERT = ".insert";
public static final String POSTFIX_UPDATE = ".update";
public static final String POSTFIX_DELETE = ".delete";
public static final String POSTFIX_DELETE_PRIAMARYKEY = ".deleteByPrimaryKey";
public static final String POSTFIX_SELECT = ".select";
public static final String POSTFIX_SELECTMAP = ".selectByMap";
public static final String POSTFIX_SELECTSQL = ".selectBySql";
public static final String POSTFIX_COUNT = ".count";
///
/* 根据ID获取对象
/*
/* @throws BaseException
/* @throws SQLException
/*/
public
T get(Class entityClass, Serializable id) throws BaseException, SQLException { T o = (T) getSqlMapClient().queryForObject(entityClass.getName() + POSTFIX_SELECT, id);
if (o == null)
throw new BaseException(BaseException.DATA_NOTFOUND, "未找到实体: " + id);
return o;
}
///
/* 获取全部对象
/* @throws SQLException
/*/
public
List getAll(Class entityClass) throws SQLException { return getSqlMapClient().queryForList(entityClass.getName() + POSTFIX_SELECT, null);
}
///
/* 新增对象
/* @throws SQLException
/*/
public void insert(Object o) throws SQLException {
getSqlMapClient().insert(o.getClass().getName() + POSTFIX_INSERT, o);
}
///
/* 保存对象
/* @throws SQLException
/*/
public int update(Object o) throws SQLException {
return getSqlMapClient().update(o.getClass().getName() + POSTFIX_UPDATE, o);
}
///
/* 删除对象
/* @throws SQLException
/*/
public int remove(Object o) throws SQLException {
return getSqlMapClient().delete(o.getClass().getName() + POSTFIX_DELETE, o);
}
///
/* 根据ID删除对象
/* @throws SQLException
/*/
public
int removeById(Class entityClass, Serializable id) throws SQLException { return getSqlMapClient().delete(entityClass.getName() + POSTFIX_DELETE_PRIAMARYKEY, id);
}
///
/* map查询.
/*
/* @param map
/* 包含各种属性的查询
/* @throws SQLException
/*/
public
List find(Class entityClass, Map map) throws SQLException { if (map == null)
return this.getSqlMapClient().queryForList(entityClass.getName() + POSTFIX_SELECT, null);
else {
map.put("findBy", "True");
return this.getSqlMapClient()
.queryForList(entityClass.getName() + POSTFIX_SELECTMAP, map);
}
}
///
/* sql 查询.
/*
/* @param sql
/* 直接sql的语句(需要防止注入式攻击)
/* @throws SQLException
/*/
public
List find(Class entityClass, String sql) throws SQLException { Assert.hasText(sql);
if (StringUtils.isEmpty(sql))
return this.getSqlMapClient().queryForList(entityClass.getName() + POSTFIX_SELECT, null);
else
return this.getSqlMapClient()
.queryForList(entityClass.getName() + POSTFIX_SELECTSQL, sql);
}
///
/* 根据属性名和属性值查询对象.
/*
/* @return 符合条件的对象列表
/* @throws SQLException
/*/
public
List findBy(Class entityClass, String name, Object value) throws SQLException { Assert.hasText(name);
Map
map = new HashMap (); map.put(name, value);
return find(entityClass, map);
}
///
/* 根据属性名和属性值查询对象.
/*
/* @return 符合条件的唯一对象
/*/
public
T findUniqueBy(Class entityClass, String name, Object value) { Assert.hasText(name);
Map
map = new HashMap (); try {
PropertyUtils.getProperty(entityClass.newInstance(), name);
map.put(name, value);
map.put("findUniqueBy", "True");
return (T) getSqlMapClient().queryForObject(entityClass.getName() + POSTFIX_SELECTMAP,
map);
} catch (Exception e) {
logger.error("Error when propertie on entity," + e.getMessage(), e.getCause());
return null;
}
}
///
/* 根据属性名和属性值以Like AnyWhere方式查询对象.
/* @throws SQLException
/*/
public
List findByLike(Class entityClass, String name, String value) throws SQLException { Assert.hasText(name);
Map
map = new HashMap (); map.put(name, value);
map.put("findLikeBy", "True");
return getSqlMapClient().queryForList(entityClass.getName() + POSTFIX_SELECTMAP, map);
}
///
/* 判断对象某些属性的值在数据库中不存在重复
/*
/* @param tableName
/* 数据表名字
/* @param names
/* 在POJO里不能重复的属性列表,以逗号分割 如"name,loginid,password"
/* FIXME how about in different schema?
/*/
public boolean isNotUnique(Object entity, String tableName, String names) {
try {
String primarykey;
Connection con = getSqlMapClient().getCurrentConnection();
ResultSet dbMetaData = con.getMetaData().getPrimaryKeys(con.getCatalog(), null, tableName);
dbMetaData.next();
if (dbMetaData.getRow() > 0) {
primarykey = dbMetaData.getString(4);
if (names.indexOf(primarykey) > -1)
return false;
} else {
return true;
}
} catch (SQLException e) {
logger.error(e.getMessage(), e);
return false;
}
return false;
}
///
/* 分页查询函数,使用PaginatedList.
/*
/* @param pageNo
/* 页号,从0开始.
/* @throws SQLException
/*/
@SuppressWarnings("rawtypes")
public DataPage pagedQuery(String sqlName, HashMap
hashMap, Integer pageNo, Integer pageSize) throws SQLException {
if (pageNo == null || pageSize == null) {
List list = getSqlMapClient().queryForList(sqlName, hashMap);
if (list == null || list.size() == 0) {
return new DataPage();
} else {
return new DataPage(0, list.size(), list.size(), list);
}
} else {
Assert.hasText(sqlName);
Assert.isTrue(pageNo >= 1, "pageNo should start from 1");
// Count查询
Integer totalCount = (Integer) getSqlMapClient().queryForObject(sqlName + ".Count", hashMap);
if (totalCount < 1) {
return new DataPage();
}
// 实际查询返回分页对象
int startIndex = DataPage.getStartOfPage(pageNo, pageSize);
hashMap.put("startIndex", startIndex);
hashMap.put("pageSize", pageSize);
List list = getSqlMapClient().queryForList(sqlName, hashMap);
return new DataPage(startIndex, totalCount, pageSize, list);
}
}
public String getMappedSQL(String sqlName) {
String sql = null;
SqlMapClientImpl sqlmap = (SqlMapClientImpl) getSqlMapClient();
MappedStatement stmt = sqlmap.getMappedStatement(sqlName);
StaticSql staticSql = (StaticSql) stmt.getSql();
sql = staticSql.getSql(null, null);
return sql;
}
}
package com.nfschina.utils.dao.ibatis import java.io.Serializable; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.commons.beanutils.PropertyUtils; import org.apache.commons.lang.StringUtils; import org.springframework.util.Assert; import com.ibatis.sqlmap.engine.impl.SqlMapClientImpl; import com.ibatis.sqlmap.engine.mapping.sql.stat.StaticSql; import com.ibatis.sqlmap.engine.mapping.statement.MappedStatement; importcom.nfschina.utils.BaseException; import com.nfschina.utils.DataPage; import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport /// / IBatis Dao的泛型基类. / 继承于Spring的SqlMapClientDaoSupport,提供分页函数和若干便捷查询方法,并对返回值作了泛型类型转换. // @SuppressWarnings("unchecked") public class IBatisGenericDao extends SqlMapClientDaoSupport { public static final String POSTFIX_INSERT = ".insert"; public static final String POSTFIX_UPDATE = ".update"; public static final String POSTFIX_DELETE = ".delete"; public static final String POSTFIX_DELETE_PRIAMARYKEY = ".deleteByPrimaryKey"; public static final String POSTFIX_SELECT = ".select"; public static final String POSTFIX_SELECTMAP = ".selectByMap"; public static final String POSTFIX_SELECTSQL = ".selectBySql"; public static final String POSTFIX_COUNT = ".count"; /// / 根据ID获取对象 / / @throws BaseException / @throws SQLException // public
/ FIXME how about in different schema? // public boolean isNotUnique(Object entity, String tableName, String names) { try { String primarykey; Connection con = getSqlMapClient().getCurrentConnection(); ResultSet dbMetaData = con.getMetaData().getPrimaryKeys(con.getCatalog(), null, tableName); dbMetaData.next(); if (dbMetaData.getRow() > 0) { primarykey = dbMetaData.getString(4); if (names.indexOf(primarykey) > -1) return false; } else { return true; } } catch (SQLException e) { logger.error(e.getMessage(), e); return false; } return false; } /// / 分页查询函数,使用PaginatedList. / / @param pageNo / 页号,从0开始. / @throws SQLException /*/ @SuppressWarnings("rawtypes") public DataPage pagedQuery(String sqlName, HashMap
view plaincopy to clipboardprint?
package com.nfschina.utils.dao.ibatis;
import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.orm.ObjectRetrievalFailureException;
import com.nfschina.utils.BaseException;
import com.nfschina.utils.GenericsUtils;
import
///
/* 负责为单个Entity 提供CRUD操作的IBatis DAO基类.
/*
/* 子类只要在类定义时指定所管理Entity的Class, 即拥有对单个Entity对象的CRUD操作.
/*
/*
/* public class UserManagerIbatis extends IBatisEntityDao
{ /* }
/*
/*/
public class IBatisEntityDao
extends IBatisGenericDao { ///
/* DAO所管理的Entity类型.
/*/
protected Class
entityClass; protected String primaryKeyName;
///
/* 在构造函数中将泛型T.class赋给entityClass.
/*/
@SuppressWarnings("unchecked")
public IBatisEntityDao() {
entityClass = (Class
) GenericsUtils.getSuperClassGenricType(getClass()); }
///
/* 根据属性名和属性值查询对象.
/*
/* @return 符合条件的对象列表
/* @throws SQLException
/*/
public List
findBy(String name, Object value) throws SQLException { return findBy(getEntityClass(), name, value);
}
///
/* 根据属性的名值对查询对象
/* @param map
/* @return
/* @throws SQLException
/*/
public List
find(Map map) throws SQLException{ return find(getEntityClass(), map);
}
///
/* 根据属性的名值对查询唯一对象
/* @param map
/* @return
/* @throws SQLException
/*/
public T findUniqueByMap(Map
map) throws SQLException{ List
list = find(getEntityClass(), map); if(list == null || list.size() <= 0){
return null;
}
return list.get(0);
}
///
/* 根据属性名和属性值以Like AnyWhere方式查询对象.
/* @throws SQLException
/*/
public List
findByLike(String name, String value) throws SQLException { return findByLike(getEntityClass(), name, value);
}
///
/* 根据属性名和属性值查询单个对象.
/*
/* @return 符合条件的唯一对象
/*/
public T findUniqueBy(String name, Object value) {
return findUniqueBy(getEntityClass(), name, value);
}
///
/* 根据ID获取对象.
/*
/* @throws BaseException
/* @throws SQLException
/*/
public T get(Serializable id) throws BaseException, SQLException {
return get(getEntityClass(), id);
}
///
/* 获取全部对象.
/* @throws SQLException
/*/
public List
getAll() throws SQLException { return getAll(getEntityClass());
}
///
/* 取得entityClass.
/*
/* JDK1.4不支持泛型的子类可以抛开Class
entityClass,重载此函数达到相同效果。 /*/
protected Class
getEntityClass() { return entityClass;
}
public String getPrimaryKeyName() {
if (StringUtils.isEmpty(primaryKeyName))
primaryKeyName = "id";
return primaryKeyName;
}
protected Object getPrimaryKeyValue(Object o) throws NoSuchMethodException, IllegalAccessException,
InvocationTargetException, InstantiationException {
return PropertyUtils.getProperty(entityClass.newInstance(), getPrimaryKeyName());
}
///
/* 根据ID移除对象.
/* @throws SQLException
/*/
public int removeById(Serializable id) throws SQLException {
return removeById(getEntityClass(), id);
}
///
/* 保存对象. 为了实现IEntityDao 我在内部使用了insert和upate 2个方法.
/* @throws SQLException
/*/
public void saveOrUpdate(Object o) throws SQLException {
Object primaryKey;
try {
primaryKey = getPrimaryKeyValue(o);
} catch (Exception e) {
throw new ObjectRetrievalFailureException(entityClass, e);
}
if (primaryKey == null)
insert(o);
else
update(o);
}
public void setPrimaryKeyName(String primaryKeyName) {
this.primaryKeyName = primaryKeyName;
}
public String getIdName(Class<?> clazz) {
return "id";
}
}
package com.nfschina.utils.dao.ibatis; import java.io.Serializable; import java.lang.reflect.InvocationTargetException; import java.sql.SQLException; import java.util.List; import java.util.Map; import org.apache.commons.beanutils.PropertyUtils; import org.apache.commons.lang.StringUtils; import org.springframework.orm.ObjectRetrievalFailureException; import com.nfschina.utils.BaseException; import com.nfschina.utils.GenericsUtils; import /// / 负责为单个Entity 提供CRUD操作的IBatis DAO基类. /
/ 子类只要在类定义时指定所管理Entity的Class, 即拥有对单个Entity对象的CRUD操作. / //public class UserManagerIbatis extends IBatisEntityDao