spring boot 通过mybatis连接MySQL数据库

淩亂°似流年 2021-09-23 04:02 360阅读 0赞

# 在[上篇][Link 1]中，已经创建好了boot的三个服务。现在在service-A中说明一下连接数据库的详细过程。 #

## 1.在pom.xml中加入 ##

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
            
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.3.0</version>
            </dependency>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.1</version>
            </dependency>
            
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.4.4</version>
            </dependency>
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis-spring</artifactId>
                <version>1.3.1</version>
            </dependency>

## 2.创建实体类Test.java ##

/** * Created by qqg on 2017/9/19. */
        public class Test { 
            private int id;
            private String name;
            private String password;
            @Override
            public String toString() {
                return "Test{" +
                        "id=" + id +
                        ", name='" + name + '\'' +
                        ", password='" + password + '\'' +
                        '}';
            }
            public int getId() {
                return id;
            }
            public void setId(int id) {
                this.id = id;
            }
            public String getName() {
                return name;
            }
            public void setName(String name) {
                this.name = name;
            }
            public String getPassword() {
                return password;
            }
            public void setPassword(String password) {
                this.password = password;
            }
        }

## 3.创建mapper接口 ##

import com.hand.dto.Test;
    import java.util.List;
    /** * Created by qqg on 2017/9/19. */
    public interface TestMapper { 
        List<Test> findAll();
    }

## 4.创建service接口 ##

import com.hand.dto.Test;
    import org.springframework.stereotype.Service;
    import java.util.List;
    /** * Created by qqg on 2017/9/19. */
    public interface ITestService { 
        List<Test> findAll();
    }

## 5.实现service接口 ##

import com.hand.dto.Test;
    import com.hand.mapper.TestMapper;
    import com.hand.service.ITestService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import java.util.List;
    /** * Created by qqg on 2017/9/19. */
    @Service
    public class TestServiceImpl implements ITestService { 
        @Autowired
        private TestMapper mapper;
        @Override
        public List<Test> findAll() {
            return mapper.findAll();
        }
    }

## 6.在配置文件resources下创建mapper文件夹，并在里面添加 TestMapper.xml文件。 ##

## TestMapper.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.hand.mapper.TestMapper">
        <select id="findAll" resultType="com.hand.dto.Test">
            SELECT id,name,password from test
        </select>
    </mapper>

## 7.resources下创建application.yml，并配置 ##

