一、bean结构
1、baseBean.java
baseBean作为所有bean的继承对象,需要追加以下字段:
1)nvf字段名Upd :boolean
用于判断此字段是否需要更新
2)ord字段名by :String
用于select的排序方式,取值为“desc”、“asc”
3)operatorStr :Map<String, String>
用于where条件的操作符,Map<"字段名","具体操作符">
2、Commonbean.java
CommonBean作为一个共通的bean,仅用在共通方法中,不继承baseBean,也没有Bean继承CommonBean
1)loginUserId :Integer
用于存放当前登录用户,update及insert时作为updaterId以及creatorId
2)limit :Integer
用于select的查询最大条数
3)entityList :List<EntityBean>
用于存放字段名、字段值、操作符、排序方式
3、EntityBean.java
EntityBean作为共通方法中存放字段相关的Bean,仅用在共通方法中,不继承baseBean,也没有Bean继承EntityBean
1)entityName :String
用于存放字段名
2)entityValue :Object
用户存放字段值
3)operatorStr :String
用于存放操作符(where用)
4)orderBy :String
用于存放排序方式(select用)
二、具体实现方式
1、CommomServiceImpl
package com.test.moxan.common.service.impl;
import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.postgresql.util.PSQLException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.test.moxan.common.bean.CommonBean;
import com.test.moxan.common.bean.EntityBean;
import com.test.moxan.common.config.CommonConfig;
import com.test.moxan.common.dao.CommonMapper;
import com.test.moxan.common.service.CommonService;
import com.test.moxan.common.util.JsonUtil;
import com.test.moxan.common.util.TokenUtil;
@Service
public class CommonServiceImpl extends BaseServiceImpl implements CommonService{
@Autowired
private CommonMapper commonMapper;
/**
* @param String Schema 模块名
* @param Object value 修改后的值
* @param Object cond 条件
*
*/
@Override
public int updateByCond(Object target, Object condit, Class<?> clazz) throws PSQLException {
//定义修改后的值List
List<EntityBean> targetList = new ArrayList<>();
//定义条件List
List<EntityBean> conditList = new ArrayList<>();
//定义目标Bean
CommonBean targetBean = new CommonBean();
//定义条件Bean
CommonBean conditBean = new CommonBean();
//根据Bean名获取表名
String tableName = clazz.getSimpleName();
//根据Bean名获取Schema名
String schema = tableName.substring(0, 2).toLowerCase();
//根据Schema拼接update语句中的表名 例如sc.tbl_user
tableName = schema + CommonConfig.const_TABLE_PREFIX + camel2Underline(tableName.substring(2));
//获取修改后的值List
targetList = getValueList(target,clazz);
//获取条件List
conditList = getValueList(condit,clazz);
//设置updaterId
targetBean.setLoginUserId(Integer.valueOf(TokenUtil.getLoginUserIdStr()));
//设置目标List
targetBean.setEntityList(targetList);
//设置条件List
conditBean.setEntityList(conditList);
//更新数据库
int result = commonMapper.updateByCond(targetBean,conditBean,tableName);
//返回结果
return result;
}
@Override
public int insertCommon(Object target,Class<?> clazz) throws PSQLException {
//定义返回值
int result = 0;
//定义目标List
List<EntityBean> targetList = new ArrayList<>();
//定义目标Bean
CommonBean targetBean = new CommonBean();
//根据Bean名获取表名
String tableName = clazz.getSimpleName();
//根据Bean名获取Schema名
String schemaName = tableName.substring(0, 2).toLowerCase();
//根据Schema拼接update语句中的表名 例如sc.tbl_user
tableName = schemaName + CommonConfig.const_TABLE_PREFIX + camel2Underline(tableName.substring(2));
//获取目标List
targetList = getValueList(target,clazz);
//设置当前登录用户
targetBean.setLoginUserId(Integer.valueOf(TokenUtil.getLoginUserIdStr()));
//设置目标List
targetBean.setEntityList(targetList);
//更新数据库
result = commonMapper.insertCommon(targetBean,tableName);
//返回结果
return result;
}
@Override
public <T> List<T> selectCommon(Object entity, Class<?> beanClass)throws PSQLException{
return selectCommon(entity,null,beanClass);
}
@SuppressWarnings("unchecked")
@Override
public <T> List<T> selectCommon(Object condit, Integer limit, Class<?> clazz) throws PSQLException{
//定义查询结果集
List<Map<String, Object>> resultList = new ArrayList<>();
//定义返回结果集
List<T> returnList = new ArrayList<>();
//定义字段List
List<EntityBean> entityList = new ArrayList<>();
//定义条件List
List<EntityBean> conditList = new ArrayList<>();
//定义条件Bean
CommonBean conditBean = new CommonBean();
//根据Bean名获取Schema名
String tableName = clazz.getSimpleName();
//根据Bean名获取Schema名
String schemaName = tableName.substring(0, 2).toLowerCase();
//根据Schema拼接update语句中的表名 例如sc.tbl_user
tableName = schemaName + CommonConfig.const_TABLE_PREFIX + camel2Underline(tableName.substring(2));
//获取条件List
conditList = getValueList(condit,clazz);
//设置查询最大条数
conditBean.setLimit(limit);
//设置条件List
conditBean.setEntityList(conditList);
//获取查询字段(全部)
entityList = getSelectList(condit, clazz);
//查询数据库
resultList = commonMapper.selectCommon(entityList,conditBean,tableName);
//返回结果转List<T>
returnList = JsonUtil.listToBeans(resultList, clazz);
//返回姐估计
return returnList;
}
private List<EntityBean> getSelectList(Object entity,Class<?> clazz){
//定义BeanInfo,用于存放Bean信息
BeanInfo beanInfo;
//定义字段名
String fieldName;
//定义返回结果,字段List
List<EntityBean> commonList = new ArrayList<>();
try {
//获取Bean信息
beanInfo = Introspector.getBeanInfo(entity.getClass(), Object.class);
if(beanInfo != null) {
//从Bean信息中获取字段数组
PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
for(PropertyDescriptor p:propertyDescriptors) {
EntityBean entityBean = new EntityBean();
//获取字段名
fieldName = p.getName();
//如果获取到了操作符字段,则跳过
if(CommonConfig.noNeedUpdateEntity.contains(fieldName) || "operatorStr".equals(fieldName)) {
continue;
}
//如果获取到了排序字段,则跳过
if(fieldName.startsWith("ord") && fieldName.endsWith("by")) {
continue;
}
//如果获取到了判断是否更改字段,则跳过
if(fieldName.startsWith(CommonConfig.const_FIELD_CHANGED_PREFIX) && fieldName.endsWith(CommonConfig.const_FIELD_CHANGED_SUFFIX)) {
continue;
}
//设置Bean字段名
entityBean.setEntityValue(fieldName);
//设置db字段名
entityBean.setEntityName(camel2Underline(fieldName));
//插入List
commonList.add(entityBean);
}
}
return commonList;
} catch (Exception e) {
e.printStackTrace();
return commonList;
}
}
@SuppressWarnings("unchecked")
private <T> List<T> getValueList(Object entity,Class<?> clazz){
//定义BeanInfo,用于存放Bean信息
BeanInfo beanInfo;
//定义字段名
String fieldName;
//定义字段值
Object fieldValue;
//定义返回结果,字段List
List<T> commonList = new ArrayList<>();
//定义需要操作符的字段Set
Set<String> operatorKey = null;
//定义操作符Map<字段,操作符>
Map<String, String> operator = null;
try {
//获取Bean信息
beanInfo = Introspector.getBeanInfo(entity.getClass(), Object.class);
if(beanInfo != null) {
//从Bean信息中获取字段数组
PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
for(PropertyDescriptor p:propertyDescriptors) {
//获取字段名
fieldName = p.getName();
//调用字段名相应的get方法
fieldValue = new PropertyDescriptor(fieldName, clazz).getReadMethod().invoke(entity);
//如果是操作符字段,则处理操作符,操作符字段只有一个,执行后即可break
if("operatorStr".equals(fieldName) && fieldValue != null) {
operator = (Map<String, String>)fieldValue;
operatorKey = operator.keySet();
break;
}
}
for(PropertyDescriptor p:propertyDescriptors){
EntityBean entityBean = new EntityBean();
//获取字段名
fieldName = p.getName();
//调用字段名相应的get方法
fieldValue = new PropertyDescriptor(fieldName, clazz).getReadMethod().invoke(entity);
//如果获取到了判断是否更改字段,则跳过
if(fieldName.startsWith(CommonConfig.const_FIELD_CHANGED_PREFIX) && fieldName.endsWith(CommonConfig.const_FIELD_CHANGED_SUFFIX)) {
continue;
}
//如果获取到了排序字段,则跳过
if(fieldName.startsWith("ord") && fieldName.endsWith("by")) {
continue;
}
//如果获取到了操作符字段,则跳过
if(CommonConfig.noNeedUpdateEntity.contains(fieldName) || "operatorStr".equals(fieldName)) {
continue;
}
//获取当前字段的是否更改标志
Object value = getPropertyValue(entity, CommonConfig.const_FIELD_CHANGED_PREFIX +
toUpperCaseFirstOne(fieldName) + CommonConfig.const_FIELD_CHANGED_SUFFIX);
//未获取到或为false(未更改),则跳过
if(value != null && !(boolean)value) {
continue;
}
//获取当前字段的排序标志
Object orderBy = getPropertyValue(entity, "ord" + toUpperCaseFirstOne(fieldName) + "by");
if(orderBy != null) {
//设置排序方式
entityBean.setOrderBy(orderBy.toString());
}
//如果操作符不为空且字段名自定义了操作符
if(operatorKey != null && operatorKey.contains(fieldName)) {
entityBean.setOperatorStr(operator.get(fieldName));
}else {
//未定义默认为=
entityBean.setOperatorStr("=");
}
//设置db字段
entityBean.setEntityName(camel2Underline(fieldName));
//设置字段值
entityBean.setEntityValue(fieldValue);
//添加至结果List
commonList.add((T)entityBean);
}
}
//返回结果集
return commonList;
} catch (Exception e) {
e.printStackTrace();
return commonList;
}
}
//大写转下划线
public static String camel2Underline(String param) {
if (param == null || "".equals(param.trim())) {
return "";
}
int len = param.length();
StringBuilder sb = new StringBuilder(len);
for (int i = 0; i < len; i++) {
char c = param.charAt(i);
if (Character.isUpperCase(c)){
if (i > 0) sb.append("_");
sb.append(Character.toLowerCase(c));
} else{
sb.append(c);
}
}
return sb.toString();
}
//获取指定字段值
public static Object getPropertyValue(Object bean, String propertyName) {
String method = CommonConfig.const_GET_METHOD_PREFIX + toUpperCaseFirstOne(propertyName);
return excuteMethod(bean, method);
}
//首字母大写
public static String toUpperCaseFirstOne(String s){
return Character.toUpperCase(s.charAt(0)) + s.substring(1);
}
//获取指定字段值
public static Object excuteMethod(Object bean, String method, Object ...param) {
if (bean == null) return null;
try {
Class<?> clazz = bean.getClass();
Class<?>[] clazzes = new Class<?>[param.length];
for (int i = 0; i < param.length; i++) {
clazzes[i] = param[i].getClass();
}
Method m = (Method) clazz.getMethod(method, clazzes);
return m.invoke(bean, param);
} catch (Exception e) {
return null;
}
}
}
2、CommonMapper.java
package com.test.moxan.common.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.test.moxan.common.bean.CommonBean;
import com.test.moxan.common.bean.EntityBean;
import com.test.moxan.common.bean.ScTest01;
public interface CommonMapper {
int updateByCond(@Param("entity")CommonBean commonBean,@Param("cond") CommonBean conditBean, String tableName);
int insertCommon(@Param("entity")CommonBean targetBean,@Param("tableName")String tableName);
<T> List<T> selectCommon(@Param("entity") List<EntityBean> entityList,@Param("cond") CommonBean conditBean, @Param("tableName")String tableName);
}
3、CommonMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.test.moxan.common.dao.CommonMapper">
<sql id="whereSQL">
<trim prefix="WHERE" prefixOverrides="AND">
<foreach item="cond" collection="cond.entityList">
<choose>
<when test="cond.entityValue != null">
AND ${cond.entityName} ${cond.operatorStr} ${cond.entityValue}
</when>
<otherwise>
<if test="cond.operatorStr == '='.toString()" >
AND ${cond.entityName} IS NULL
</if>
<if test="cond.operatorStr != '='.toString()" >
AND ${cond.entityName} IS NOT NULL
</if>
</otherwise>
</choose>
</foreach>
</trim>
</sql>
<update id="updateByCond">
UPDATE
${tableName}
SET
<foreach item="entity" collection="entity.entityList" separator=",">
${entity.entityName} = ${entity.entityValue}
</foreach>
,updated_time = NOW()
,updater_id = #{entity.loginUserId}
,version = version + 1
<include refid="whereSQL"></include>
</update>
<insert id="insertCommon">
INSERT INTO
${tableName}
<foreach item="entity" collection="entity.entityList" separator="," open="(">
${entity.entityName}
</foreach>
,creator_id
,created_time
,updater_id
,updated_time
)
VALUES
<foreach item="entity" collection="entity.entityList" separator="," open="(">
${entity.entityValue}
</foreach>
,#{entity.loginUserId}
,NOW()
,#{entity.loginUserId}
,NOW()
)
</insert>
<select id="selectCommon" resultType="java.util.LinkedHashMap">
SELECT
<foreach item="entity" collection="entity" separator=",">
${entity.entityName} AS "${entity.entityValue}"
</foreach>
FROM
${tableName}
<include refid="whereSQL"></include>
<trim prefix="ORDER BY">
<foreach item="cond" collection="cond.entityList" separator=",">
<if test="cond.orderBy != null">
${cond.entityName} ${cond.orderBy}
</if>
</foreach>
</trim>
<if test="cond.limit != null">
LIMIT
${cond.limit}
</if>
</select>
</mapper>