SpringBoot 学习记录(二): mybaits

继续前篇学习，加入数据库访问层mybatis，这里数据库使用的是mysql

一，首先在原有的pom.xml中加入依赖：

	<dependency>
		<groupId>org.mybatis.spring.boot</groupId>
		<artifactId>mybatis-spring-boot-starter</artifactId>
		<version>1.1.1</version>
	</dependency>
	
	<!-- mybatis分页工具,需要版本1.1.1及以上 -->
	<dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper</artifactId>
        <version>4.1.0</version>
    </dependency>
	
     <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>

这里引入了mybaits分页插件，PageHelper，是Github上以为开发者提供的开源插件，使用很方便。

先看完整目录结构：

二，在application.properties中配置数据源

#datasource
spring.datasource.url = jdbc:mysql://localhost:3306/yun?characterEncoding=utf-8
spring.datasource.username = root
spring.datasource.password = centos
spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.max-active=20
spring.datasource.max-idle=8
spring.datasource.min-idle=8
spring.datasource.initial-size=10
spring.datasource.sql-script-encoding=UTF-8

三，创建测试table

CREATE TABLE `demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

四，创建实体类Demo

package com.example.entity;

public class Demo {
	
	private long id;
	
	private String name;

	public long getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

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

}

五，创建接口DemoMapper，这里先采用注解方式实现，注意接口上不需要添加注解

package com.example.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Select;

import com.example.entity.Demo;

public interface DemoMapper {

    @Select("select * from demo where name = #{name}")
    public List<Demo> likeName(String name);
   
    @Select("select * from demo where id = #{id}")
    public Demo getById(long id);
}

六，创建DemoService + DemoServiceImpl

package com.example.service;

import java.util.List;

import com.example.entity.Demo;

public interface DemoService {
	
	public List<Demo> likeName(String name);
	
	public Demo getById(long id);

}

package com.example.service.impl;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.example.entity.Demo;
import com.example.mapper.DemoMapper;
import com.example.service.DemoService;

@Service
public class DemoServiceImpl implements DemoService {
	
	@Resource
	private DemoMapper demoMapper;

	@Override
	public List<Demo> likeName(String name) {
		return demoMapper.likeName(name);
	}

	@Override
	public Demo getById(long id) {
		return demoMapper.getById(id);
	}

}

七，创建DemoController

package com.example.controller;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.example.entity.Demo;
import com.example.service.DemoService;

@RestController
public class DemoController {

	@Resource
	private DemoService demoService;
	
	@RequestMapping(value="/demo/list",method=RequestMethod.GET)
	public List<Demo> list(String name){
		return demoService.likeName(name);
	}
	
}

八，启动类DemoApplication添加注解，扫描mapper

package com.example;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.*.mapper")//扫描该包下接口
public class DemoApplication {

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

九，启动测试

http://localhost:8088/spring-boot/demo/list?name=test

返回结果如下(预先在数据库插入了4条数据，便于测试)：

[
  {
    "id": 1,
    "name": "test"
  },
  {
    "id": 2,
    "name": "test"
  },
  {
    "id": 3,
    "name": "test"
  },
  {
    "id": 4,
    "name": "test"
  }
]

十，使用分页插件，通过@Bean注解注入

package com.example.config;

import java.util.Properties;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.github.pagehelper.PageHelper;

@Configuration
public class PageHelperConfiguration {

	@Bean
    public PageHelper pageHelper() {
        PageHelper pageHelper = new PageHelper();
        Properties p = new Properties();
        p.setProperty("offsetAsPageNum", "true");
        p.setProperty("rowBoundsWithCount", "true");
        p.setProperty("reasonable", "true");
        pageHelper.setProperties(p);
        return pageHelper;
    }
}

十一，DemoController新增分页方法

@RequestMapping(value="/demo/page",method=RequestMethod.GET)
	public List<Demo> page(String name){
		PageHelper.startPage(1,2);
		return demoService.likeName(name);
	}

十二，访问测试

http://localhost:8088/spring-boot/demo/page?name=test

返回结果如下：

[
  {
    "id": 1,
    "name": "test"
  },
  {
    "id": 2,
    "name": "test"
  }
]

=================================================================================================

以上是采用注解方式实现sql语句，下面采用常规的xml配置方式

十三，新增DemoMapper.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.example.mapper.DemoMapper" >

  <resultMap id="BaseResultMap" type="com.example.entity.Demo" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="name" property="name" jdbcType="VARCHAR" />
  </resultMap>
  
  <sql id="Base_Column_List" >
    id, name
  </sql>
  
  <select id="selectAll" resultMap="BaseResultMap" >
    select 
    <include refid="Base_Column_List" />
    from demo
  </select>
  
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
    select 
    <include refid="Base_Column_List" />
    from demo
    where id = #{id,jdbcType=BIGINT}
  </select>
  
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
    delete from demo
    where id = #{id,jdbcType=BIGINT}
  </delete>
</mapper>

十四，接口中添加相应的方法

DemoMapper

int deleteByPrimaryKey(Long id);

    Demo selectByPrimaryKey(Long id);

	List<Demo> selectAll();

DemoService

int deleteByPrimaryKey(Long id);

    Demo selectByPrimaryKey(Long id);

	List<Demo> selectAll();

DemoServiceImpl

@Override
	public int deleteByPrimaryKey(Long id) {
		return demoMapper.deleteByPrimaryKey(id);
	}

	@Override
	public Demo selectByPrimaryKey(Long id) {
		return demoMapper.selectByPrimaryKey(id);
	}

	@Override
	public List<Demo> selectAll() {
		return demoMapper.selectAll();
	}

十五，DemoController

	@RequestMapping(value="/demo/all",method=RequestMethod.GET)
	public List<Demo> all(){
		PageHelper.startPage(1,2);
		return demoService.selectAll();
	}
	
	@RequestMapping(value="/del",method=RequestMethod.DELETE)
	public int del(long id){
		return demoService.deleteByPrimaryKey(id);
	}
	
	@RequestMapping(value="/demo",method=RequestMethod.GET)
	public Demo get(long id){
		return demoService.selectByPrimaryKey(id);
	}

十六，方法都写好了，最主要是在application.properties中注册*.xml的目录，添加如下配置：

#mapper.xml location
mybatis.mapperLocations=classpath:mybatis/mapper/*.xml
mybatis.config-locations=classpath:mybatis/mybatis-config.xml

注意这里配置的mybatis-config.xml主要是配置了别名，可以不用，这里加上作为补充知识点，方便以后使用

十七，测试，注意分页插件依然生效，测试结果如下：

mybaits的使用到此结束，下篇继续学习sping_boot为我们提供的数据库访问层，SpringBoot 学习记录(三): jpa