所有代码在https://gitee.com/xiandafu/beetlsql/tree/master/sql-integration/sql-spring/，如果你不熟悉BeetlSQL3，建议先从《快速开始》阅读

https://gitee.com/xiandafu/springboot3-beetl-beetlsql-example 包含了Spring Boot的例子，如果你项目允许使用Spring Boot，强烈建议你使用Spring Boot 集成

环境准备

项目演示使用maven工程，新建一个Maven工程，直接引用如下库

<dependency>
  <groupId>com.ibeetl</groupId>
  <artifactId>sql-spring</artifactId>
  <version>${version}</version>
</dependency>

然后再引入数据库驱动，本章使用H2数据库作为例子，作为快速开始，避免安装数据库服务器，你也可以使用任意其他数据库

<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <version>1.4.200</version>
</dependency>

引入数据库连接池HikariCP

 <dependency>
      <groupId>com.zaxxer</groupId>
      <artifactId>HikariCP</artifactId>
      <version>3.3.1</version>
</dependency>

最后，需要准备一个sql脚本，以初始化H2数据库。保存如下sql到resources/db/schema.sql

DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

BEGIN;
INSERT INTO `sys_user` VALUES (1, 'lijz', 1, NULL);
INSERT INTO `sys_user` VALUES (2, 'lucy', 1, NULL);
INSERT INTO `sys_user` VALUES (3, 'bear', 2, NULL);
INSERT INTO `sys_user` VALUES (4, 'mike', 1, NULL);
INSERT INTO `sys_user` VALUES (5, 'lisan', 1, NULL);
INSERT INTO `sys_user` VALUES (6, 'xb', 1, NULL);
INSERT INTO `sys_user` VALUES (7, 'duanwu', 2, NULL);
INSERT INTO `sys_user` VALUES (8, 'fenh', 1, NULL);
INSERT INTO `sys_user` VALUES (9, 'lj', 2, NULL);
INSERT INTO `sys_user` VALUES (10, 'gshen', 1, NULL);
INSERT INTO `sys_user` VALUES (11, 'lihui', 1, NULL);
COMMIT;

这个脚本也适合mysql执行

环境搭建

为了方便快速演示，使用Spring单元测试。 在src/test下创建一个java类SimpleTest，如下(所有代码在 https://gitee.com/xiandafu/beetlsql/tree/master/sql-integration/sql-spring/src/test/java/org/beetl/sql/ext/spring/test )

import org.beetl.sql.core.SQLManager;
import org.beetl.sql.ext.DBInitHelper;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath*:spring-simple.xml"})
public class SimpleTest {

  @Autowired
  SQLManager sqlManager;
  
  @Autowired
  UserService userService;

  @Before
  public void init(){
    //初始化内存数据库
    DBInitHelper.executeSqlScript(sqlManager,"db/schema.sql");
  }

  @Test
  public void test(){
    UserInfo info = new UserInfo();
    info.setId(199999l);
    info.setName("hello");
    userService.addUser(info);
  }
}

spring-simple.xml 是spring的配置文件，内容如下

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
    <context:annotation-config/>
    <context:component-scan base-package="org.beetl.sql.ext.spring.test"/>
    <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource"
          destroy-method="close">
        <property name="driverClassName" value="org.h2.Driver"></property>
        <property name="jdbcUrl" value="jdbc:h2:mem:dbtest;DB_CLOSE_ON_EXIT=FALSE"></property>
        <property name="username" value="sa"></property>
        <property name="password" value=""></property>
    </bean>

    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <!--声明以注解的方式配置spring 的事物-->
    <tx:annotation-driven transaction-manager="transactionManager"></tx:annotation-driven>

    <!-- BeetlSQL集成 -->

    <!-- DAO接口所在包名，Spring会自动查找其下的类 -->
    <bean name="beetlSqlScannerConfigurer" class="org.beetl.sql.ext.spring.BeetlSqlScannerConfigurer">
        <!-- 哪些类可以自动注入 -->
        <property name="basePackage" value="org.beetl.sql.ext.spring"/>
        <!-- 通过类后缀 来自动注入Dao -->
        <property name="daoSuffix" value="Mapper"/>
        <property name="sqlManagerFactoryBeanName" value="sqlManagerFactoryBean"/>
    </bean>

    <bean id="sqlManagerFactoryBean" class="org.beetl.sql.ext.spring.SqlManagerFactoryBean">
        <property name="cs" >
            <bean  class="org.beetl.sql.ext.spring.SpringConnectionSource">
                <property name="masterSource" ref="dataSource"></property>
                <!-- 还可以设置从数据库 slaves -->
            </bean>
        </property>
        <property name="dbStyle">
            <bean class="org.beetl.sql.core.db.MySqlStyle"/>
        </property>
        <property name="sqlLoader">
            <bean class="org.beetl.sql.core.loader.MarkdownClasspathLoader">
                <property name="sqlRoot" value="/sql"></property>
            </bean>
        </property>
        <property name="nc">
            <bean class="org.beetl.sql.core.UnderlinedNameConversion"/>
        </property>
        <property name="interceptors">
            <list>
                <bean class="org.beetl.sql.ext.DebugInterceptor"></bean>
            </list>
        </property>
    </bean>
</beans>

Spring配置需要配置如下Bean

• datasource，这里使用H2库
• beetlSqlScannerConfigurer 用于自动扫描java类，找到Mapper接口，自动为其生成代理类，并注册为Spring Bean
• sqlManagerFactoryBean, 定义sqlManager，其中masterSource 关联了我们配置好的datasource

UserInfo对象是一个POJO，关联了sys_user表

@Table(name="sys_user")
@Data
public class UserInfo {
    @AssignID
    private Long id;
    private String name;
}

UserService是一个Spring的Service，简单的调用userMapper.insert

@Service
@Transactional
public class UserService {
    @Autowired
    UserMapper userMapper;
    public void addUser(UserInfo userInfo){
        userMapper.insert(userInfo);
    }
}

UserMapper是一个BeetlSQL3的Mapper,我们使用BaseMapper提供的内置insert方法操作

public interface UserMapper extends BaseMapper<UserInfo> {
}