spring boot 學(xué)習(xí)（配置mysql數(shù)據(jù)庫）

此文做記錄交流惠拭，如有不當(dāng)三椿，還望指正庆械。

在web服務(wù)器中少不了的是與數(shù)據(jù)庫打交道，這里我們采用的是MySQL數(shù)據(jù)庫盒让，也許你對于在Spring中如何進(jìn)行MySQL數(shù)據(jù)庫配置非常熟悉梅肤，這里我們介紹一下如何在Spring Boot環(huán)境下配置，并感受一下它的優(yōu)越性邑茄。

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 中配置數(shù)據(jù)庫參數(shù)

server.name=helloword


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


#數(shù)據(jù)庫連接配置
#驅(qū)動
spring.datasource.driverClassName = com.mysql.jdbc.Driver
#數(shù)據(jù)庫鏈接
spring.datasource.url = jdbc:mysql://localhost:3306/testdb
#用戶名
spring.datasource.username = root
#密碼
spring.datasource.password = 123456

在controller中注入datasource并訪問數(shù)據(jù)庫

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;
    }
}

啟動項(xiàng)目訪問 http://localhost:8080/restHello 查看結(jié)果

image.png

配置數(shù)據(jù)源 druid

同樣姨蝴，首先在pom文件中增加druid依賴

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

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


#數(shù)據(jù)庫連接配置
#驅(qū)動
spring.datasource.driverClassName = com.mysql.jdbc.Driver
#數(shù)據(jù)庫鏈接
spring.datasource.url = jdbc:mysql://localhost:3306/testdb
#用戶名
spring.datasource.username = root
#密碼
spring.datasource.password = 123456

#數(shù)據(jù)庫連接池配置
#初始化鏈接數(shù)
spring.datasource.initialSize=5  
#最小的空閑連接數(shù)
spring.datasource.minIdle=5
#最大的空閑連接數(shù)
spring.datasource.maxIdle=20
#最大活動連接數(shù)
spring.datasource.maxActive=20
#從池中取連接的最大等待時間，單位ms.  
spring.datasource.maxWait=60000
#每XXms運(yùn)行一次空閑連接回收器
spring.datasource.timeBetweenEvictionRunsMillis=60000  
#池中的連接空閑XX毫秒后被回收 
spring.datasource.minEvictableIdleTimeMillis=300000  
#驗(yàn)證使用的SQL語句  
spring.datasource.validationQuery=SELECT 1 FROM DUAL  
#指明連接是否被空閑連接回收器(如果有)進(jìn)行檢驗(yàn).如果檢測失敗,則連接將被從池中去除.    
spring.datasource.testWhileIdle=true  
#借出連接時不要測試肺缕，否則很影響性能  
spring.datasource.testOnBorrow=false  
#歸還連接時執(zhí)行validationQuery檢測連接是否有效左医，
做了這個配置會降低性能
spring.datasource.testOnReturn=false  
#是否緩存preparedStatement授帕，也就是PSCache。
PSCache對支持游標(biāo)的數(shù)據(jù)庫性能提升巨大浮梢，比如說oracle跛十。
在mysql5.5以下的版本中沒有PSCache功能，建議關(guān)閉掉秕硝。
5.5及以上版本有PSCache芥映，建議開啟。
spring.datasource.poolPreparedStatements=true  
#屬性類型是字符串远豺，通過別名的方式配置擴(kuò)展插件奈偏，
常用的插件有：
監(jiān)控統(tǒng)計(jì)用的filter:stat 
日志用的filter:log4j
 防御sql注入的filter:wall
spring.datasource.filters=stat,wall,log4j  
#數(shù)據(jù)池連接參數(shù)
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

目前Spring Boot中默認(rèn)支持的連接池有dbcp,dbcp2, tomcat, hikari三種連接池。
由于Druid暫時不在Spring Bootz中的直接支持憋飞，故需要進(jìn)行配置信息的定制：
創(chuàng)建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實(shí)例  
    @Primary  //在同樣的DataSource中霎苗，首先使用被標(biāo)注的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描述對象－關(guān)系表的映射關(guān)系内狸，并將運(yùn)行期的實(shí)體對象持久化到數(shù)據(jù)庫中检眯。Sun引入新的JPA ORM規(guī)范出于兩個原因：其一，簡化現(xiàn)有Java EE和Java SE應(yīng)用開發(fā)工作昆淡；其二锰瘸，Sun希望整合ORM技術(shù)，實(shí)現(xiàn)天下歸一昂灵。
由于Spring-data-jpa依賴于Hibernate避凝。我們需要配置Hibernate的屬性，如果我們不進(jìn)行配置則springboot 會采用默認(rèn)配置
我們在之前的項(xiàng)目中加入jpa的包依賴,完成后pom.xml中內(nèi)容如下


<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 中配置數(shù)據(jù)庫連接參數(shù)

server.name=helloword


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


