SpringBoot数据访问之MyBatis

心已赠人 2021-11-01 07:36 356阅读 0赞

**目录**

1、新建项目并进行相应配置，最终架构如下

2、配置application.yml

3、mybatis-config.xml信息

4、pom.xml文件

5、mybatis文件夹下新增Java类

6、controller文件夹

7、dao层代码

8、service代码

9、service.impl代码

10、mapper.xml代码

11、bean代码

12、数据库表

13、启动项目

--------------------

### 1、新建项目并进行相应配置，最终架构如下 ###

***注意：SpringbootmybatisApplication必须和example 同级目录***

**springboot版本号1.5.13**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3MyOTQ4NzgzMDQ_size_16_color_FFFFFF_t_70][]

### 2、配置application.yml ###

# DATASOURCE
    jdbc:
        dataSourceClassName: com.mysql.jdbc.jdbc2.optional.MysqlDataSource
        url: jdbc:mysql://192.168.xxx.xxx:3306/
        databaseName: xxxx
        serverName: 192.168.120.xxx
        username: root
        password: xxx
        cachePrepStmts: true
        prepStmtsSize: 250
        prepStmtsCacheSqlLimit: 2048
        userServerPrepStmts: true
    # MyBatis
    mybatis:
            typeAliasesPackage: com.example.*.dao
            mapperLocations: classpath:/mappers/*.xml
            configLocation: classpath:/mybatis-config.xml

### 3、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>
        <properties>
            <property name="dialect" value="mysql" />
        </properties>
        <settings>
            
            <setting name="cacheEnabled" value="true" />
            
            <setting name="lazyLoadingEnabled" value="true" />
            
            <setting name="multipleResultSetsEnabled" value="true" />
            
            <setting name="useColumnLabel" value="true" />
            
            <setting name="useGeneratedKeys" value="false" />
            
            <setting name="defaultExecutorType" value="SIMPLE" />
            
            <setting name="defaultStatementTimeout" value="25000" />
        </settings>
        <plugins>
            <plugin interceptor="com.github.pagehelper.PageHelper">
                <property name="dialect" value="mysql" />
                <property name="offsetAsPageNum" value="true" />
                <property name="rowBoundsWithCount" value="true" />
                <property name="pageSizeZero" value="true" />
                <property name="reasonable" value="true" />
            </plugin>
        </plugins>
    </configuration>

### 4、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>
    	<parent>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-parent</artifactId>
    		
    		<version>1.5.13.RELEASE</version>
    		<relativePath/> 
    	</parent>
    	<groupId>com.example</groupId>
    	<artifactId>springbootmybatis</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<name>springbootmybatis</name>
    	<description>Demo project for Spring Boot</description>
    
    	<properties>
    		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    		<java.version>1.8</java.version>
    		
    		<mybatis.version>3.2.8</mybatis.version>
    		<mybatis-generator-core.version>1.3.2</mybatis-generator-core.version>
    		<mybatis-spring.version>1.2.2</mybatis-spring.version>
    		<mysql-connector-java.version>5.1.34</mysql-connector-java.version>
    		
    		<pagehelper.version>3.6.3</pagehelper.version>
    	</properties>
    	<dependencies>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>org.mybatis.spring.boot</groupId>
    			<artifactId>mybatis-spring-boot-starter</artifactId>
    			<version>2.1.0</version>
    		</dependency>
    
    		
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    		</dependency>
    		
    		<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>org.mybatis.generator</groupId>
    			<artifactId>mybatis-generator-core</artifactId>
    			<version>${mybatis-generator-core.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>com.github.pagehelper</groupId>
    			<artifactId>pagehelper</artifactId>
    			<version>${pagehelper.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>com.zaxxer</groupId>
    			<artifactId>HikariCP-java6</artifactId>
    			<version>2.3.13</version>
    			<exclusions>
    				<exclusion>
    					<artifactId>tools</artifactId>
    					<groupId>com.sun</groupId>
    				</exclusion>
    			</exclusions>
    		</dependency>
    		
    		<dependency>
    			<groupId>javax.inject</groupId>
    			<artifactId>javax.inject</artifactId>
    			<version>1</version>
    		</dependency>
    		<dependency>
    			<groupId>javax.servlet</groupId>
    			<artifactId>jstl</artifactId>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-jdbc</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-tx</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>io.springfox</groupId>
    			<artifactId>springfox-swagger2</artifactId>
    			<version>2.7.0</version>
    		</dependency>
    		<dependency>
    			<groupId>io.springfox</groupId>
    			<artifactId>springfox-swagger-ui</artifactId>
    			<version>2.7.0</version>
    		</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>

### 5、mybatis文件夹下新增Java类 ###

package com.example.mybatis;
    import com.zaxxer.hikari.HikariConfig;
    import com.zaxxer.hikari.HikariDataSource;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.boot.bind.RelaxedPropertyResolver;
    import org.springframework.context.ApplicationContextException;
    import org.springframework.context.EnvironmentAware;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.env.Environment;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    import javax.sql.DataSource;
    import java.util.Arrays;
    
    @Configuration
    @EnableTransactionManagement
    public class DataBaseConfiguration implements EnvironmentAware {
     
    	private RelaxedPropertyResolver propertyResolver;
     
    	private static Logger log = LoggerFactory
                .getLogger(DataBaseConfiguration.class);
     
    	private Environment env;
     
    	@Override
    	public void setEnvironment(Environment env) {
    		this.env = env;
    		this.propertyResolver = new RelaxedPropertyResolver(env, "jdbc.");
    	}
     
    	@Bean(destroyMethod ="shutdown")
    	public DataSource dataSource() {
    		log.debug("Configruing DataSource");
    		if (propertyResolver.getProperty("url") == null
    				&& propertyResolver.getProperty("databaseName") == null) {
    			log.error("Your database conncetion pool configuration is incorrct ! The application "
    					+ "cannot start . Please check your jdbc");
    			Arrays.toString(env.getActiveProfiles());
    			throw new ApplicationContextException(
    					"DataBase connection pool is not configured correctly");
    		}
    		HikariConfig config = new HikariConfig();
    		config.setDataSourceClassName(propertyResolver
    				.getProperty("dataSourceClassName"));
    		if (propertyResolver.getProperty("url") == null
    				|| "".equals(propertyResolver.getProperty("url"))) {
    			config.addDataSourceProperty("databaseName",
    					propertyResolver.getProperty("databaseName"));
    			config.addDataSourceProperty("serverName",
    					propertyResolver.getProperty("serverName"));
    		} else {
    			config.addDataSourceProperty("url",
    					propertyResolver.getProperty("url"));
    		}
    		config.setUsername(propertyResolver.getProperty("username"));
    		config.setPassword(propertyResolver.getProperty("password"));
    		if ("com.mysql.jdbc.jdbc2.optional.MysqlDataSource"
    				.equals(propertyResolver.getProperty("dataSourceName"))) {
    			config.addDataSourceProperty("cachePrepStmts",
    					propertyResolver.getProperty("cachePrepStmts"));
    			config.addDataSourceProperty("prepStmtCacheSize",
    					propertyResolver.getProperty("prepStmtsCacheSize"));
    			config.addDataSourceProperty("prepStmtCacheSqlLimit",
    					propertyResolver.getProperty("prepStmtCacheSqlLimit"));
    			config.addDataSourceProperty("userServerPrepStmts",
    					propertyResolver.getProperty("userServerPrepStmts"));
    		}
    		return new HikariDataSource(config);
    	}
     
    }

package com.example.mybatis;
    
    import org.apache.commons.logging.Log;
    import org.apache.commons.logging.LogFactory;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.boot.autoconfigure.AutoConfigureAfter;
    import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
    import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
    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 org.springframework.core.io.DefaultResourceLoader;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
     
    import javax.inject.Inject;
    import javax.sql.DataSource;
     
    //import javax.persistence.EntityManager;
     
    @Configuration
    @ConditionalOnClass({ EnableTransactionManagement.class})
    @AutoConfigureAfter({ DataBaseConfiguration.class })
    @MapperScan(basePackages={"com.example.dao"})
    public class MybatisConfiguration implements EnvironmentAware {
     
    	private static Log logger = LogFactory.getLog(MybatisConfiguration.class);
     
    	private RelaxedPropertyResolver propertyResolver;
     
    	@Inject
        private DataSource dataSource;
     
    	@Override
    	public void setEnvironment(Environment environment) {
    		this.propertyResolver = new RelaxedPropertyResolver(environment,
    				"mybatis.");
    	}
     
    	@Bean
    	@ConditionalOnMissingBean
    	public SqlSessionFactory sqlSessionFactory() {
    		try {
    			SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
    			sessionFactory.setDataSource(dataSource);
    			sessionFactory.setTypeAliasesPackage(propertyResolver
    					.getProperty("typeAliasesPackage"));
    			sessionFactory
    					.setMapperLocations(new PathMatchingResourcePatternResolver()
    							.getResources(propertyResolver
    									.getProperty("mapperLocations")));
    			sessionFactory
    					.setConfigLocation(new DefaultResourceLoader()
    							.getResource(propertyResolver
    									.getProperty("configLocation")));
     
    			return sessionFactory.getObject();
    		} catch (Exception e) {
    			logger.warn("Could not confiure mybatis session factory");
    			return null;
    		}
    	}
     
    	@Bean
    	@ConditionalOnMissingBean
    	public DataSourceTransactionManager transactionManager() {
    		return new DataSourceTransactionManager(dataSource);
    	}
    }

### 6、controller文件夹 ###

package com.example.controller;
    
    
    import com.example.bean.AnchorBaseInfoBean;
    import com.example.dao.AnchorBaseInfoDao;
    import com.example.service.AnchorBaseInfoService;
    import io.swagger.annotations.ApiParam;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.*;
    
    @RestController
    @RequestMapping(value="/anchor", produces = "application/json; charset=UTF-8")
    public class AnchorBaseInfoController {
     
        @Autowired
        private AnchorBaseInfoService anchorBaseInfoService;
        @Autowired
        private AnchorBaseInfoDao anchorBaseInfoDao;
    //  http://localhost:8080/anchor/getTodayBaseInfoByPlatRoomTimeHorizon?platID=2&roomID=1023513
        @RequestMapping(value = "/getTodayBaseInfoByPlatRoomTimeHorizon", method = {RequestMethod.GET})
        public AnchorBaseInfoBean getAnchorBaseInfoByPlatRoom(
                @ApiParam(value = "平台ID", required = true)  @RequestParam(value = "platID", required = true) int platID,
                @ApiParam(value = "房间ID", required = true)  @RequestParam(value = "roomID", required = true) String roomID
        ) {
    
            AnchorBaseInfoBean resultBean=anchorBaseInfoDao.getAnchorBaseInfoByPlatRoom(platID,roomID);
    
            System.out.println(resultBean);
            return  resultBean;
        }
     
    }

### 7、dao层代码 ###

package com.example.dao;
    
    import com.example.bean.AnchorBaseInfoBean;
    
    public interface AnchorBaseInfoDao {
        AnchorBaseInfoBean getAnchorBaseInfoByPlatRoom(int platform_id,String room_id);
    }

### 8、service代码 ###

package com.example.service;
    
    import com.example.bean.AnchorBaseInfoBean;
    
    public interface AnchorBaseInfoService {
    
        AnchorBaseInfoBean getAnchorBaseInfoBean(int platform_id, String room_id);
    
    }

### 9、service.impl代码 ###

package com.example.service.impl;
    
    import com.example.bean.AnchorBaseInfoBean;
    import com.example.dao.AnchorBaseInfoDao;
    import com.example.service.AnchorBaseInfoService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    @Service
    public class AnchorBaseInfoServiceImpl implements AnchorBaseInfoService {
     
        @Autowired
        private AnchorBaseInfoDao anchorBaseInfoDao;
    
    
        @Override
        public AnchorBaseInfoBean getAnchorBaseInfoBean(int platform_id,String room_id)
        {
            AnchorBaseInfoBean anchorBaseInfoBean=anchorBaseInfoDao.getAnchorBaseInfoByPlatRoom(platform_id,room_id);
            return anchorBaseInfoBean;
        }
    }

### 10、mapper.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.dao.AnchorBaseInfoDao" >
    
        <select id="getAnchorBaseInfoByPlatRoom" resultType="com.example.bean.AnchorBaseInfoBean">
            SELECT platform_id,room_id,sex from live_platform.anchor_base_info where platform_id=#{0} and room_id=#{1} ;
        </select>
    
    </mapper>

### 11、bean代码 ###

package com.example.bean;
    public class AnchorBaseInfoBean {
        private int platform_id;
        private String room_id;
        private  String sex;
    
        public int getPlatform_id() {
            return platform_id;
        }
    
        public void setPlatform_id(int platform_id) {
            this.platform_id = platform_id;
        }
    
        public String getRoom_id() {
            return room_id;
        }
    
        public void setRoom_id(String room_id) {
            this.room_id = room_id;
        }
    
        public String getSex() {
            return sex;
        }
    
        public void setSex(String sex) {
            this.sex = sex;
        }
    }

### 12、数据库表 ###

![20190811211948931.png][]

### 13、启动项目 ###

访问接口

[http://localhost:8080/anchor/getTodayBaseInfoByPlatRoomTimeHorizon?platID=2&roomID=1023513][http_localhost_8080_anchor_getTodayBaseInfoByPlatRoomTimeHorizon_platID_2_roomID_1023513]

![20190811212016474.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3MyOTQ4NzgzMDQ_size_16_color_FFFFFF_t_70]: /images/20211101/d17410b6fac14632a069e2e3a9794833.png
[20190811211948931.png]: /images/20211101/18ef851eb3734c5fb30adf885e083b5c.png
[http_localhost_8080_anchor_getTodayBaseInfoByPlatRoomTimeHorizon_platID_2_roomID_1023513]: http://localhost:8080/anchor/getTodayBaseInfoByPlatRoomTimeHorizon?platID=2&roomID=1023513
[20190811212016474.png]: /images/20211101/65262b10fb5342ffaae1bf51ad7cccfc.png