Mybatis常用工具类（二）-- ScriptRunner、SqlRunner

原文链接：Mybatis常用工具类（二）-- ScriptRunner 和 SqlRunner

使用 ScriptRunner 执行脚本

ScriptRunner全路径：org.apache.ibatis.jdbc.ScriptRunner

try (Connection connection = DriverManager.getConnection(
        "jdbc:hsqldb:mem:mybatis-test",
        "sa", "");) {

    ScriptRunner scriptRunner = new ScriptRunner(connection);
    scriptRunner.runScript(Resources.getResourceAsReader("init-table.sql"));
} catch (SQLException | IOException e) {
    e.printStackTrace();
}

使用 SqlRunner 操作数据库

SqlRunner全路径：org.apache.ibatis.jdbc. SqlRunner

主要操作数据库的方法如下：

方法	说明
selectOne(String sql, Object... args)	执行 SELECT 语句，可包含占位符，返回一个Map<String, Object>。若查询行数不为1时，则会抛 SQLException 异常。
selectAll(String sql, Ojbect... args)	执行 SELECT 语句，可包含占位符，返回多条记录（List<Map<String, Object>>）。
insert(String sql, Object... args)	执行一条 INSERT 语句，可包占位符。
update(String sql, Object... args)	执行一条 UPDATE 语句，可包占位符。
delete(String sql, Object... args)	执行一条 DELETE 语句，可包占位符。
run(String sql)	执行 SQL 语句，没有占位符。
closeConnection()	关闭Connection对象。

相关demo

selectOne

SqlRunner sqlRunner = new SqlRunner(connection);
String sql = new SQL()
        .SELECT("*")
        .FROM("person")
        .WHERE("person_id = ?")
        .toString();

Map<String, Object> resultMap = sqlRunner.selectOne(sql, 1);
System.out.println(resultMap);

打印结果>>>

{PERSON_NAME=张三, PERSON_ID=1, TITLE=后端, NICK_NAME=小三, COMPANY_ID=1, AGE=28}

selectAll

SqlRunner sqlRunner = new SqlRunner(connection);
String sql = new SQL()
        .SELECT("*")
        .FROM("person")
        .WHERE("company_id = ?")
        .toString();

List<Map<String, Object>> results = sqlRunner.selectAll(sql, 1);
System.out.println(results);

打印结果>>>

[{PERSON_NAME=张三, PERSON_ID=1, TITLE=后端, NICK_NAME=小三, COMPANY_ID=1, AGE=28}, {PERSON_NAME=李四, PERSON_ID=2, TITLE=前端, NICK_NAME=小四, COMPANY_ID=1, AGE=25}, {PERSON_NAME=王五, PERSON_ID=3, TITLE=CTO, NICK_NAME=小五, COMPANY_ID=1, AGE=31}]

delete

SqlRunner sqlRunner = new SqlRunner(connection);
String deleteUserSql = new SQL()
        .DELETE_FROM("person")
        .WHERE("person_id = ?")
        .toString();
sqlRunner.delete(deleteUserSql, 1);

System.out.println("查询\"person_id=1\": " + getPersonById(1));

打印结果>>>

查询"person_id=1": null

update

SqlRunner sqlRunner = new SqlRunner(connection);
String updateUserSql = new SQL()
        .UPDATE("person")
        .SET("nick_name = ?")
        .WHERE("person_id = ?")
        .toString();
sqlRunner.update(updateUserSql, "三哥", 1);
    
System.out.println("查询\"person_id=1\": " + getPersonById(1));

打印结果>>>

查询"person_id=1": {PERSON_NAME=张三, PERSON_ID=1, TITLE=后端, NICK_NAME=三哥, COMPANY_ID=1, AGE=28}

insert

SqlRunner sqlRunner = new SqlRunner(connection);
String insertUserSql = new SQL()
        .INSERT_INTO("person")
        .INTO_COLUMNS("person_name, nick_name, title, age, company_id")
        .INTO_VALUES("?,?,?,?,?")
        .toString();
sqlRunner.setUseGeneratedKeySupport(true);
int result = sqlRunner.insert(insertUserSql, "久九", "小九", "产品助理", 20, 1);
    
System.out.println("插入对象: " + getPersonById(result));

打印结果>>>

{PERSON_NAME=久九, PERSON_ID=7, TITLE=产品助理, NICK_NAME=小九, COMPANY_ID=1, AGE=20}

测试脚本

init-table.sql

drop table person if exists;
create table person (
  person_id int generated by default as identity(start with 1, increment by 1),
  person_name varchar(32),
  nick_name varchar(32),
  title varchar(16),
  age int,
  company_id int,
  primary key(person_id)
);

drop table company if exists;
create table company (
  company_id int generated by default as identity,
  company_name varchar(32),
  city varchar(16),
  primary key(company_id)
);

init-data.sql

insert into company(company_id, company_name, city) values (1, 'IBIT科技', '深圳');
insert into company(company_id, company_name, city) values (2, 'IBIT程序猿联盟', '广州');
insert into company(company_id, company_name, city) values (3, 'IBIT', '成都');

insert into person(person_name, nick_name, title, age, company_id) values ('张三', '小三', '后端', 28, 1);
insert into person(person_name, nick_name, title, age, company_id) values ('李四', '小四', '前端', 25, 1);
insert into person(person_name, nick_name, title, age, company_id) values ('王五', '小五', 'CTO', 31, 1);
insert into person(person_name, nick_name, title, age, company_id) values ('陆六', '小陆', '产品', 23, 2);
insert into person(person_name, nick_name, title, age, company_id) values ('柒七', '小柒', '测试', 20, 2);
insert into person(person_name, nick_name, title, age, company_id) values ('巴八', '小巴', '运维', 26, 3);

公众号

喜欢我的文章，请关注公众号