mybatis generator 的分表插件
来源：互联网 发布：数据分析师工资 编辑：程序博客网 时间：2024/06/10 17:11
最近几个月写了点后台程序，一般java的后台开发就是spring mvc + mysql+redis这样，然后做一些CRUE操作，那对redis的操作有基本的客户端jedis来完成，而对mysql操作会依赖mybatis来操作完成，而mybatis需要生成对应的mapper.xml文件，一份典型的mapper文件如下，完成了一个select 查询操作。
<?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.iMybatis.abc.dao.UserDao">
<select id="queryUsers" parameterType="UserDto" resultType="UserDto"
useCache="false">
<![CDATA[
select * from t_user t where t.username = #{username}
]]>
</select>
</mapper>
为了减少些mapper.xml的时间，已经写java的model，example，文件的时间，可以用mybatis generator直接生成mybatis相关的一些文件，一般对于一个table，mybatis generator会帮助我们生成4份文件分别是：
1. TableNameExample.java
2.TableName.java
3.TableNameMapper.java
4.TableNameMapper.xml
用过mybatis genenrator的人都知道，genenrator生成的文件只支持单表查询，不支持多表查询，本文不对这个问题进行讨论，还有一个问题是分表查询，我们一般会需要创建于时间相关的表比如：table20150921 table20150922 table20150923 ,如果按照正常请求我们需要对每个表的生成一个4份文件，显然不合理，所以大家一般会手动写mapper文件使他支持接受表名查询。
<?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.iMybatis.abc.dao.UserDao">
<select id="queryUsers" parameterType="UserDto" resultType="UserDto"
useCache="false">
<![CDATA[
select * from ${userDto.tablename} where t.username = #{username}
]]>
</select>
</mapper>
这样我们就需要花点时间去修改xml和*.java文件。
======================================================================================================================
为了解决这个问题，我在mybatis genernator添加了插件，使我们能通过设置sql语句的表明来对同一个类型的表进行操作。具体做法是：
1. 把xml文件中的table都换成${tablename}
2.把生成好的tablename.java和tablenameExample.java添加一个String tablename 的字段，必要的时候可以设置tablename来对指定的分表进行查询。
3.以上两个操作我只要添加mybatis generator的plugin来完成。
==============================================
首先设置mybatis genenraotr的config中添加一个plugin
<plugintype="org.mybatis.generator.plugins.myplugin.MysqlSplitingTablePlugin"></plugin>
接着生成对应的4份文件
tablenameMapper.java不变
tablename.java和tablenameExample.java 多了一个privateString tableName ="city";字段
tablenameMapper.xml中的tablename变为了${tablename}
下面是这改变的文件，生成的table的mysql自带的一个city的table
mapper.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.dao.CityMapper" > <resultMap id="BaseResultMap" type="com.model.City" > <id column="ID" property="id" jdbcType="INTEGER" /> <result column="Name" property="name" jdbcType="CHAR" /> <result column="CountryCode" property="countrycode" jdbcType="CHAR" /> <result column="District" property="district" jdbcType="CHAR" /> <result column="Population" property="population" jdbcType="INTEGER" /> </resultMap> <sql id="Example_Where_Clause" > <where > <foreach collection="oredCriteria" item="criteria" separator="or" > <if test="criteria.valid" > <trim prefix="(" suffix=")" prefixOverrides="and" > <foreach collection="criteria.criteria" item="criterion" > <choose > <when test="criterion.noValue" > and ${criterion.condition} </when> <when test="criterion.singleValue" > and ${criterion.condition} #{criterion.value} </when> <when test="criterion.betweenValue" > and ${criterion.condition} #{criterion.value} and #{criterion.secondValue} </when> <when test="criterion.listValue" > and ${criterion.condition} <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," > #{listItem} </foreach> </when> </choose> </foreach> </trim> </if> </foreach> </where> </sql> <sql id="Update_By_Example_Where_Clause" > <where > <foreach collection="example.oredCriteria" item="criteria" separator="or" > <if test="criteria.valid" > <trim prefix="(" suffix=")" prefixOverrides="and" > <foreach collection="criteria.criteria" item="criterion" > <choose > <when test="criterion.noValue" > and ${criterion.condition} </when> <when test="criterion.singleValue" > and ${criterion.condition} #{criterion.value} </when> <when test="criterion.betweenValue" > and ${criterion.condition} #{criterion.value} and #{criterion.secondValue} </when> <when test="criterion.listValue" > and ${criterion.condition} <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," > #{listItem} </foreach> </when> </choose> </foreach> </trim> </if> </foreach> </where> </sql> <sql id="Base_Column_List" > ID, Name, CountryCode, District, Population </sql> <select id="selectByExample" resultMap="BaseResultMap" parameterType="com.model.CityExample" > select <if test="distinct" > distinct </if> <include refid="Base_Column_List" /> from ${tableName} <if test="_parameter != null" > <include refid="Example_Where_Clause" /> </if> <if test="orderByClause != null" > order by ${orderByClause} </if> <if test="offset >= 0" > limit #{offset} , #{limitCount} </if> <if test="groupByColumn != null" > group by #{groupByColumn} </if> </select> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" > select <include refid="Base_Column_List" /> from city where ID = #{id,jdbcType=INTEGER} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" > delete from city where ID = #{id,jdbcType=INTEGER} </delete> <delete id="deleteByExample" parameterType="com.model.CityExample" > delete from ${tableName} <if test="_parameter != null" > <include refid="Example_Where_Clause" /> </if> </delete> <insert id="insert" parameterType="com.model.City" > insert into ${tableName} (ID, Name, CountryCode, District, Population) values (#{id,jdbcType=INTEGER}, #{name,jdbcType=CHAR}, #{countrycode,jdbcType=CHAR}, #{district,jdbcType=CHAR}, #{population,jdbcType=INTEGER}) </insert> <insert id="insertSelective" parameterType="com.model.City" > insert into ${tableName} <trim prefix="(" suffix=")" suffixOverrides="," > <if test="id != null" > ID, </if> <if test="name != null" > Name, </if> <if test="countrycode != null" > CountryCode, </if> <if test="district != null" > District, </if> <if test="population != null" > Population, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides="," > <if test="id != null" > #{id,jdbcType=INTEGER}, </if> <if test="name != null" > #{name,jdbcType=CHAR}, </if> <if test="countrycode != null" > #{countrycode,jdbcType=CHAR}, </if> <if test="district != null" > #{district,jdbcType=CHAR}, </if> <if test="population != null" > #{population,jdbcType=INTEGER}, </if> </trim> </insert> <select id="countByExample" parameterType="com.model.CityExample" resultType="java.lang.Integer" > select count(*) from city <if test="_parameter != null" > <include refid="Example_Where_Clause" /> </if> </select> <update id="updateByExampleSelective" parameterType="map" > update ${record.tableName} <set > <if test="record.id != null" > ID = #{record.id,jdbcType=INTEGER}, </if> <if test="record.name != null" > Name = #{record.name,jdbcType=CHAR}, </if> <if test="record.countrycode != null" > CountryCode = #{record.countrycode,jdbcType=CHAR}, </if> <if test="record.district != null" > District = #{record.district,jdbcType=CHAR}, </if> <if test="record.population != null" > Population = #{record.population,jdbcType=INTEGER}, </if> </set> <if test="_parameter != null" > <include refid="Update_By_Example_Where_Clause" /> </if> </update> <update id="updateByExample" parameterType="map" > update ${record.tableName} set ID = #{record.id,jdbcType=INTEGER}, Name = #{record.name,jdbcType=CHAR}, CountryCode = #{record.countrycode,jdbcType=CHAR}, District = #{record.district,jdbcType=CHAR}, Population = #{record.population,jdbcType=INTEGER} <if test="_parameter != null" > <include refid="Update_By_Example_Where_Clause" /> </if> </update> <update id="updateByPrimaryKeySelective" parameterType="com.model.City" > update ${tableName} <set > <if test="name != null" > Name = #{name,jdbcType=CHAR}, </if> <if test="countrycode != null" > CountryCode = #{countrycode,jdbcType=CHAR}, </if> <if test="district != null" > District = #{district,jdbcType=CHAR}, </if> <if test="population != null" > Population = #{population,jdbcType=INTEGER}, </if> </set> where ID = #{id,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="com.model.City" > update ${tableName} set Name = #{name,jdbcType=CHAR}, CountryCode = #{countrycode,jdbcType=CHAR}, District = #{district,jdbcType=CHAR}, Population = #{population,jdbcType=INTEGER} where ID = #{id,jdbcType=INTEGER} </update></mapper>tablenameMapper.java
package com.dao;import com.model.City;import com.model.CityExample;import java.util.List;import org.apache.ibatis.annotations.Param;public interface CityMapper { int countByExample(CityExample example); int deleteByExample(CityExample example); int deleteByPrimaryKey(Integer id); int insert(City record); int insertSelective(City record); List<City> selectByExample(CityExample example); City selectByPrimaryKey(Integer id); int updateByExampleSelective(@Param("record") City record, @Param("example") CityExample example); int updateByExample(@Param("record") City record, @Param("example") CityExample example); int updateByPrimaryKeySelective(City record); int updateByPrimaryKey(City record);}tablename.java
package com.model;public class City { private Integer id; private String name; private String countrycode; private String district; private Integer population; private String tableName = "city"; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name == null ? null : name.trim(); } public String getCountrycode() { return countrycode; } public void setCountrycode(String countrycode) { this.countrycode = countrycode == null ? null : countrycode.trim(); } public String getDistrict() { return district; } public void setDistrict(String district) { this.district = district == null ? null : district.trim(); } public Integer getPopulation() { return population; } public void setPopulation(Integer population) { this.population = population; } public void setTableName(String tableName) { this.tableName=tableName; } public String getTableName() { return tableName; }}tableNameexample.java
package com.model;import java.util.ArrayList;import java.util.List;public class CityExample { protected String orderByClause; protected boolean distinct; protected List<Criteria> oredCriteria; protected Integer offset = -1; protected Integer limitCount = -1; private String tableName = "city"; protected String groupByColumn = null; public CityExample() { oredCriteria = new ArrayList<Criteria>(); } public void setOrderByClause(String orderByClause) { this.orderByClause = orderByClause; } public String getOrderByClause() { return orderByClause; } public void setDistinct(boolean distinct) { this.distinct = distinct; } public boolean isDistinct() { return distinct; } public List<Criteria> getOredCriteria() { return oredCriteria; } public void or(Criteria criteria) { oredCriteria.add(criteria); } public Criteria or() { Criteria criteria = createCriteriaInternal(); oredCriteria.add(criteria); return criteria; } public Criteria createCriteria() { Criteria criteria = createCriteriaInternal(); if (oredCriteria.size() == 0) { oredCriteria.add(criteria); } return criteria; } protected Criteria createCriteriaInternal() { Criteria criteria = new Criteria(); return criteria; } public void clear() { oredCriteria.clear(); orderByClause = null; distinct = false; } public void setOffset(Integer offset) { this.offset=offset; } public Integer getOffset() { return offset; } public void setLimitCount(Integer limitCount) { this.limitCount=limitCount; } public Integer getLimitCount() { return limitCount; } public void setTableName(String tableName) { this.tableName=tableName; } public String getTableName() { return tableName; } public void setGroupByColumn(String groupByColumn) { this.groupByColumn=groupByColumn; } public String getGroupByColumn() { return groupByColumn; } protected abstract static class GeneratedCriteria { protected List<Criterion> criteria; protected GeneratedCriteria() { super(); criteria = new ArrayList<Criterion>(); } public boolean isValid() { return criteria.size() > 0; } public List<Criterion> getAllCriteria() { return criteria; } public List<Criterion> getCriteria() { return criteria; } protected void addCriterion(String condition) { if (condition == null) { throw new RuntimeException("Value for condition cannot be null"); } criteria.add(new Criterion(condition)); } protected void addCriterion(String condition, Object value, String property) { if (value == null) { throw new RuntimeException("Value for " + property + " cannot be null"); } criteria.add(new Criterion(condition, value)); } protected void addCriterion(String condition, Object value1, Object value2, String property) { if (value1 == null || value2 == null) { throw new RuntimeException("Between values for " + property + " cannot be null"); } criteria.add(new Criterion(condition, value1, value2)); } public Criteria andIdIsNull() { addCriterion("ID is null"); return (Criteria) this; } public Criteria andIdIsNotNull() { addCriterion("ID is not null"); return (Criteria) this; } public Criteria andIdEqualTo(Integer value) { addCriterion("ID =", value, "id"); return (Criteria) this; } public Criteria andIdNotEqualTo(Integer value) { addCriterion("ID <>", value, "id"); return (Criteria) this; } public Criteria andIdGreaterThan(Integer value) { addCriterion("ID >", value, "id"); return (Criteria) this; } public Criteria andIdGreaterThanOrEqualTo(Integer value) { addCriterion("ID >=", value, "id"); return (Criteria) this; } public Criteria andIdLessThan(Integer value) { addCriterion("ID <", value, "id"); return (Criteria) this; } public Criteria andIdLessThanOrEqualTo(Integer value) { addCriterion("ID <=", value, "id"); return (Criteria) this; } public Criteria andIdIn(List<Integer> values) { addCriterion("ID in", values, "id"); return (Criteria) this; } public Criteria andIdNotIn(List<Integer> values) { addCriterion("ID not in", values, "id"); return (Criteria) this; } public Criteria andIdBetween(Integer value1, Integer value2) { addCriterion("ID between", value1, value2, "id"); return (Criteria) this; } public Criteria andIdNotBetween(Integer value1, Integer value2) { addCriterion("ID not between", value1, value2, "id"); return (Criteria) this; } public Criteria andNameIsNull() { addCriterion("Name is null"); return (Criteria) this; } public Criteria andNameIsNotNull() { addCriterion("Name is not null"); return (Criteria) this; } public Criteria andNameEqualTo(String value) { addCriterion("Name =", value, "name"); return (Criteria) this; } public Criteria andNameNotEqualTo(String value) { addCriterion("Name <>", value, "name"); return (Criteria) this; } public Criteria andNameGreaterThan(String value) { addCriterion("Name >", value, "name"); return (Criteria) this; } public Criteria andNameGreaterThanOrEqualTo(String value) { addCriterion("Name >=", value, "name"); return (Criteria) this; } public Criteria andNameLessThan(String value) { addCriterion("Name <", value, "name"); return (Criteria) this; } public Criteria andNameLessThanOrEqualTo(String value) { addCriterion("Name <=", value, "name"); return (Criteria) this; } public Criteria andNameLike(String value) { addCriterion("Name like", value, "name"); return (Criteria) this; } public Criteria andNameNotLike(String value) { addCriterion("Name not like", value, "name"); return (Criteria) this; } public Criteria andNameIn(List<String> values) { addCriterion("Name in", values, "name"); return (Criteria) this; } public Criteria andNameNotIn(List<String> values) { addCriterion("Name not in", values, "name"); return (Criteria) this; } public Criteria andNameBetween(String value1, String value2) { addCriterion("Name between", value1, value2, "name"); return (Criteria) this; } public Criteria andNameNotBetween(String value1, String value2) { addCriterion("Name not between", value1, value2, "name"); return (Criteria) this; } public Criteria andCountrycodeIsNull() { addCriterion("CountryCode is null"); return (Criteria) this; } public Criteria andCountrycodeIsNotNull() { addCriterion("CountryCode is not null"); return (Criteria) this; } public Criteria andCountrycodeEqualTo(String value) { addCriterion("CountryCode =", value, "countrycode"); return (Criteria) this; } public Criteria andCountrycodeNotEqualTo(String value) { addCriterion("CountryCode <>", value, "countrycode"); return (Criteria) this; } public Criteria andCountrycodeGreaterThan(String value) { addCriterion("CountryCode >", value, "countrycode"); return (Criteria) this; } public Criteria andCountrycodeGreaterThanOrEqualTo(String value) { addCriterion("CountryCode >=", value, "countrycode"); return (Criteria) this; } public Criteria andCountrycodeLessThan(String value) { addCriterion("CountryCode <", value, "countrycode"); return (Criteria) this; } public Criteria andCountrycodeLessThanOrEqualTo(String value) { addCriterion("CountryCode <=", value, "countrycode"); return (Criteria) this; } public Criteria andCountrycodeLike(String value) { addCriterion("CountryCode like", value, "countrycode"); return (Criteria) this; } public Criteria andCountrycodeNotLike(String value) { addCriterion("CountryCode not like", value, "countrycode"); return (Criteria) this; } public Criteria andCountrycodeIn(List<String> values) { addCriterion("CountryCode in", values, "countrycode"); return (Criteria) this; } public Criteria andCountrycodeNotIn(List<String> values) { addCriterion("CountryCode not in", values, "countrycode"); return (Criteria) this; } public Criteria andCountrycodeBetween(String value1, String value2) { addCriterion("CountryCode between", value1, value2, "countrycode"); return (Criteria) this; } public Criteria andCountrycodeNotBetween(String value1, String value2) { addCriterion("CountryCode not between", value1, value2, "countrycode"); return (Criteria) this; } public Criteria andDistrictIsNull() { addCriterion("District is null"); return (Criteria) this; } public Criteria andDistrictIsNotNull() { addCriterion("District is not null"); return (Criteria) this; } public Criteria andDistrictEqualTo(String value) { addCriterion("District =", value, "district"); return (Criteria) this; } public Criteria andDistrictNotEqualTo(String value) { addCriterion("District <>", value, "district"); return (Criteria) this; } public Criteria andDistrictGreaterThan(String value) { addCriterion("District >", value, "district"); return (Criteria) this; } public Criteria andDistrictGreaterThanOrEqualTo(String value) { addCriterion("District >=", value, "district"); return (Criteria) this; } public Criteria andDistrictLessThan(String value) { addCriterion("District <", value, "district"); return (Criteria) this; } public Criteria andDistrictLessThanOrEqualTo(String value) { addCriterion("District <=", value, "district"); return (Criteria) this; } public Criteria andDistrictLike(String value) { addCriterion("District like", value, "district"); return (Criteria) this; } public Criteria andDistrictNotLike(String value) { addCriterion("District not like", value, "district"); return (Criteria) this; } public Criteria andDistrictIn(List<String> values) { addCriterion("District in", values, "district"); return (Criteria) this; } public Criteria andDistrictNotIn(List<String> values) { addCriterion("District not in", values, "district"); return (Criteria) this; } public Criteria andDistrictBetween(String value1, String value2) { addCriterion("District between", value1, value2, "district"); return (Criteria) this; } public Criteria andDistrictNotBetween(String value1, String value2) { addCriterion("District not between", value1, value2, "district"); return (Criteria) this; } public Criteria andPopulationIsNull() { addCriterion("Population is null"); return (Criteria) this; } public Criteria andPopulationIsNotNull() { addCriterion("Population is not null"); return (Criteria) this; } public Criteria andPopulationEqualTo(Integer value) { addCriterion("Population =", value, "population"); return (Criteria) this; } public Criteria andPopulationNotEqualTo(Integer value) { addCriterion("Population <>", value, "population"); return (Criteria) this; } public Criteria andPopulationGreaterThan(Integer value) { addCriterion("Population >", value, "population"); return (Criteria) this; } public Criteria andPopulationGreaterThanOrEqualTo(Integer value) { addCriterion("Population >=", value, "population"); return (Criteria) this; } public Criteria andPopulationLessThan(Integer value) { addCriterion("Population <", value, "population"); return (Criteria) this; } public Criteria andPopulationLessThanOrEqualTo(Integer value) { addCriterion("Population <=", value, "population"); return (Criteria) this; } public Criteria andPopulationIn(List<Integer> values) { addCriterion("Population in", values, "population"); return (Criteria) this; } public Criteria andPopulationNotIn(List<Integer> values) { addCriterion("Population not in", values, "population"); return (Criteria) this; } public Criteria andPopulationBetween(Integer value1, Integer value2) { addCriterion("Population between", value1, value2, "population"); return (Criteria) this; } public Criteria andPopulationNotBetween(Integer value1, Integer value2) { addCriterion("Population not between", value1, value2, "population"); return (Criteria) this; } } public static class Criteria extends GeneratedCriteria { protected Criteria() { super(); } } public static class Criterion { private String condition; private Object value; private Object secondValue; private boolean noValue; private boolean singleValue; private boolean betweenValue; private boolean listValue; private String typeHandler; public String getCondition() { return condition; } public Object getValue() { return value; } public Object getSecondValue() { return secondValue; } public boolean isNoValue() { return noValue; } public boolean isSingleValue() { return singleValue; } public boolean isBetweenValue() { return betweenValue; } public boolean isListValue() { return listValue; } public String getTypeHandler() { return typeHandler; } protected Criterion(String condition) { super(); this.condition = condition; this.typeHandler = null; this.noValue = true; } protected Criterion(String condition, Object value, String typeHandler) { super(); this.condition = condition; this.value = value; this.typeHandler = typeHandler; if (value instanceof List<?>) { this.listValue = true; } else { this.singleValue = true; } } protected Criterion(String condition, Object value) { this(condition, value, null); } protected Criterion(String condition, Object value, Object secondValue, String typeHandler) { super(); this.condition = condition; this.value = value; this.secondValue = secondValue; this.typeHandler = typeHandler; this.betweenValue = true; } protected Criterion(String condition, Object value, Object secondValue) { this(condition, value, secondValue, null); } }}关键是源码我放在git上了。enjoy it
https://github.com/NanYoMy/mybatis-generator
0 0
- mybatis generator 的分表插件
- mybatis generator的eclipse插件
- mybatis generator插件的使用 。
- MyBatis Generator的内置插件
- mybatis generator插件的使用
- mybatis插件 mybatis-generator的使用
- MyBatis插件的使用：mybatis-generator、mybatis-plugin、mybatis-pagehelper
- mybatis generator eclipse插件的安装
- mybatis generator eclipse插件的安装
- mybatis generator eclipse插件的安装
- mybatis generator eclipse插件的安装
- mybatis generator eclipse插件的安装
- generator eclipse插件的安装mybatis
- mybatis generator eclipse插件的安装
- eclipse中mybatis generator插件的安装
- mybatis generator eclipse插件的安装
- Eclipse插件：mybatis generator的使用步骤
- mybatis generator eclipse插件的安装
- 约梭芬杀人法(2015年9月12日)
- easyui的combobox控件进一步优化
- IMOOC_intanceof
- 网卡上除RJ-45接口外,还需要其它元件吗?
- 三周的忙碌生活
- mybatis generator 的分表插件
- Caffe blob reshape
- C语言进程间通信
- JAVA CAS 原理分析
- BestCoder Round #55 ($) 1001
- 10BaseT和100BaseTX PHY实现方式不同的原因何在?
- tp基本的增删改查的操作demo
- CCF 2014年9月 3题
- URAL 1022 Genealogical Tree (拓扑排序)
