通用mybatis单表操作接口

时间：2022-11-10 19:58:26
<?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)=='&lt;-%-&gt;' and v.substring(v.length-5,v.length)=='&lt;-%-&gt;'">

                ${k} like concat("%",substring_index(substring_index(#{v},"&lt;-%-&gt;",2),"&lt;-%-&gt;",-1),"%")

            </when>

            <when test="v.substring(0,5)=='&lt;-%-&gt;' and v.substring(v.length-5,v.length)!='&lt;-%-&gt;'">

                ${k} like concat("%",substring_index(#{v},"&lt;-%-&gt;",-1))

            </when>

            <when test="v.substring(0,5)!='&lt;-%-&gt;' and v.substring(v.length-5,v.length)=='&lt;-%-&gt;'">

                ${k} like concat(substring_index(#{v},"&lt;-%-&gt;",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('&lt;-%-&gt;')">

                                    ${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;

    }

相关文章