#數(shù)據(jù)庫連接配置
#驅(qū)動
spring.datasource.driverClassName = com.mysql.jdbc.Driver
#數(shù)據(jù)庫鏈接
spring.datasource.url = jdbc:mysql://localhost:3306/testdb
#用戶名
spring.datasource.username = root
#密碼
spring.datasource.password = 123456

#JPA 配置
#數(shù)據(jù)庫名稱
spring.jpa.database=MySQL
#是否顯示sql
spring.jpa.show-sql=true

創(chuàng)建實(shí)體類

package com.demo.springboot_helloword.model;

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

@Entity
public class OrganizationName {
    @Id
    private Long id ;  //機(jī)構(gòu)名稱id
    private String  organizationName; //機(jī)構(gòu)名稱
    private Long parent_id; //父機(jī)構(gòu)id
    private Integer organizationType; //機(jī)構(gòu)類型
    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;
    }
    
}

創(chuàng)建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> {

}

創(chuàng)建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();
}

創(chuàng)建service實(shí)現(xiàn)

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 中調(diào)用

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

啟動項(xiàng)目眨补，訪問http://localhost:8080/restHello查看效果

image.png

Mybatis及通用mapper配置

MyBatis是一個支持普通SQL查詢管削，存儲過程和高級映射的優(yōu)秀持久層框架。MyBatis消除了幾乎所有的JDBC代碼和參數(shù)的手工設(shè)置以及對結(jié)果集的檢索封裝撑螺。MyBatis可以使用簡單的XML或注解用于配置和原始映射含思，將接口和Java的POJO（Plain Old Java Objects，普通的Java對象）映射成數(shù)據(jù)庫中的記錄甘晤。

由于之前的項(xiàng)目已經(jīng)配置的過于復(fù)雜含潘，我們不在那個項(xiàng)目之上做更改，配置mybatis我們將重新建一個新項(xiàng)目進(jìn)行測試

第一步同樣是配置pom线婚，增加依賴遏弱，我們在之前用過的基礎(chǔ)上新增兩個依賴，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下面創(chuàng)建application.properties配置文件漱逸，在配置文件中增加數(shù)據(jù)庫的相關(guān)配置缀踪，項(xiàng)目的相關(guān)配置

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

#數(shù)據(jù)庫連接配置
#驅(qū)動
spring.datasource.driverClassName = com.mysql.jdbc.Driver
#數(shù)據(jù)庫鏈接
spring.datasource.url = jdbc:mysql://localhost:3306/testdb
#用戶名
spring.datasource.username = root
#密碼
spring.datasource.password = 123456

#數(shù)據(jù)庫連接池配置
#初始化鏈接數(shù)
spring.datasource.initialSize=5  
#最小的空閑連接數(shù)
spring.datasource.minIdle=5
#最大的空閑連接數(shù)
spring.datasource.maxIdle=20
#最大活動連接數(shù)
spring.datasource.maxActive=20
#從池中取連接的最大等待時間，單位ms.  
spring.datasource.maxWait=60000
#每XXms運(yùn)行一次空閑連接回收器
spring.datasource.timeBetweenEvictionRunsMillis=60000  
#池中的連接空閑XX毫秒后被回收 
spring.datasource.minEvictableIdleTimeMillis=300000  
#驗(yàn)證使用的SQL語句  
spring.datasource.validationQuery=SELECT 1 FROM DUAL  
#指明連接是否被空閑連接回收器(如果有)進(jìn)行檢驗(yàn).如果檢測失敗,則連接將被從池中去除.    
spring.datasource.testWhileIdle=true  
#借出連接時不要測試虹脯，否則很影響性能  
spring.datasource.testOnBorrow=false  
#歸還連接時執(zhí)行validationQuery檢測連接是否有效驴娃，
做了這個配置會降低性能
spring.datasource.testOnReturn=false  
#是否緩存preparedStatement，也就是PSCache循集。
PSCache對支持游標(biāo)的數(shù)據(jù)庫性能提升巨大唇敞，比如說oracle。
在mysql5.5以下的版本中沒有PSCache功能咒彤，建議關(guān)閉掉疆柔。
5.5及以上版本有PSCache，建議開啟镶柱。
spring.datasource.poolPreparedStatements=true  
#屬性類型是字符串旷档，通過別名的方式配置擴(kuò)展插件，
常用的插件有：
監(jiān)控統(tǒng)計(jì)用的filter:stat 
日志用的filter:log4j
 防御sql注入的filter:wall
spring.datasource.filters=stat,wall,log4j  
#數(shù)據(jù)池連接參數(shù)
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

第三步編寫druid數(shù)據(jù)源類歇拆，如上
第四步編寫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對應(yīng)的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執(zhí)行的比較早，所以必須有下面的注解

@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;
    }

}

第六步創(chuàng)建model

package com.demo.springboot_mybatis.model;


