HasorDB 如何执行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​ 个结果，否则会面临 HasorDB 无法确认选择哪一个结果的问题。在这个情况下会引发异常。

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 ​个结果，否则会面临 HasorDB 无法确认选择哪一个结果的问题。在这个情况下会引发异常。

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 语句一定要确保只会返回 ​1​ 行 ​1​ 列，否则会面临 HasorDB 无法确认选择哪一个结果的问题。在这个情况下会引发异常。

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