### 依赖模块 ``` <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jdbc</artifactId> </dependency> ``` ### 封装方法 ``` public Map<String, Object> queryForMap(String sql) public Map<String, Object> queryForMap(String sql, Object paramDto) public Map<String, Object> queryForMap(String sql, Map<String, ?> paramMap) public List<Map<String, Object>> queryForList(String sql) public List<Map<String, Object>> queryForList(String sql, Object paramDto) public List<Map<String, Object>> queryForList(String sql, Map<String, ?> paramMap) public <T> T queryForMap(String sql, Class<T> clazz) public <T> T queryForMap(String sql, Object paramDto, Class<T> clazz) public <T> T queryForMap(String sql, Map<String, ?> paramMap, Class<T> clazz) public <T> List<T> queryForList(String sql, Class<T> clazz) public <T> List<T> queryForList(String sql, Object paramDto, Class<T> clazz) public <T> List<T> queryForList(String sql, Map<String, ?> paramMap, Class<T> clazz) public int update(String sql) public int update(String sql, Object paramDto) public int update(String sql, Map<String, ?> paramMap) ``` ### 如何使用: 方式1 ``` @Autowired JdbcPlusTemplate mJdbcPlusTemplate; ``` 方式2 ``` JdbcPlusTemplate mJdbcPlusTemplate = SpringHelper.getBean(JdbcPlusTemplate.class); ``` ### 测试示例 测试Dto ``` @Data public static class TestDto { public Integer id; public String name; public Integer parent; public Integer sort; } @Data public static class CommonDto { public Integer ct; } ``` ### 查询单个 方式1 ``` @Test public void map_SQL() { String sql = "select count(1) as ct from sys_region"; Map<String, Object> map = mJdbcTemplatePlus.queryForMap(sql); log.info("queryForMap(sql) : {} : {}", JSON.toJSONString(map), map.getOrDefault("ct", 0)); CommonDto comm = mJdbcTemplatePlus.queryForMap(sql, CommonDto.class); log.info("queryForMap(sql, clazz) : {} : {}", JSON.toJSONString(comm), comm.getCt()); } ``` ``` [FBOOT][ INFO][08-31 23:20:22]-->[main: 4558][logSQL(Slf4JLogger.java:60)] | - | took 10ms | | statement | select count(1) as ct from sys_region [FBOOT][ INFO][08-31 23:20:22]-->[main: 4572][map_SQL(JdbcTemplatePlusTest.java:31)] | - queryForMap(sql) : {"ct":"46462"} : 46462 [FBOOT][ INFO][08-31 23:20:22]-->[main: 4580][logSQL(Slf4JLogger.java:60)] | - | took 6ms | | statement | select count(1) as ct from sys_region [FBOOT][ INFO][08-31 23:20:22]-->[main: 4598][map_SQL(JdbcTemplatePlusTest.java:33)] | - queryForMap(sql, clazz) : {"ct":46462} : 46462 ``` 方式2 ``` @Test public void map_DTO() { TestDto dto = new TestDto(); dto.setId(1); String sql = "select id, name from sys_region where id = :id"; Map<String, Object> map = mJdbcTemplatePlus.queryForMap(sql, dto); log.info("queryForMap(sql, dto) : {} : {}", JSON.toJSONString(map), map.get("name")); TestDto obj = mJdbcTemplatePlus.queryForMap(sql, dto, TestDto.class); log.info("queryForMap(sql, dto, clazz) : {} : {}", JSON.toJSONString(obj), obj.getName()); } ``` ``` [FBOOT][ INFO][08-31 23:21:33]-->[main: 4462][logSQL(Slf4JLogger.java:60)] | - | took 4ms | | statement | select id, name from sys_region where id = 1 [FBOOT][ INFO][08-31 23:21:33]-->[main: 4475][map_DTO(JdbcTemplatePlusTest.java:42)] | - queryForMap(sql, dto) : {"id":"1","name":"北京市"} : 北京市 [FBOOT][ INFO][08-31 23:21:33]-->[main: 4477][logSQL(Slf4JLogger.java:60)] | - | took 1ms | | statement | select id, name from sys_region where id = 1 [FBOOT][ INFO][08-31 23:21:33]-->[main: 4494][map_DTO(JdbcTemplatePlusTest.java:44)] | - queryForMap(sql, dto, clazz) : {"id":1,"name":"北京市"} : 北京市 ``` 方式3 ``` @Test public void map_MAP() { Map<String, Object> param = Maps.newHashMap(); param.put("id", 1); String sql = "select * from sys_region where id = :id"; Map<String, Object> map = mJdbcTemplatePlus.queryForMap(sql, param); log.info("queryForMap(sql, param) : {} : {}", JSON.toJSONString(map), map.get("name")); TestDto obj = mJdbcTemplatePlus.queryForMap(sql, param, TestDto.class); log.info("queryForMap(sql, param, clazz) : {} : {}", JSON.toJSONString(obj), obj.getName()); } ``` ``` [FBOOT][ INFO][08-31 23:22:44]-->[main: 4444][logSQL(Slf4JLogger.java:60)] | - | took 3ms | | statement | select * from sys_region where id = 1 [FBOOT][ INFO][08-31 23:22:44]-->[main: 4456][map_MAP(JdbcTemplatePlusTest.java:53)] | - queryForMap(sql, param) : {"id":"1","create_date":1567589270000,"update_date":1567589270000,"name":"北京市","name_short":"北京","code":"110000","level":1,"lng":"116.405289","lat":"39.904987","parent":"-1","sort":1} : 北京市 [FBOOT][ INFO][08-31 23:22:44]-->[main: 4458][logSQL(Slf4JLogger.java:60)] | - | took 0ms | | statement | select * from sys_region where id = 1 [FBOOT][ INFO][08-31 23:22:44]-->[main: 4480][map_MAP(JdbcTemplatePlusTest.java:55)] | - queryForMap(sql, param, clazz) : {"id":1,"name":"北京市","parent":-1,"sort":1} : 北京市 ``` ### 查询多个 方式1 ``` @Test public void list_SQL() { String sql = "select * from sys_region limit 10"; List<Map<String, Object>> list_map = mJdbcTemplatePlus.queryForList(sql); log.info("queryForList(sql) : {} : {}", list_map.size(), list_map.get(0).get("name")); List<TestDto> list_obj = mJdbcTemplatePlus.queryForList(sql, TestDto.class); log.info("queryForList(sql, clazz) : {} : {}", list_obj.size(), list_obj.get(0).getName()); } ``` ``` [FBOOT][ INFO][08-31 23:23:22]-->[main: 4513][logSQL(Slf4JLogger.java:60)] | - | took 3ms | | statement | select * from sys_region limit 10 [FBOOT][ INFO][08-31 23:23:22]-->[main: 4520][list_SQL(JdbcTemplatePlusTest.java:62)] | - queryForList(sql) : 10 : 北京市 [FBOOT][ INFO][08-31 23:23:22]-->[main: 4522][logSQL(Slf4JLogger.java:60)] | - | took 0ms | | statement | select * from sys_region limit 10 [FBOOT][ INFO][08-31 23:23:22]-->[main: 4535][list_SQL(JdbcTemplatePlusTest.java:64)] | - queryForList(sql, clazz) : 10 : 北京市 ``` 方式2 ``` @Test public void list_DTO() { TestDto dto = new TestDto(); dto.setParent(-1); String sql = "select id, name from sys_region where parent = :parent"; List<Map<String, Object>> list_map = mJdbcTemplatePlus.queryForList(sql, dto); log.info("queryForList(sql, dto) : {} : {}", list_map.size(), list_map.get(0).get("name")); List<TestDto> list_obj = mJdbcTemplatePlus.queryForList(sql, dto, TestDto.class); log.info("queryForList(sql, dto, clazz) : {} : {}", list_obj.size(), list_obj.get(0).getName()); } ``` ``` [FBOOT][ INFO][08-31 23:24:48]-->[main: 4712][logSQL(Slf4JLogger.java:60)] | - | took 33ms | | statement | select id, name from sys_region where parent = -1 [FBOOT][ INFO][08-31 23:24:48]-->[main: 4718][list_DTO(JdbcTemplatePlusTest.java:73)] | - queryForList(sql, dto) : 34 : 北京市 [FBOOT][ INFO][08-31 23:24:48]-->[main: 4730][logSQL(Slf4JLogger.java:60)] | - | took 11ms | | statement | select id, name from sys_region where parent = -1 [FBOOT][ INFO][08-31 23:24:48]-->[main: 4748][list_DTO(JdbcTemplatePlusTest.java:75)] | - queryForList(sql, dto, clazz) : 34 : 北京市 ``` 方式3 ``` @Test public void list_MAP() { Map<String, Object> param = Maps.newHashMap(); param.put("parent", -1); String sql = "select * from sys_region where parent = :parent"; List<Map<String, Object>> list_map = mJdbcTemplatePlus.queryForList(sql, param); log.info("queryForList(sql, param) : {} : {}", list_map.size(), list_map.get(0).get("name")); List<TestDto> list_obj = mJdbcTemplatePlus.queryForList(sql, param, TestDto.class); log.info("queryForList(sql, param, clazz) : {} : {}", list_obj.size(), list_obj.get(0).getName()); } ``` ``` [FBOOT][ INFO][08-31 23:25:29]-->[main: 4638][logSQL(Slf4JLogger.java:60)] | - | took 29ms | | statement | select * from sys_region where parent = -1 [FBOOT][ INFO][08-31 23:25:29]-->[main: 4648][list_MAP(JdbcTemplatePlusTest.java:84)] | - queryForList(sql, param) : 34 : 北京市 [FBOOT][ INFO][08-31 23:25:29]-->[main: 4670][logSQL(Slf4JLogger.java:60)] | - | took 20ms | | statement | select * from sys_region where parent = -1 [FBOOT][ INFO][08-31 23:25:29]-->[main: 4685][list_MAP(JdbcTemplatePlusTest.java:86)] | - queryForList(sql, param, clazz) : 34 : 北京市 ``` > 注意啦:直接转换到实体类,需注意保证查询字段与Dto字段保持一致,若使用函数的可以通过别名转换。如:`count(1) as ct` 与 `CommonDto中 ct`保持一致 ### 添加 方式3(1、2略) ``` @Test public void insert() { String sql = "INSERT INTO sys_region (`id`,`name`,`name_short`,`code`) VALUES (:id,:name,:name_short,:code)"; Map<String, Object> param = Maps.newHashMap(); param.put("id", 100000); param.put("name", "测试添加"); param.put("name_short", "test"); param.put("code", "12345"); log.info("insert(sql, param) row : {}", mJdbcTemplatePlus.update(sql, param)); } ``` ``` [FBOOT][ INFO][08-31 23:28:10]-->[main: 4480][logSQL(Slf4JLogger.java:60)] | - | took 9ms | | statement | INSERT INTO sys_region (`id`,`name`,`name_short`,`code`) VALUES (100000,'测试添加','test','12345') [FBOOT][ INFO][08-31 23:28:10]-->[main: 4483][insert(JdbcTemplatePlusTest.java:97)] | - insert(sql, param) row : 1 ``` ### 修改 方式1 ``` @Test public void update_SQL() { String sql = "update sys_region SET sort = 2 where id = 100000"; log.info("update(sql) row : {}", mJdbcTemplatePlus.update(sql)); } ``` ``` [FBOOT][ INFO][08-31 23:30:26]-->[main: 4457][logSQL(Slf4JLogger.java:60)] | - | took 16ms | | statement | update sys_region SET sort = 2 where id = 100000 [FBOOT][ INFO][08-31 23:30:26]-->[main: 4460][update_SQL(JdbcTemplatePlusTest.java:103)] | - update(sql) row : 1 ``` 方式2 ``` @Test public void update_DTO() { String sql = "update sys_region SET sort = :sort where id = :id"; TestDto dto = new TestDto(); dto.setSort(3); dto.setId(100000); log.info("update(sql, dto) row : {}", mJdbcTemplatePlus.update(sql, dto)); } ``` ``` [FBOOT][ INFO][08-31 23:31:07]-->[main: 4746][logSQL(Slf4JLogger.java:60)] | - | took 12ms | | statement | update sys_region SET sort = 3 where id = 100000 [FBOOT][ INFO][08-31 23:31:07]-->[main: 4749][update_DTO(JdbcTemplatePlusTest.java:112)] | - update(sql, dto) row : 1 ``` 方式3 ``` @Test public void update_MAP() { String sql = "update sys_region SET sort = :sort where id = :id"; Map<String, Object> param = Maps.newHashMap(); param.put("sort", 1); param.put("id", 100000); log.info("update(sql, param) row : {}", mJdbcTemplatePlus.update(sql, param)); } ``` ``` [FBOOT][ INFO][08-31 23:31:30]-->[main: 4444][logSQL(Slf4JLogger.java:60)] | - | took 11ms | | statement | update sys_region SET sort = 1 where id = 100000 [FBOOT][ INFO][08-31 23:31:30]-->[main: 4448][update_MAP(JdbcTemplatePlusTest.java:121)] | - update(sql, param) row : 1 ``` ### 删除 方式1(2、3略) ``` @Test public void delete() { String sql = "delete from sys_region where id = 100000"; log.info("delete(sql) row : {}", mJdbcTemplatePlus.update(sql)); } ``` ``` [FBOOT][ INFO][08-31 23:31:30]-->[main: 4472][logSQL(Slf4JLogger.java:60)] | - | took 23ms | | statement | delete from sys_region where id = 100000 [FBOOT][ INFO][08-31 23:31:30]-->[main: 4472][update_MAP(JdbcTemplatePlusTest.java:125)] | - delete(sql) row : 1 ```