通用mybatis单表操作接口
<?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"> <!-- 默认情况insert和update成功后返回影响行数 --> <mapper namespace="com.yy.fastcustom.dataoperation.IDataOperationMapper"> <!--=====返回值区=====--> <resultMap id="idAndNameMap" type="java.util.Map"> <!--column="数据库表字段" property="map中的"--> <result column="id" javaType="java.lang.String" jdbcType="VARCHAR" property="_id"/> <result column="name" javaType="java.lang.String" jdbcType="VARCHAR" property="_name"/> <result column="birthday" javaType="java.lang.String" jdbcType="DATE" property="_birthday"/> </resultMap> <!--=====sql区=====--> <sql id="byId"> <choose> <!--如果首先判断id，id为null的情况就会报map没有实例化或map属性没有实例化；综上探讨判断attrMap是否为null的时候id出现异常较容易观察--> <when test="attrMap!=null and attrMap.id!=null and attrMap.id!=''"> id=#{attrMap.id} </when> <otherwise> id=#{id} </otherwise> </choose> </sql> <sql id="where_like"> <!--如果首先判断id，id为null的情况就会报map没有实例化或map属性没有实例化；综上探讨判断attrMap是否为null的时候id出现异常较容易观察--> <choose> <when test="v.substring(0,5)=='<-%->' and v.substring(v.length-5,v.length)=='<-%->'"> ${k} like concat("%",substring_index(substring_index(#{v},"<-%->",2),"<-%->",-1),"%") </when> <when test="v.substring(0,5)=='<-%->' and v.substring(v.length-5,v.length)!='<-%->'"> ${k} like concat("%",substring_index(#{v},"<-%->",-1)) </when> <when test="v.substring(0,5)!='<-%->' and v.substring(v.length-5,v.length)=='<-%->'"> ${k} like concat(substring_index(#{v},"<-%->",1),"%") </when> </choose> </sql> <sql id="where_and_order"> <where> <foreach collection="attrMap.entrySet()" item="v" index="k" separator=" and "> <if test="v!=null"> <choose> <when test="v instanceof java.util.List "> ${k} IN ( <foreach collection="v" item="e" separator=","> #{e} </foreach>) </when> <otherwise> <choose> <when test="v=='' or !v.contains('<-%->')"> ${k}=#{v} </when> <otherwise> <include refid="where_like"></include> </otherwise> </choose> </otherwise> </choose> </if> </foreach> </where> <if test="orderList!=null and orderList.size()>0"> order by <foreach collection="orderList" item="v" separator=","> ${v.v1} ${v.v2} </foreach> </if> </sql> <insert id="basicInsert" parameterType="dataOperationModel"> insert into ${tableName} <foreach collection="attrMap.keySet()" item="k" separator="," open="(" close=")"> ${k} </foreach> values <foreach collection="attrMap.values()" item="v" separator="," open="(" close=")"> #{v} </foreach> </insert> <update id="basicUpdate" parameterType="dataOperationModel"> update ${tableName} set <foreach collection="attrMap.entrySet()" index="k" item="v" separator=","> <if test="k!='id'"> ${k}=#{v} </if> </foreach> <where> <include refid="byId"></include> </where> </update> <delete id="basicDelete" parameterType="dataOperationModel"> delete from ${tableName} <where> <include refid="byId"></include> </where> </delete> <select id="basicSelect" parameterType="dataOperationModel" resultType="java.util.Map"> select <foreach collection="attrMap.keySet()" item="k" separator=","> ${k} </foreach> from ${tableName} <include refid="where_and_order"></include> <if test="limitIndex!=-1l and limitSize!=-1l"> limit #{limitIndex},#{limitSize} </if> </select> <select id="basicSelectCount" parameterType="dataOperationModel" resultType="int"> select count(1) from ${tableName} <include refid="where_and_order"></include> </select> </mapper>
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 加载属性文件 --> <properties resource="db.properties"> <!--properties中还可以配置一些属性名和属性值 --> <!-- <property name="jdbc.driver" value=""/> --> </properties> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings> <typeAliases> <!-- 类的别名定义，配置中名称和类型名称间的映射 --> <typeAlias type="com.yy.fastcustom.bak.Person" alias="person"/> <typeAlias type="com.yy.fastcustom.dataoperation.DataOperationModel" alias="dataOperationModel"/> </typeAliases> <environments default="development"> <!--这里除了配置开发环境，也可以配置test环境--> <environment id="development"> <!-- 使用jdbc事务管理，事务控制由mybatis --> <transactionManager type="JDBC"/> <!-- druid数据库连接池，由mybatis管理 --> <dataSource type="com.yy.fastcustom.dataoperation.DruidDataSourceFactory"> <property name="driver" value="${jdbc.driver}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </dataSource> </environment> </environments> <!-- 加载 映射文件 --> <mappers> <!--通过resource方法一次加载一个映射文件 --> <!--注意这里的路径和xml文件 --> <mapper resource="DataOperationMapper.xml"/> </mappers> </configuration>
package com.yy.fastcustom.dataoperation; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; import java.util.Map; /** * Created by zzq on 2019/5/11/011. */ public class DataOperationManager { private static volatile SqlSessionFactory sqlSessionFactory; public static SqlSessionFactory getSqlSessionFactory() { if (sqlSessionFactory == null) synchronized (DataOperationManager.class) { if (sqlSessionFactory == null) { String filePath = "SqlConfig.xml"; InputStream in = null; try { in = Resources.getResourceAsStream(filePath); } catch (IOException e) { e.printStackTrace(); } sqlSessionFactory = new SqlSessionFactoryBuilder() .build(in); } } return sqlSessionFactory; } public static <T> void operate(Class<T> tClz, DataOperation<T> operation) { SqlSession ss = getSqlSessionFactory().openSession(); try { T t = ss.getMapper(tClz); operation.execute(t); } catch (Exception e) { e.printStackTrace(); } finally { ss.commit(); ss.close(); } } public static List<Map<String, Object>> basicSelect(DataOperationModel dataOperationModel) { List<Map<String, Object>> ret = new ArrayList<>(); DataOperationManager.operate(IDataOperationMapper.class, basicMapper -> ret.addAll(basicMapper.basicSelect(dataOperationModel))); return ret; } public static int basicSelectCount(DataOperationModel dataOperationModel) { int ret[] = new int[1]; DataOperationManager.operate(IDataOperationMapper.class, basicMapper -> ret[0] = basicMapper.basicSelectCount(dataOperationModel)); return ret[0]; } public static int basicInsert(DataOperationModel dataOperationModel) { int ret[] = new int[1]; DataOperationManager.operate(IDataOperationMapper.class, basicMapper -> ret[0] = basicMapper.basicInsert(dataOperationModel)); return ret[0]; } public static int basicDelete(DataOperationModel dataOperationModel) { int ret[] = new int[1]; DataOperationManager.operate(IDataOperationMapper.class, basicMapper -> ret[0] = basicMapper.basicDelete(dataOperationModel)); return ret[0]; } public static int basicUpdate(DataOperationModel dataOperationModel) { int ret[] = new int[1]; DataOperationManager.operate(IDataOperationMapper.class, basicMapper -> ret[0] = basicMapper.basicUpdate(dataOperationModel)); return ret[0]; } public interface DataOperation<T> { void execute(T t); } }
package com.yy.fastcustom.dataoperation; import lombok.Getter; import lombok.Setter; import java.util.Arrays; import java.util.List; import java.util.Map; /** * Created by zzq on 2019/5/12/012. */ @Getter @Setter public class DataOperationModel { private String tableName; private Map<String, Object> attrMap; private String id; private long limitIndex = -1l; private long limitSize = -1l; private List<Tuple2> orderList; public DataOperationModel() { } public DataOperationModel(String tableName, Map<String, Object> attrMap) { this.tableName = tableName; this.attrMap = attrMap; } public DataOperationModel(String tableName, Map<String, Object> attrMap, String id) { this.tableName = tableName; this.attrMap = attrMap; this.id = id; } public DataOperationModel(String tableName, String id) { this.tableName = tableName; this.id = id; } /** * 计算分页参数 * * @param pageIndex * @param pageSize */ public void calcPage(long pageIndex, long pageSize) { this.limitIndex = (pageIndex - 1) * pageSize; this.limitSize = pageSize; } /** * 排序字段 * * @param ts */ public void orderBy(Tuple2... ts) { this.orderList = Arrays.asList(ts); } }
package com.yy.fastcustom.dataoperation; import java.util.List; import java.util.Map; /** * Created by zzq on 2019/5/12/012. */ public interface IDataOperationMapper { int basicInsert(DataOperationModel dataOperationModel); int basicUpdate(DataOperationModel dataOperationModel); int basicDelete(DataOperationModel dataOperationModel); int basicSelectCount(DataOperationModel dataOperationModel); List<Map<String, Object>> basicSelect(DataOperationModel dataOperationModel); }
package com.yy.fastcustom.dataoperation; /** * Created by zzq on 2019/6/11. */ public class Tuple2 { private String v1; private String v2; public Tuple2(String v1, String v2) { this.v1 = v1; this.v2 = v2; } public String getV1() { return v1; } public String getV2() { return v2; } }
package com.yy.fastcustom.dataoperation; import com.alibaba.druid.pool.DruidDataSource; import org.apache.ibatis.datasource.DataSourceFactory; import javax.sql.DataSource; import java.sql.SQLException; import java.util.Properties; /** * Created by zzq on 2019/5/11/011. */ public class DruidDataSourceFactory implements DataSourceFactory { private Properties props; @Override public void setProperties(Properties properties) { this.props = properties; } @Override public DataSource getDataSource() { DruidDataSource dds = new DruidDataSource(); dds.setDriverClassName(this.props.getProperty("driver")); dds.setUrl(this.props.getProperty("url")); dds.setUsername(this.props.getProperty("username")); dds.setPassword(this.props.getProperty("password")); // 其他配置可以根据MyBatis主配置文件进行配置 try { dds.init(); } catch (SQLException e) { e.printStackTrace(); } return dds; } }
DataOperationManager 如果需要手工初始化数据源可以使用如下做法：
public static SqlSessionFactory getSqlSessionFactory() throws IOException { if (sqlSessionFactory == null) synchronized (DataOperationManager.class) { if (sqlSessionFactory == null) { // String filePath = "SqlConfig.xml"; // InputStream in = Resources.getResourceAsStream(filePath); TransactionFactory transactionFactory = new JdbcTransactionFactory(); Map<String, DataSource> dataSourceMap = new HashMap<>(); // 配置第一个数据源 DruidDataSource dataSource1 = new DruidDataSource(); dataSource1.setDriverClassName("com.mysql.jdbc.Driver"); dataSource1.setUrl("jdbc:mysql://localhost:3306/ds0?characterEncoding=UTF-8"); dataSource1.setUsername("root"); dataSource1.setPassword("123456"); dataSourceMap.put("ds0", dataSource1); // 配置第二个数据源 DruidDataSource dataSource2 = new DruidDataSource(); dataSource2.setDriverClassName("com.mysql.jdbc.Driver"); dataSource2.setUrl("jdbc:mysql://localhost:3306/ds1?characterEncoding=UTF-8"); dataSource2.setUsername("root"); dataSource2.setPassword("123456"); dataSourceMap.put("ds1", dataSource2); // 配置Order表规则 TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("td_table", "ds${0..1}.td_table${0..1}"); // 配置分库 + 分表策略 orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "ds${id % 2}")); orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "td_table${order_id % 2}")); // 配置分片规则 ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration(); shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig); // 省略配置order_item表规则... // 获取数据源对象 DataSource dataSource = null; try { dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties()); } catch (SQLException e) { e.printStackTrace(); } Configuration c = new Configuration(); c.setLogImpl(StdOutImpl.class); //注册类别名必须在设置Mapper之前，否则mybatis执行时将找不到 c.getTypeAliasRegistry().registerAlias("dataOperationModel", "com.yy.fastcustom.dataoperation.DataOperationModel"); c.addMappers("com.yy.fastcustom.dataoperation"); Environment environment = new Environment("1", transactionFactory, dataSource); c.setEnvironment(environment); sqlSessionFactory = new SqlSessionFactoryBuilder() .build(c); } } return sqlSessionFactory; }
