BeetSql是一個全功能DAO工具, 同時具有Hibernate 優(yōu)點 & Mybatis優(yōu)點功能秀鞭,適用于承認以SQL為中心趋观,同時又需求工具能自動能生成大量常用的SQL的應用。
beetsql優(yōu)點
- 開發(fā)效率
- 無需注解锋边,自動使用大量內(nèi)置SQL皱坛,輕易完成增刪改查功能,節(jié)省50%的開發(fā)工作量
- 數(shù)據(jù)模型支持Pojo豆巨,也支持Map/List這種快速模型剩辟,也支持混合模型
- SQL 模板基于Beetl實現(xiàn),更容易寫和調(diào)試,以及擴展
- 維護性
- SQL 以更簡潔的方式贩猎,Markdown方式集中管理熊户,同時方便程序開發(fā)和數(shù)據(jù)庫SQL調(diào)試。
- 可以自動將sql文件映射為dao接口類
- 靈活直觀的支持支持一對一吭服,一對多嚷堡,多對多關(guān)系映射而不引入復雜的OR Mapping概念和技 - 術(shù)。
- 具備Interceptor功能艇棕,可以調(diào)試蝌戒,性能診斷SQL,以及擴展其他功能
- 其他
- 內(nèi)置支持主從數(shù)據(jù)庫支持的開源工具
- 支持跨數(shù)據(jù)庫平臺欠肾,開發(fā)者所需工作減少到最小瓶颠,目前跨數(shù)據(jù)庫支持mysql,postgres,oracle,sqlserver,h2,sqllite,DB2.
添加依賴
在上一節(jié)基礎(chǔ)上添加如下依賴:
<!--beetsql-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.ibeetl</groupId>
<artifactId>beetl</artifactId>
<version>2.7.23</version>
</dependency>
<dependency>
<groupId>com.ibeetl</groupId>
<artifactId>beetlsql</artifactId>
<version>2.9.12</version>
</dependency>
或
<!--beetsql結(jié)合springboot-->
<dependency>
<groupId>com.ibeetl</groupId>
<artifactId>beetl-framework-starter</artifactId>
<version>1.1.22.RELEASE</version>
</dependency>
整合階段
由于springboot沒有對 beatlsql的快速啟動裝配,所以需要我自己導入相關(guān)的bean刺桃,包括數(shù)據(jù)源粹淋,包掃描,事物管理器等瑟慈。
在啟動類中添加如下代碼:
@Bean(initMethod = "init", name = "beetlConfig")
public BeetlGroupUtilConfiguration getBeetlGroupUtilConfiguration() {
BeetlGroupUtilConfiguration beetlGroupUtilConfiguration = new BeetlGroupUtilConfiguration();
ResourcePatternResolver patternResolver = ResourcePatternUtils.getResourcePatternResolver(new DefaultResourceLoader());
try {
// WebAppResourceLoader 配置root路徑是關(guān)鍵
WebAppResourceLoader webAppResourceLoader = new WebAppResourceLoader(patternResolver.getResource("classpath:/templates").getFile().getPath());
beetlGroupUtilConfiguration.setResourceLoader(webAppResourceLoader);
} catch (IOException e) {
e.printStackTrace();
}
//讀取配置文件信息
return beetlGroupUtilConfiguration;
}
@Bean(name = "beetlViewResolver")
public BeetlSpringViewResolver getBeetlSpringViewResolver(@Qualifier("beetlConfig") BeetlGroupUtilConfiguration beetlGroupUtilConfiguration) {
BeetlSpringViewResolver beetlSpringViewResolver = new BeetlSpringViewResolver();
beetlSpringViewResolver.setContentType("text/html;charset=UTF-8");
beetlSpringViewResolver.setOrder(0);
beetlSpringViewResolver.setConfig(beetlGroupUtilConfiguration);
return beetlSpringViewResolver;
}
//配置包掃描
@Bean(name = "beetlSqlScannerConfigurer")
public BeetlSqlScannerConfigurer getBeetlSqlScannerConfigurer() {
BeetlSqlScannerConfigurer conf = new BeetlSqlScannerConfigurer();
conf.setBasePackage("com.stellar.springboot.bloglearning.beetsql.dao");
conf.setDaoSuffix("Dao");
conf.setSqlManagerFactoryBeanName("sqlManagerFactoryBean");
return conf;
}
@Bean(name = "sqlManagerFactoryBean")
@Primary
public SqlManagerFactoryBean getSqlManagerFactoryBean(@Qualifier("datasource") DataSource datasource) {
SqlManagerFactoryBean factory = new SqlManagerFactoryBean();
BeetlSqlDataSource source = new BeetlSqlDataSource();
source.setMasterSource(datasource);
factory.setCs(source);
factory.setDbStyle(new MySqlStyle());
factory.setInterceptors(new Interceptor[]{new DebugInterceptor()});
factory.setNc(new UnderlinedNameConversion());//開啟駝峰
factory.setSqlLoader(new ClasspathLoader("/sql"));//sql文件路徑
return factory;
}
//配置數(shù)據(jù)庫
@Bean(name = "datasource")
public DataSource getDataSource() {
return DataSourceBuilder.create().url("jdbc:mysql://127.0.0.1:3306/test").username("root").password("root").build();
}
//開啟事務(wù)
@Bean(name = "txManager")
public DataSourceTransactionManager getDataSourceTransactionManager(@Qualifier("datasource") DataSource datasource) {
DataSourceTransactionManager dsm = new DataSourceTransactionManager();
dsm.setDataSource(datasource);
return dsm;
}
在resouces包下桃移,加META_INF文件夾,文件夾中加入spring-devtools.properties:
restart.include.beetl=/beetl-2.3.2.jar
restart.include.beetlsql=/beetlsql-2.3.1.jar
在templates下加一個index.btl文件葛碧。
舉個restful的栗子
實體類
public class Account {
private int id ;
private String name ;
private double money;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
}
DAO
@SqlResultSetMapping(name="account.md")
public interface BeetSqlAccountDao extends BaseMapper<Account> {
@SqlStatement(params = "name")
Account selectAccountByName(String name);
}
controller
@RestController
@RequestMapping("/beetsqlAccount")
public class BeetSqlAccountController {
@Autowired
BeetSqlAccountDao beetSqlAccountDao;
@RequestMapping(value = "/list",method = RequestMethod.GET)
public List<Account> getAccounts(){
return beetSqlAccountDao.all();
}
@RequestMapping(value = "/{id}",method = RequestMethod.GET)
public Account getAccountById(@PathVariable("id") int id){
return beetSqlAccountDao.unique(id);
}
@RequestMapping(value = "",method = RequestMethod.GET)
public Account getAccountById(@RequestParam("name") String name){
return beetSqlAccountDao.selectAccountByName(name);
}
@RequestMapping(value = "/{id}",method = RequestMethod.PUT)
public String updateAccount(@PathVariable("id")int id , @RequestParam(value = "name",required = true)String name,
@RequestParam(value = "money" ,required = true)double money){
Account account=new Account();
account.setMoney(money);
account.setName(name);
account.setId(id);
int t= beetSqlAccountDao.updateById(account);
if(t==1){
return account.toString();
}else {
return "fail";
}
}
@RequestMapping(value = "",method = RequestMethod.POST)
public String postAccount( @RequestParam(value = "name")String name,
@RequestParam(value = "money" )double money) {
Account account = new Account();
account.setMoney(money);
account.setName(name);
KeyHolder t = beetSqlAccountDao.insertReturnKey(account);
if (t.getInt() > 0) {
return account.toString();
} else {
return "fail";
}
}
}