xplly'Blog

切换主题

接口 ​

java

import java.util.List;

import com.commnetsoft.commons.Order;
import com.commnetsoft.commons.PageQuery;
import com.commnetsoft.commons.Pager;
import com.commnetsoft.db.exception.DaoException;
import com.commnetsoft.db.model.KeyEntity;

/**
 * 基础的Service接口
 * @author sunwen
 */
public interface IService<T extends KeyEntity, PK> {

	/**
	 * 查询一个对象，如果返回的结果多于一个对象将会抛出TooManyResultsException
	 * @param query 查询对象，不能为null
	 * @return Mapper中映射的对象，继承自 T对象，一般是Vo对象
	 */
	T queryOne(T query);

	/**
	 * 通过Id查询一个对象，如果id为null这会抛出IllegalArgumentException异常
	 * @param id 主键，不能为null
	 * @return  结果对象，如果未找到返回null
	 */
	T queryByKey(PK id);
	
	/**
	 * 查询对象列表
	 * @param query 查询参数，如果未null则查询所有
	 * @param orders 排序对象
	 * @return 结果对象列表
	 */
	<Q extends T> List<Q> queryList(Q query, Order... orders);

	/**
	 * 查询所有记录列表
	 * @return List 结果列表
	 */
	List<T> queryAll();

	/**
	 * 查询总记录数
	 * @return long 记录总数
	 */
	Long queryCount();

	/**
	 * 查询记录数
	 * @param query 查询对象，如果为null，则查询对象总数
	 * @return long 记录总数
	 */
	Long queryCount(T query);

	/**
	 * 添加对象,如果要添加的对象没有设置Id或者Id为空字符串或者是空格，则添加数据之前会调用 generateId()方法设置Id
	 * @param entity 要实例化的实体，不能为null
	 */
	void insert(T entity);

	/**
	 * 根据Id删除对象
	 * @param id  要删除的ID，不能为null
	 * @return int 受影响结果数
	 */
	int deleteByKey(PK id);

	/**
	 * 更新对象，对象必须设置ID
	 * @param entity 实体的Id不能为null
	 * @return int 受影响结果数
	 */
	int updateByKey(T entity);

	/**
	 * 更新对象中已设置的字段。
	 * <p>注意<b>未设置的字段不更新</b><p>
	 * @param entity 要更新的实体对象，不能为null，切ID必须不为null
	 * @return int 受影响结果数
	 */
	int updateByKeySelective(T entity);

	/**
	 * 根据id，批量删除记录，如果传入的列表为null或为空列表则直接返回
	 * @param idList 批量删除ID列表
	 */
	int deleteByKeyInBatch(List<PK> idList);

	/**
	 * 批量插入，如果为空列表则直接返回
	 * @param entityList 需要批量插入的实体对象列表
	 */
	void insertInBatch(List<T> entityList);

	/**
	 * 批量更新，改方法根据实体ID更新已设置的字段，未设置的字段不更新
	 * @param entityList 批量更新的实体对象列表
	 */
	void updateInBatch(List<T> entityList);

	/**
	 *	<pre>查询对象列表，注意：在给定非null的分页对象时该方法自动设置分页总记录数,如果query和pageable同时为null则查询所有</pre>
	 * @param query 查询参数
	 * @param pageQuery 分页查询对象
	 * @return Page 信息方便前台显示
	 */
	<Q extends T> Pager<Q> queryPageList(Q query, PageQuery pageQuery);
	
	/**
	 * <pre>查询对象是否唯一，如果id不为空，则排除id之外是否唯一</pre>
	 * @param query 查询对象
	 * @return boolean
	 */
	boolean unique(T query) throws DaoException;
}

通用Service ​

java

import java.util.List;
import java.util.Map;
import java.util.Set;

import com.commnetsoft.commons.Order;
import com.commnetsoft.commons.PageQuery;
import com.commnetsoft.commons.Pager;
import com.commnetsoft.commons.utils.BeanUtils;
import com.commnetsoft.commons.utils.ClassUtils;
import com.commnetsoft.commons.utils.NumberUtils;
import com.commnetsoft.commons.utils.StringUtils;
import com.commnetsoft.db.dao.BaseDao;
import com.commnetsoft.db.dao.EntityDao;
import com.commnetsoft.db.dao.Alias;
import com.commnetsoft.db.dao.ExtraSelectMapper;
import com.commnetsoft.db.exception.DaoException;
import com.commnetsoft.db.model.KeyEntity;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.ibatis.session.RowBounds;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;
import tk.mybatis.mapper.entity.EntityColumn;
import tk.mybatis.mapper.entity.Example;
import tk.mybatis.mapper.mapperhelper.EntityHelper;

