### 依赖模块
```
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
```
### 封装方法
```
public static Map<String, Object> queryForMap(String sql)
public static Map<String, Object> queryForMap(String sql, Object obj)
public static List<Map<String, Object>> queryForList(String sql)
public static List<Map<String, Object>> queryForList(String sql, Object obj)
public static <T> T queryForMap(String sql, Class<T> cla)
public static <T> T queryForMap(String sql, Object obj, Class<T> cla)
public static <T> List<T> queryForList(String sql, Class<T> cla)
public static <T> List<T> queryForList(String sql, Object obj, Class<T> cla)
public static int update(String sql)
public static int update(String sql, Object obj)
public static int batchUpdate(String sql, List<?> data)
```
### 如何使用：
方式1
```
@Autowired
JdbcTemplatePlus mJdbcTemplatePlus;
```
方式2
```
JdbcTemplatePlus mJdbcTemplatePlus= SpringHelper.getBean(JdbcTemplatePlus.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());
}
```
```
[FastBoot][ INFO][08-31 23:20:22]-->[main: 4558][logSQL(Slf4JLogger.java:60)] | - | took 10ms | | statement | select count(1) as ct from sys_region
[FastBoot][ INFO][08-31 23:20:22]-->[main: 4572][map_SQL(JdbcTemplatePlusTest.java:31)] | - queryForMap(sql) : {"ct":"46462"} : 46462
[FastBoot][ INFO][08-31 23:20:22]-->[main: 4580][logSQL(Slf4JLogger.java:60)] | - | took 6ms | | statement | select count(1) as ct from sys_region
[FastBoot][ 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());
}
```
```
[FastBoot][ INFO][08-31 23:21:33]-->[main: 4462][logSQL(Slf4JLogger.java:60)] | - | took 4ms | | statement | select id, name from sys_region where id = 1
[FastBoot][ INFO][08-31 23:21:33]-->[main: 4475][map_DTO(JdbcTemplatePlusTest.java:42)] | - queryForMap(sql, dto) : {"id":"1","name":"北京市"} : 北京市
[FastBoot][ INFO][08-31 23:21:33]-->[main: 4477][logSQL(Slf4JLogger.java:60)] | - | took 1ms | | statement | select id, name from sys_region where id = 1
[FastBoot][ 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());
}
```
```
[FastBoot][ INFO][08-31 23:22:44]-->[main: 4444][logSQL(Slf4JLogger.java:60)] | - | took 3ms | | statement | select * from sys_region where id = 1
[FastBoot][ 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} : 北京市
[FastBoot][ INFO][08-31 23:22:44]-->[main: 4458][logSQL(Slf4JLogger.java:60)] | - | took 0ms | | statement | select * from sys_region where id = 1
[FastBoot][ 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());
}
```
```
[FastBoot][ INFO][08-31 23:23:22]-->[main: 4513][logSQL(Slf4JLogger.java:60)] | - | took 3ms | | statement | select * from sys_region limit 10
[FastBoot][ INFO][08-31 23:23:22]-->[main: 4520][list_SQL(JdbcTemplatePlusTest.java:62)] | - queryForList(sql) : 10 : 北京市
[FastBoot][ INFO][08-31 23:23:22]-->[main: 4522][logSQL(Slf4JLogger.java:60)] | - | took 0ms | | statement | select * from sys_region limit 10
[FastBoot][ 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());
}
```
```
[FastBoot][ INFO][08-31 23:24:48]-->[main: 4712][logSQL(Slf4JLogger.java:60)] | - | took 33ms | | statement | select id, name from sys_region where parent = -1
[FastBoot][ INFO][08-31 23:24:48]-->[main: 4718][list_DTO(JdbcTemplatePlusTest.java:73)] | - queryForList(sql, dto) : 34 : 北京市
[FastBoot][ INFO][08-31 23:24:48]-->[main: 4730][logSQL(Slf4JLogger.java:60)] | - | took 11ms | | statement | select id, name from sys_region where parent = -1
[FastBoot][ 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());
}
```
```
[FastBoot][ INFO][08-31 23:25:29]-->[main: 4638][logSQL(Slf4JLogger.java:60)] | - | took 29ms | | statement | select * from sys_region where parent = -1
[FastBoot][ INFO][08-31 23:25:29]-->[main: 4648][list_MAP(JdbcTemplatePlusTest.java:84)] | - queryForList(sql, param) : 34 : 北京市
[FastBoot][ INFO][08-31 23:25:29]-->[main: 4670][logSQL(Slf4JLogger.java:60)] | - | took 20ms | | statement | select * from sys_region where parent = -1
[FastBoot][ 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));
}
```
```
[FastBoot][ 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')
[FastBoot][ 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));
}
```
```
[FastBoot][ INFO][08-31 23:30:26]-->[main: 4457][logSQL(Slf4JLogger.java:60)] | - | took 16ms | | statement | update sys_region SET sort = 2 where id = 100000
[FastBoot][ 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));
}
```
```
[FastBoot][ INFO][08-31 23:31:07]-->[main: 4746][logSQL(Slf4JLogger.java:60)] | - | took 12ms | | statement | update sys_region SET sort = 3 where id = 100000
[FastBoot][ 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));
}
```
```
[FastBoot][ INFO][08-31 23:31:30]-->[main: 4444][logSQL(Slf4JLogger.java:60)] | - | took 11ms | | statement | update sys_region SET sort = 1 where id = 100000
[FastBoot][ 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));
}
```
```
[FastBoot][ INFO][08-31 23:31:30]-->[main: 4472][logSQL(Slf4JLogger.java:60)] | - | took 23ms | | statement | delete from sys_region where id = 100000
[FastBoot][ INFO][08-31 23:31:30]-->[main: 4472][update_MAP(JdbcTemplatePlusTest.java:125)] | - delete(sql) row : 1
```
