5.3 数据库操作

这里我们介绍Blade-JDBC这款数据库支持框架的使用方法，为Blade而生，
不能再简单了！

这是一款Java版的ActiveRecord框架，很大程度上让你快速开发，目前只支持MYSQL语法。

BladeJdbc版本 0.2.0+。

配置

Base.open("jdbc:mysql://localhost:3306/demo", "root", "123456");

当然也可以使用连接池，只要你提供一个DataSource即可:

InputStream in    = new FileInputStream(new File("druid.properties"));
Properties  props = new Properties();
props.load(in);
DataSource dataSource = DruidDataSourceFactory.createDataSource(props);
Base.open(dataSource);

创建一个表结构

CREATE TABLE `t_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `real_name` varchar(20) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Model

@Table(value = "t_user")
@Data
public class User extends ActiveRecord {
    private Integer id;
    private String  username;
    private String  password;
    private Integer age;
    private String  realName;
}

这里使用了lombok插件，帮你自动生成Getter、Setter。

插入

User user = new User();
user.setUsername("jack");
user.setPassword("123556");
user.setRealName("杰克");
user.setAge(20);
// insert into t_user (id, username, password, age, real_name) values (?, ?, ?, ?, ?)
user.save();

更新

根据主键更新

User user = new User();
user.setUsername("jack_up");
// update t_user set username = ? where id = ?
user.where("id", 43).update();

User user = new User();
user.setAge(19);
// update t_user set age = ? where id = ?
user.update(43);

根据条件更新

User user = new User();
user.setAge(32);
// update t_user set age = ? where age < ?
user.where("age", "<", 20).update();

删除

User user = new User();
user.where("id", 44).delete();
// delete from t_user where id = ?
user.delete("id", 44);
user.delete(44);

查询

根据主键查询

User user = new User();
// select * from t_user where id = ?
User u1   = user.find(1);

查询所有

User       user  = new User();
List<User> users = user.findAll();
System.out.println(users);

按条件查询

User       user  = new User();
user.setId(1);
List<User> users = user.findAll();
System.out.println(users);

User       user  = new User();
List<User> users = user.where("id", 2).findAll();
System.out.println(users);

User       user  = new User();
List<User> users = user.where("id", "<", 2).findAll();
System.out.println(users);

排序

User       user  = new User();
List<User> users = user.findAll(OrderBy.desc("id"));

查询记录数

User user = new User();
long count = user.count();
System.out.println(count);

使用SQL查询单条

User user = new User();
System.out.println(user.query("select * from t_user order by id desc"));
System.out.println(user.query("select * from t_user where id = ?", 1));

使用SQL查询列表

User user = new User();
System.out.println(user.queryAll("select * from t_user"));
System.out.println(user.queryAll("select * from t_user where id = ?", 1));

Like Or

User user = new User();
user.where("username", "jack").or("real_name", "jack");
long count = user.count();
System.out.println("count=" + count);
List<User> users = user.like("username", "%jac%").and("age", ">", 18).findAll();
System.out.println(users);

分页查询

User       user = new User();
Page<User> page = user.page(1, 3);
System.out.println(page);

User       user = new User();
Page<User> page = user.page(new PageRow(1, 2));
System.out.println(page);

转换分页结果

User         user         = new User();
Page<User>   page         = user.page(1, 10);
Page<String> userNamePage = page.map(u -> u.getUsername());
System.out.println(userNamePage);

事务

Base.atomic(() -> {
    User user = new User();
    user.setPassword("999");
    user.update(42);
//            int a = 1 / 0;
    System.out.println("aasdasd");
    return true;
});

执行任意SQL

ActiveRecord activeRecord = new ActiveRecord();
activeRecord.execute("update t_user set age = 22 where age < 20");