SpringBoot整合ibeetlSQL的简单实现（附：踩到的坑）

1.新建maven工程

Next:

 

Finish

2.在pom.xml中添加依赖
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.juwenzhe.beetl</groupId>
    <artifactId>springboot-ibeetl-test</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.0.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.ibeetl</groupId>
            <artifactId>beetl-framework-starter</artifactId>
            <version>1.1.15.RELEASE</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

3.搭建框架

-->

对应的文件：
①DataSourceConfig类
package com.springboot.beetlsql.conf;
import javax.sql.DataSource;
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 com.zaxxer.hikari.HikariDataSource;
@Configuration
public class DataSourceConfig {
    @Bean("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("beetlSqlDataSource")
    public BeetlSqlDataSource beetlSqlDataSource(@Qualifier("dataSource") DataSource dataSource) {
        BeetlSqlDataSource source = new BeetlSqlDataSource();
        source.setMasterSource(dataSource);
        return source;
    }
}
②UserDao接口
package com.springboot.beetlsql.dao;
import org.beetl.sql.core.annotatoin.SqlResource;
import org.beetl.sql.core.mapper.BaseMapper;
import com.springboot.beetlsql.entity.User;
@SqlResource("ibeetlsql.user ")
public interface UserDao extends BaseMapper<User>{
    User selectUserById(@Param("number") Integer pk); // 更新，之前是User selectUserById(Integer pk);
}

---------2018.11.12--update----------

对应的user.md文件：

---------2018.11.12--update----------

UserService类
package com.springboot.beetlsql.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.springboot.beetlsql.dao.UserDao;
import com.springboot.beetlsql.entity.User;
@Service("userService")
public class UserService {
    @Autowired
    UserDao userDao;
    public User selectUserById(int i){
        return userDao.selectUserById(i);
    }
}
④UserController类
package com.springboot.beetlsql.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.springboot.beetlsql.entity.User;
import com.springboot.beetlsql.service.UserService;
@Controller
public class UserController {
    @Autowired UserService userService;
    @RequestMapping("/selectUser")
    @ResponseBody
    public User selectUser(){
        return userService.selectUserById(1);
    }
}
⑤User类
package com.springboot.beetlsql.entity
import java.util.Date;
public class User {
    private Integer id;
    private String name;
    private Integer departmentId;
    private Date createTime;
    \\省略setter、getter
}
⑥application.properties文件
spring.datasource.url=jdbc:mysql://localhost:33306/orm?useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
beetlsql.basePackage=com
beetl-beetlsql.dev=true
spring.devtools.restart.enabled=true

mysql建表语句、插入值语句：
CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL COMMENT '名称',
  `department_id` int(10) unsigned NOT NULL,
  `create_time` date NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO `user` (`id`,`name`,`department_id`,`create_time`) VALUES  (1,'juwenzhe',101,'2018-11-08');
commit;

--------2018.11.12 新增SpringBoot整合ibeetlSQL的分页查询简单实现---------

最后，启动项目：

访问：http://localhost:8080/selectUser

返回：{"id":1,"name":"juwenzhe","departmentId":101,"createTime":"2018-11-07T16:00:00.000+0000"}

参考文献：
1. SpingBoot与BeetlSQL结合    https://blog.csdn.net/Ancony_/article/details/81091164
2. springboot整合 beatlsql的实例代码    https://www.jb51.net/article/112900.htm

附：遇到的坑
1.参考文献1中，spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
报错：找不到数据源驱动，具体错误如下：
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'beetlSqlScannerConfigurer' defined in class path resource [com/ibeetl/starter/BeetlSqlConfig.class]: Unsatisfied dependency expressed through method 'getBeetlSqlScannerConfigurer' parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'sqlManagerFactoryBean' defined in class path resource [com/ibeetl/starter/BeetlSqlConfig.class]: Unsatisfied dependency expressed through method 'getSqlManagerFactoryBean' parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'beetlSqlDataSource' defined in class path resource [com/ibeetl/starter/BeetlSqlConfig.class]: Unsatisfied dependency expressed through method 'beetlSqlDataSource' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [com/juwenzhe/springboot/conf/DataSourceConfig.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'datasource' threw exception; nested exception is java.lang.RuntimeException: Failed to load driver class com.mysql.cj.jdbc.Driver in either of HikariConfig class loader or Thread context classloader

改成前面文档写的：spring.datasource.driver-class-name=com.mysql.jdbc.Driver

2.报错如下：
Parameter 0 of method beetlSqlDataSource in com.ibeetl.starter.BeetlSqlConfig required a bean of type 'javax.sql.DataSource' that could not be found.
    - Bean method 'dataSource' not loaded because @ConditionalOnProperty (spring.datasource.type) did not find property 'spring.datasource.type'
    - Bean method 'dataSource' not loaded because @ConditionalOnProperty (spring.datasource.jndi-name) did not find property 'jndi-name'
    - Bean method 'dataSource' not loaded because @ConditionalOnClass did not find required class 'javax.transaction.TransactionManager'

错误原因：缺少@Bean("beetlSqlDataSource")配置，具体配置参考改正后的DataSourceConfig类

3.报错如下：

错误原因：service不是SpringBoot中Tomcat容器的类，更不是spring管理的类，所以没有实例化，也就是null。不能在这个Main方法里写测试，正确的测试还是访问：http://localhost:8080/selectUser