spring boot 学习（配置mysql数据库）

此文做记录交流，如有不当，还望指正。

在web服务器中少不了的是与数据库打交道，这里我们采用的是MySQL数据库，也许你对于在Spring中如何进行MySQL数据库配置非常熟悉，这里我们介绍一下如何在Spring Boot环境下配置，并感受一下它的优越性。

JDBC

首先我们先来看看最简单的jdbc的配置
在pom中增加jdbc的依赖


<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>com.demo</groupId>
    <artifactId>springboot-helloword</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>springboot-helloword</name>
    <url>http://maven.apache.org</url>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.0.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

在application.properties 中配置数据库参数

server.name=helloword


spring.mvc.throw-exception-if-no-handler-found=true
spring.resources.add-mappings=false


#数据库连接配置
#驱动
spring.datasource.driverClassName = com.mysql.jdbc.Driver
#数据库链接
spring.datasource.url = jdbc:mysql://localhost:3306/testdb
#用户名
spring.datasource.username = root
#密码
spring.datasource.password = 123456

在controller中注入datasource并访问数据库

package com.demo.springboot_helloword.webrest;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;



@RestController
public class RestHelloController {
    
    @Autowired
    DataSource dataSource;
    
    @RequestMapping("/restHello")
    public Object restHello() throws Exception {
        Connection connect = dataSource.getConnection();
        PreparedStatement pre = connect.prepareStatement("select * from organization_name");
        ResultSet result = pre.executeQuery();
         List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        while (result.next()) {
            Map<String,Object> map = new HashMap<String, Object>();
            map.put("id", result.getObject("id"));
            map.put("organization_name", result.getObject("organization_name"));
            map.put("parent_id", result.getObject("parent_id"));
            map.put("organization_type", result.getObject("organization_type"));
            list.add(map);
        }
        if(result!= null ) result.close();
        if(pre!= null ) pre.close();
        if(connect!= null ) connect.close();
        return list;
    }
}

启动项目访问 http://localhost:8080/restHello 查看结果

image.png

配置数据源 druid

同样，首先在pom文件中增加druid依赖

<dependency>  
            <groupId>com.alibaba</groupId>  
            <artifactId>druid</artifactId>  
            <version>1.0.25</version>  
        </dependency>

然后在application.properties中增加连接池配置


#数据库连接配置
#驱动
spring.datasource.driverClassName = com.mysql.jdbc.Driver
#数据库链接
spring.datasource.url = jdbc:mysql://localhost:3306/testdb
#用户名
spring.datasource.username = root
#密码
spring.datasource.password = 123456

#数据库连接池配置
#初始化链接数
spring.datasource.initialSize=5  
#最小的空闲连接数
spring.datasource.minIdle=5
#最大的空闲连接数
spring.datasource.maxIdle=20
#最大活动连接数
spring.datasource.maxActive=20
#从池中取连接的最大等待时间，单位ms.  
spring.datasource.maxWait=60000
#每XXms运行一次空闲连接回收器
spring.datasource.timeBetweenEvictionRunsMillis=60000  
#池中的连接空闲XX毫秒后被回收 
spring.datasource.minEvictableIdleTimeMillis=300000  
#验证使用的SQL语句  
spring.datasource.validationQuery=SELECT 1 FROM DUAL  
#指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除.    
spring.datasource.testWhileIdle=true  
#借出连接时不要测试，否则很影响性能  
spring.datasource.testOnBorrow=false  
#归还连接时执行validationQuery检测连接是否有效，
做了这个配置会降低性能
spring.datasource.testOnReturn=false  
#是否缓存preparedStatement，也就是PSCache。
PSCache对支持游标的数据库性能提升巨大，比如说oracle。
在mysql5.5以下的版本中没有PSCache功能，建议关闭掉。
5.5及以上版本有PSCache，建议开启。
spring.datasource.poolPreparedStatements=true  
#属性类型是字符串，通过别名的方式配置扩展插件，
常用的插件有：
监控统计用的filter:stat 
日志用的filter:log4j
 防御sql注入的filter:wall
