SpringBoot 整合 BeetlSQL
1. beetlsql介绍
BeetSql是一个全功能DAO工具, 同时具有Hibernate 优点 & Mybatis优点功能,适用于承认以SQL为中心,同时又需求工具能自动能生成大量常用的SQL的应用。
- 开发效率
- 无需注解,自动使用大量内置SQL,轻易完成增删改查功能,节省50%的开发工作量
- 数据模型支持Pojo,也支持Map/List这种快速模型,也支持混合模型
- SQL 模板基于Beetl实现,更容易写和调试,以及扩展
- 可以针对单个表(或者视图)代码生成pojo类和sql模版,甚至是整个数据库。能减少代码编写工作量
- 维护性
- SQL 以更简洁的方式,Markdown方式集中管理,同时方便程序开发和数据库SQL调试。
- 可以自动将sql文件映射为dao接口类
- 灵活直观的支持支持一对一,一对多,多对多关系映射而不引入复杂的OR Mapping概念和技术。
- 具备Interceptor功能,可以调试,性能诊断SQL,以及扩展其他功能
- 其他
- 内置支持主从数据库支持的开源工具
- 性能数倍于JPA,MyBatis
- 支持跨数据库平台,开发者所需工作减少到最小,目前跨数据库支持 mysql,postgres,oracle,sqlserver,h2,sqllite,DB2.
2. maven引入,添加相关的依赖
<dependencies>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.ibeetl</groupId>
<artifactId>beetl-framework-starter</artifactId>
<version>1.1.68.RELEASE</version>
</dependency>
<dependency>
<groupId>com.ibeetl</groupId>
<artifactId>beetlsql</artifactId>
<version>2.10.10</version>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.5</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.1.RELEASE</version>
</dependency>
</dependencies>
###3. 配置resources 下yml文件
#数据源配置
spring:
application:
name: beetlsql-test
datasource:
name: blog
url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false
username: root
password: *****
driver-class-name: com.mysql.cj.jdbc.Driver
beetlsql:
basePackage: com.platform.web.dao
dbStyle: org.beetl.sql.core.db.MySqlStyle
sqlRoot: /sql
debug: 1
4.创建数据源的配置
package com.platform.web.conf;
import com.zaxxer.hikari.HikariDataSource;
import org.beetl.sql.ext.spring4.BeetlSqlDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import javax.sql.DataSource;
@Configuration
public class DataSourceConfig {
@Bean(name = "dataSource")
public DataSource dataSource(Environment env) {
HikariDataSource ds = new HikariDataSource();
ds.setJdbcUrl(env.getProperty("spring.datasource.url"));
ds.setUsername(env.getProperty("spring.datasource.username"));
ds.setPassword(env.getProperty("spring.datasource.password"));
ds.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
return ds;
}
@Bean
public BeetlSqlDataSource beetlSqlDataSource(@Qualifier("dataSource") DataSource dataSource) {
BeetlSqlDataSource source = new BeetlSqlDataSource();
source.setMasterSource(dataSource);
return source;
}
}
####5.创建代码生成,自动生成entity
自动生成文件,自测可生成entity,已经sql文件,但是如果新增字段的话会覆盖掉原来的sql,所以可根据自己的情况执行。
package com.platform.web.entity.init;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;
import org.beetl.sql.ext.gen.GenConfig;
import org.beetl.sql.ext.gen.MapperCodeGen;
import org.springframework.util.ResourceUtils;
import java.io.File;
public class DbMain {
private static final String mysqlDriver="com.mysql.jdbc.Driver";
private static final String url="jdbc:mysql://localhost:3306/regan_erp?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false";
private static final String userName="root";
private static final String password="******";
/**
* 生成代码
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
ConnectionSource source = ConnectionSourceHelper
.getSimple(mysqlDriver, url, userName, password);
DBStyle mysql = new MySqlStyle();
//SQL语句放于classpath的sql目录下
SQLLoader loader = new ClasspathLoader("web/src/main/resources/sql");
//数据库命名和java命名一样,所以采用DefaultNameConversion,还有一个UnderlinedNameConversion下划线风格的
ReDbNameConversion nc = new ReDbNameConversion();
//最后,创建一个SQLManager,DebugInterceptor,不是必须的,但可以通过它查看SQL的执行情况
SQLManager sqlManager = new SQLManager(mysql, loader, source, nc, new Interceptor[]{new DebugInterceptor()});
// 或者直接生成java文件
GenConfig config = new GenConfig();
config.preferBigDecimal(true);
config.setBaseClass("org.beetl.sql.core.TailBean");
MapperCodeGen mapper = new MapperCodeGen("com.platform.web.dao");
config.codeGens.add(mapper);
// sqlManager.genPojoCodeToConsole("t_user", config);
sqlManager.genPojoCode(
"t_menu",
"com.platform.web.entity",
new File(ResourceUtils.getURL("web/src/main/java").getPath()).getPath(),
config);
//
sqlManager.genSQLFile("t_menu");
}
}
构建数据库前缀忽略,已经属性驼峰命名
package com.platform.web.entity.init;
import org.beetl.sql.core.DefaultNameConversion;
import org.beetl.sql.core.NameConversion;
import org.beetl.sql.core.annotatoin.Table;
import org.beetl.sql.core.kit.StringKit;
public class ReDbNameConversion extends DefaultNameConversion {
@Override
public String getTableName(Class<?> c) {
//遵循BeetlSQL规范,@Table拥有最高优先级
Table table = (Table)c.getAnnotation(Table.class);
if(table!=null){
return table.name();
}
//UserRole -> user_role
String tableName = StringKit.enCodeUnderlined(c.getSimpleName());
//user_role -> bt_user_role
return "t_"+tableName;
}
@Override
public String getClassName(String tableName){
//假定所有表都是以bt_开头
//bt_user_role -> user_role
tableName = tableName.substring(tableName.indexOf("_")+1);
//user_role -> userRole
String clsName = StringKit.deCodeUnderlined(tableName);
//userRole -> UserRole
return StringKit.toUpperCaseFirstOne(clsName);
}
@Override
public String getPropertyName(Class<?> c, String attrName) {
return StringKit.deCodeUnderlined(attrName);
}
}
6.创建DAO,已经service
service代码
package com.platform.web.service.impl;
import com.platform.web.dao.UserDao;
import com.platform.web.entity.User;
import com.platform.web.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserServiceImpl implements UserService {
@Autowired
UserDao userDao;
@Override
public User getUserById(Integer id) {
return userDao.unique(id);
}
}
dao代码
package com.platform.web.dao;
import com.platform.web.entity.User;
import org.beetl.sql.core.mapper.BaseMapper;
import java.util.List;
public interface UserDao extends BaseMapper<User> {
}
7.创建controller建立查询
BaseMapper为我们内置了很多crud的方法,所以一些简单的操作我们可以无需自己去写sql就可以方便调用,如果需要自己去写的话可以调用SQLManager去操作
package com.platform.web.controller;
import com.platform.web.entity.User;
import com.platform.web.service.UserService;
import org.beetl.sql.core.SQLManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping(value = "/test")
public class TestController {
@Autowired
UserService userService;
@Autowired
private SQLManager splManager;
@RequestMapping(value = "/sql", method = {RequestMethod.GET, RequestMethod.POST})
public String getUser() {
//通过userService,建立查询
User user = userService.getUserById(1);
//通过splManager,建立查询
List<User> users = splManager.execute("select * from t_user", User.class,null);
return "hello world";
}
}
####8. 参考文件