server:
      port: 9092
    
    spring:
      application:
        name: spring-cloud-consumer
      datasource:
        name: test
        url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8
        username: root
        password: 123456
        #使用druid数据源 &useSSL=true
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        filters: stat
        maxActive: 20
        initialSize: 1
        maxWait: 60000
        minIdle: 1
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: select 'x'
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxOpenPreparedStatements: 20
    
    mybatis:
      mapperLocations: classpath:mapper/*.xml #指定*Mapper.xml的位置 #设置日志级别，打印mybatis的日志 logging: level: root: debug

## 8.1 mybatis映射文件类MybatisConfig ##

import com.alibaba.druid.pool.DruidDataSource;
    import org.apache.ibatis.logging.LogFactory;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.SqlSessionTemplate;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.env.Environment;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.core.io.support.ResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import javax.sql.DataSource;
    /** * Created by qqg on 2017/9/19. */
    @Configuration
    public class MybatisConfig { 
        /** * 注入环境变量的值 */
        @Autowired
        private Environment environment;
    
        /** * 获取数据源DataSource * @return */
        @Bean
        public DataSource druidDataSource() {
            DruidDataSource druidDataSource = new DruidDataSource();
            druidDataSource.setUrl(environment.getProperty("spring.datasource.url"));
            druidDataSource.setUsername(environment.getProperty("spring.datasource.username"));
            druidDataSource.setPassword(environment.getProperty("spring.datasource.password"));
            druidDataSource.setDriverClassName(environment.getProperty("spring.datasource.driverClassName"));
            druidDataSource.setMaxActive(Integer.parseInt(environment.getProperty("spring.datasource.maxActive")));
            druidDataSource.setInitialSize(Integer.parseInt(environment.getProperty("spring.datasource.initialSize")));
            druidDataSource.setMaxWait(Long.parseLong(environment.getProperty("spring.datasource.maxWait")));
            druidDataSource.setMinIdle(Integer.parseInt(environment.getProperty("spring.datasource.minIdle")));
            druidDataSource.setTimeBetweenEvictionRunsMillis(Long.parseLong(environment.getProperty("spring.datasource.timeBetweenEvictionRunsMillis")));
            druidDataSource.setMinEvictableIdleTimeMillis(Long.parseLong(environment.getProperty("spring.datasource.minEvictableIdleTimeMillis")));
            druidDataSource.setValidationQuery(environment.getProperty("spring.datasource.validationQuery"));
            druidDataSource.setTestWhileIdle(Boolean.parseBoolean(environment.getProperty("spring.datasource.testWhileIdle")));
            druidDataSource.setTestOnBorrow(Boolean.parseBoolean(environment.getProperty("spring.datasource.testOnBorrow")));
            druidDataSource.setTestOnReturn(Boolean.parseBoolean(environment.getProperty("spring.datasource.testOnReturn")));
            druidDataSource.setPoolPreparedStatements(Boolean.parseBoolean(environment.getProperty("spring.datasource.poolPreparedStatements")));
           druidDataSource.setMaxOpenPreparedStatements(Integer.parseInt(environment.getProperty("spring.datasource.maxOpenPreparedStatements")));
            return druidDataSource;
        }
        /** * 获取SqlSessionFactory * @param druidDataSource * @return */
        @Bean(name = "sqlSessionFactory")
        public SqlSessionFactory sqlSessionFactoryBean(DataSource druidDataSource) {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(druidDataSource);
            LogFactory.useLog4JLogging();
            //添加XML目录
            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            String xmlPath = environment.getProperty("mybatis.mapperLocations");
            try {
             bean.setMapperLocations(resolver.getResources(xmlPath));
                return bean.getObject();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
        /** * * @param sqlSessionFactory * @return */
        @Bean
        public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
        /** * 增加事务 * @param druidDataSource * @return */
        @Bean
        public DataSourceTransactionManager transactionManager(DataSource druidDataSource) {
            return new DataSourceTransactionManager(druidDataSource);
        }
    }

## 8.2MabatisMapperScanConfig类 ##

import org.mybatis.spring.mapper.MapperScannerConfigurer;
    import org.springframework.boot.autoconfigure.AutoConfigureAfter;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    /** * Created by qqg on 2017/9/19. */
    @Configuration
    //注意，由于MabatisMapperScanConfig执行的比较早，所以必须有下面的注解
    @AutoConfigureAfter(MybatisConfig.class)
    public class MabatisMapperScanConfig { 
        @Bean
        public MapperScannerConfigurer mapperScannerConfigurer() {
            MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
            //注意这里的sqlSessionFactory就是MybatisConfig里面的sqlSessionFactoryBean方法，注解bean的名字
           mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
            //接口路径，通过这些接口调用sql的配置，操作数据库
          mapperScannerConfigurer.setBasePackage("com.mapper");
            return mapperScannerConfigurer;
        }
    }

## 9. controller的编写 ##

import com.hand.dto.Test;
    import com.hand.service.ITestService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.cloud.client.ServiceInstance;
    import org.springframework.cloud.client.discovery.DiscoveryClient;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.RestController;
    import java.util.List;
    import java.util.logging.Logger;
    /** * Created by qqg on 2017/9/15. */
    @RestController
    public class ComputeController {
        private final Logger logger = Logger.getLogger(String.valueOf(getClass()));
        @Autowired
        private DiscoveryClient client;
        @Autowired
        private ITestService service;
        @RequestMapping(value = "/list")
        public List<Test> getList(){
            return service.findAll();
        }
        @RequestMapping(value = "/add" ,method = RequestMethod.GET)
        public Integer add(@RequestParam Integer a, @RequestParam Integer b) {
            ServiceInstance instance = client.getLocalServiceInstance();
    
            Integer r = a + b;
            logger.info("/add, host:" + instance.getHost() + ", service_id:" + instance.getServiceId() + ", result:" + r);
            return r;
        }
    }

## 10.建立数据库，以及表，这里的数据库名称为test,表名为test，里面的字段根据实体类的自己看。 ##

## 11.重新启动3个服务，访问[http://localhost:5555/api-a/list?accessToken=token][http_localhost_5555_api-a_list_accessToken_token] ##

![这里写图片描述][SouthEast]

# OK。 #

### 相关连接：[http://blog.csdn.net/j903829182/article/details/75643661][http_blog.csdn.net_j903829182_article_details_75643661] ###

[Link 1]: http://blog.csdn.net/qinqigang/article/details/78032084
[http_localhost_5555_api-a_list_accessToken_token]: http://localhost:5555/api-a/list?accessToken=token
[SouthEast]: /images/20210923/1b90916e51444eb8b4e5914eb45b7ad9.png
[http_blog.csdn.net_j903829182_article_details_75643661]: http://blog.csdn.net/j903829182/article/details/75643661