/**
 * 基础Service服务接口实现类
 * @author sunwen
 * @since 2019/4/22
 */
public abstract class AbsService<T extends KeyEntity, PK> implements IService<T, PK> {

	protected Class<?> entityClass;

	public AbsService(){
		entityClass = ClassUtils.getParameterType(getClass(), IService.class, 0);
	}

	/**
	 * 获取基础数据库操作类
	 */
	protected abstract EntityDao<T> getBaseDao();

	@Override
	public T queryOne(T query) {
		Assert.notNull(query, "查询条件不能为空");
		return getBaseDao().selectOne(query);
	}
  
	@Override
	public T queryByKey(PK id) {
		if(id != null) {
			return getBaseDao().selectByPrimaryKey(id);
		}
		return null;
	}

	@Override
	public <Q extends T> List<Q> queryList(Q query, Order... orders) {
		if(query != null && !query.getClass().equals(entityClass)){//不是实体对象，定义为扩展查询
			if(getBaseDao() instanceof ExtraSelectMapper){
				Map<String, Object> conditions = BeanUtils.toMap(query);
				String alias = (query instanceof Alias) ? ((Alias) query).as() : null;
				String clause = getOrderbyClause(alias, orders);//TODO 排序为空，从Entity获取默认排序
				PageHelper.orderBy(clause);
				return ((ExtraSelectMapper) getBaseDao()).selectExtra(conditions);
			}else{
				throw new DaoException(String.format("扩展查询对象（%s）必须提供一个实现ExtraSelectMapper的Dao", query.getClass()));
			}
		}else{
			PageHelper.orderBy(getOrderbyClause(orders));
			return (List<Q>)getBaseDao().select(query);
		}
	}

	@Override
	public List<T> queryAll() {
		return queryList(null);
	}

	@Override
	public Long queryCount() {
		return queryCount(null);
	}

	@Override
	public Long queryCount(T query) {
		return (long)getBaseDao().selectCount(query);
	}

	@Override
	public void insert(T entity) {
		Assert.notNull(entity, "插入数据不能为空");
		getBaseDao().insertSelective(entity);
	}

	@Override
	public int deleteByKey(PK id) {
		if(id == null){
			return 0;
		}else{
			return getBaseDao().deleteByPrimaryKey(id);
		}
	}

	@Override
	public int updateByKey(T entity) {
		Assert.notNull(entity, "更新数据不能为空");
		if(!entity.existKey()){
			throw new IllegalArgumentException("更新数据对象主键不存在");
		}
		return getBaseDao().updateByPrimaryKey(entity);
	}

	@Override
	public int updateByKeySelective(T entity) {
		Assert.notNull(entity, "更新数据不能为空");
		if(!entity.existKey()){
			throw new IllegalArgumentException("更新数据对象主键不存在");
		}
		return getBaseDao().updateByPrimaryKeySelective(entity);
	}

	@Transactional
	@Override
	public int deleteByKeyInBatch(List<PK> idList) {
		int count = 0;
		if(idList != null && idList.size() > 0){
			EntityDao dao = getBaseDao();
			if(dao instanceof BaseDao){
				count = ((BaseDao) dao).deleteByIdList(idList);
			}else{
				for (PK k : idList){
					count += deleteByKey(k);
				}
			}
		}
		return count;
	}

	@Override
	public void insertInBatch(List<T> entityList) {
		for (T t : entityList) {
			insert(t);
		}
	}

	@Override
	public void updateInBatch(List<T> entityList) {
		for (T t : entityList) {
			updateByKey(t);
		}
	}

	@Override
	public <Q extends T> Pager<Q> queryPageList(Q query, PageQuery pageQuery) {
		openPageQuery(pageQuery);
		Page<Q> page = (Page<Q>) queryList(query, pageQuery.getOrders());
		return convertPager(page, page);
	}

	/**
	 * 根据Example查询对象列表
     * Note: example中存在OrderBy条件时，pageQuery中的order参数将失效
	 */
	public Pager<T> queryPageList(Example example, PageQuery pageQuery) {
		openPageQuery(pageQuery);
		if(StringUtils.isEmpty(example.getOrderByClause())){
			example.setOrderByClause(getOrderbyClause(pageQuery.getOrders()));
		}
		Page<T> page = (Page<T>)getBaseDao().selectByExample(example);
		return convertPager(page, page);
	}