public class OrganizationName {
    private Long id ;  //機(jī)構(gòu)名稱id
    private String  organizationName; //機(jī)構(gòu)名稱
    private Long parent_id; //父機(jī)構(gòu)id
    private Integer organizationType; //機(jī)構(gòu)類型
    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;
    }
    
}

第七步創(chuàng)建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();
}

第八步創(chuàng)建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();
}

第九步創(chuàng)建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();
    }

}

第十步創(chuàng)建controller調(diào)用

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

啟動項(xiàng)目输吏，訪問http://localhost:8080/getOrgNames 查看效果

image.png

配置通用mapper及分頁插件

通用mapper是為了我們方便操作數(shù)據(jù)庫提取出來的一部分公共接口权旷，它能使我們的開發(fā)更便捷高效，下來我們就來配置通用的mapper插件及分頁插件

第一步當(dāng)然還是引入依賴

  <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>

第二部創(chuàng)建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對應(yīng)的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; 現(xiàn)在為
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執(zhí)行的比較早，所以必須有下面的注解

@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進(jìn)行改造

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

}

好了邮弹，我們啟動項(xiàng)目訪問http://localhost:8080/getOrgNames 看看結(jié)果

image.png

此時數(shù)據(jù)已經(jīng)被正常查詢出來了黔衡，我們再在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;
    } 
}

我們啟動項(xiàng)目訪問http://localhost:8080/getOrgNames 再看結(jié)果

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> {
}

啟動項(xiàng)目訪問http://localhost:8080/getOrgNames 看結(jié)果

image.png

最后編輯于：2017.11.24 11:00:53

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個濱河市腌乡，隨后出現(xiàn)的幾起案子盟劫，更是在濱河造成了極大的恐慌，老刑警劉巖与纽，帶你破解...
沈念sama閱讀 206,968評論 6贊 482
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件侣签，死亡現(xiàn)場離奇詭異塘装，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)影所，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 88,601評論 2贊 382
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門蹦肴，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人猴娩，你說我怎么就攤上這事阴幌。” “怎么了卷中？”我有些...
開封第一講書人閱讀 153,220評論 0贊 344
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵矛双，是天一觀的道長。經(jīng)常有香客問我蟆豫，道長议忽，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 55,416評論 1贊 279
?港島之戀（遺憾婚禮）
正文為了忘掉前任十减，我火速辦了婚禮栈幸，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘嫉称。我一直安慰自己侦镇，他們只是感情好，可當(dāng)我...
茶點(diǎn)故事閱讀 64,425評論 5贊 374
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布织阅。她就那樣靜靜地躺著，像睡著了一般震捣。火紅的嫁衣襯著肌膚如雪荔棉。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 49,144評論 1贊 285
城市分裂傳說
那天蒿赢，我揣著相機(jī)與錄音润樱，去河邊找鬼。笑死羡棵，一個胖子當(dāng)著我的面吹牛壹若，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播皂冰，決...
沈念sama閱讀 38,432評論 3贊 401
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼店展，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了秃流？” 一聲冷哼從身側(cè)響起赂蕴，我...
開封第一講書人閱讀 37,088評論 0贊 261
萬榮殺人案實(shí)錄
序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎舶胀，沒想到半個月后概说，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體碧注，經(jīng)...
沈念sama閱讀 43,586評論 1贊 300
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 36,028評論 2贊 325
?白月光啟示錄
正文我和宋清朗相戀三年糖赔，在試婚紗的時候發(fā)現(xiàn)自己被綠了萍丐。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點(diǎn)故事閱讀 38,137評論 1贊 334
活死人
序言：一個原本活蹦亂跳的男人離奇死亡放典，死狀恐怖逝变，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情刻撒，我是刑警寧澤骨田，帶...
沈念sama閱讀 33,783評論 4贊 324
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站声怔，受9級特大地震影響态贤，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜醋火，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 39,343評論 3贊 307
男人毒藥：我在死后第九天來索命
文/蒙蒙一悠汽、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧芥驳，春花似錦柿冲、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,333評論 0贊 19
一樁弒父案假抄，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽。三九已至丽猬，卻和暖如春宿饱，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背脚祟。一陣腳步聲響...
開封第一講書人閱讀 31,559評論 1贊 262
情欲美人皮
我被黑心中介騙來泰國打工谬以，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人由桌。一個月前我還...
沈念sama閱讀 45,595評論 2贊 355
代替公主和親
正文我出身青樓为黎，卻偏偏與公主長得像，于是被迫代替她去往敵國和親行您。傳聞我的和親對象是個殘疾皇子铭乾，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 42,901評論 2贊 345

spring boot 學(xué)習(xí)（配置mysql數(shù)據(jù)庫）

JDBC

配置數(shù)據(jù)源 druid

JPA

Mybatis及通用mapper配置

配置通用mapper及分頁插件

編寫通用service

推薦閱讀更多精彩內(nèi)容