spring.datasource.filters=stat,wall,log4j  
#数据池连接参数
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

目前Spring Boot中默认支持的连接池有dbcp,dbcp2, tomcat, hikari三种连接池。
由于Druid暂时不在Spring Bootz中的直接支持，故需要进行配置信息的定制：
创建DruidConfig类

package com.demo.springboot_helloword.config;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import com.alibaba.druid.pool.DruidDataSource;

@Configuration
public class DruidConfig {

    private Logger logger = LoggerFactory.getLogger(DruidConfig.class);  
    @Value("${spring.datasource.url}")  
    private String dbUrl;  
      
    @Value("${spring.datasource.username}")  
    private String username;  
      
    @Value("${spring.datasource.password}")  
    private String password;  
      
    @Value("${spring.datasource.driverClassName}")  
    private String driverClassName;  
      
    @Value("${spring.datasource.initialSize}")  
    private int initialSize;  
      
    @Value("${spring.datasource.minIdle}")  
    private int minIdle;  
      
    @Value("${spring.datasource.maxActive}")  
    private int maxActive;  
      
    @Value("${spring.datasource.maxWait}")  
    private int maxWait;  
      
    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")  
    private int timeBetweenEvictionRunsMillis;  
      
    @Value("${spring.datasource.minEvictableIdleTimeMillis}")  
    private int minEvictableIdleTimeMillis;  
      
    @Value("${spring.datasource.validationQuery}")  
    private String validationQuery;  
      
    @Value("${spring.datasource.testWhileIdle}")  
    private boolean testWhileIdle;  
      
    @Value("${spring.datasource.testOnBorrow}")  
    private boolean testOnBorrow;  
      
    @Value("${spring.datasource.testOnReturn}")  
    private boolean testOnReturn;  
      
    @Value("${spring.datasource.poolPreparedStatements}")  
    private boolean poolPreparedStatements;  
      
    @Value("${spring.datasource.filters}")  
    private String filters;  
      
    @Value("${spring.datasource.connectionProperties}")  
    private String connectionProperties;  
      
    @Bean     //声明其为Bean实例  
    @Primary  //在同样的DataSource中，首先使用被标注的DataSource  
    public DataSource dataSource(){  
        DruidDataSource datasource = new DruidDataSource();  
          
        datasource.setUrl(this.dbUrl);  
        datasource.setUsername(username);  
        datasource.setPassword(password);  
        datasource.setDriverClassName(driverClassName);  
          
        //configuration  
        datasource.setInitialSize(initialSize);  
        datasource.setMinIdle(minIdle);  
        datasource.setMaxActive(maxActive);  
        datasource.setMaxWait(maxWait);  
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);  
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);  
        datasource.setValidationQuery(validationQuery);  
        datasource.setTestWhileIdle(testWhileIdle);  
        datasource.setTestOnBorrow(testOnBorrow);  
        datasource.setTestOnReturn(testOnReturn);  
        datasource.setPoolPreparedStatements(poolPreparedStatements);  
        try {  
            datasource.setFilters(filters);  
        } catch (SQLException e) {  
            logger.error("druid configuration initialization filter", e);  
        }  
        datasource.setConnectionProperties(connectionProperties);  
          
        return datasource;  
    }  
}

至此，druid池就配置完成了

JPA

JPA是Java Persistence API的简称，中文名Java持久层API，是JDK 5.0注解或XML描述对象－关系表的映射关系，并将运行期的实体对象持久化到数据库中。Sun引入新的JPA ORM规范出于两个原因：其一，简化现有Java EE和Java SE应用开发工作；其二，Sun希望整合ORM技术，实现天下归一。
由于Spring-data-jpa依赖于Hibernate。我们需要配置Hibernate的属性，如果我们不进行配置则springboot 会采用默认配置
我们在之前的项目中加入jpa的包依赖,完成后pom.xml中内容如下