	@Override
	public boolean unique(T query) throws DaoException {
		try {
			Class<?> entityClass = query.getClass();
			Example example = new Example(entityClass);
			Example.Criteria criteria = example.createCriteria();
			Set<EntityColumn> pkcs = EntityHelper.getColumns(entityClass);
			for(EntityColumn column : pkcs){
				Object value = column.getEntityField().getValue(query);
				if(value != null){
					if(column.isId()){//主键
						criteria.andNotEqualTo(column.getProperty(), value);
					}else{
						criteria.andEqualTo(column.getProperty(), value);
					}
				}
			}
			List<T> list = getBaseDao().selectByExampleAndRowBounds(example, new RowBounds(0, 1));
			return 0 == list.size();
		}catch (Exception e){
			throw new DaoException("获取主键失败", e);
		}
	}

	/**
	 * 插入或者更新（更新采用updateByIdSelective方式更新）
	 * @return boolean 是否新增
	 * @author sunwen
	 * date 2019/5/8
	 */
	public boolean insertOrUpdate(T entity){
		Set<EntityColumn> pkcs = EntityHelper.getPKColumns(entity.getClass());
		if(pkcs.size() == 1 && pkcs.iterator().next().isIdentity()){//单主键自增
			if(entity.existKey()){
				updateByKeySelective(entity);
				return false;
			}else{
				insert(entity);
				return true;
			}
		}else if(entity.existKey()){
			if(getBaseDao().existsWithPrimaryKey(entity)){
				updateByKeySelective(entity);
				return false;
			}else{
				insert(entity);
				return true;
			}
		}else{
			throw new DaoException("非自增主键对象，调用insertOrUpdate方法必须设置主键");
		}
	}

	/**
	 * 开启分页查询
	 * @author sunwen
	 * date 2019/4/22
	 */
	protected void openPageQuery(PageQuery pageQuery){
		if(pageQuery != null){
			//pagesize为0时查询所有数据，不需要查询总数
			boolean needCount = pageQuery.getPagesize() > 0 && pageQuery.isNeedtotal();
			PageHelper.startPage(pageQuery.getPagenum(), pageQuery.getPagesize(), needCount);
		}
	}

	/**
	 * 获取排序条件
	 * Note: 排序字段必须是模型类中的成员名（不是数据库字段名）
	 * @author sunwen
	 * date 2019/4/22
	 */
	protected String getOrderbyClause(Order... orders) throws DaoException{
		return getOrderbyClause(null, orders);
	}

	/**
	 * 开启排序查询
	 * Note: 排序字段必须是模型类中的成员名（不是数据库字段名）
	 * @author sunwen
	 * date 2019/4/22
	 */
	protected String getOrderbyClause(String entityName, Order... orders) throws DaoException{
		if(orders != null && orders.length > 0){
			StringBuilder orderbyClause = new StringBuilder();
            Map<String, EntityColumn> propertyMap = EntityHelper.getEntityTable(entityClass).getPropertyMap();
            boolean separate = false;
            for (Order order : orders) {
                EntityColumn column = propertyMap.get(order.getOrderby());
                if (column == null) {
                    throw new DaoException(String.format("排序字段不存在与实体类:%s", entityClass));
                }
                if (!separate) {
                    separate = true;
                } else {
                    orderbyClause.append(",");
                }
				if (StringUtils.isNoneBlank(entityName)) {
					orderbyClause.append(entityName).append('.');
				}
                orderbyClause.append(column.getColumn());
				orderbyClause.append(' ').append(order.getOrdertype());
            }
			return orderbyClause.toString();
		}
		return null;
	}

    /**
     * 根据page转换成Pager对象
     */
    protected <M> Pager<M> convertPager(Page<?> page, List<M> rows){
        Pager<M> pager = new Pager<>();
        pager.setPagenum(page.getPageNum());
        pager.setPagesize(page.getPageSize());
        pager.setTotal(page.getTotal());
        pager.setRows(rows);
        return pager;
    }

}

Mapper中json字段到类的映射 ​

java

import com.alibaba.fastjson.JSON;
import com.commnetsoft.commons.utils.ClassUtils;
import com.commnetsoft.commons.utils.JSONUtils;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

import java.lang.reflect.Type;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * mapper里json型字段到类的映射。
 */
public abstract class JsonTypeHandler<T> extends BaseTypeHandler<T> {

    private Class<?> clazz;

    public JsonTypeHandler() {
        clazz = ClassUtils.getParameterType(getClass(), TypeHandler.class, 0);
    }

