SpringBoot_持久化方案_Mybatis

原创 于 2020-07-27 09:15:28 发布 · 136 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

博客介绍了项目的依赖 pom.xml、配置文件 bootstrap.yml 以及配置源码，还提及了分页插件，并给出基于 xml 和注解的示例，为项目开发提供了配置和使用参考。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

SpringBoot_持久化方案_Mybatis


额外需要数据连接池配置提供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>

	<!-- Check Package -->
	<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>
	
	<!-- Check Package -->
	<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>
	
	<!-- Check Package -->
	<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);

}
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值