Skip to main content

数据库-基础应用

配置#

  1. 通过YAML配置文件
# configuration for dev environment
---
readyWork:
server:
# This is the default binding address.
ip: 0.0.0.0
# Http port if enableHttp is true.
httpPort: 8080
database:
sqlDebug: false # SQL调试，输出SQL到日志
dataSource: # 所有的数据源需要配置在dataSource下面
main: # 主数据源固定名称为"main"，其他数据源名称自定义
type: mysql # 类型支持H2、Mysql、Postgre、Oracle、SqlServer、Sqlite、Ignite，当前版本强烈推荐Mysql，其他类型尚未进行严格测试。
#driverClass: com.mysql.cj.jdbc.Driver #since mysql jdbc 8, The driver is automatically registered via the SPI
jdbcUrl: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&useSSL=false&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
username: root
password: 12345678
#autoMapping: true # 自动映射，默认开启
#table: demo # 单数据源或者程序手工映射时不需要此设定，只有多数据源时，自动映射模型需要明确模型表和数据源的关系，自动映射时，多数据源的表名不能重复，后续会改进该问题
#ignoreTable: xxx # 忽略某些表，如果有某些表是不需要该项目访问
#sqlTemplatePath: /sql/ # SQL模版文件路径，相对于项目工作空间路径，如果设置了此项，下面的sqlTemplate就相对于此路径
sqlTemplate: sql/demo.sql # 需要加载的SQL模版文件，如果上面sqlTemplatePath没有指明路径，这里需要带上相对于项目工作空间的路径
  1. 通过程序配置

基本上Boostrap中的配置项都可以通过globalConfig进行程序化配置，数据库配置也不例外。

public class Main extends Application {
@Override
protected void globalConfig(ApplicationConfig config) {
// 配置文件中的配置项，都可以通过程序化配置完成，上面通过YAMl配置了main数据源并加载了demo.sql模版，这里通过程序配置了test数据源并加载test.sql模版
config.getDatabase().setDataSource("test", new DataSourceConfig()
//.setType(DataSourceConfig.TYPE_MYSQL) // mysql是默认项，这里可以不用指定
.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&useSSL=false&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull")
.setUsername("root").setPassword("12345678").setSqlTemplate("sql/test.sql")
);
}
@Override
protected void initialize() {
// 如果配置中关闭了autoMapping，那么通过这里进行数据模型映射。
// dbManager().getDatasourceAgent("main").addMapping("demo", Demo.class);
}
public static void main(String[] args) {
Ready.For(Main.class).Work(args);
}
}

演示数据#

