SpringBoot_持久化方案_Mybatis

墨蓝 2021-10-06 03:34 311阅读 0赞

### SpringBoot\_持久化方案\_Mybatis ###

*  依赖pom.xml
 *  配置文件bootstrap.yml
 *  配置源码
 *  分页插件
 *  基于xml示例
 *  基于注解示例

额外需要数据连接池配置提供DataSource对象

# 依赖pom.xml #

<dependency>
    		<groupId>org.mybatis.spring.boot</groupId>
    		<artifactId>mybatis-spring-boot-starter</artifactId>
    	</dependency>

# 配置文件bootstrap.yml #

#-----------持久层解决方案-Mybatis---------------
    pingruan.base.use-mybaits=true
    pingruan.base.mybatis-page-dialect=mysql
    pingruan.base.mybatis-page-pattern=Page
    pingruan.base.mapper-scan=classpath:mapper/*.xml

# 配置源码 #

/**
     * 入口类添加@MapperScan 扫描接口包
     * Description: MyBatis配置
     * @author Vander
     * @author 2018年5月23日
     * @version 0.8
     */
    @Configuration
    @ConditionalOnClass({ SqlSessionFactory.class, SqlSessionFactoryBean.class })
    @ConditionalOnBean(DataSource.class)
    @AutoConfigureAfter(DataSourceAutoConfiguration.class)
    @ConditionalOnProperty(value="pingruan.base.use-mybaits",havingValue="true")
    @EnableTransactionManagement //开启注解事务
    public class MyBatisConfiguration {
    	
    	private static final Logger log = LoggerFactory.getLogger(MyBatisConfiguration.class);
    	
    	@Autowired
    	private BProperties bProperties;
    	
    	@Autowired
    	private DataSource dataSource;
    
    	@Bean
    	@ConditionalOnMissingBean // 当容器里没有指定的Bean的情况下创建该对象
    	public SqlSessionFactory sqlSessionFactory() throws Exception {
    		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
    		// 设置数据源
    		sqlSessionFactoryBean.setDataSource(dataSource);
    		log.info(String.format("数据源加载成功，目前使用：%s",dataSource.getClass().getName()));
    		
    		// 设置mybatis的主配置文件
    		ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
    		//
    		Resource[] mapperXml = resolver.getResources(bProperties.getMapperScan());
    		// 分页插件
    		PageInterceptor pagePlugin = new PageInterceptor();// 默认mysql
    		pagePlugin.setDialect(bProperties.getMybatisPageDialect());//分页匹配的数据库支持mysql、oracle
    		pagePlugin.setPattern(bProperties.getMybatisPagePattern().toLowerCase());//分页DAO方法名匹配
    		sqlSessionFactoryBean.setPlugins(new Interceptor[]{pagePlugin});
    		//方式一：基于xml
    		sqlSessionFactoryBean.setMapperLocations(mapperXml);
    		//方式二：基于注解  可在该类上添加@MapperScan("com.plxc.data.handler.dao")注解扫描dao接口包
    		org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
    		configuration.setMapUnderscoreToCamelCase(true);
    		sqlSessionFactoryBean.setConfiguration(configuration);
    		return sqlSessionFactoryBean.getObject();
    	}
    
    	@Bean
    	public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
    		return new SqlSessionTemplate(sqlSessionFactory);
    	}
    }

# 分页插件 #