<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>com.demo</groupId>
    <artifactId>springboot-helloword</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>springboot-helloword</name>
    <url>http://maven.apache.org</url>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.0.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

在 application.properties 中配置数据库连接参数

server.name=helloword


spring.mvc.throw-exception-if-no-handler-found=true
spring.resources.add-mappings=false


#数据库连接配置
#驱动
spring.datasource.driverClassName = com.mysql.jdbc.Driver
#数据库链接
spring.datasource.url = jdbc:mysql://localhost:3306/testdb
#用户名
spring.datasource.username = root
#密码
spring.datasource.password = 123456

#JPA 配置
#数据库名称
spring.jpa.database=MySQL
#是否显示sql
spring.jpa.show-sql=true

创建实体类

package com.demo.springboot_helloword.model;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class OrganizationName {
    @Id
    private Long id ;  //机构名称id
    private String  organizationName; //机构名称
    private Long parent_id; //父机构id
    private Integer organizationType; //机构类型
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getOrganizationName() {
        return organizationName;
    }
    public void setOrganizationName(String organizationName) {
        this.organizationName = organizationName;
    }
    
    public Long getParent_id() {
        return parent_id;
    }
    public void setParent_id(Long parent_id) {
        this.parent_id = parent_id;
    }
    public Integer getOrganizationType() {
        return organizationType;
    }
    public void setOrganizationType(Integer organizationType) {
        this.organizationType = organizationType;
    }
    
}

创建Reposition

package com.demo.springboot_helloword.mapper;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import com.demo.springboot_helloword.model.OrganizationName;

@Repository
public interface OrganizationNameReposition extends JpaRepository<OrganizationName, Long> {

}

创建service

package com.demo.springboot_helloword.service;

import java.util.List;

import com.demo.springboot_helloword.model.OrganizationName;


public interface OrganizationNameService {
   public List<OrganizationName> getAll();
}

创建service实现

package com.demo.springboot_helloword.service.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.demo.springboot_helloword.mapper.OrganizationNameReposition;
import com.demo.springboot_helloword.model.OrganizationName;
import com.demo.springboot_helloword.service.OrganizationNameService;

@Service
public class OrganizationNameServerImpl implements OrganizationNameService {
    @Autowired
    OrganizationNameReposition organizationNameReposition;
    @Override
    public List<OrganizationName> getAll() {
        return organizationNameReposition.findAll();
    }

}

在controller 中调用

package com.demo.springboot_helloword.webrest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.demo.springboot_helloword.service.OrganizationNameService;

@RestController
public class RestHelloController {
    
    @Autowired
    OrganizationNameService organizationNameService;
    
    @RequestMapping("/restHello")
    public Object restHello() throws Exception {
        return organizationNameService.getAll();
    }
}

启动项目，访问http://localhost:8080/restHello查看效果

image.png

Mybatis及通用mapper配置

MyBatis是一个支持普通SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装。MyBatis可以使用简单的XML或注解用于配置和原始映射，将接口和Java的POJO（Plain Old Java Objects，普通的Java对象）映射成数据库中的记录。

由于之前的项目已经配置的过于复杂，我们不在那个项目之上做更改，配置mybatis我们将重新建一个新项目进行测试

第一步同样是配置pom，增加依赖，我们在之前用过的基础上新增两个依赖，mybatis及spring-mybatis

<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>com.demo</groupId>
    <artifactId>springboot-helloword</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>springboot-helloword</name>
    <url>http://maven.apache.org</url>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <mybatis.version>3.4.1</mybatis.version>
        <mybatis.spring.version>1.3.0</mybatis.spring.version>
    </properties>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.0.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>  
            <groupId>com.alibaba</groupId>  
            <artifactId>druid</artifactId>  
            <version>1.0.25</version>  
        </dependency>
         <!--Mybatis-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>${mybatis.version}</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>${mybatis.spring.version}</version>
        </dependency> 
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

