Spring Boot，Maven，Mybatis，Mysql整合

1、在本地创建一个MYSQL数据库，用户为test,登录用户名为root，密码root

先看下eclipse项目中各文件所在位置

其中有部分内容没有用到，比如MemberWithBLOBs.java，这个是利用工具对应表生成的实体类文件，此处没有用到，忽略。

同时，不再记录如果创建一个MAVEN项目，只将用到的配置进行贴入
2、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.cl.sql</groupId>
  <artifactId>sqltest</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  
  	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.0.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<dependencies>
    	<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
	<dependency>
    	<groupId>org.mybatis</groupId>
    	<artifactId>mybatis</artifactId>
    	<version>3.5.0</version>
	</dependency>
	<!-- 不添加则报Property 'sqlSessionFactory' or 'sqlSessionTemplate' are required -->
	<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.9</version>
        </dependency>
	<!-- 不添加则@MapperScan注解无效 -->
	<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.2.0</version>
        </dependency>
	
	 <!-- Spring Boot Test 依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
         <!-- MySQL 连接驱动依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.45</version>
        </dependency>
        <dependency>
        	<groupId>org.mybatis.generator</groupId>
     		<artifactId>mybatis-generator-core</artifactId>
        	<version>1.3.5</version>
       </dependency>
        <!-- mybatis通用mapper -->
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper</artifactId>
            <version>3.3.0</version>
        </dependency>

  </dependencies>
  
  	<build>
      <plugins>
            <plugin>
            <!-- 配置 generator-->
           <groupId>org.mybatis.generator</groupId>
           <artifactId>mybatis-generator-maven-plugin</artifactId>
           <version>1.3.2</version>
              <configuration>
                  <!--允许移动生成的文件-->   
                <verbose>true</verbose>
                <!--允许覆盖生成的文件-->    
                <overwrite>true</overwrite>  
              </configuration>  
              <dependencies>
              	<dependency>
              		<groupId>mysql</groupId>
              		<artifactId>mysql-connector-java</artifactId>
              		 <version>${mysql.version}</version>
              	</dependency>
              </dependencies>
           </plugin>
        </plugins>
	</build>
</project>

2、application.properties的配置

#      application.properties

############################### 数据库配置 ###############################
#//  数据库名字
spring.datasource.name=test
#//  数据源url， 通过url可以关联数据库 
spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8
#// 数据库用户名和密码
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#//  数据库驱动
spring.datasource.driverClassName=com.mysql.jdbc.Driver
# 
##############################  mybatis配置  ###############################
#全局映射器启用缓存
mybatis.configuration.cache-enabled=true
#查询时,关闭关联对象及时加载以提高性能
mybatis.configuration.lazy-loading-enabled=false
#按需加载字段(加载字段由SQL指定),不会加载关联表的所有字段,以提高性能
mybatis.configuration.aggressive-lazy-loading=false
#允许返回不同的结果集以达到通用的效果
mybatis.configuration.multiple-result-sets-enabled=true
#对于批量更新操作缓存SQL以提高性能
mybatis.configuration.default-executor-type=REUSE
#数据库执行超时时间
mybatis.configuration.default-statement-timeout=25000

3、启动项

package com.cl.sqltest;

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

@SpringBootApplication()
@MapperScan("com.cl.sqltest.*.mapper")
public class MySqlApplication {
	public static void main(String[] args){
		
		SpringApplication.run(MySqlApplication.class, args);
	}

}

4、Member实体

package com.cl.sqltest.entities.entity;

public class Member {
    private Integer memberId;

    private String memberName;

    private String memberTruename;
 
    private Boolean memberSex;

    private String memberBirthday;

    private String memberPasswd;

  
    public Integer getMemberId() {
        return memberId;
    }

    public void setMemberId(Integer memberId) {
        this.memberId = memberId;
    }

    public String getMemberName() {
        return memberName;
    }

    public void setMemberName(String memberName) {
        this.memberName = memberName == null ? null : memberName.trim();
    }

    public String getMemberTruename() {
        return memberTruename;
    }

    public void setMemberTruename(String memberTruename) {
        this.memberTruename = memberTruename == null ? null : memberTruename.trim();
    }



    public Boolean getMemberSex() {
        return memberSex;
    }

    public void setMemberSex(Boolean memberSex) {
        this.memberSex = memberSex;
    }

    public String getMemberBirthday() {
        return memberBirthday;
    }

    public void setMemberBirthday(String memberBirthday) {
        this.memberBirthday = memberBirthday == null ? null : memberBirthday.trim();
    }

    public String getMemberPasswd() {
        return memberPasswd;
    }

    public void setMemberPasswd(String memberPasswd) {
        this.memberPasswd = memberPasswd == null ? null : memberPasswd.trim();
    }

}

5、MemberExtendMapper.java接口

package com.cl.sqltest.entities.mapper;

import com.cl.sqltest.entities.entity.Member;


public interface MemberExtendMapper {

	int selectCount();
	
	Member getByMemberId(int memberId);
}

6、MemberExtendMapper.xml具体的SQL语句

<?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.cl.sqltest.entities.mapper.MemberExtendMapper">
  <resultMap id="BaseResultMap" type="com.cl.sqltest.entities.entity.Member">
    <id column="member_id" jdbcType="INTEGER" property="memberId" />
    <result column="member_name" jdbcType="VARCHAR" property="memberName" />
    <result column="member_sex" jdbcType="BIT" property="memberSex" />
    <result column="member_birthday" jdbcType="VARCHAR" property="memberBirthday" />
    <result column="member_passwd" jdbcType="VARCHAR" property="memberPasswd" />
  </resultMap>
  <select id="selectCount" resultType = "java.lang.Integer" >
   select count(*) from jxdd_member 
  </select>
  <select id="getByMemberId" resultMap = "BaseResultMap" parameterType="java.lang.Integer">
  select member_id,member_name,member_sex,member_birthday,member_passwd from member where member_id = #{memberId,jdbcType=INTEGER}
  </select>
  </mapper>

7、MemberResource.java接口

package com.cl.sqltest.resources;

import com.cl.sqltest.entities.entity.Member;

public interface MemberResource {

	public Member getMember(int memberId);
}

8、MemberResourceImp.java接口实现类，控制项

package com.cl.sqltest.resources;

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

import com.cl.sqltest.entities.entity.Member;
import com.cl.sqltest.services.MemberService;

@RestController
@RequestMapping(value="/mysqltest")
public class MemberResourceImp implements MemberResource {

	@Autowired
	MemberService memberService;
	
	public MemberResourceImp(MemberService memberService){
		this.memberService = memberService;
	}	
	
	@Override
	@RequestMapping(value="/get/member/{memberId}",method= RequestMethod.GET)
	public Member getMember(@PathVariable int memberId) {
		Member member = memberService.getMember(memberId);
		return member;
	}

}

 

9、具体的实现类MemberService.java

package com.cl.sqltest.services;

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

import com.cl.sqltest.entities.entity.Member;
import com.cl.sqltest.entities.mapper.MemberExtendMapper;

@Service
public class MemberService{

	
	private MemberExtendMapper memberExtendMapper;
	
	@Autowired
	public MemberService(MemberExtendMapper memberExtendMapper){
		this.memberExtendMapper = memberExtendMapper;
	}

	public Member getMember(int memberId) {
		
		int count = memberExtendMapper.selectCount();
		Member member = memberExtendMapper.getByMemberId(memberId);
		System.out.println(count);
		return member;
	}

}

 

10、项目启动，通过postman或者浏览器调用，可返回结果