/**
     * 接口方法名
     * 
     * Description: mybatis-分页拦截器
     * @author 2018年5月22日
     * @version 0.8
     */
    @Intercepts({ @Signature(method = "prepare", type = StatementHandler.class, args = { Connection.class,Integer.class }) })
    public class PageInterceptor implements Interceptor {
    
    	public static final String PAGE_NUM_KEY = "_page";
    
    	public static final String PAGE_SIZE_KEY = "_pageSize";
    
    	static final Integer DEF_PAGE_SIZE = 10;
    
    	private String dialect = "mysql";
    
    	private String pattern = "page";
    	
    	public void setDialect(String dialect) {
    		this.dialect = dialect;
    	}
    
    	public void setPattern(String pattern) {
    		this.pattern = pattern;
    	}
    
    	@SuppressWarnings("unchecked")
    	public Object intercept(Invocation invocation) throws Throwable {
    		final RoutingStatementHandler handler = (RoutingStatementHandler) invocation.getTarget();
    		final StatementHandler delegate = (StatementHandler) ReflectUtils.getFieldValue(handler, "delegate");
    		final BoundSql boundSql = delegate.getBoundSql();
    		final MappedStatement mappedStatement = (MappedStatement) ReflectUtils.getFieldValue(delegate, "mappedStatement");
    		final String methodName = mappedStatement.getId().substring(mappedStatement.getId().lastIndexOf("."));
    		Object params = boundSql.getParameterObject();
    		if (methodName.toLowerCase().contains(pattern) && !"".equals(pattern) && params instanceof Map) {
    			Map<String, Object> paramObj = (Map<String, Object>) params;
    			Integer pageNum = paramObj.get(PAGE_NUM_KEY) == null ? 1 : Integer.parseInt(paramObj.get(PAGE_NUM_KEY).toString());
    			if (pageNum < 1) {
    				pageNum = 1;
    			}
    			Integer pageSize = paramObj.get(PAGE_SIZE_KEY) == null ? DEF_PAGE_SIZE : Integer.parseInt(paramObj.get(PAGE_SIZE_KEY).toString());
    			final String sql = boundSql.getSql();
    			final String pageSql = this.getPageSql(sql, pageNum, pageSize);
    			ReflectUtils.setFieldValue(boundSql, "sql", pageSql);
    		}
    		return invocation.proceed();
    	}
    
    	public Object plugin(Object target) {
    		return Plugin.wrap(target, this);
    	}
    
    	public void setProperties(Properties properties) {
    		this.dialect = properties.getProperty("dialect");
    		this.pattern = properties.getProperty("pattern");
    		if (this.pattern == null) {
    			this.pattern = "page";
    		}
    		this.pattern = this.pattern.toUpperCase();
    	}
    
    	private String getPageSql(String sql, Integer pageNum, Integer pageSize) {
    		final StringBuilder buffer = new StringBuilder(sql);
    		if ("oracle".equalsIgnoreCase(dialect)) {
    			return getOraclePageSql(buffer, pageNum, pageSize);
    		} else {
    			return getMysqlPageSql(buffer, pageNum, pageSize);
    		}
    	}
    
    	private String getOraclePageSql(StringBuilder sqlBuffer, Integer pageNum, Integer pageSize) {
    		// 计算第一条记录的位置，Oracle分页是通过rownum进行的，而rownum是从1开始的
    		int offset = (pageNum - 1) * pageSize + 1;
    		sqlBuffer.insert(0, "select u.*, rownum r from (").append(") u where rownum < ").append(offset + pageSize);
    		sqlBuffer.insert(0, "select * from (").append(") where r >= ").append(offset);
    		// 上面的Sql语句拼接之后大概是这个样子：
    		// select * from (select u.*, rownum r from (select * from t_user) u where rownum < 31) where r >= 16
    		return sqlBuffer.toString();
    	}
    
    	private String getMysqlPageSql(StringBuilder buffer, Integer pageNum, Integer pageSize) {
    		buffer.append(" limit ").append((pageNum - 1) * pageSize).append(",").append(pageSize);
    		return buffer.toString();
    	}
    
    	private static class ReflectUtils {
    
    		static Logger LOG = LoggerFactory.getLogger(ReflectUtils.class);
    
    		public static final Object getFieldValue(Object source, String fieldName) {
    			try {
    				Field field = getField(source, fieldName);
    				if (field != null) {
    					field.setAccessible(true);
    					return field.get(source);
    				}
    			} catch (Throwable e) {
    				LOG.error(e.getMessage());
    			}
    			return null;
    		}
    
    		public static final void setFieldValue(Object source, String fieldName, Object value) {
    			try {
    				Field field = getField(source, fieldName);
    				if (field != null) {
    					field.setAccessible(true);
    					field.set(source, value);
    				}
    			} catch (Throwable e) {
    				LOG.error(e.getMessage());
    			}
    		}
    
    		private static Field getField(Object obj, String fieldName) {
    			Field field = null;
    			for (Class<?> clazz = obj.getClass(); clazz != Object.class; clazz = clazz.getSuperclass()) {
    				try {
    					field = clazz.getDeclaredField(fieldName);
    					break;
    				} catch (NoSuchFieldException e) {
    				}
    			}
    			return field;
    		}
    	}
    }

