Mysql操作

YonBuilder专业版后端脚手架提供两种方式操作数据库，当然主流的MyBatis也是支持的。

脚手架配置文件配置好连接，不需要注入，就可以直接操作mysql了。

一、MetaDaoHelper方式

源码类：MetaDaoHelper.class

注：mdd方式，需要当前线程中有租户id和token信息

1.新增

void insert(String fullname, T bill)

新增一条

        // fullname即实体的URL
        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        // 构造实体map
        BizObject map = new BizObject();
        map.set("new1","111");
        map.set("new2","222");
        map.set("new3","333");
        // 脚手架自带工具类生成的分布式id
        long id = IdManager.getInstance().nextId();
        // 若主键不是自增，需要设置
        map.set("id",id);
        // 其中dr、tenant_id 自动初始化
        MetaDaoHelper.insert(fullname,map);

void insert(String fullname, List< T> list)

新增list

        // fullname即实体的URL
        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        // 构造实体list
        List<BizObject> list = new ArrayList<>();
        for (int i = 0; i < 2 ; i++){
            BizObject map = new BizObject();
            map.set("new1","111"+i);
            map.set("new2","111"+i);
            map.set("new3","111"+i);
            map.set("id",IdManager.getInstance().nextId());
            list.add(map);
        }
        MetaDaoHelper.insert(fullname,list);

void insert(String fullname, String jsonString)

按json串新增（单条或批量操作）

        // fullname即实体的URL
        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        // json字符串，可对象可数组
        String jsonString = "[\n"
            + "  {\n"
            + "    \"new1\": \"1\",\n"
            + "    \"new2\": \"112\",\n"
            + "    \"new3\": \"developer.yonyoucloud.com\",\n"
            + "    \"id\":111111\n"
            + "  },\n"
            + "  {\n"
            + "    \"new1\": \"2\",\n"
            + "    \"new2\": \"112\",\n"
            + "    \"new3\": \"developer.yonyoucloud.com\",\n"
            + "    \"id\":111112\n"
            + "  }\n"
            + "]\n";
        MetaDaoHelper.insert(fullname,jsonString);

注：其中fullname参数获取途径：

2.删除

void delete(String fullname, Long id)

根据主键id删除

        // fullname即实体的URL
        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        Long id = 111111L;
        MetaDaoHelper.delete(fullname,id);

void delete(String fullname, T bill)

根据对象删除(必须要有主键id)

        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        BizObject map = new BizObject();
        map.set("id","111112");
        MetaDaoHelper.delete(fullname,map);

void delete(String fullname, List< T> bills)

根据集合删除

        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        List<BizObject> list = new ArrayList<>();
        BizObject map = new BizObject();
        map.set("id", 111111L);
        list.add(map);
        map = new BizObject();
        map.set("id", 2411305563164928L);
        list.add(map);
        MetaDaoHelper.delete(fullname,list);

3.更新

void update(String fullname, String jsonString)

根据json串更新。

        // fullname即实体的URL
        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        String jsonString = "[\n"
            + "  {\n"
            + "    \"new1\": \"修改1\",\n"
            + "    \"new2\": \"修改12\",\n"
            + "    \"new3\": \"修改\",\n"
            + "    \"_status\": \"Update\",\n"
            + "    \"id\":2578822063395072\n"
            + "  },\n"
            + "  {\n"
            + "    \"new1\": \"update2\",\n"
            + "    \"new2\": \"update112\",\n"
            + "    \"new3\": \"developer.yonyoucloud.com\",\n"
            + "    \"_status\": \"Update\",\n"
            + "    \"id\":2578822063395073\n"
            + "  }\n"
            + "]\n";
        MetaDaoHelper.update(fullname,jsonString);

< T extends BizObject> void update(String fullname, T bill)

更新对象

        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        BizObject map = new BizObject();
        map.set("new1","111");
        map.set("new2","222");
        map.set("new3","333");
        map.setEntityStatus(EntityStatus.Update);//_status：Update
        map.setId(2578822063395073L); // 主键
        MetaDaoHelper.update(fullname,map);

< T extends BizObject> void update(String fullname, List< T> list)

批量更新

        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        List< BizObject> list = new ArrayList<>();
        BizObject map = new BizObject();
        map.set("new1","111");
        map.set("new2","222");
        map.set("new3","333333333333");
        map.setEntityStatus(EntityStatus.Update);
        map.setId(2578822063395073L);
        list.add(map);

        map = new BizObject();
        map.set("new3","333333333334");
        map.setEntityStatus(EntityStatus.Update);
        map.setId(2578780210172160L);
        list.add(map);
        MetaDaoHelper.update(fullname,list);

< T extends BizObject> void updateEntity(Entity entity, T bill)

更新对象

        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        Entity entity = MetaDaoHelper.getEntity(fullname);
        BizObject map = new BizObject();
        map.set("new1","111");
        map.set("new2","222");
        map.set("new3","333");
        map.setEntityStatus(EntityStatus.Update);//_status：Update
        map.setId(2578822063395073L); // 主键
        MetaDaoHelper.updateEntity(fullname,map);

< T extends BizObject> void updateEntity(Entity entity, List< T> list)

批量更新

        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        Entity entity = MetaDaoHelper.getEntity(fullname);

        List<BizObject> list = new ArrayList<>();
        BizObject map = new BizObject();
        map.set("new1","111");
        map.set("new2","222");
        map.set("new3","333333333331");
        map.setEntityStatus(EntityStatus.Update);
        map.setId(2578822063395073L);
        list.add(map);

        map = new BizObject();
        map.set("new3","333333333332");
        map.setEntityStatus(EntityStatus.Update);
        map.setId(2578780210172160L);
        list.add(map);
        MetaDaoHelper.updateEntity(entity,list);

