<?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;
}