不灭的焱

革命尚未成功,同志仍须努力下载JDK17

作者:Albert.Wen  添加时间:2018-09-24 21:42:03  修改时间:2024-03-29 19:10:18  分类:Java框架/系统  编辑

正式项目中,使用MyBatis操作数据库比较多,但是需要各种类型的文件配合(xml、entity、mapper)才能对某张表进行增删改的操作。

有时候会有些临时小任务,需要对数据进行批处理。这个时候,如果有个数据表工具类能直接对数据表操作,就非常方便了,Hutool类库中,就提供了这样的工具,官方教程:数据库简单操作-Db

常用的几段代码:

1、配置数据库连接

在项目目录 src/main/resources 或 src/main/resources/config 下,新增数据库配置文件:db.setting

showSql = true
formatSql = true
showParams = true
sqlLevel = debug

[local_db]
url = jdbc:mysql://106.52.67.xxx:53306/albert_cms?useUnicode=true&useSSL=false&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&rewriteBatchedStatements=true&autoReconnect=true&serverTimezone=Asia/Shanghai
username = albert_user_cms
password = G81snZVPXH9ppz6G

[remote_db]
url = jdbc:mysql://xxx.mysql.rds.aliyuncs.com:53306/albert_cms?useUnicode=true&useSSL=false&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&rewriteBatchedStatements=true&autoReconnect=true&serverTimezone=Asia/Shanghai
username = albert_user_cms
password = 56vg&odw!zgb#XOu

2、执行SQL语句

(1)选择数据源

DataSource remoteDS = DSFactory.get("remote_db");

(2) 查询

List<Entity> userList = Db.use(remoteDS).query("select * from user where age < ?", 3);
List<Entity> userList = Db.use(remoteDS).query("select * from user where name like ?", "王%");
Entity article = Db.use(remoteDS).queryOne("select * from article where article_id=" + id);

(3) 新增

Db.use(remoteDS).execute("insert into user values (?, ?, ?)", "张三", 17, 1);

(4) 删除

Db.use(remoteDS).execute("delete from user where name = ?", "张三");

(5) 更新

Db.use(remoteDS).execute("update user set age = ? where name = ?", 3, "张三");

(6) 事务

Db.use(remoteDS).tx(db -> {
    Db.use(remoteDS).execute("insert into user values (?, ?, ?)", "张三", 17, 1);
    Db.use(remoteDS).execute("delete from user where name = ?", "李四");
});