Mybatis常用工具类(二)-- ScriptRunner、SqlRunner

iBit程序猿 2020年06月15日 2,776次浏览

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