第二步，在/src/main/resourcesx下面创建application.properties配置文件，在配置文件中增加数据库的相关配置，项目的相关配置

server.name=helloword

#配置model所在位置
spring.datasource.mybatis.base.typeAliases=com.demo.springboot_mybatis.model
#配置mapper所在位置
spring.datasource.mybatis.base.mapper=com.demo.springboot_mybatis.mapper

#数据库连接配置
#驱动
spring.datasource.driverClassName = com.mysql.jdbc.Driver
#数据库链接
spring.datasource.url = jdbc:mysql://localhost:3306/testdb
#用户名
spring.datasource.username = root
#密码
spring.datasource.password = 123456

#数据库连接池配置
#初始化链接数
spring.datasource.initialSize=5  
#最小的空闲连接数
spring.datasource.minIdle=5
#最大的空闲连接数
spring.datasource.maxIdle=20
#最大活动连接数
spring.datasource.maxActive=20
#从池中取连接的最大等待时间，单位ms.  
spring.datasource.maxWait=60000
#每XXms运行一次空闲连接回收器
spring.datasource.timeBetweenEvictionRunsMillis=60000  
#池中的连接空闲XX毫秒后被回收 
spring.datasource.minEvictableIdleTimeMillis=300000  
#验证使用的SQL语句  
spring.datasource.validationQuery=SELECT 1 FROM DUAL  
#指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除.    
spring.datasource.testWhileIdle=true  
#借出连接时不要测试，否则很影响性能  
spring.datasource.testOnBorrow=false  
#归还连接时执行validationQuery检测连接是否有效，
做了这个配置会降低性能
spring.datasource.testOnReturn=false  
#是否缓存preparedStatement，也就是PSCache。
PSCache对支持游标的数据库性能提升巨大，比如说oracle。
在mysql5.5以下的版本中没有PSCache功能，建议关闭掉。
5.5及以上版本有PSCache，建议开启。
spring.datasource.poolPreparedStatements=true  
#属性类型是字符串，通过别名的方式配置扩展插件，
常用的插件有：
监控统计用的filter:stat 
日志用的filter:log4j
 防御sql注入的filter:wall
spring.datasource.filters=stat,wall,log4j  
#数据池连接参数
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

第三步编写druid数据源类，如上
第四步编写mybatiesConfig配置类

package com.demo.springboot_mybatis.config;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;


@Configuration
@AutoConfigureAfter(MyDruidDataSource.class)
public class MybatiesConfig {

    @Autowired
    DataSource dataSource;
    @Value("${spring.datasource.mybatis.base.typeAliases}")
    private String typeAliases;
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactoryBean() throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        
        if(typeAliases != null  && !"".equals(typeAliases)){
            typeAliases=","+typeAliases;
        }else{
            typeAliases="";
        }
        bean.setTypeAliasesPackage(typeAliases);
        //分页插件
        //添加XML目录
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        try {
            //配置mapper对应的xml文件在classpath:下
            Resource[] res = resolver.getResources("classpath:mapper/*.xml");
            if(res!= null && res.length > 0 ){
                 bean.setMapperLocations(res);
            }
        } catch (Exception e) {
        }
        return bean.getObject();
    }
}

第五步编写Mapperscan配置类

package com.demo.springboot_mybatis.config;


import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.boot.bind.RelaxedPropertyResolver;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;

/**
 * MyBatis扫描接口，使用的tk.mybatis.spring.mapper.MapperScannerConfigurer，如果你不使用通用Mapper，可以改为org.xxx...
 *
 * @author zgf
 * @since 2016-11-14 14:46
 */
//TODO 注意，由于MapperScannerConfigurer执行的比较早，所以必须有下面的注解

@Configuration
@AutoConfigureAfter(MybatiesConfig.class)
public class MyBatisMapperScannerConfig implements EnvironmentAware {
    