    /**
     * 获取范型
     * @author sunwen
     * @since 2020/11/12
     */
    public Type[] getGenericTypes(){
        return null;
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, this.toJson(parameter));
    }

    @Override
    public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return this.toObject(rs.getString(columnName), clazz);
    }

    @Override
    public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return this.toObject(rs.getString(columnIndex), clazz);
    }

    @Override
    public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return this.toObject(cs.getString(columnIndex), clazz);
    }

    private String toJson(T object) {
        if(object != null){
            return JSON.toJSONString(object);
        }
        return null;
    }

    private T toObject(String content, Class<?> clazz) {
        if (content != null && !content.isEmpty()) {
            return (T) JSONUtils.parseGenericsObject(content, clazz, getGenericTypes());
        } else {
            return null;
        }
    }
}

数据库配置 ​

java

import com.commnetsoft.commons.utils.StringUtils;
import com.commnetsoft.db.dao.EntityDao;
import com.zaxxer.hikari.HikariDataSource;
import liquibase.integration.spring.SpringLiquibase;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DatabaseDriver;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import tk.mybatis.spring.annotation.MapperScan;

import javax.sql.DataSource;

/**
 * 数据库配置
 *
 * @author sunwen
 * @date 2019/3/26
 */
@Configuration
@PropertySource(value = {
        "classpath:db.properties",
        "file:config/db.properties"
}, ignoreResourceNotFound = true)
@MapperScan(basePackages = "com.commnetsoft.**.dao", markerInterface = EntityDao.class,
        sqlSessionFactoryRef = "sqlSessionFactory")
public class DBConfig {

//    @Bean
//    @ConfigurationProperties(prefix = "spring.datasource")
//    DataSourceProperties dataSourceProperties() {
//        return new DataSourceProperties();
//    }

