HasorDB 如何執(zhí)行SQL

作為例子要先準備一張表，并初始化一些數(shù)據(jù)

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? 的數(shù)據(jù)，并返回 ?List/Map? 形式

String querySql = "select * from test_user where age > 40";
List<Map<String, Object>> result = jdbcTemplate.queryForList(querySql);

數(shù)組傳參

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);

執(zhí)行結(jié)果，是列名為標題

/--------------------------------------------\
| 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? 的數(shù)據(jù)，并返回 ?TestUser? 結(jié)果集

String querySql = "select * from test_user where age > 40";
List<TestUser> result = jdbcTemplate.queryForList(querySql, TestUser.class);

數(shù)組傳參

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);

執(zhí)行結(jié)果，是對象的屬性名為標題

/---------------------------------------------------\
| 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結(jié)果?

查詢 ?age > 40 ?的數(shù)據(jù)，并返回 ?1? 個結(jié)果，結(jié)果為 ?Map? 形式。

警告
需要提示的是，執(zhí)行的 SQL 語句一定要確保只會返回 ?1? 個結(jié)果，否則會面臨 HasorDB 無法確認選擇哪一個結(jié)果的問題。在這個情況下會引發(fā)異常。

String querySql = "select * from test_user where age > 40 order by age limit 1";
Map<String, Object> result = jdbcTemplate.queryForMap(querySql);

數(shù)組傳參

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);

執(zhí)行結(jié)果，Map的 Key 是列名

/--------------------------------------------\
| id | name    | age | create_time           |
|--------------------------------------------|
| 3  | jon wes | 41  | 2021-11-26 12:20:12.0 |
\--------------------------------------------/

查詢并返回一條記錄 DTO結(jié)果?

查詢 ?age > 40? 的數(shù)據(jù)，并返回 ?1? 個結(jié)果，結(jié)果為 ?TestUser? 對象

警告
需要提示的是，執(zhí)行的 SQL 語句一定要確保只會返回 ?1 ?個結(jié)果，否則會面臨 HasorDB 無法確認選擇哪一個結(jié)果的問題。在這個情況下會引發(fā)異常。

String querySql = "select * from test_user where age > 40 order by age limit 1";
TestUser result = jdbcTemplate.queryForMap(querySql, TestUser.class);

數(shù)組傳參

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);

執(zhí)行結(jié)果，是對象的屬性名為標題

/---------------------------------------------------\
| createTime                   | name    | id | age |
|---------------------------------------------------|
| Fri Nov 26 12:12:03 CST 2021 | jon wes | 3  | 41  |
\---------------------------------------------------/

查詢并返回匯總數(shù)據(jù)?

查詢 ?age > 40 ?的數(shù)據(jù)總數(shù)

String querySql = "select count(*) from test_user where age > 40";
int result = jdbcTemplate.queryForInt(querySql);

數(shù)組傳參

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);

執(zhí)行結(jié)果

2

提示

• 如果 int 的數(shù)據(jù)類型無法承載結(jié)果值，可以選擇 ?queryForLong? 系列方法

• 若 long 依然不夠，可以選用 ?queryForObject("sql", BigInteger.class)? 方式。

查詢并返回一列值 List/String?

查詢 ?age > 40? 的數(shù)據(jù)，并返回 ?List/String? 形式

String querySql = "select name from test_user where age > 40";
List<String> result = jdbcTemplate.queryForList(querySql, String.class);

數(shù)組傳參

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);

執(zhí)行結(jié)果，是列名為標題

/---------\
| jon wes |
| mary    |
\---------/

查詢并返回一列值?

查詢? age > 40? 的數(shù)據(jù)，并返回 ?String ?形式

警告
需要提示的是，執(zhí)行的 SQL 語句一定要確保只會返回 ?1? 行 ?1? 列，否則會面臨 HasorDB 無法確認選擇哪一個結(jié)果的問題。在這個情況下會引發(fā)異常。

String querySql = "select name from test_user where age > 40 order by age limit 1";
String result = jdbcTemplate.queryForObject(querySql, String.class);

數(shù)組傳參

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);

執(zhí)行結(jié)果

jon wes

提示
對于? queryForObject(querySql, queryArg, String.class) ?這樣的方法可以用簡化的 ?queryForString ?來替代

執(zhí)行 update 語句?

將 id 為 1 的數(shù)據(jù) name 字段更新為 mala，并返回影響行數(shù)

String querySql = "update test_user set name = 'mala' where id = 1";
int result = jdbcTemplate.executeUpdate(querySql);

數(shù)組傳參

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);

執(zhí)行結(jié)果

1

執(zhí)行 delete 語句?

將 id 為 1 的數(shù)據(jù)刪掉，并返回影響行數(shù)

String querySql = "delete from test_user where id = 1";
int result = jdbcTemplate.executeUpdate(querySql);

數(shù)組傳參

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);

執(zhí)行結(jié)果

1

執(zhí)行 insert 語句?

使用 SQL 的方式新增一條數(shù)據(jù)，并返回影響行數(shù)

String querySql = ""insert into `test_user` values (10, 'david', 26, now())"";
int result = jdbcTemplate.executeUpdate(querySql);

數(shù)組傳參

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);

執(zhí)行結(jié)果

1