    private RelaxedPropertyResolver propertyResolver;
    @Override
    public void setEnvironment(Environment arg0) {
        this.propertyResolver = new RelaxedPropertyResolver(arg0,
                "");
    } 
    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
        String mapper = propertyResolver.getProperty("spring.datasource.mybatis.base.mapper");
        if(mapper != null  && !"".equals(mapper)){
            mapper=","+mapper;
        }else{
            mapper="";
        }
        mapperScannerConfigurer.setBasePackage(mapper);
        return mapperScannerConfigurer;
    }

}

第六步创建model

package com.demo.springboot_mybatis.model;


public class OrganizationName {
    private Long id ;  //机构名称id
    private String  organizationName; //机构名称
    private Long parent_id; //父机构id
    private Integer organizationType; //机构类型
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getOrganizationName() {
        return organizationName;
    }
    public void setOrganizationName(String organizationName) {
        this.organizationName = organizationName;
    }
    
    public Long getParent_id() {
        return parent_id;
    }
    public void setParent_id(Long parent_id) {
        this.parent_id = parent_id;
    }
    public Integer getOrganizationType() {
        return organizationType;
    }
    public void setOrganizationType(Integer organizationType) {
        this.organizationType = organizationType;
    }
    
}

第七步创建mapper

package com.demo.springboot_mybatis.mapper;

import java.util.List;

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

import com.demo.springboot_mybatis.model.OrganizationName;

@Mapper
public interface OrganizationNameMapper{
    @Select("select * from organization_name")
    public List<OrganizationName> getAll();
}

第八步创建service

package com.demo.springboot_mybatis.service;

import java.util.List;

import com.demo.springboot_mybatis.model.OrganizationName;


public interface OrganizationNameService {
   public List<OrganizationName> getAll();
}

第九步创建serviceImpl

package com.demo.springboot_mybatis.service.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.demo.springboot_mybatis.mapper.OrganizationNameMapper;
import com.demo.springboot_mybatis.model.OrganizationName;
import com.demo.springboot_mybatis.service.OrganizationNameService;

@Service
public class OrganizationNameServerImpl implements OrganizationNameService {
    @Autowired
    OrganizationNameMapper organizationNameMapper;
    @Override
    public List<OrganizationName> getAll() {
        return organizationNameMapper.getAll();
    }

}

第十步创建controller调用

package com.demo.springboot_mybatis.web;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.demo.springboot_mybatis.model.OrganizationName;
import com.demo.springboot_mybatis.service.OrganizationNameService;



@RestController
public class RestHelloController {
    
    @Autowired
    OrganizationNameService organizationNameService;
    @Autowired
    DataSource dataSource;
    
    @RequestMapping("/getOrgNames")
    public List<OrganizationName> getOrgNames() throws Exception{
        return organizationNameService.getAll();
    } 
}

操作做完后整体包接口如下

image.png

启动项目，访问http://localhost:8080/getOrgNames 查看效果

image.png

配置通用mapper及分页插件

通用mapper是为了我们方便操作数据库提取出来的一部分公共接口，它能使我们的开发更便捷高效，下来我们就来配置通用的mapper插件及分页插件

第一步当然还是引入依赖

  <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <mybatis.version>3.4.1</mybatis.version>
        <mybatis.spring.version>1.3.0</mybatis.spring.version>
        <mapper.version>3.3.6</mapper.version>
        <pagehelper.version>4.1.1</pagehelper.version>
  </properties>
    <!--分页插件-->
      <dependency>
          <groupId>com.github.pagehelper</groupId>
          <artifactId>pagehelper</artifactId>
          <version>${pagehelper.version}</version>
      </dependency>
      <!--通用Mapper-->
      <dependency>
          <groupId>tk.mybatis</groupId>
          <artifactId>mapper</artifactId>
          <version>${mapper.version}</version>
      </dependency>

第二部创建MyMapper通用接口类，注意此接口不能被扫描到

package com.demo.util;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;
/**
 * 继承自己的MyMapper
 *
 * @author zgf
 * @since 2016-11-14 21:53
 */