    @ConditionalOnClass(HikariDataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource", matchIfMissing = true)
    static class Hikari {

        /**
         * 主数据源hikari（兼容项目中配置其它数据源，手动注册数据库相关Bean）
         */
        @Bean
        @Primary
        @ConfigurationProperties(prefix = "spring.datasource.hikari")
        public HikariDataSource dataSource(DataSourceProperties properties) {
            HikariDataSource dataSource = createDataSource(properties, HikariDataSource.class);
            if (org.springframework.util.StringUtils.hasText(properties.getName())) {
                dataSource.setPoolName(properties.getName());
            }
            return dataSource;
        }
    }


    /**
     * DBCP DataSource configuration.
     */
    @ConditionalOnClass(BasicDataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.commons.dbcp2.BasicDataSource", matchIfMissing = true)
    static class Dbcp2 {

        /**
         * 主数据源dbcp2（兼容项目中配置其它数据源，手动注册数据库相关Bean）
         */
        @Bean
        @Primary
        @ConfigurationProperties(prefix = "spring.datasource.dbcp2")
        public BasicDataSource dataSource(
                DataSourceProperties properties) {
            return createDataSource(properties,
                    BasicDataSource.class);
        }

    }


    /**
     * Tomcat Pool DataSource configuration.
     */
    @ConditionalOnClass(org.apache.tomcat.jdbc.pool.DataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.tomcat.jdbc.pool.DataSource", matchIfMissing = true)
    static class Tomcat {

        /**
         * 主数据源tomcat（兼容项目中配置其它数据源，手动注册数据库相关Bean）
         */
        @Bean
        @Primary
        @ConfigurationProperties(prefix = "spring.datasource.tomcat")
        public org.apache.tomcat.jdbc.pool.DataSource dataSource(
                DataSourceProperties properties) {
            org.apache.tomcat.jdbc.pool.DataSource dataSource = createDataSource(
                    properties, org.apache.tomcat.jdbc.pool.DataSource.class);
            DatabaseDriver databaseDriver = DatabaseDriver
                    .fromJdbcUrl(properties.determineUrl());
            String validationQuery = databaseDriver.getValidationQuery();
            if (validationQuery != null) {
                dataSource.setTestOnBorrow(true);
                dataSource.setValidationQuery(validationQuery);
            }
            return dataSource;
        }

    }


    @Bean
    @Primary
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource, MybatisProperties properties) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        factoryBean.setConfiguration(properties.getConfiguration());
        factoryBean.setMapperLocations(properties.resolveMapperLocations());
        return factoryBean.getObject();
    }

    @Bean
    @Primary
    public DataSourceTransactionManager dataSourceTransactionManager(DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    public SpringLiquibase demoLiquibase(DataSource dataSource) {
        return createLiquibase("demo", dataSource);
    }

    public static SpringLiquibase createLiquibase(String module, DataSource dataSource) {
        return createLiquibase(module, null, dataSource);
    }

    /**
     * @param module  模块名
     * @param project 项目名称（部分项目化的表）
     * @author sunwen
     * @since 2020/8/28
     */
    public static SpringLiquibase createLiquibase(String module, String project, DataSource dataSource) {
        if (!StringUtils.isLetter(module)) {
            throw new IllegalArgumentException("模块名称(" + module + ")不合法");
        }
        if (StringUtils.isNotEmpty(project)) {
            if (!StringUtils.isLetter(project)) {
                throw new IllegalArgumentException("项目名称(" + project + ")不合法");
            }
            module += '_' + project;
        }
        SpringLiquibase liquibase = new SpringLiquibase();
        // 用户模块Liquibase文件路径
        liquibase.setChangeLog("classpath:sql/" + module + "/master.xml");
        liquibase.setDataSource(dataSource);
        liquibase.setShouldRun(true);
        liquibase.setResourceLoader(new DefaultResourceLoader());
        // 覆盖Liquibase changelog表名
        liquibase.setDatabaseChangeLogTable(module + "_changelog_table");
        liquibase.setDatabaseChangeLogLockTable(module + "_changelog_lock_table");
        return liquibase;
    }

    protected static <T> T createDataSource(DataSourceProperties properties, Class<? extends DataSource> type) {
        return (T) properties.initializeDataSourceBuilder().type(type).build();
    }


}

配置文件 ​

properties
# jdbc_config   datasource


<NolebasePageProperties />




spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=${jdbc.url}
spring.datasource.username=${jdbc.username}
spring.datasource.password=${jdbc.password}
# Hikari will use the above plus the following to setup connection pooling
#spring.datasource.type=com.zaxxer.hikari.HikariDataSource
#spring.datasource.hikari.minimum-idle=10
#spring.datasource.hikari.maximum-pool-size=10
#spring.datasource.hikari.auto-commit=true
#spring.datasource.hikari.idle-timeout=30000
#spring.datasource.hikari.pool-name=DatebookHikariCP
#spring.datasource.hikari.max-lifetime=1800000
#spring.datasource.hikari.connection-timeout=30000
#spring.datasource.hikari.connection-test-query=SELECT 1

# mybatis_config
#mybatis.mapper-locations=classpath*:com/commnetsoft/*/dao/*.xml
mybatis.mapper-locations=classpath*:mapper/*.xml
#全局映射器启用缓存
mybatis.configuration.cache-enabled=true
#查询时，关闭关联对象即时加载以提高性能
mybatis.configuration.lazy-loading-enabled=false
#设置关联对象加载的形态，此处为按需加载字段(加载字段由SQL指定)，不会加载关联表的所有字段，以提高性能
mybatis.configuration.aggressive-lazy-loading=false
#对于未知的SQL查询，允许返回不同的结果集以达到通用的效果
mybatis.configuration.multiple-result-sets-enabled=true
#允许使用列标签代替列名
mybatis.configuration.use-column-label=true
#允许使用自定义的主键值(比如由程序生成的UUID 32位编码作为键值)，数据表的PK生成策略将被覆盖
mybatis.configuration.use-generated-keys=true
#给予被嵌套的resultMap以字段-属性的映射支持
mybatis.configuration.auto-mapping-behavior=full
#对于批量更新操作缓存SQL以提高性能
mybatis.configuration.default-executor-type=simple
#数据库超过25000秒仍未响应则超时
mybatis.configuration.default-statement-timeout=25000
mybatis.configuration.log-impl=org.apache.ibatis.logging.slf4j.Slf4jImpl
mybatis.configuration.log-prefix=dao.
#默认枚举按索引保存
mybatis.configuration.default-enum-type-handler=org.apache.ibatis.type.EnumOrdinalTypeHandler

#mapper_mybatis_config
mapper.identity=MYSQL
#关键字自动转换
mapper.wrap-keyword=`{0}`
mapper.safe-delete=true
mapper.safe-update=true
#开启枚举映射
mapper.enum-as-simple-type=true
#是否只映射简单类型
#mapper.use-simple-type=false

#分页配置
pagehelper.auto-dialect=mysql
#将RowBounds第一个参数offset当成pageNum页码使用
pagehelper.offset-as-page-num=true
#使用RowBounds分页会进行count查询
pagehelper.row-bounds-with-count=true
pagehelper.page-size-zero=false
#开启分页合理化
pagehelper.reasonable=true

#liquibase数据库版本维护
spring.liquibase.enabled=false