springboot 整合 mybatis

启动类

//mybatis会扫描的包
@MapperScan(basePackages = "com.example")
@SpringBootApplication(scanBasePackages = "com.example")
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

}

application.properties

server.port=8081
server.context-path=/springboot
#debug设为true,将在控制台打印当前项目已启用和未启用的自动配置报告
#debug=true
#指定使用的配置文件
spring.profiles.active=dev
#spring.profiles.active=test
spring.datasource.url = jdbc:mysql://127.0.0.1:3306/qinwei?useUnicode=true&characterEncoding=utf-8
#替换默认的数据库连接池
spring.datasource.type= com.alibaba.druid.pool.DruidDataSource
spring.datasource.username = root
spring.datasource.password = 123456
spring.datasource.driverClassName = com.mysql.jdbc.Driver
# 下面为连接池的补充设置，应用到上面所有数据源中
# 初始化时建立物理连接的个数。
spring.datasource.initialSize=5
# 最小连接的个数。
spring.datasource.minIdle=5
# 最大连接的个数。
spring.datasource.maxActive=10

DruidConfiguration.java

@Configuration
@EnableTransactionManagement // 启注解事务管理，等同于xml配置方式的 <tx:annotation-driven />
public class DruidConfiguration implements TransactionManagementConfigurer {

    @Value("${spring.datasource.driverClassName}")
    private String driver;

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.initialSize}")
    private int initialSize;

    @Value("${spring.datasource.minIdle}")
    private int minIdle;

    @Value("${spring.datasource.maxActive}")
    private int maxActive;

    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),
                "/druid/*");
        // 添加初始化参数：initParams
        // 白名单：
        // servletRegistrationBean.addInitParameter("allow","127.0.0.1");
        // IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not
        // permitted to view this page.
        // servletRegistrationBean.addInitParameter("deny","ip");
        // 登录查看信息的账号密码.
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "123456");
        // 是否能够重置数据.
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    /**
     * 配置过滤器
     */
    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        // 过滤地址
        filterRegistrationBean.addUrlPatterns("/*");
        // 不需要参与过滤的地址或者文件
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }

    @Bean
    public DataSource druidDataSource() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(driver);
        druidDataSource.setUrl(url);
        druidDataSource.setUsername(username);
        druidDataSource.setPassword(password);
        druidDataSource.setInitialSize(initialSize);
        druidDataSource.setMinIdle(minIdle);
        druidDataSource.setMaxActive(maxActive);

        try {
            druidDataSource.setFilters("stat,wall");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return druidDataSource;
    }

    @Bean(name = "txManage")
    public PlatformTransactionManager txManager() {
        return new DataSourceTransactionManager(druidDataSource());
    }

    // 实现接口 TransactionManagementConfigurer 方法，其返回值代表在拥有多个事务管理器的情况下默认使用的事务管理器
    @Override
    public PlatformTransactionManager annotationDrivenTransactionManager() {
        return txManager();
    }

}

TestMapper

@Mapper
public interface TestMapper {

    @Select("SELECT * FROM CITY WHERE state = #{state}")
    City findByState(@Param("state") String state);

    @Update("update city set name=#{city.name},state=#{city.state} where id=#{city.id}")
    void upadate(@Param("city") City city);

    @Delete("delete from city where id = #{id}")
    void deleteId(@Param("id") int id);

    @Insert("insert into city(name, state, country) values(#{city.name}, #{city.state}, #{city.country})")
    @Options(useGeneratedKeys = true, keyProperty = "city.id")
    int insert(@Param("city") City city);

    @Select("SELECT * FROM city WHERE id = #{0}")
    City findById(int id);

    /**
     * 级联查询,一对一,返回自定义对象
     */
    @Select("select * from school where id=#{id}")
    @Results(id = "school", value = { @Result(column = "id", property = "id"),
            @Result(column = "name", property = "name"), @Result(column = "state", property = "state"),
            @Result(column = "cityId", property = "cityId"),
            @Result(column = "cityId", property = "city", one = @One(select = "com.example.mybatis.TestMapper.findById")) })
    School findOneToOne(@Param("id") int id);

    @Select(" select * from school where cityId=#{cityId} ")
    School findManyToOne(@Param("cityId") int id);

    @Select(" select * from  city where id=#{id} ")
    @Results(id = "city", value = { @Result(column = "id", property = "id", id = true),
            @Result(column = "name", property = "name"), @Result(column = "state", property = "state"),
            @Result(column = "country", property = "country"),
            @Result(column = "id", property = "schools", many = @Many(select = "com.example.mybatis.TestMapper.findManyToOne")) })
    City findOneToMany(@Param("id") int id);
}

City .java

public class City {
    private Integer id;

    private String name;

    private String state;

    private String country;

    private List<School> schools;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getState() {
        return state;
    }

    public void setState(String state) {
        this.state = state;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

    public List<School> getSchools() {
        return schools;
    }

    public void setSchools(List<School> schools) {
        this.schools = schools;
    }

}

School.java

public class School {
    private Integer id;

    private String name;

    private String state;

    private String cityId;

    private City city;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getState() {
        return state;
    }

    public void setState(String state) {
        this.state = state;
    }

    public String getCityId() {
        return cityId;
    }

    public void setCityId(String cityId) {
        this.cityId = cityId;
    }

    public City getCity() {
        return city;
    }

    public void setCity(City city) {
        this.city = city;
    }

}

@RestController
public class MybatisTest {
    private static final Logger LOGGER = LoggerFactory.getLogger(MybatisTest.class);

    @Autowired
    private TestMapper mapper;

    private Gson gson = new Gson();

    // 在存在多个事务管理器的情况下，如果使用value具体指定
    // 则默认使用方法 annotationDrivenTransactionManager() 返回的事务管理器
    @RequestMapping("mybatis")
    @Transactional(value = "txManage")
    public void mybatisMethod() {
        City c1 = mapper.findById(6);
        LOGGER.info(gson.toJson(c1));

        City city = mapper.findByState("OFF");
        LOGGER.info(gson.toJson(city));

        School school = mapper.findOneToOne(1);
        LOGGER.info(gson.toJson(school));

        city = mapper.findOneToMany(1);
        LOGGER.info(gson.toJson(city));

        City c = new City();
        c.setId(5);
        c.setName("广东");
        c.setState("ON");
        c.setCountry("CN");
        LOGGER.info("insert result： " + mapper.insert(c));
//      throw new RuntimeException("just a test");
    }
}

CREATE TABLE `city` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `state` varchar(32) DEFAULT NULL,
  `country` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;

CREATE TABLE `school` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `state` varchar(32) DEFAULT NULL,
  `cityId` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;