传统情况下,我们使用mybatis,都是通过映射文件获取sql。在这里,我们通过对实体添加注解类,在调用的过程中,通过对实体参数对象和注解类进行解析,获取到动态的sql。
大致流程:
如图,我们创建一个Test类,Test类调用具体的Dao,而Dao通过调用IBatisDaoUtils和DynamicSql工具类获取到动态的sql,从而通过sqlMapClientTemplate实现访问数据库的功能。
mybatis配置spring-ibatis.xml
<context:component-scan base-package="com"/>
<!-- 数据 -->
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/lpWeb"/>
<property name="username" value="root"/>
<property name="password" value="root123"/>
</bean>
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation" value="classpath:/sql-map-config.xml"/>
<property name="dataSource" ref="dataSource"/>
</bean>
<bean id="sqlMapClientTemplate" class="org.springframework.orm.ibatis.SqlMapClientTemplate">
<property name="sqlMapClient" ref="sqlMapClient"/>
</bean>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
映射文件
sql-map-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<settings cacheModelsEnabled="true" enhancementEnabled="true"
lazyLoadingEnabled="true" maxRequests="320" maxSessions="100"
maxTransactions="100" useStatementNamespaces="true" />
<sqlMap resource="file.xml"/>
<sqlMap resource="dynamicSqlMapper.xml"/>
</sqlMapConfig>
<pre name="code" class="html">
dynamicSqlMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="GenericSql">
<statement id="SelectObjectVOSql" parameterClass="java.util.HashMap"
resultClass="java.util.HashMap" remapResults="true">
select
<isNotEmpty property="sqlvalue">
$sqlvalue$
</isNotEmpty>
from
<isNotEmpty property="sqltablename">
$sqltablename$
</isNotEmpty>
<dynamic prepend="where">
<isNotNull property="sqlwhere">
<iterate prepend=" " property="sqlwhere" conjunction=",">
$sqlwhere[].key$ = #sqlwhere[].value#
</iterate>
</isNotNull>
</dynamic>
</statement>
</sqlMap>
Test
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:/spring-ibatis.xml" })
public class TestNewsDao {
@Autowired
BaseDao baseDao;
@Test
public void testNew() {
try {
NewsDto newsDto = new NewsDto();
newsDto.setNewsId(1);
newsDto = (NewsDto) baseDao.getRow(newsDto);
System.out.println(newsDto);
} catch (Exception e) {
e.printStackTrace();
}
}
NewsDaoImpl
@Scope("prototype")
@Service("newsDao")
public class NewsDaoImpl extends BaseDaoImpl<NewsDto,String> implements INewsDao{
public NewsDaoImpl() {
super(NewsDto.class);
}
}
INewsDao
public interface INewsDao {
}
BaseDaoImpl
package com.file.dao;
import java.io.Serializable;
import java.util.List;
import javax.annotation.Resource;
import org.springframework.orm.ibatis.SqlMapClientTemplate;
import com.entity.IBaseDto;
import com.sql.DynamicSql;
import com.util.IBatisDaoUtils;
public abstract class BaseDaoImpl<T extends IBaseDto, PK extends Serializable>
implements BaseDao<T, PK> {
@Resource(name = "sqlMapClientTemplate")
private SqlMapClientTemplate sqlMapClientTemplate;
private Class<T> persistentClass;
public BaseDaoImpl(Class<T> persistentClass) {
this.persistentClass = persistentClass;
}
private List<T> getAll(T object, String tableName) {
List list = sqlMapClientTemplate.queryForList("GenericSql.SelectObjectVOSql",
DynamicSql.getInstance().SelectSql(object, tableName));
return DynamicSql.getInstance().ListHashToListbean(list, object);
}
public List<T> getAll(T object) {
return getAll(object, IBatisDaoUtils.getTableName(object));
}
private T getRow(T object, String tableName) {
List list = getAll(object, tableName);
if ((list != null) && (list.size() > 0)) {
T ob = (T) list.get(0);
return ob;
}
return null;
}
public T getRow(T object){
return getRow(object, IBatisDaoUtils.getTableName(object));
}
public boolean existsPhysicallyData(T object) {
return exists(object, IBatisDaoUtils.getTableName(object));
}
public boolean exists(T object) {
return exists(object, IBatisDaoUtils.getTableName(object));
}
private boolean exists(T object, String tableName) {
Integer countNum = (Integer) sqlMapClientTemplate.queryForObject("GenericSql.SelectCountObjectVOSql",
DynamicSql.getInstance().SelectCountSql(object, tableName));
if (countNum.intValue() > 0) {
return true;
}
return false;
}
}
BaseDao
package com.file.dao;
import java.io.Serializable;
import java.util.List;
import com.entity.IBaseDto;
import com.exception.BusiException;
public abstract interface BaseDao<T extends IBaseDto,PK extends Serializable> {
public abstract List<T> getAll(T paramT);
public abstract T getRow(T paramT);
public abstract boolean exists(T paramT);
}
IBatisDaoUtils
dao工具类,这个是dao实现持久化和查询前进行的包装,及组装可使用的动态sql给dao备用。
package com.util;
import java.lang.reflect.Method;
import java.text.MessageFormat;
import org.springframework.util.ClassUtils;
import com.entity.EntityPK;
import com.entity.IBaseDto;
import com.exception.BusiException;
public final class IBatisDaoUtils {
/**
* 获取表名
*
* @param o
* @return
* @throws BusiException
*/
public static String getTableName(Object o) {
// 获取对象o的注解类
EntityPK entity = o.getClass().getAnnotation(EntityPK.class);
if (entity == null || "".equals(entity)) {
String msg = MessageFormat.format("Could not set '{0}' tableName",
new Object[] { ClassUtils.getShortName(o.getClass()) });
}
// 如果注解类不为空,则获取table
return entity.tableName();
}
/**
* 返回主键字段
*
* @param o
* @return
*/
public static String getPrimaryKeyFieldName(Object o) {
// 获取对象o的注解类
EntityPK entity = o.getClass().getAnnotation(EntityPK.class);
if (entity == null || "".equals(entity)) {
return null;
}
return "".equals(entity.PK()) ? null : entity.PK();
}
public static Object getPrimaryKeyFieldValue(IBaseDto o) {
String fieldName = getPrimaryKeyFieldName(o);
if (fieldName == null) {
return null;
}
// 获取get方法
String getterMethod = "get" + Character.toUpperCase(fieldName.charAt(0)) + fieldName.substring(1);
try {
Method getMethod = o.getClass().getMethod(getterMethod, (Class[]) null);
// 回调方法
return getMethod.invoke(o, (Object[]) null);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public static void setPrimaryKey(IBaseDto o, Object val) {
String fieldName = "";
try {
fieldName = getPrimaryKeyFieldName(o);
EntityPK entity = (EntityPK) o.getClass().getAnnotation(EntityPK.class);
Class clazz = o.getClass().getDeclaredField(entity.PK()).getType();
// 获取get方法
String getterMethod = "set" + Character.toUpperCase(fieldName.charAt(0)) + fieldName.substring(1);
Method setMethod = o.getClass().getMethod(getterMethod, (Class[]) null);
if (val != null) {
setMethod.invoke(o, new Object[] { val });
}
} catch (Exception e) {
// TODO Auto-generated catch block
String msg = MessageFormat.format("Could not set '{0}.{1}' with value {2}",
new Object[] { ClassUtils.getShortName(o.getClass()), fieldName, val });
}
}
public static String getTableName(Object ob, String tableName) {
if (tableName == null) {
tableName = ClassUtils.getShortName(ob.getClass());
}
return tableName;
}
}
动态sql获取类,通过传入的实体对象,获取到动态的sql
package com.sql;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.lang.reflect.Field;
import java.sql.Clob;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
import com.entity.IBaseDto;
import com.util.IBatisDaoUtils;
import DynamicSql.KeyVO;
public class DynamicSql {
private final Logger log = Logger.getLogger(getClass());
private static DynamicSql m_instance = null;
private void init() {
}
public static synchronized DynamicSql getInstance() {
if (m_instance == null) {
m_instance = new DynamicSql();
m_instance.init();
}
return m_instance;
}
public HashMap<String, Object> beanTohashmap(Object ob) throws IllegalArgumentException, IllegalAccessException {
Field[] field = ob.getClass().getDeclaredFields();
HashMap map = new HashMap();
for (int i = 0; i < field.length; i++) {
Field f = field[i];
f.setAccessible(true);
map.put(f.getName(), f.get(ob));
}
return map;
}
public HashMap<String, Object> UpdatebeanToSqlPk(Object ob, String tableName) {
HashMap sql_hs = new HashMap();
ArrayList setsql = new ArrayList();
ArrayList wherepk = new ArrayList();
try {
ObjectVOtoKeyVOListPK(ob, setsql, wherepk);
if (wherepk.size() <= 0) {
throw new RuntimeException("no pk value!!!!");
}
tableName = getTableName(ob, tableName);
sql_hs.put("sqltablename", tableName);
sql_hs.put("sqlvalue", setsql);
sql_hs.put("sqlwhere", wherepk);
} catch (IllegalArgumentException e) {
this.log.error("::", e);
} catch (IllegalAccessException e) {
this.log.error("::", e);
}
return sql_hs;
}
public HashMap<String, Object> DeletebeanToSqlPk(Object ob, String tableName) {
HashMap sql_hs = new HashMap();
ArrayList wherepk = new ArrayList();
try {
ObjectVOtoKeyVOListForWherePK(ob, wherepk);
if (wherepk.size() <= 0) {
throw new RuntimeException("no pk value!!!");
}
tableName = getTableName(ob, tableName);
sql_hs.put("sqltablename", tableName);
sql_hs.put("sqlwhere", wherepk);
} catch (IllegalArgumentException e) {
this.log.error("::", e);
} catch (IllegalAccessException e) {
this.log.error("::", e);
}
return sql_hs;
}
public HashMap<String, Object> DeletebeanToSql(Object ob, String tableName) {
HashMap sql_hs = new HashMap();
ArrayList setsql = new ArrayList();
try {
ObjectVOtoKeyVOList(ob, setsql);
tableName = getTableName(ob, tableName);
sql_hs.put("sqltablename", tableName);
sql_hs.put("sqlwhere", setsql);
} catch (IllegalArgumentException e) {
this.log.error("::", e);
} catch (IllegalAccessException e) {
this.log.error("::", e);
}
return sql_hs;
}
public HashMap<String, Object> SelectSql(Object ob, String tableName) {
StringBuffer selectsql = new StringBuffer(" ");
HashMap sql_hs = new HashMap();
ArrayList<KeyVO> wheresetsql = new ArrayList<KeyVO>();
try {
ObjectVOtoKeyVOListSelect(ob, wheresetsql, selectsql);
tableName = getTableName(ob, tableName);
sql_hs.put("sqltablename", tableName);
sql_hs.put("sqlvalue", selectsql.toString().substring(0, selectsql.toString().lastIndexOf(",")));
sql_hs.put("sqlwhere", wheresetsql);
} catch (IllegalArgumentException e) {
this.log.error("::", e);
} catch (IllegalAccessException e) {
this.log.error("::", e);
}
return sql_hs;
}
public HashMap<String, Object> SelectCountSql(Object ob, String tableName) {
StringBuffer selectsql = new StringBuffer(" ");
HashMap sql_hs = new HashMap();
ArrayList wheresetsql = new ArrayList();
try {
ObjectVOtoSelectCount(ob, wheresetsql);
tableName = getTableName(ob, tableName);
sql_hs.put("sqltablename", tableName);
sql_hs.put("sqlwhere", wheresetsql);
} catch (IllegalArgumentException e) {
this.log.error("::", e);
} catch (IllegalAccessException e) {
this.log.error("::", e);
}
return sql_hs;
}
public HashMap<String, Object> InsertSql(Object ob, String tableName, Object tablePk) {
StringBuffer selectsql = new StringBuffer("( ");
HashMap sql_hs = new HashMap();
ArrayList wheresetsql = new ArrayList();
try {
tableName = getTableName(ob, tableName);
ObjectVOtoKeyVOListInsert(ob, wheresetsql, selectsql, tablePk);
sql_hs.put("sqltablename", tableName);
sql_hs.put("sqlvalue", selectsql.toString().substring(0, selectsql.toString().lastIndexOf(",")) + " ) ");
sql_hs.put("sqlwhere", wheresetsql);
} catch (IllegalArgumentException e) {
this.log.error("::", e);
} catch (IllegalAccessException e) {
this.log.error("::", e);
}
return sql_hs;
}
private void ObjectVOtoKeyVOListPK(Object ob, ArrayList<KeyVO> keyVoList, ArrayList<KeyVO> wherePk)
throws IllegalArgumentException, IllegalAccessException {
Field[] field = getFields(ob);
KeyVO thisvo = null;
for (int i = 0; i < field.length; i++) {
Field f = field[i];
f.setAccessible(true);
if (!f.getName().toUpperCase().equals("SERIALVERSIONUID")) {
if (f.get(ob) != null) {
String type = f.getType().getCanonicalName();
if (CleanoutType(type)) {
String selectColumn = f.getName();
thisvo = new KeyVO();
thisvo.setKey(selectColumn.toUpperCase());
thisvo.setValue(f.get(ob));
if (f.getName().toUpperCase()
.equals(IBatisDaoUtils.getPrimaryKeyFieldName((IBaseDto) ob).toUpperCase())) {
if (wherePk != null) {
wherePk.add(thisvo);
}
} else {
keyVoList.add(thisvo);
}
thisvo = null;
}
}
}
}
}
private void ObjectVOtoKeyVOListForWherePK(Object ob, ArrayList<KeyVO> wherePk)
throws IllegalArgumentException, IllegalAccessException {
Field[] field = getFields(ob);
KeyVO thisvo = null;
for (int i = 0; i < field.length; i++) {
Field f = field[i];
f.setAccessible(true);
if (!f.getName().toUpperCase().equals("SERIALVERSIONUID")) {
if (f.get(ob) != null) {
String type = f.getType().getCanonicalName();
if (CleanoutType(type)) {
String selectColumn = f.getName();
thisvo = new KeyVO();
thisvo.setKey(selectColumn.toUpperCase());
thisvo.setValue(f.get(ob));
if (f.getName().toUpperCase()
.equals(IBatisDaoUtils.getPrimaryKeyFieldName((IBaseDto) ob).toUpperCase())) {
if (wherePk != null) {
wherePk.add(thisvo);
}
}
thisvo = null;
}
}
}
}
}
private void ObjectVOtoKeyVOList(Object ob, ArrayList<KeyVO> keyvolist)
throws IllegalArgumentException, IllegalAccessException {
Field[] field = ob.getClass().getDeclaredFields();
KeyVO thisvo = null;
for (int i = 0; i < field.length; i++) {
Field f = field[i];
f.setAccessible(true);
if (!f.getName().toUpperCase().equals("SERIALVERSIONUID")) {
if (f.get(ob) != null) {
String type = f.getType().getCanonicalName();
if (CleanoutType(type)) {
String selectColumn = f.getName();
thisvo = new KeyVO();
thisvo.setKey(selectColumn.toUpperCase());
thisvo.setValue(f.get(ob));
keyvolist.add(thisvo);
thisvo = null;
}
}
}
}
}
private void ObjectVOtoKeyVOListSelect(Object ob, ArrayList<KeyVO> whereKeyVoList, StringBuffer selectValue)
throws IllegalArgumentException, IllegalAccessException {
Field[] field = getFields(ob);
KeyVO thisvo = null;
for (int i = 0; i < field.length; i++) {
Field f = field[i];
f.setAccessible(true);
if (!f.getName().toUpperCase().equals("SERIALVERSIONUID")) {
String selectColumn = f.getName();
String type = f.getType().getCanonicalName();
if (CleanoutType(type)) {
selectValue.append(selectColumn + " \"" + selectColumn + "\" , ");
if (f.get(ob) != null) {
thisvo = new KeyVO();
thisvo.setKey(selectColumn.toUpperCase());
thisvo.setValue(f.get(ob));
whereKeyVoList.add(thisvo);
thisvo = null;
}
}
}
}
}
private void ObjectVOtoSelectCount(Object ob, ArrayList<KeyVO> whereKeyVoList)
throws IllegalArgumentException, IllegalAccessException {
Field[] field = getFields(ob);
KeyVO thisvo = null;
for (int i = 0; i < field.length; i++) {
Field f = field[i];
f.setAccessible(true);
if (!f.getName().toUpperCase().equals("SERIALVERSIONUID")) {
String selectColumn = f.getName();
String type = f.getType().getCanonicalName();
if (f.get(ob) != null) {
thisvo = new KeyVO();
thisvo.setKey(selectColumn.toUpperCase());
thisvo.setValue(f.get(ob));
whereKeyVoList.add(thisvo);
thisvo = null;
}
}
}
}
private void ObjectVOtoKeyVOListInsert(Object ob, ArrayList<KeyVO> keyvolist, StringBuffer selectvalue,
Object tablePk) throws IllegalArgumentException, IllegalAccessException {
Field[] field = getFields(ob);
KeyVO thisvo = null;
for (int i = 0; i < field.length; i++) {
Field f = field[i];
f.setAccessible(true);
if (f.getName().toUpperCase().equals(IBatisDaoUtils.getPrimaryKeyFieldName((IBaseDto) ob).toUpperCase())) {
selectvalue.append(f.getName().toUpperCase() + ", ");
thisvo = new KeyVO();
thisvo.setKey(f.getName().toUpperCase());
thisvo.setValue(tablePk);
keyvolist.add(thisvo);
} else if (f.get(ob) != null) {
if (!f.getName().toUpperCase().equals("SERIALVERSIONUID")) {
String selectColumn = f.getName();
String type = f.getType().getCanonicalName();
if (CleanoutType(type)) {
selectvalue.append(selectColumn.toUpperCase() + ", ");
thisvo = new KeyVO();
thisvo.setKey(selectColumn.toUpperCase());
thisvo.setValue(f.get(ob));
keyvolist.add(thisvo);
thisvo = null;
}
}
}
}
}
public HashMap<String, Object> UpdateBatchbeanToSqlPk(Object ob, List wherepk, String tableName) {
HashMap sql_hs = new HashMap();
ArrayList setsql = new ArrayList();
try {
ObjectVOtoKeyVOListPK(ob, setsql, null);
tableName = getTableName(ob, tableName);
sql_hs.put("sqltablename", tableName);
sql_hs.put("sqlvalue", setsql);
sql_hs.put("sqlwhere", wherepk);
sql_hs.put("sqltablepkname", IBatisDaoUtils.getPrimaryKeyFieldName((IBaseDto) ob).toUpperCase());
} catch (IllegalArgumentException e) {
this.log.error("::", e);
} catch (IllegalAccessException e) {
this.log.error("::", e);
}
return sql_hs;
}
public List ListHashToListbean(List list, Object model)
{
List beanlist = new ArrayList();
if (list != null) {
try {
for (int i = 0; i < list.size(); i++) {
Object ob = model.getClass().newInstance();
HashMap hs = (HashMap)list.get(i);
BeanWrapper beanWrapper = new BeanWrapperImpl(ob);
beanWrapper.setPropertyValues(hs);
beanlist.add(ob);
}
} catch (IllegalAccessException e) {
this.log.error("::", e);
} catch (InstantiationException e) {
this.log.error("::", e);
} catch (Exception e) {
this.log.error("::", e);
}
}
return beanlist;
}
private boolean CleanoutType(String type) {
if ((type.equals("java.lang.Float")) || (type.equals("java.lang.Long")) || (type.equals("java.lang.String"))
|| (type.equals("java.lang.Integer")) || (type.equals("java.lang.Double"))
|| (type.equals("java.sql.Timestamp")) || (type.equals("java.math.BigDecimal"))
|| (type.equals("java.util.Date")) || (type.equals("java.sql.Date"))) {
return true;
}
return false;
}
private String getTableName(Object ob, String tableName) {
return IBatisDaoUtils.getTableName(ob, tableName);
}
private Field[] getFields(Object ob) {
Field[] field = ob.getClass().getDeclaredFields();
return field;
}
private String clobToString(Clob clob) {
String reString = "";
Reader is = null;
try {
is = clob.getCharacterStream();
} catch (SQLException e) {
e.printStackTrace();
}
BufferedReader br = new BufferedReader(is);
String s = null;
try {
s = br.readLine();
} catch (IOException e) {
e.printStackTrace();
}
StringBuffer sb = new StringBuffer();
while (s != null) {
sb.append(s);
try {
s = br.readLine();
} catch (IOException e) {
e.printStackTrace();
}
}
reString = sb.toString();
return reString;
}
}
实体类
NewsDto
package com.entity;
@EntityPK(PK = "newsId",defaultColumn = false,tableName = "NEWS")
public class NewsDto implements IBaseDto{
private Integer newsId;
private String title;
private String brief;
public Integer getNewsId() {
return newsId;
}
public void setNewsId(Integer newsId) {
this.newsId = newsId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getBrief() {
return brief;
}
public void setBrief(String brief) {
this.brief = brief;
}
@Override
public String toString() {
return "NewsDto [newsId=" + newsId + ", title=" + title + ", brief=" + brief + "]";
}
}
IBaseDto
package com.entity;
public interface IBaseDto {
}
KeyVO
在组装过程中,用到健值对对象。
package DynamicSql;
public class KeyVO
{
private String key;
private Object value;
public String getKey()
{
return this.key;
}
public void setKey(String key) {
this.key = key;
}
public Object getValue() {
return this.value;
}
public void setValue(Object value) {
this.value = value;
}
}
注解类
作用:注解类对实体对象进行标注,在dao开始时,传入实体对象。在持久化或查询过程中,我们通过该实现对象的的注解类进行解析获取到传入的参数,同时组装出动态的sql。
EntityPK
package com.entity;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface EntityPK {
String PK();
boolean defaultColumn() default true;
String tableName();
}