SpringBoot + MyBatis + H2

       在SpringBoot环境下使用Mybatis,数据库使用内嵌数据库H2

环境

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<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>zl</groupId>
	<artifactId>mybatis</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>mybatis</name>
	<description>Demo project for Spring Boot</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.9.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.1</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
<!-- 		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency> -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

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


</project>

properties

server.port=8080

#************H2  Begin****************
#db schema
spring.datasource.schema=classpath:db/schema.sql
#db data
spring.datasource.data=classpath:db/data.sql

#remote visit
spring.h2.console.settings.web-allow-others=true
#console url
spring.h2.console.path=/h2-console
#default true
spring.h2.console.enabled=true
spring.h2.console.settings.trace=true

#db url,default :jdbc:h2:mem:testdbsa
spring.datasource.url=jdbc:h2:mem:testdbsa
#driver default:org.h2.Driver
spring.datasource.driver-class-name=org.h2.Driver
#default sa
spring.datasource.username=sa
#default null
spring.datasource.password=
#************H2  End****************

#************Mybatis  Begin****************
mybatis.config-location=classpath:mybatis-config.xml
#************Mybatis  End****************

#************Log  Begin****************
logging.level.root=WARN
#mapper log
logging.level.mybatis.mapper=TRACE
#view initialize message
logging.level.org.hibernate=DEBUG
#************Log  Begin****************

H2

     H2内嵌数据库,如properties配置,开启console控制,启动logging.level.org.hibernate方便查看初始化H2信息(需要jpa的依赖).

      启动Spring, 访问http://localhost:8080/h2-console/,即出现控制

连接

     有两个是通过程序插入,其他consle使用详见官网

mybatis

      springboot整合mybatis参考

基本目录结构

以City为例

CityMapper

package mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import mybatis.model.City;

@Mapper
public interface CityMapper {

	/**
	 *注解
	 */
	@Select("select * from city where state = #{state}")
	City findByState(@Param("state") String state);
	
	/**xml 
	 */
	City selectCityById(int city_id);

}

     基于两种,一个注解,一种使用xml,注解的局限性太大,一般使用xml配置,xml配置需额外配置(mybatis-config.xml,CityMapper.xml),对于@Mapper注解可在application中进行统一注解

@SpringBootApplication
@MapperScan("mybatis.mapper")
public class MybatisApplication {

application.properties指定mybatis配置文件

mybatis.config-location=classpath:mybatis-config.xml

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <package name="mybatis.model"/>
    </typeAliases>
    <mappers>
        <mapper resource="mapper/CityMapper.xml"/>
        <mapper resource="mapper/HotelMapper.xml"/>
    </mappers>
</configuration>

      对上面mappers,可在application.properties中统一配置

mybatis.mapper-locations=classpath:mapper/*.xml

CityMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mybatis.mapper.CityMapper">
    <select id="selectCityById" resultType="City">
        select * from city where id = #{id}
    </select>
</mapper>

City

package mybatis.model;

import java.io.Serializable;

public class City implements Serializable {

	private static final long serialVersionUID = 1L;

	private Long id;

	private String name;

	private String state;

	private String country;

	public Long getId() {
		return this.id;
	}

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

	public String getName() {
		return this.name;
	}

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

	public String getState() {
		return this.state;
	}

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

	public String getCountry() {
		return this.country;
	}

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

	@Override
	public String toString() {
		return getId() + "," + getName() + "," + getState() + "," + getCountry();
	}
	
}

data.sql

insert into city (name, state, country) values ('San Francisco', 'CA', 'US');
insert into hotel(city, name, address, zip) values (1, 'Conrad Treasury Place', 'William & George Streets', '4001')

schema.sql

drop table if exists city;
drop table if exists hotel;

create table city (id int primary key auto_increment, name varchar, state varchar, country varchar);
create table hotel (city int, name varchar, address varchar, zip varchar);

启动

package mybatis;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;

import mybatis.mapper.CityMapper;
import mybatis.mapper.HotelMapper;

@SpringBootApplication
public class MybatisApplication {

	public static void main(String[] args) {
		ConfigurableApplicationContext run = SpringApplication.run(MybatisApplication.class, args);
		CityMapper cityMapper = run.getBean(CityMapper.class);
		System.out.println(cityMapper.findByState("CA"));
		System.out.println(cityMapper.selectCityById(1));
		
		HotelMapper hotelMapper = run.getBean(HotelMapper.class);
		System.out.println(hotelMapper.selectByCityId(1));
	}
}

DataSource

     对于只有一个数据源,使用SpringBoot自动装配的数据源,显得更为优雅,但更多时候需要自定义数据源(比如定义多个数据源)或保证数据源与SpringBoot解耦合(某些SpringBoot并未集成数据源格式).

package mybatis.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

@Configuration
@MapperScan(basePackages="mybatis.mapper",sqlSessionFactoryRef="sqlSessionFactory")
public class MybatisDataSourceConfig {

	//此处使用自动装配的H2数据源,可自定义数据源
    @Bean("sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        //sessionFactory.setTypeAliasesPackage("mybatis.model");
        sessionFactory.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("classpath:mybatis-config.xml"));
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return sessionFactory.getObject();
    }
}

          此处仅使用自动装配的h2 dataSource,其余mapper相关配置均独立配置,因此application.properties中的相关配置需注释

#************Mybatis  Begin****************
#mybatis.config-location=classpath:mybatis-config.xml
#mybatis.mapper-locations=classpath:mapper/*.xml
#************Mybatis  End****************

          自定义装配数据源,需禁用SpringBoot的自动装配,同时保证每个每个mybatis factory的独立性,其mapper 相关设置均独立设置,因此将@MapperScan移至此config下,application改动

//DataSourceAutoConfiguration 自动配置spring.datasource.*数据源(此处加上,还是会自动生成DataSource???)
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})

     MybatisDataSourceConfig配置自定义事物(SpringBoot自动装配了事物DataSourceTransactionManager)

    @Bean
    @Primary
    public DataSourceTransactionManager transactionManager(DataSource dataSource){
    	return new DataSourceTransactionManager(dataSource);
    }

     同时也需要@EnableTransactionManagement开启注解(SpringBoot 其他autoConfig也配置了,可能会复用,此处不配也能生效)