SpringBoot初学（4）– JdbcTemplate和Mybatis

前言

github： https://github.com/vergilyn/SpringBootDemo

代码位置：

一、Spring Boot集成JdbcTemplate或NamedParameterJdbcTemplate

spring boot中JdbcTemplate与NamedParameterJdbcTemplate都是被自动配置的，所以只要注入就可用。（在传统的spring中NamedParameterJdbcTemplate并没有被自动配置）

必须准备：

如果你使用spring-boot-starter-jdbc或spring-boot-starter-data-jpa 'starter POMs'，你将会自动获取对tomcat-jdbc的依赖。

<dependency>

	<groupId>org.springframework.boot</groupId>

	<artifactId>spring-boot-starter-jdbc</artifactId>

</dependency>

application.properties:

#### 28.1.2. 连接到一个生产环境数据库

## 注：其他的连接池可以手动配置。如果你定义自己的DataSource bean，自动配置不会发生。

####

## Spring Boot能够从大多数数据库的url上推断出driver-class-name，那么你就不需要再指定它了。

spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

## 默认会从url推断driver class

spring.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl

spring.datasource.username=vergilyn

spring.datasource.password=409839163

## JDNI

# spring.datasource.jndi-name=java:jboss/datasources/customers

/* Spring的JdbcTemplate和NamedParameterJdbcTemplate类是被自动配置的，

 * 你可以在自己的beans中通过@Autowire直接注入它们。

 */

@Repository

public class JdbcTemplateDao {

	@Autowired

	private JdbcTemplate jdbcTemplate;

	@Autowired

	private NamedParameterJdbcTemplate namedJdbc;

	@SuppressWarnings("unused")

	public Long testDao(){

		Map<String,Object> param = new HashMap<String,Object>();

		String sql = " select count(*) from child";

		if(true){

			sql += "  where child_name = :name ";

			param.put("name", "周一");

		}

		Long count = this.namedJdbc.queryForObject(sql, param, Long.class);

		System.out.println(count);

		return count;

	}

}

@SpringBootApplication

@RestController

public class DatabaseApplication {

	@Autowired

	private JdbcTemplateDao dao;

	public static void main(String[] args) {

		SpringApplication.run(DatabaseApplication.class, args);

	}

	@RequestMapping("/jdbc")

	public String testDao(){

		return dao.testDao()+"";

	}

}

二、Spring boot集成Mybatis

2.1 准备

<dependency>

	<groupId>org.mybatis.spring.boot</groupId>

	<artifactId>mybatis-spring-boot-starter</artifactId>

	<version>1.2.0</version>

</dependency>

2.2 xml模式的mybatis配置

mybatis的sql映射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="com.vergilyn.demo.springboot.mybatis.domain"/>

    </typeAliases>

    <mappers>

        <mapper resource="config/mybatis/mapper/CityMapper.xml"/>

        <mapper resource="config/mybatis/mapper/HotelMapper.xml"/>

    </mappers>

</configuration>

application.properties中对应要指明此映射路径。

application.properties：

## spring.datasource.schema=classpath:config/mybatis/init-oracle.sql

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

mybatis的mapper定义：

<?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="com.vergilyn.demo.springboot.mybatis.mapper.HotelMapper">

    <select id="selectByCityId" resultType="Hotel">

        select * from hotel where city = #{id}

    </select>

</mapper>

HotelMapper.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="com.vergilyn.demo.springboot.mybatis.mapper.CityMapper">

    <select id="selectCityById" resultType="City">

        select * from city where id = #{id}

    </select>

</mapper>

CityMapper.xml

mybatis的domain定义：

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();

	}

}

City.java

public class Hotel implements Serializable {

	private static final long serialVersionUID = 1L;

	private Long city;

	private String name;

	private String address;

	private String zip;

	public Long getCity() {

		return city;

	}

	public void setCity(Long city) {

		this.city = city;

	}

	public String getName() {

		return name;

	}

	public void setName(String name) {

		this.name = name;

	}

	public String getAddress() {

		return address;

	}

	public void setAddress(String address) {

		this.address = address;

	}

	public String getZip() {

		return zip;

	}

	public void setZip(String zip) {

		this.zip = zip;

	}

	@Override

	public String toString() {

		return getCity() + "," + getName() + "," + getAddress() + "," + getZip();

	}

}

Hotel.java

mybatis的dao定义，mybatis的SqlSession可直接注入：

@Component

public class CityDao {

	@Autowired

	private SqlSession sqlSession;

	public City selectCityById(long id) {

		return this.sqlSession.selectOne("selectCityById", id);

	}

}

测试代码:

@SpringBootApplication

public class MybatisApplication implements CommandLineRunner {

	public static void main(String[] args) {

		SpringApplication app = new SpringApplication(MybatisApplication.class);

		app.setAdditionalProfiles("mybatis");

		app.run(args);

	}

	@Autowired

	private CityDao cityDao;

	@Override

	public void run(String... args) throws Exception {

		System.out.println(this.cityDao.selectCityById(1));

	}

}

2.3 mybatis注解形式

相对于xml形式，使用注解模式可减少很多xml的定义。例如：无需定义映射sql的mybatis-config.xml，*Mapper.xml中的sql也可以用注解写java文件中，application.properties中无需配置任何指定。

但，个人偏向于还是在xml中写sql。

注解形式的domain与xml形式的是一样的，jar的引用也是一样的。

注解形式的*Mapper.java定义，主要是注解org.apache.ibatis.annotations.Mapper：

@Mapper

public interface CityMapper {

	// 混用xml形式

	City selectByCityId(int city_id);

	/**

	 * 注解形式不需要通过dao调用*Mapper.xml。所以可以不写dao、*Mapper.xml、mybatis-config.xml

	 * @param i

	 * @return

	 */

	@Select("select * from city where id = #{id}")

	City selectByAnnotation(int i);

}

@Mapper

public interface HotelMapper {

	Hotel selectByHotelId(int city_id);

}

以上可能并不是混用xml，反正要用xml，那么相应的application.properties中就需要指明mybatis-config.xml。

(一篇不错的介绍mybatis中Mapper的文章: Mybatis中配置Mapper的方法 )

测试代码：

@SpringBootApplication

public class MybatisApplication implements CommandLineRunner {

	public static void main(String[] args) {

		SpringApplication app = new SpringApplication(MybatisApplication.class);

		app.setAdditionalProfiles("mybatis");

		app.run(args);

	}

	@Autowired

	private CityDao cityDao;

	@Autowired

	private HotelMapper hotelMapper;

	@Autowired

	private CityMapper cityMapper;

	@Override

	public void run(String... args) throws Exception {

		System.out.println(this.cityDao.selectCityById(1));

		System.out.println(this.cityMapper.selectByAnnotation(1));

		System.out.println(this.hotelMapper.selectByHotelId(1));

	}

}

三、题外话

到现在，主要用过的有hibernate、mybatis、JdbcTemplate。个人最喜欢的还是mybatis, 简单一句话相对hibernate而言，纯sql的可控性高太多。

另外，spring boot学到现在，发现其简化的spring配置不是一点两点那么简单。就像用注解形式的mybatis，spring boot中都可以不需要任何手动配置，因为在mybatis.jar中有一套自动配置。

那么就完全省去了配置的麻烦（虽然实际开发中这并不关一个初中级开发的事。）

然后剩下的只是mybatis要怎么用？@Mapper有哪些作用？这些都无关繁琐的配置，而是实际开发要用到的。

巴特西