跳到主要内容

执行SQL

作为例子要先准备一张表,并初始化一些数据

create table `test_user` (
`id` int(11) auto_increment,
`name` varchar(255),
`age` int,
`create_time` datetime,
primary key (`id`)
);

insert into `test_user` values (1, 'mali', 26, now());
insert into `test_user` values (2, 'dative', 32, now());
insert into `test_user` values (3, 'jon wes', 41, now());
insert into `test_user` values (4, 'mary', 66, now());
insert into `test_user` values (5, 'matt', 25, now());

查询并返回 List/Map

查询 age > 40 的数据,并返回 List/Map 形式

String querySql = "select * from test_user where age > 40";
List<Map<String, Object>> result = jdbcTemplate.queryForList(querySql);
数组传参
String querySql = "select * from test_user where age > ?";
Object[] queryArg = new Object[] { 40 };
List<Map<String, Object>> result = jdbcTemplate.queryForList(querySql, queryArg);
Map传参
String querySql = "select * from test_user where age > :age";
Map<String, Object> queryArg = Collections.singletonMap("age", 40);
List<Map<String, Object>> result = jdbcTemplate.queryForList(querySql, queryArg);
执行结果(列名为标题)
/--------------------------------------------\
| id | name | age | create_time |
|--------------------------------------------|
| 3 | jon wes | 41 | 2021-11-26 12:10:21.0 |
| 4 | mary | 66 | 2021-11-26 12:10:21.0 |
\--------------------------------------------/

查询并返回 List/DTO

查询 age > 40 的数据,并返回 TestUser 结果集

String querySql = "select * from test_user where age > 40";
List<TestUser> result = jdbcTemplate.queryForList(querySql, TestUser.class);
数组传参
String querySql = "select * from test_user where age > ?";
Object[] queryArg = new Object[] { 40 };
List<TestUser> result = jdbcTemplate.queryForList(querySql, queryArg, TestUser.class);
Map传参
String querySql = "select * from test_user where age > :age";
Map<String, Object> queryArg = Collections.singletonMap("age", 40);
List<TestUser> result = jdbcTemplate.queryForList(querySql, queryArg, TestUser.class);
执行结果(对象的属性名为标题)
/---------------------------------------------------\
| createTime | name | id | age |
|---------------------------------------------------|
| Fri Nov 26 12:12:03 CST 2021 | jon wes | 3 | 41 |
| Fri Nov 26 12:12:03 CST 2021 | mary | 4 | 66 |
\---------------------------------------------------/

查询并返回一条记录 Map结果

查询 age > 40 的数据,并返回 1 个结果,结果为 Map 形式。

警告

需要提示的是,执行的 SQL 语句一定要确保只会返回 1 个结果,否则会面临 dbVisitor 无法确认选择哪一个结果的问题。在这个情况下会引发异常。

String querySql = "select * from test_user where age > 40 order by age limit 1";
Map<String, Object> result = jdbcTemplate.queryForMap(querySql);
数组传参
String querySql = "select * from test_user where age > ? order by age limit 1";
Object[] queryArg = new Object[] { 40 };
Map<String, Object> result = jdbcTemplate.queryForMap(querySql, queryArg);
Map传参
String querySql = "select * from test_user where age > :age order by age limit 1";
Map<String, Object> queryArg = Collections.singletonMap("age", 40);
Map<String, Object> result = jdbcTemplate.queryForMap(querySql, queryArg);
执行结果(Map的 Key 是列名)
/--------------------------------------------\
| id | name | age | create_time |
|--------------------------------------------|
| 3 | jon wes | 41 | 2021-11-26 12:20:12.0 |
\--------------------------------------------/

查询并返回一条记录 DTO结果

查询 age > 40 的数据,并返回 1 个结果,结果为 TestUser 对象

警告

需要提示的是,执行的 SQL 语句一定要确保只会返回 1 个结果,否则会面临 dbVisitor 无法确认选择哪一个结果的问题。在这个情况下会引发异常。

String querySql = "select * from test_user where age > 40 order by age limit 1";
TestUser result = jdbcTemplate.queryForMap(querySql, TestUser.class);
数组传参
String querySql = "select * from test_user where age > ? order by age limit 1";
Object[] queryArg = new Object[] { 40 };
TestUser result = jdbcTemplate.queryForMap(querySql, queryArg, TestUser.class);
Map传参
String querySql = "select * from test_user where age > :age order by age limit 1";
Map<String, Object> queryArg = Collections.singletonMap("age", 40);
TestUser result = jdbcTemplate.queryForMap(querySql, queryArg, TestUser.class);
执行结果(对象的属性名为标题)
/---------------------------------------------------\
| createTime | name | id | age |
|---------------------------------------------------|
| Fri Nov 26 12:12:03 CST 2021 | jon wes | 3 | 41 |
\---------------------------------------------------/