4.查询

List< E> selectSql(String sql, Map< String, Object> param)

根据sql查询

        // 定义sql
        String sql = " SELECT * FROM admin_1 WHERE new1 = #{new1_key} and dr = #{dr} ";
        // 定义条件
        HashMap<String, Object> mapParam = Maps.newHashMap(ImmutableMap.of("new1_key", "111","dr","0"));
        // 查询
        List<Object> list = MetaDaoHelper.selectSql(sql, mapParam);
        System.err.println(list);

< T extends Map< String, Object>> List< T> queryById(String fullname, String selectFieldString, Object id)

根据id查询部分字段

        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        String selectFieldString = "new1,new2,new3";
        Long id = 2578780210172160L;
        List<Map<String, Object>> list = MetaDaoHelper.queryById(fullname, selectFieldString, id);
        System.err.println(list);

List< T> queryByIds(String fullname, String selectFieldString, Long[] ids)

根据id数组查询部分字段

        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        String selectFieldString = "new1,new2,new3";
        Long [] ids = {2578780210172160L,2578822063395073L};
        List<Map<String, Object>> list = MetaDaoHelper.queryByIds(fullname, selectFieldString, ids);
        System.err.println(list);

List< T> queryByIds(String fullname, String selectFieldString, List< String> ids)

根据id集合查询部分字段

        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        String selectFieldString = "new1,new2,new3";
        List<String> ids = Arrays.asList("2578780210172160","2578822063395073");
        List<Map<String, Object>> list = MetaDaoHelper.queryByIds(fullname, selectFieldString, ids);
        System.err.println(list);

< T extends BizObject> T findById(String fullname, Object id)

根据主键查询2层(deepth默认2,就是可以返回主子表数据)

        // 根据id查询数据（包含子表数据）
        // 第一个参数fullname即实体的URL,第二个参数是id
        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        BizObject storeOut = MetaDaoHelper.findById(fullname, "2423198867837184");

< T extends BizObject> T findById(String fullname, Object id, int deepth)

根据主键查询多少层

        // 第一个参数fullname即实体的URL,第二个参数是id
        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        // 返回主、子、孙 数据
        BizObject storeOut = MetaDaoHelper.findById(fullname, "2423198867837184", 3);

< T extends Map< String, Object>> List< T> query(String fullname, QuerySchema schema)

QuerySchema查询所有

        // 查询所有
        String selectFields = "id,new1,new2,new3,tenant_id";
        // String selectFields = "*"; // 支持 *，查询所有字段
        QuerySchema querySchema = QuerySchema.create().addSelect(selectFields);
        List<Map<String, Object>> queryLists = MetaDaoHelper.query(fullname, querySchema);
        System.err.println("queryLists = "+queryLists);

QuerySchema根据条件查询所有

        // 查询数据
        // 1.创建QueryCondition，拼接where条件
        QueryConditionGroup queryConditionGroup = QueryConditionGroup.and(
            QueryCondition.name("id").eq("2423198867837184"),
            QueryCondition.name("verifystate").eq("0")
        );
        // 2.创建QuerySchema，用于sql查询返回的字段
        QuerySchema schema = QuerySchema.create().addSelect("id,new1,new2,new3,tenant_id").addCondition(queryConditionGroup);
        // fullname即实体的URL,schema封装的sql
        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        // 3.执行query查询
        List<Map<String,Object>> results = MetaDaoHelper.query(fullname, schema);

Pager queryByPage(String fullname, QuerySchema schema)

分页查询

        String fullname = "GT12477AT26.GT12477AT26.admin_1";
        String selectFields = "id,new1,new2,new3,tenant_id"; // 查询部分字段
        // String selectFields = "*"; // *，查询所有字段
        QuerySchema querySchema = QuerySchema.create().addSelect(selectFields).addPager(1,10);// pageIndex,pageSize
        Pager pager = MetaDaoHelper.queryByPage(fullname, querySchema);
        System.err.println("pager = "+ pager.getRecordList());

更多方法，请查看 源码类：MetaDaoHelper.class

二、SqlHelper方式

源码类：SqlHelper.class
该工具类是mdd对数据库操作组件的进一步封装

1.查询一条

        HashMap<String, String> paramMap = new HashMap<>();
        paramMap.put("tenantId",tenantId);
        paramMap.put("new1","0001");
        // 第一个参数：xml中namespace.方法id；第二个参数：sql条件参数
        Object obj = SqlHelper.selectOne("com.yonyou.ucf.mdf.app.mapper.AdminTestMapper.getObjById", paramMap);
        System.err.println(obj);

2.查询集合

        List<Object> objectsList = SqlHelper.selectList("com.yonyou.ucf.mdf.app.mapper.AdminTestMapper.getListById", tenantId);

示例：

SqlHelper方式，不需要写dao接口层，只需要写xml即可（简化开发）。

上面方法调用的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.yonyou.ucf.mdf.app.mapper.AdminTestMapper">


    <select id="getListById" parameterType="string" resultType="java.util.Map">
        SELECT * from admin_1 where tenant_id = #{tenantId}
    </select>

    <select id="getObjById" parameterType="map" resultType="java.util.Map">
        SELECT * from admin_1 where tenant_id = #{tenantId} and new1 = #{new1}
    </select>


</mapper>

注：该方式不需要创建dao接口层，SqlHelper根据第一个参数就可以和statement的sql进行映射生成最终执行的sql语句，最后由mybatis框架执行sql并将结果映射java对象并返回。