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或者浏览器调用,可返回结果