public interface MyMapper<T> extends Mapper<T>, MySqlMapper<T> {
    //TODO
    //FIXME 特别注意，该接口不能被扫描到，否则会出错
}

第三步在MybatiesConfig中增加pagehelper配置

package com.demo.springboot_mybatis.config;

import java.util.Properties;

import javax.sql.DataSource;

import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;

import com.github.pagehelper.PageHelper;


@Configuration
@AutoConfigureAfter(MyDruidDataSource.class)
public class MybatiesConfig {

    @Autowired
    DataSource dataSource;
    @Value("${spring.datasource.mybatis.base.typeAliases}")
    private String typeAliases;
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactoryBean() throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        
        if(typeAliases != null  && !"".equals(typeAliases)){
            typeAliases=","+typeAliases;
        }else{
            typeAliases="";
        }
        bean.setTypeAliasesPackage(typeAliases);
      //分页插件
        PageHelper pageHelper = new PageHelper();
        Properties properties = new Properties();
        properties.setProperty("reasonable", "true");
        properties.setProperty("supportMethodsArguments", "true");
        properties.setProperty("returnPageInfo", "check");
        properties.setProperty("params", "count=countSql");
        pageHelper.setProperties(properties);
        //添加插件
        bean.setPlugins(new Interceptor[]{pageHelper});
        //添加XML目录
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        try {
            //配置mapper对应的xml文件在classpath:下
            Resource[] res = resolver.getResources("classpath:mapper/*.xml");
            if(res!= null && res.length > 0 ){
                 bean.setMapperLocations(res);
            }
        } catch (Exception e) {
        }
        return bean.getObject();
    }
}

第三步在MyBatisMapperScannerConfig中增加MyMapper配置
注意，之前我们MapperScannerConfigurer包为import org.mybatis.spring.mapper.MapperScannerConfigurer; 现在为
import tk.mybatis.spring.mapper.MapperScannerConfigurer;

package com.demo.springboot_mybatis.config;


import java.util.Properties;

import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.boot.bind.RelaxedPropertyResolver;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;

import tk.mybatis.spring.mapper.MapperScannerConfigurer;

/**
 * MyBatis扫描接口，使用的tk.mybatis.spring.mapper.MapperScannerConfigurer，如果你不使用通用Mapper，可以改为org.xxx...
 *
 * @author zgf
 * @since 2016-11-14 14:46
 */
//TODO 注意，由于MapperScannerConfigurer执行的比较早，所以必须有下面的注解

@Configuration
@AutoConfigureAfter(MybatiesConfig.class)
public class MyBatisMapperScannerConfig implements EnvironmentAware {
    
    private RelaxedPropertyResolver propertyResolver;
    @Override
    public void setEnvironment(Environment arg0) {
        this.propertyResolver = new RelaxedPropertyResolver(arg0,
                "");
    } 
    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
        String mapper = propertyResolver.getProperty("spring.datasource.mybatis.base.mapper");
        if(mapper != null  && !"".equals(mapper)){
            mapper=","+mapper;
        }else{
            mapper="";
        }
        mapperScannerConfigurer.setBasePackage(mapper);
        Properties properties = new Properties();
        properties.setProperty("mappers", "com.demo.util.MyMapper");
        properties.setProperty("notEmpty", "false");
        properties.setProperty("IDENTITY", "MYSQL");
        mapperScannerConfigurer.setProperties(properties);
        return mapperScannerConfigurer;
    }

}

最后我们对我们的OrganizationNameMapper和OrganizationNameServerImpl进行改造

package com.demo.springboot_mybatis.mapper;

import com.demo.springboot_mybatis.model.OrganizationName;
import com.demo.util.MyMapper;

public interface OrganizationNameMapper extends MyMapper<OrganizationName>{
}


package com.demo.springboot_mybatis.service.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.demo.springboot_mybatis.mapper.OrganizationNameMapper;
import com.demo.springboot_mybatis.model.OrganizationName;
import com.demo.springboot_mybatis.service.OrganizationNameService;