# 基于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="org.pingruan.service.chat.dao.UserMapper">
    
        <resultMap type="org.pingruan.service.chat.pojo.User" id="BaseResultMap">
            <id column="id" jdbcType="VARCHAR" property="id" />
            <result property="id" column="id" jdbcType="VARCHAR"/>
            <result property="username" column="username" jdbcType="VARCHAR"/>
            <result property="password" column="password" jdbcType="VARCHAR"/>
            <result property="picSmall" column="pic_small" jdbcType="VARCHAR"/>
            <result property="picNormal" column="pic_normal" jdbcType="VARCHAR"/>
            <result property="nickname" column="nickname" jdbcType="VARCHAR"/>
            <result property="qrcode" column="qrcode" jdbcType="VARCHAR"/>
            <result property="clientId" column="client_id" jdbcType="VARCHAR"/>
            <result property="sign" column="sign" jdbcType="VARCHAR"/>
            <result property="createtime" column="createtime" jdbcType="DATE"/>
            <result property="phone" column="phone" jdbcType="VARCHAR"/>
        </resultMap>
    
    	<sql id="Base_Column_List">
    		id, 
    		username, 
    		password, 
    		pic_small, 
    		pic_normal, 
    		nickname, 
    		qrcode, 
    		client_id, 
    		sign, 
    		createtime, 
    		phone
    	</sql>
    
    	<select id="selectByPrimaryKey" parameterType="String"
    		resultMap="BaseResultMap">
    		select
    		<include refid="Base_Column_List" />
    		from tb_user
    		where id = #{id,jdbcType=VARCHAR}
    	</select>
    
    	<delete id="deleteByPrimaryKey" parameterType="String">
    		delete from
    		tb_user
    		where id = #{id,jdbcType=VARCHAR}
    	</delete>
    
    	
    	<insert id="insert" parameterType="org.pingruan.service.chat.pojo.User">
    	    <selectKey keyProperty="id" resultType="String" order="AFTER">
    	    	select last_insert_id()
    	    </selectKey>
    		insert tb_user
    		(
    `id`, `username`, `password`, `pic_small`, `pic_normal`, `nickname`, `qrcode`, `client_id`, `sign`, `createtime`, `phone`)
    values (
    #{id,jdbcType=VARCHAR}, #{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, #{picSmall,jdbcType=VARCHAR}, #{picNormal,jdbcType=VARCHAR}, #{nickname,jdbcType=VARCHAR}, #{qrcode,jdbcType=VARCHAR}, #{clientId,jdbcType=VARCHAR}, #{sign,jdbcType=VARCHAR}, #{createtime,jdbcType=DATE}, #{phone,jdbcType=VARCHAR})
    	</insert>
    	
    	
    	<update id="updateByPrimaryKeySelective" parameterType="org.pingruan.service.chat.pojo.User">
    		update tb_user
    		<set>
    			<if test="username != null and username != ''">
    				username = #{username,jdbcType=VARCHAR},
    			</if>
    			<if test="password != null and password != ''">
    				password = #{password,jdbcType=VARCHAR},
    			</if>
    			<if test="picSmall != null and picSmall != ''">
    				pic_small = #{picSmall,jdbcType=VARCHAR},
    			</if>
    			<if test="picNormal != null and picNormal != ''">
    				pic_normal = #{picNormal,jdbcType=VARCHAR},
    			</if>
    			<if test="nickname != null and nickname != ''">
    				nickname = #{nickname,jdbcType=VARCHAR},
    			</if>
    			<if test="qrcode != null and qrcode != ''">
    				qrcode = #{qrcode,jdbcType=VARCHAR},
    			</if>
    			<if test="clientId != null and clientId != ''">
    				client_id = #{clientId,jdbcType=VARCHAR},
    			</if>
    			<if test="sign != null and sign != ''">
    				sign = #{sign,jdbcType=VARCHAR},
    			</if>
    			<if test="createtime != null and createtime != ''">
    				createtime = #{createtime,jdbcType=DATE},
    			</if>
    			<if test="phone != null and phone != ''">
    				phone = #{phone,jdbcType=VARCHAR},
    			</if>
    		</set>
    		where id = #{id,jdbcType=VARCHAR}
    	</update>
    	
    	
    	<update id="updateByPrimaryKey" parameterType="org.pingruan.service.chat.pojo.User">
    		update tb_user
    		<set>
          username = #{username,jdbcType=VARCHAR},      password = #{password,jdbcType=VARCHAR},      pic_small = #{picSmall,jdbcType=VARCHAR},      pic_normal = #{picNormal,jdbcType=VARCHAR},      nickname = #{nickname,jdbcType=VARCHAR},      qrcode = #{qrcode,jdbcType=VARCHAR},      client_id = #{clientId,jdbcType=VARCHAR},      sign = #{sign,jdbcType=VARCHAR},      createtime = #{createtime,jdbcType=DATE},      phone = #{phone,jdbcType=VARCHAR}     </set>
         where id = #{id,jdbcType=VARCHAR}
    	</update>	
    	
    	<sql id="condition">
    		<if test="id != null and id != ''">
    			AND id = #{id,jdbcType=VARCHAR}
    		</if>
    		<if test="username != null and username != ''">
    			AND username = #{username,jdbcType=VARCHAR}
    		</if>
    		<if test="password != null and password != ''">
    			AND password = #{password,jdbcType=VARCHAR}
    		</if>
    		<if test="picSmall != null and picSmall != ''">
    			AND pic_small = #{picSmall,jdbcType=VARCHAR}
    		</if>
    		<if test="picNormal != null and picNormal != ''">
    			AND pic_normal = #{picNormal,jdbcType=VARCHAR}
    		</if>
    		<if test="nickname != null and nickname != ''">
    			AND nickname = #{nickname,jdbcType=VARCHAR}
    		</if>
    		<if test="qrcode != null and qrcode != ''">
    			AND qrcode = #{qrcode,jdbcType=VARCHAR}
    		</if>
    		<if test="clientId != null and clientId != ''">
    			AND client_id = #{clientId,jdbcType=VARCHAR}
    		</if>
    		<if test="sign != null and sign != ''">
    			AND sign = #{sign,jdbcType=VARCHAR}
    		</if>
    		<if test="createtime != null and createtime != ''">
    			AND createtime = #{createtime,jdbcType=DATE}
    		</if>
    		<if test="phone != null and phone != ''">
    			AND phone = #{phone,jdbcType=VARCHAR}
    		</if>
    	</sql>
    
    	<sql id="table">
    		tb_user
    	</sql>
    
    	<sql id="baseSelect">
    		SELECT
    		<include refid="Base_Column_List" />
    		FROM
    		<include refid="table" />
    	</sql>
    
    	<select id="findList" parameterType="map" resultMap="BaseResultMap">
    		<include refid="baseSelect" />
    		WHERE 1=1
    		<include refid="condition" />
    	</select>
    
    	<select id="findListByPage" parameterType="map" resultMap="BaseResultMap">
    		<include refid="baseSelect" />
    		WHERE 1=1
    		<include refid="condition" />
    	</select>
    
    	<select id="findTotal" parameterType="map" resultType="java.lang.Long">
    		SELECT COUNT(1) FROM (
    		<include refid="baseSelect" />
    		WHERE 1=1
    		<include refid="condition" />
    		) temp
    	</select>
    	
    	<insert id="insertBatch" parameterType="java.util.List">
    		insert tb_user
    		(
    			`id`, 			`username`, 			`password`, 			`pic_small`, 			`pic_normal`, 			`nickname`, 			`qrcode`, 			`client_id`, 			`sign`, 			`createtime`, 			`phone`		)
    		values 
    		<foreach collection="list" item="item" index="index" separator=",">
    		(
    			#{item.id,jdbcType=VARCHAR}, 			#{item.username,jdbcType=VARCHAR}, 			#{item.password,jdbcType=VARCHAR}, 			#{item.picSmall,jdbcType=VARCHAR}, 			#{item.picNormal,jdbcType=VARCHAR}, 			#{item.nickname,jdbcType=VARCHAR}, 			#{item.qrcode,jdbcType=VARCHAR}, 			#{item.clientId,jdbcType=VARCHAR}, 			#{item.sign,jdbcType=VARCHAR}, 			#{item.createtime,jdbcType=DATE}, 			#{item.phone,jdbcType=VARCHAR}		)
    		</foreach>
    	</insert>
    
    </mapper>

# 基于注解示例 #

public interface DataHandlerDao {
    	
    	@Select("select count(1) from information_schema.TABLES t where t.TABLE_NAME =\"${tableName}\"")
    	public long exist(@Param("tableName") String tableName);
    
    	@Update("${sql}")
    	public void exeSql(@Param("sql")String sql); 
    	
    	@Select("${sql}")
    	public List<Map<String, Object>> data(@Param("sql")String sql);
    	
    	@Results(id = "deptMap", value = {
    	        @Result(id=true, column = "id", property = "id"),
    	        @Result(column = "parent_id", property = "parentId"),
    	        @Result(column = "status", property = "status"),
    	        @Result(column = "created_time", property = "createdTime"),
    	        @Result(column = "update_time", property = "updateTime"),
    	        @Result(column = "name", property = "name"),
    	        @Result(column = "order_num", property = "orderNum")
    	})
    	@Select("select * from t_dept where parent_id = #{id}")
    	public List<Dept> getListById(@Param("id")Integer id);
    
    }