请先建立一个test的数据库，然后导入下面的demo表结构和数据。或者直接导入demo.sql，文件在examples\quickstart示例包中的resources文件夹。

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for demo
-- ----------------------------
DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`gender` int DEFAULT '1',
`age` int DEFAULT NULL,
`height` int DEFAULT NULL,
`weight` int DEFAULT NULL,
`hobbies` text,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
`status` int DEFAULT NULL,
`isDeleted` bit(1) DEFAULT NULL,
`version` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- ----------------------------
-- Records of demo
-- ----------------------------
BEGIN;
INSERT INTO `demo` VALUES (1, 'Jimmy', 1, 18, 170, 65, 'Study', '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (2, 'name2', 0, 18, 170, 68, 'PC Games', '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (3, 'name3', 1, 20, 168, 65, NULL, '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (4, 'name4', 0, 20, 172, 70, NULL, '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (5, 'name5', 1, 22, 170, 72, NULL, '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (6, 'name6', 0, 23, 165, 60, NULL, '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (7, 'name7', 1, 25, 175, 80, NULL, '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (8, 'name8', 0, 25, 172, 75, NULL, '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (9, 'name9', 1, 26, 160, 60, NULL, '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (10, 'name10', 0, 28, 165, 68, NULL, '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (11, 'name11', 1, 28, 168, 65, NULL, '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (12, 'name12', 1, 28, 172, 72, NULL, '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (13, 'name13', 0, 30, 175, 75, NULL, '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (14, 'name14', 1, 30, 168, 80, 'Sports', '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (15, 'name15', 1, 32, 165, 60, 'Study', '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (16, 'name16', 0, 33, 178, 75, NULL, '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (17, 'name17', 1, 33, 180, 80, NULL, '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (18, 'name18', 1, 20, 175, 72, NULL, '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (19, 'name19', 0, 36, 168, 60, NULL, '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
INSERT INTO `demo` VALUES (20, 'name20', 1, 36, 172, 65, 'Chess', '2021-08-08 08:08:08', '2021-08-08 08:08:08', 1, b'0', 0);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;

基本使用#

不使用任何高级特性，直接操作数据库表。可以直接获取DB实例进行各类基础SQL操作。以下是一组对数据库进行基本增删改查的操作实例：

public class BasicDemoService {
// 默认数据源是当前数据源，通常是主数据源main
private static Db db = Ready.dbManager().getDb();
// 返回结果是Record类型，基础数据库查询的结果集都将以Record类型存在，它是一个基于Map的容器。
public Record getByName(String name) {
var sql = "select * from demo where name = ? limit 1";
return db.findFirst(sql, name);
}
// 多数据源可以随时切换，db.use("test")切换到test数据源
public Record getByNameFromTest(String name) {
var sql = "select * from demo where name = ? limit 1";
return db.use("test").findFirst(sql, name);
}
// 返回多行数据时，以List<Record>形式返回
public List<Record> getAllByAge(int age) {
var sql = "select * from demo where age > ?";
return db.find(sql, age);
}
// 翻页查询时，以Page<Record>形式返回，注意SQL语句分拆
public Page<Record> getPageByAge(int page, int size, int age) {
return db.paginate(page, size, "select *", "from demo where age > ?", age);
}
// 翻页查询支持GROUP BY，注意SQL语句分拆，MYSQL服务器需要关闭ONLY_FULL_GROUP_BY
public Page<Record> getPageByAgeWithGroupBy(int page, int size, int age) {
return db.paginate(page, size, true, "select *", "from demo where age > ? group by age", age);
}
// 翻页查询，完全自定义SQL查询，上面的查询会自动进行select count(*) ...查询，这里可以完全按需自定义
public Page<Record> getPageByFullSql(int page, int size, int age) {
String from = "from demo where age > ?";
String totalRowSql = "select count(*) " + from;
String findSql = "select * " + from + " order by age";
return db.paginateByFullSql(page, size, totalRowSql, findSql, age);
}
// 添加一行数据，纯SQL操作
public int insertBySql(){
return db.update("insert into demo (name, gender, age, height, weight) values(?,?,?,?,?)", "test", 1, 20, 170, 65);
}
// 添加一行数据，通过record对象操作
public boolean insertByRecord(){
return db.save("demo",
db.record(Map.of("name","test", "gender", 1, "age", 20, "height", 170, "weight", 65)));
}
// 修改数据，纯SQL操作
public int updateAgeBySql(String name, int age){
return db.update("update demo set age = ? where name = ?", age, name);
}
// 修改数据，先查询出record记录列，再通过record对象进行更新，这种操作模式表的主键列名必须统一为"id"，后续会改进
public int updateAgeByRecord(String name, int age){
var list = db.find("select * from demo where name = ?", name);
int count = 0;
for(Record record : list) {
if(db.update("demo", record.set("age", age))){
count ++;
}
}
return count;
}
// 修改一行数据，通过主键结合record对象操作，，这种操作模式表的主键列名必须统一为"id"，后续会改进
public boolean updateAgeByRecordId(int id, int age){
return db.update("demo", db.record().set("id", id).set("age", age));
}
// 删除数据，纯SQL操作
public int deleteBySql(){
return db.delete("delete from demo where name = ?", "test");
}
// 删除数据，先查询出record记录列，再通过record对象进行删除，这种操作模式表的主键列名必须统一为"id"，后续会改进
public int deleteByRecord() {
var list = db.find("select * from demo where name = ?", "test");
int count = 0;
for(Record record : list) {
if(db.delete("demo", record)){
count ++;
}
}
return count;
}
// 删除一行数据，通过主键结合record对象操作，这种操作模式表的主键列名必须统一为"id"，后续会改进
public boolean deleteByRecordId(int id){
return db.delete("demo", db.record().set("id", id));
}
}

例子中默认使用当前数据源，通常是主数据源main，如果有多个数据源或者需要切换数据源，可以通过db.use("数据源名称")动态切换。

@RequestMapping(value = "/basic/")
public class BasicController extends Controller {
@Autowired
private BasicDemoService demoService;
@RequestMapping
public Result<String> index() {
return Success.of("hello world !");
}
@RequestMapping
public Result<Record> getByName() {
String name = Assert.notEmpty(getParam("name"), "name is required");
return Success.of(demoService.getByName(name));
}
@RequestMapping
public Result<Record> getByNameFromTest() {
String name = Assert.notEmpty(getParam("name"), "name is required");
return Success.of(demoService.getByNameFromTest(name));
}
@RequestMapping
public Result<List<Record>> getAllByAge() {
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.getAllByAge(age));
}
@RequestMapping
public Result<Page<Record>> getPageByAge() {
int page = getParamToInt("page", 1);
int size = getParamToInt("size",5);
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.getPageByAge(page, size, age));
}
@RequestMapping
public Result<Page<Record>> getPageByAgeWithGroupBy() {
int page = getParamToInt("page", 1);
int size = getParamToInt("size",5);
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.getPageByAgeWithGroupBy(page, size, age));
}
@RequestMapping
public Result<Page<Record>> getPageByFullSql() {
int page = getParamToInt("page", 1);
int size = getParamToInt("size",5);
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.getPageByFullSql(page, size, age));
}
@RequestMapping(method = RequestMethod.POST)
public Result<Integer> insertBySql() {
return Success.of(demoService.insertBySql());
}
@RequestMapping(method = RequestMethod.POST)
public Result<Boolean> insertByRecord() {
return Success.of(demoService.insertByRecord());
}
@RequestMapping(method = RequestMethod.PUT)
public Result<Integer> updateAgeBySql() {
String name = Assert.notEmpty(getParam("name"), "name is required");
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.updateAgeBySql(name, age));
}
@RequestMapping(method = RequestMethod.PUT)
public Result<Integer> updateAgeByRecord() {
String name = Assert.notEmpty(getParam("name"), "name is required");
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.updateAgeByRecord(name, age));
}
@RequestMapping(method = RequestMethod.PUT)
public Result<Boolean> updateAgeByRecordId() {
int id = Assert.notNull(getParamToInt("id"), "id is required");
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.updateAgeByRecordId(id, age));
}
@RequestMapping(method = RequestMethod.DELETE)
public Result<Integer> deleteBySql() {
return Success.of(demoService.deleteBySql());
}
@RequestMapping(method = RequestMethod.DELETE)
public Result<Integer> deleteByRecord() {
return Success.of(demoService.deleteByRecord());
}
@RequestMapping(method = RequestMethod.DELETE)
public Result<Boolean> deleteByRecordId() {
int id = Assert.notNull(getParamToInt("id"), "id is required");
return Success.of(demoService.deleteByRecordId(id));
}
}

创建一个Controller来使用上面的BasicDemoService，通过访问http://127.0.0.1:8080/basic/...即可查看结果。

代码生成器#

数据库表结构创建好后，可以通过代码生成器自动生成表模型，方便进行快速业务开发，请看下面的例子：

public class CodeGenerator {
// 这里一共实现了3各版本的代码生成方法，由简单到定制化的进行了演示
public static void main(String[] args) {
customizedWithPath(); // 根据需要选择生成方案
}
// 最简单的生成器，定义好模型和服务类的包名，自动生成代码到当前项目中
public static void easy() {
String modelPackage = "work.ready.test.model";
String servicePackage = "work.ready.test.service";
// 下面默认使用bootstrap.yml中的main数据库配置，针对main数据源下的所以表
var generator = new Generator(DatabaseManager.MAIN_CONFIG_NAME, modelPackage, servicePackage);
generator.generate();
}
// 定制生成器，定义好模型和服务类的包名，自动生成代码到当前项目中
public static void customized() {
String modelPackage = "work.ready.test.model";
String baseModelPackage = modelPackage + ".base";
String servicePackage = "work.ready.test.service";
// 下面默认使用bootstrap.yml中的main数据库配置，针对main数据源下的所以表
var generator = new Generator(Generator.getMainDatasource(), Generator.getMainMetaBuilder(),
new BaseModelGenerator(baseModelPackage),
new ModelGenerator(modelPackage, baseModelPackage),
new ServiceInterfaceGenerator(servicePackage, modelPackage), //如果不需要生成service，可以为null
new ServiceImplGenerator(servicePackage, modelPackage) //如果不需要生成service，可以为null
);
generator.generate();
}
// 定制生成器，定义好模型和服务类的包名，自动生成代码到指定路径
public static void customizedWithPath() {
String modelPackage = "work.ready.test.model";
String baseModelPackage = modelPackage + ".base";
String servicePackage = "work.ready.test.service";
// 定义各类型代码的保存路径
String path = PathUtil.getProjectRootPath() + "/src/test/java/";
String baseModelPath = path + baseModelPackage.replace(".", "/");
String modelPath = path + modelPackage.replace(".", "/");
String servicePath = path + servicePackage.replace(".", "/");
String serviceImplPath = path + (servicePackage + ".impl").replace(".", "/");
// 下面默认使用bootstrap.yml中的main数据库配置，针对main数据源下的所以表
String ds = "main";
var generator = new Generator(Generator.getDatasource(ds), Generator.getMetaBuilder(ds),
new BaseModelGenerator(baseModelPackage, baseModelPath),
new ModelGenerator(modelPackage, baseModelPackage, modelPath),
new ServiceInterfaceGenerator(servicePackage, modelPackage, servicePath), //如果不需要生成service，可以为null
new ServiceImplGenerator(servicePackage, modelPackage, serviceImplPath) //如果不需要生成service，可以为null
);
generator.setGenerateDataDictionary(true);
generator.setGenerateRemarks(true);
generator.generate();
}
}

模型应用#

使用模型操作比直接数据库访问更加便捷，能通过操作对象属性方式对数据字段进行操作，且拥有更多扩展功能。应用模型需要先建立表的模型并进行表关系映射，建立表模型通常由代码生成器完成，生成器会为每张表生成2个Java类文件。如下所示：

BaseDemo.java
/**
* Generated by Ready.Work
*/
@SuppressWarnings("serial")
public abstract class BaseDemo<M extends BaseDemo<M>> extends Model<M> implements Bean {
public M setId(Integer id) {
set("id", id);
return (M)this;
}
public Integer getId() {
return getInt("id");
}
public M setName(String name) {
set("name", name);
return (M)this;
}
public String getName() {
return getStr("name");
}
public M setGender(Integer gender) {
set("gender", gender);
return (M)this;
}
public Integer getGender() {
return getInt("gender");
}
public M setAge(Integer age) {
set("age", age);
return (M)this;
}
public Integer getAge() {
return getInt("age");
}
public M setHeight(Integer height) {
set("height", height);
return (M)this;
}
public Integer getHeight() {
return getInt("height");
}
public M setWeight(Integer weight) {
set("weight", weight);
return (M)this;
}
public Integer getWeight() {
return getInt("weight");
}
public M setHobbies(String hobbies) {
set("hobbies", hobbies);
return (M)this;
}
public String getHobbies() {
return getStr("hobbies");
}
public M setCreated(java.util.Date created) {
set("created", created);
return (M)this;
}
public java.util.Date getCreated() {
return get("created");
}
public M setModified(java.util.Date modified) {
set("modified", modified);
return (M)this;
}
public java.util.Date getModified() {
return get("modified");
}
public M setStatus(Integer status) {
set("status", status);
return (M)this;
}
public Integer getStatus() {
return getInt("status");
}
public M setIsDeleted(Boolean isDeleted) {
set("isDeleted", isDeleted);
return (M)this;
}
public Boolean getIsDeleted() {
return get("isDeleted");
}
public M setVersion(Integer version) {
set("version", version);
return (M)this;
}
public Integer getVersion() {
return getInt("version");
}
}
```
上面这个BaseDemo类文件是对应表的模型基础类，不要对它做任何修改或增强，如果数据库结构有变更，重新生成覆盖该文件即可。
```java title="Demo.java"
/**
* Generated by Ready.Work
*/
@Table(tableName = "demo", primaryKey = "id")
public class Demo extends BaseDemo<Demo> {
}
```
这个Demo类文件是方便对模型进行扩展的类，如果有需要对模型进行扩展或增强，请加在这个类中。
推荐对Model进行Model级别Service层封装，方便扩展也是应对后续表结构发生变化时可以在Model的Service层进行协调或兼容。
本框架中Model的Service可以自己实现，推荐直接继承框架提供的ModelService，后续框架增强Model层相关功能时能自动增强。见下面实例：
```java title="ModelDemoService.java"
public interface ModelDemoService {
Demo getByName(String name);
Demo getByNameFromTest(String name);
List<Demo> getAllByAge(int age);
Page<Demo> getPageByAge(int page, int size, int age);
Page<Demo> getPageByAgeWithGroupBy(int page, int size, int age);
Page<Demo> getPageByFullSql(int page, int size, int age);
Boolean addRecord(String name, int gender, int age);
Boolean updateRecord(String name, int age);
Boolean updateRecord(int id, int age);
Boolean deleteRecord(String name);
Boolean deleteRecord(int id);
}
```
上面为Model的Service层接口类，通常Model的Service只对当前Model进行操作，即只对当前表进行操作。
```java title="ModelDemoServiceImpl.java"
@Service
public class ModelDemoServiceImpl extends ModelService<Demo> implements ModelDemoService {
// 返回结果是一个Demo对象。
@Override
public Demo getByName(String name) {
String sql = "select * from demo where name = ? limit 1";
return dao.findFirst(sql, name);
}
// 多数据源可以随时切换，use("test")切换到test数据源
@Override
public Demo getByNameFromTest(String name) {
String sql = "select * from demo where name = ? limit 1";
return use("test").findFirst(sql, name);
}
// 返回多行数据时，数据以Demo对象封装后以List<Demo>形式返回
@Override
public List<Demo> getAllByAge(int age) {
var sql = "select * from demo where age > ?";
return dao.find(sql, age);
}
// 翻页查询时，以Page<Demo>形式返回，注意SQL语句分拆
@Override
public Page<Demo> getPageByAge(int page, int size, int age) {
return dao.paginate(page, size, "select *", "from demo where age > ?", age);
}
// 翻页查询支持GROUP BY，注意SQL语句分拆，MYSQL服务器需要关闭ONLY_FULL_GROUP_BY
@Override
public Page<Demo> getPageByAgeWithGroupBy(int page, int size, int age) {
return dao.paginate(page, size, true, "select *", "from demo where age > ? group by age", age);
}
// 翻页查询，完全自定义SQL查询，上面的查询会自动进行select count(*) ...查询，这里可以完全按需自定义
@Override
public Page<Demo> getPageByFullSql(int page, int size, int age) {
String from = "from demo where age > ?";
String totalRowSql = "select count(*) " + from;
String findSql = "select * " + from + " order by age";
return dao.paginateByFullSql(page, size, totalRowSql, findSql, age);
}
// 插入一条记录，创建一个Demo对象，设定需要的属性即字段值，然后直接save保存。
@Override
public Boolean addRecord(String name, int gender, int age) {
return new Demo().setName(name).setGender(gender).setAge(age).save();
}
// 按条件修改一条记录，按条件查询出要修改的Demo对象，设定需要修改的属性即字段值，然后直接update更新。
@Override
public Boolean updateRecord(String name, int age) {
return dao.findFirst("select * from demo where name = ?", name).setAge(age).update();
}
// 按ID修改一条记录，按ID查询出要修改的Demo对象，设定需要修改的属性即字段值，然后直接update更新。
@Override
public Boolean updateRecord(int id, int age) {
return findById(id).setAge(age).update();
}
// 按条件删除一条记录，按条件查询出要删除的Demo对象，然后直接delete删除。
@Override
public Boolean deleteRecord(String name) {
return dao.findFirst("select * from demo where name = ?", name).delete();
}
// 按ID删除一条记录，按ID查询出要删除的Demo对象，然后直接delete删除。
@Override
public Boolean deleteRecord(int id) {
return findById(id).delete();
}
}
```
上面是ModelDemoService的实现类，演示了Model方式进行增删改查的使用。和Record方式有几分相似，但这里操作的对象是Demo模型，能以访问属性方式访问字段。
```java
@RequestMapping(value = "/model/")
public class ModelController extends Controller {
@Autowired
private ModelDemoService demoService;
@RequestMapping
public Result<String> index() {
return Success.of("hello world !");
}
@RequestMapping
public Result<Demo> getByName() {
String name = Assert.notEmpty(getParam("name"), "name is required");
return Success.of(demoService.getByName(name));
}
@RequestMapping
public Result<Demo> getByNameFromTest() {
String name = Assert.notEmpty(getParam("name"), "name is required");
return Success.of(demoService.getByNameFromTest(name));
}
@RequestMapping
public Result<List<Demo>> getAllByAge() {
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.getAllByAge(age));
}
@RequestMapping
public Result<Page<Demo>> getPageByAge() {
int page = getParamToInt("page", 1);
int size = getParamToInt("size",5);
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.getPageByAge(page, size, age));
}
@RequestMapping
public Result<Page<Demo>> getPageByAgeWithGroupBy() {
int page = getParamToInt("page", 1);
int size = getParamToInt("size",5);
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.getPageByAgeWithGroupBy(page, size, age));
}
@RequestMapping
public Result<Page<Demo>> getPageByFullSql() {
int page = getParamToInt("page", 1);
int size = getParamToInt("size",5);
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.getPageByFullSql(page, size, age));
}
@RequestMapping(method = RequestMethod.POST)
public Result<Boolean> addRecord() {
String name = Assert.notNull(getParam("name"), "name is required");
int gender = Assert.notNull(getParamToInt("gender"), "gender is required");
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.addRecord(name, gender, age));
}
@RequestMapping(method = RequestMethod.PUT)
public Result<Boolean> updateRecord() {
String name = Assert.notEmpty(getParam("name"), "name is required");
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.updateRecord(name, age));
}
@RequestMapping(method = RequestMethod.PUT)
public Result<Boolean> updateRecordById() {
int id = Assert.notNull(getParamToInt("id"), "id is required");
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.updateRecord(id, age));
}
@RequestMapping(method = RequestMethod.DELETE)
public Result<Boolean> deleteRecord() {
String name = Assert.notEmpty(getParam("name"), "name is required");
return Success.of(demoService.deleteRecord(name));
}
@RequestMapping(method = RequestMethod.DELETE)
public Result<Boolean> deleteRecordById() {
int id = Assert.notNull(getParamToInt("id"), "id is required");
return Success.of(demoService.deleteRecord(id));
}
}
```
创建一个Controller来使用上面的ModelDemoService，通过访问`http://127.0.0.1:8080/model/...`即可查看结果。
## SQL模版
本框架支持使用SQL模版对SQL进行集中统一管理。在使用SQL模版前，需要先通过sqlTemplatePath和sqlTemplate配置好SQL模版存放路径和需要加载的SQL模版文件。
也可以通过程序按需加载：
```java
public class Main extends Application {
@Override
protected void initialize() {
// 为test数据源加载sql模版
Ready.dbManager().getDatasourceAgent("test").addSqlTemplate("sql/test.sql");
// 设置test数据源为开发调试模式，此时SQL模版文件自定进行热加载，即修改SQL后不用重启应用即可立即生效。
Ready.dbManager().getDatasourceAgent("test").setDevMode(true);
}
public static void main(String[] args) {
Ready.For(Main.class).Work(args);
}
}
```
下面是demo.sql模版的内容：
```sql
#namespace("demo")
#sql("getByName")
select * from demo where name = ? limit 1
#end
#sql("getByName_1")
select * from demo where name = #param(0) and age > #param(1) limit 1
#end
#sql("getByName_2")
select * from demo where name = #param(name) and age > #param(age) limit 1
#end
#sql("getByNameLike")
select * from demo where name like concat('%', #param(0), '%')
#end
#sql("getAllByPage")
select * from demo
#end
#sql("getAllByDynamicParameter")
select * from demo
#for(x : condition)
#(for.first ? "where": "and") #(x.key) #param(x.value)
#end
#end
#end
```
其中用到了模版指令，#sql("sql标识")...#end为sql语句定义指令；#param(数字)和#param(参数名)为参数指令，分别用于按参数号和参数名获取参数的占位标识；#namespace("名称")...#end指令为 sql 语句指定命名空间，不同的命名空间可以让#sql指令使用相同的key值去定义sql，有利于模块化管理；#for(x:y)...#end是循环指令，用于遍历变量y；更多模版指令见Jfianl模版引擎文档`https://jfinal.com/doc/6-4`。
下面我们实现一个服务类来使用上面的模版：
```java
@Service
public class TemplateDemoService {
private static Db db = Ready.dbManager().getDb();
public Record getByName(String name) {
// 获取上面SQL模版中名为getByName的sql语句
var sql = db.getSql("demo.getByName");
return db.findFirst(sql, name); // 传入name，对应于sql语句中的 ? 占位符
}
public Record getByName_1(String name, int age) {
// 获取上面SQL模版中名为getByName_1的sql语句，语句中的#param(0)和#param(1)，分别对应第0个参数name和第1个参数age
var sql = db.getSqlParam("demo.getByName_1", name, age);
return db.findFirst(sql);
// 可以使用下面template方法，更简洁，下面的方法等同上面2行代码
//return db.template("demo.getByName_1", name, age).findFirst();
}
public Record getByName_2(String name, int age) {
// 获取上面SQL模版中名为getByName_2的sql语句，语句中的#param(name)和#param(age)，分别对应参数name和参数age
var sql = db.getSqlParam("demo.getByName_2", Kv.by("name", name).set("age", age));
return db.findFirst(sql);
// 可以使用下面template方法，更简洁，下面的方法等同上面2行代码
//return db.template("demo.getByName_2", Kv.by("name", name).set("age", age)).findFirst();
}
public List<Record> getByNameLike(String name) {
// 获取上面SQL模版中名为getByNameLike的sql语句，演示模糊查询
return db.template("demo.getByNameLike", name).find();
}
public Page<Record> getAllByPage(int page, int size) {
// 获取上面SQL模版中名为getAllByPage的sql语句，演示翻页查询
SqlParam sqlParam = db.getSqlParam("demo.getAllByPage");
return db.paginate(1, 5, sqlParam);
// 可以使用下面template方法，更简洁，下面的方法等同上面2行代码
//return db.template("demo.getAll").paginate(page, size);
}
public Page<Record> getAllByParam(int page, int size, Object... param) {
Assert.equals(0, param.length % 2, "param should be in pairs");
Map<String, Object> map = new HashMap<>();
for(int i = 0; i < param.length; i+=2) {
map.put(param[i].toString(), param[i+1]);
}
// 获取上面SQL模版中名为getAllByDynamicParameter的sql语句，演示动态where多条件语句组装
return db.template("demo.getAllByDynamicParameter", Kv.by("condition", map)).paginate(page, size);
}
public Page<Record> getAllByParamFromTest(int page, int size, Object... param) {
Assert.equals(0, param.length % 2, "param should be in pairs");
Map<String, Object> map = new HashMap<>();
for(int i = 0; i < param.length; i+=2) {
map.put(param[i].toString(), param[i+1]);
}
// test.sql中的内容是从demo.sql中复制了最后一个getAllByDynamicParameter语句，然后由程序配置加载，用于演示
// 下面获取test.sql模版中名为getAllByDynamicParameter的sql语句，演示动态where多条件语句组装
return db.use("test").template("test.getAllByDynamicParameter", Kv.by("condition", map)).paginate(page, size);
}
}
```
下面创建一个Controller来使用上面的TemplateDemoService
```java
@RequestMapping(value = "/template")
public class TemplateController extends Controller {
@Autowired
private TemplateDemoService demoService;
@RequestMapping
public Result<String> index() {
return Success.of("hello world !");
}
@RequestMapping
public Result<Record> getByName() {
String name = Assert.notEmpty(getParam("name"), "name is required");
return Success.of(demoService.getByName(name));
}
@RequestMapping
public Result<Record> getByName_1() {
String name = Assert.notEmpty(getParam("name"), "name is required");
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.getByName_1(name, age));
}
@RequestMapping
public Result<Record> getByName_2() {
String name = Assert.notEmpty(getParam("name"), "name is required");
int age = Assert.notNull(getParamToInt("age"), "age is required");
return Success.of(demoService.getByName_2(name, age));
}
@RequestMapping
public Result<List<Record>> getByNameLike() {
String name = Assert.notEmpty(getParam("name"), "name is required");
return Success.of(demoService.getByNameLike(name));
}
@RequestMapping
public Result<Page<Record>> getAllByPage() {
int page = getParamToInt("page", 1);
int size = getParamToInt("size",5);
return Success.of(demoService.getAllByPage(page,size));
}
@RequestMapping
public Result<Page<Record>> getAllByParam() {
int page = getParamToInt("page", 1);
int size = getParamToInt("size",5);
return Success.of(demoService.getAllByParam(page, size, "age >", 15, "height >", 0));
}
@RequestMapping
public Result<Page<Record>> getAllByParamFromTest() {
int page = getParamToInt("page", 1);
int size = getParamToInt("size",5);
return Success.of(demoService.getAllByParamFromTest(page, size, "age >", 15, "height >", 0));
}
}
```
通过访问`http://127.0.0.1:8080/template/...`即可查看结果。
## H2数据库
框架集成支持H2数据库，一方面是为了方便小微应用可以一包走天下，另一方面是框架后续需要用到H2数据库。默认没有开启，可以通过如下方式开启使用：
```yaml
# configuration for dev environment
---
readyWork:
server:
# This is the default binding address.
ip: 0.0.0.0
# Http port if enableHttp is true.
httpPort: 8080
database:
sqlDebug: false # SQL调试，输出SQL到日志
h2server: # H2数据库设定
enabled: true # 开启H2数据库支持，即启动H2数据库
#tcpPort: 9092 # H2数据库的端口
#tcpAllowOthers: true # 是否允许其他电脑连接到H2数据库
#webPort: 8088 # H2的WEB管理端口
#webAllowOthers: true # 是否允许其他电脑访问H2的WEB管理页面
dataSource: # 所有的数据源需要配置在dataSource下面
main: # 主数据源固定名称为"main"，其他数据源名称自定义
type: mysql # 类型支持H2、Mysql、Postgre、Oracle、SqlServer、Sqlite、Ignite
#driverClass: com.mysql.cj.jdbc.Driver #since mysql jdbc 8, The driver is automatically registered via the SPI
jdbcUrl: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&useSSL=false&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
username: root
password: 12345678
#autoMapping: true # 自动映射，默认开启
#table: demo # 单数据源或者程序手工映射时不需要此设定，只有多数据源时，自动映射模型需要明确模型表和数据源的关系，自动映射时，多数据源的表名不能重复，后续会改进该问题
#ignoreTable: xxx # 忽略某些表，如果有某些表是不需要该项目访问
#sqlTemplatePath: /sql/ # SQL模版文件路径，相对于项目工作空间路径，如果设置了此项，下面的sqlTemplate就相对于此路径
sqlTemplate: sql/demo.sql # 需要加载的SQL模版文件，如果上面sqlTemplatePath没有指明路径，这里需要带上相对于项目工作空间的路径
h2_1: # 如果h2server没有开启TCP端口，本机直接文件方式访问H2数据库
type: h2
driverClass: org.h2.Driver
jdbcUrl: jdbc:h2:/h2data/h2test # 这里的/h2data相对于项目工作空间目录，以h2test.mv.db的文件名存放在该目录下
username: root
password: 123456
h2_2: # 如果h2server开启了TCP端口，本机或其他机器可以通过TCP连接方式访问H2数据库
type: h2
driverClass: org.h2.Driver
jdbcUrl: jdbc:h2:tcp://localhost:9092/h2data/h2prod # 这里的/h2data相对于项目工作空间目录，以h2prod.mv.db的文件名存放在该目录下
username: root
password: 123456
```
Edit this page