@Service
public class OrganizationNameServerImpl implements OrganizationNameService {
    @Autowired
    OrganizationNameMapper organizationNameMapper;
    @Override
    public List<OrganizationName> getAll() {
        return organizationNameMapper.selectAll();
    }

}

好了，我们启动项目访问http://localhost:8080/getOrgNames 看看结果

image.png

此时数据已经被正常查询出来了，我们再在controller中加上分页试试


package com.demo.springboot_mybatis.web;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.demo.springboot_mybatis.model.OrganizationName;
import com.demo.springboot_mybatis.service.OrganizationNameService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;



@RestController
public class RestHelloController {
    
    @Autowired
    OrganizationNameService organizationNameService;
    @Autowired
    DataSource dataSource;
    
    @RequestMapping("/getOrgNames")
    public PageInfo getOrgNames() throws Exception{
        PageHelper pageHelper = new PageHelper();
        PageInfo<OrganizationName> pageinfo = new PageInfo<OrganizationName>(organizationNameService.getAll());
        return pageinfo;
    } 
}

我们启动项目访问http://localhost:8080/getOrgNames 再看结果

image.png

编写通用service

package com.demo.springboot_mybatis.service;

import java.util.List;

import tk.mybatis.mapper.entity.Example;

public interface BaseService<T> {
   public List<T> selectAll();
   public List<T> selectByExample(Example example);
   public T selectByPrimarikey(Object key);
   public int updateByPrimarikey(T t);
   public int updateByExample(T t,Example example);
   public int insert(T t);
   public int insertList(List<T> t);
   public int deleteByPrimarikey(Object key);
   public int deleteByExample(Example example);
}

编写abstractBaseService类

package com.demo.springboot_mybatis.service;

import java.io.Serializable;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import com.demo.springboot_mybatis.service.BaseService;
import com.demo.util.MyMapper;

import tk.mybatis.mapper.entity.Example;

public class AbstractBaseService<CommonMapper extends MyMapper<T>, T extends Object> implements BaseService<T> {

     @Autowired  
     protected CommonMapper mapper; 
    
    
    @Override
    public List<T> selectAll() {
        return mapper.selectAll();
    }

    @Override
    public T selectByPrimarikey(Object key) {
        return mapper.selectByPrimaryKey(key);
    }

    @Override
    public int updateByPrimarikey(T t) {
        return mapper.updateByPrimaryKeySelective(t);
    }

    @Override
    public int updateByExample(T t,Example example) {
        return mapper.updateByExampleSelective(t, example);
    }

    @Override
    public int insert(T t) {
        return mapper.insert(t);
    }

    @Override
    public int insertList(List<T> t) {
        return mapper.insertList(t);
    }

    @Override
    public int deleteByPrimarikey(Object key) {
        return mapper.deleteByPrimaryKey(key);
    }

    @Override
    public int deleteByExample(Example example) {
        return mapper.deleteByExample(example);
    }

    @Override
    public List<T> selectByExample(Example example) {
        // TODO Auto-generated method stub
        return mapper.selectByExample(example);
    }


   
}

改造OrganizationNameService

package com.demo.springboot_mybatis.service;

import org.springframework.stereotype.Service;

import com.demo.springboot_mybatis.mapper.OrganizationNameMapper;
import com.demo.springboot_mybatis.model.OrganizationName;

@Service
public class OrganizationNameService extends AbstractBaseService<OrganizationNameMapper, OrganizationName> {
}

启动项目访问http://localhost:8080/getOrgNames 看结果

image.png

最后编辑于：2017.11.24 11:00:53

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,362评论 5赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,330评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,247评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,560评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,580评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,569评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,929评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,587评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,840评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,596评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,678评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,366评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,945评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,929评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,165评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 43,271评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,403评论 2赞 342

spring boot 学习（配置mysql数据库）

JDBC

配置数据源 druid

JPA

Mybatis及通用mapper配置

配置通用mapper及分页插件

编写通用service

推荐阅读更多精彩内容