查询并返回汇总数据

查询 age > 40 的数据总数

String querySql = "select count(*) from test_user where age > 40";
int result = jdbcTemplate.queryForInt(querySql);
数组传参
String querySql = "select count(*) from test_user where age > ?";
Object[] queryArg = new Object[] { 40 };
int result = jdbcTemplate.queryForInt(querySql, queryArg);
Map传参
String querySql = "select count(*) from test_user where age > :age";
Map<String, Object> queryArg = Collections.singletonMap("age", 40);
int result = jdbcTemplate.queryForInt(querySql, queryArg);
执行结果
2
提示
  • 如果 int 的数据类型无法承载结果值,可以选择 queryForLong 系列方法
  • 若 long 依然不够,可以选用 queryForObject("sql", BigInteger.class) 方式。

查询并返回一列值 List/String

查询 age > 40 的数据,并返回 List/String 形式

String querySql = "select name from test_user where age > 40";
List<String> result = jdbcTemplate.queryForList(querySql, String.class);
数组传参
String querySql = "select name from test_user where age > ?";
Object[] queryArg = new Object[] { 40 };
List<String> result = jdbcTemplate.queryForList(querySql, queryArg, String.class);
Map传参
String querySql = "select name from test_user where age > :age";
Map<String, Object> queryArg = Collections.singletonMap("age", 40);
List<String> result = jdbcTemplate.queryForList(querySql, queryArg, String.class);
执行结果,是列名为标题
/---------\
| jon wes |
| mary |
\---------/

查询并返回一列值

查询 age > 40 的数据,并返回 String 形式

警告

需要提示的是,执行的 SQL 语句一定要确保只会返回 11 列,否则会面临 dbVisitor 无法确认选择哪一个结果的问题。在这个情况下会引发异常。

String querySql = "select name from test_user where age > 40 order by age limit 1";
String result = jdbcTemplate.queryForObject(querySql, String.class);
数组传参
String querySql = "select name from test_user where age > ? order by age limit 1";
Object[] queryArg = new Object[] { 40 };
String result = jdbcTemplate.queryForObject(querySql, queryArg, String.class);
Map传参
String querySql = "select name from test_user where age > :age order by age limit 1";
Map<String, Object> queryArg = Collections.singletonMap("age", 40);
String result = jdbcTemplate.queryForObject(querySql, queryArg, String.class);
执行结果
jon wes
提示

对于 queryForObject(querySql, queryArg, String.class) 这样的方法可以用简化的 queryForString 来替代

执行 update 语句

将 id 为 1 的数据 name 字段更新为 mala,并返回影响行数

String querySql = "update test_user set name = 'mala' where id = 1";
int result = jdbcTemplate.executeUpdate(querySql);
数组传参
String querySql = "select count(*) from test_user where age > ?";
Object[] queryArg = new Object[] { 40 };
int result = jdbcTemplate.executeUpdate(querySql, queryArg);
Map传参
String querySql = "select count(*) from test_user where age > :age";
Map<String, Object> queryArg = Collections.singletonMap("age", 40);
int result = jdbcTemplate.executeUpdate(querySql, queryArg);
执行结果
1

执行 delete 语句

将 id 为 1 的数据删掉,并返回影响行数

String querySql = "delete from test_user where id = 1";
int result = jdbcTemplate.executeUpdate(querySql);
数组传参
String querySql = "delete from test_user where id = ?";
Object[] queryArg = new Object[] { 1 };
int result = jdbcTemplate.executeUpdate(querySql, queryArg);
Map传参
String querySql = "delete from test_user where id = :id";
Map<String, Object> queryArg = Collections.singletonMap("id", 1);
int result = jdbcTemplate.executeUpdate(querySql, queryArg);
执行结果
1

执行 insert 语句

使用 SQL 的方式新增一条数据,并返回影响行数

String querySql = ""insert into `test_user` values (10, 'david', 26, now())"";
int result = jdbcTemplate.executeUpdate(querySql);
数组传参
String querySql = "insert into `test_user` values (?,?,?,?)";
Object[] queryArg = new Object[] { 10, "'david'", 26, new Date() };
int result = jdbcTemplate.executeUpdate(querySql, queryArg);
Map传参
String querySql = "insert into `test_user` values (:id , :name , :age , :create )";
Map<String, Object> queryArg = new HashMap<>();
queryArg.put("id", 10);
queryArg.put("name", "david");
queryArg.put("age", 26);
queryArg.put("create", new Date());
int result = jdbcTemplate.executeUpdate(querySql, queryArg);
执行结果
1