Spring Data JPA 好处是能用方法名代替写sql做一些简单的数据库访问。
实现方式：写一个接口、写一个类继承一个类、写方法名
用户名：root ；密码：root ；创建数据库test 表user
表结构如下
表内容如下
l Mybatis插件mapper源码参考地址：https://gitee.com/free/Mapper
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>1.1.0</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.1.0</version>
<exclusions>
<exclusion>
<artifactId>tomcat-jdbc</artifactId>
<groupId>org.apache.tomcat</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.5</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.1</version>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
在src/main/resources下的application.yml 或者bootstarp.yml添加数据库配置
spring:
aop:
auto:true
proxy-target-class:true
datasource:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/svw_account?useUnicode=true
driver-class-name: com.mysql.jdbc.Driver
username: root
password: root
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle:true
testOnBorrow:false
testOnReturn:false
poolPreparedStatements:true
maxPoolPreparedStatementPerConnectionSize: 20
#slow sql record
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# merge datasoure monitor
useGlobalDataSourceStat:true
hikari:
maximumPoolSize: 10
mybatis:
mapperLocations: classpath*:META-INF/com/svw/tbox/tcloud/user/provider/mapper/*.xml
type-aliases-package: com.svw.tbox.tcloud.user.provider.entity
mapper:
mappers:
- tk.mybatis.mapper.common.Mapper
not-empty:false
mapper.identity: MYSQL
pagehelper:
helperDialect: mySql
reasonable:true
supportMethodsArguments:true
params: count=countSql
MyBatis Generator 参考地址：http://mbg.cndocs.ml/ 。
在src/test/resources中添加配置文件generatorConfig.xml ，内容如下：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<context id="Mysql" targetRuntime="MyBatis3Simple" defaultModelType="flat">
<plugin type="tk.mybatis.mapper.generator.MapperPlugin">
<property name="mappers" value="tk.mybatis.mapper.common.Mapper"/>
<!-- caseSensitive默认false，当数据库表名区分大小写时，可以将该属性设置为true -->
<property name="caseSensitive" value="true"/>
</plugin>
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/svw_account"
userId="root"
password="root">
</jdbcConnection>
<javaModelGenerator targetPackage="com.svw.tbox.tcloud.user.consumer.entity" targetProject="D:\STS_space\tcloud-user-consumer\src\main\java"/>
<sqlMapGenerator targetPackage="\META-INF\com\svw\tbox\tcloud\user\provider\mapper" targetProject="D:\STS_space\tcloud-user-consumer\src\main\resources"/>
<javaClientGenerator targetPackage="com.svw.tbox.tcloud.user.consumer.dao" targetProject="D:\STS_space\tcloud-user-consumer\src\main\java" type="XMLMAPPER" />
<table tableName="t_user" >
<generatedKey column="id" sqlStatement="Mysql" identity="true"/>
</table>
</context>
</generatorConfiguration>
在src\test\java 下创建
packagecom.svw.tbox.tcloud.user.consumer.mapper.cfg;
import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.exception.InvalidConfigurationException;
import org.mybatis.generator.exception.XMLParserException;
import org.mybatis.generator.internal.DefaultShellCallback;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
publicclass AutoCreateDaoAndEntity {
publicstaticvoid main(String[] args) throws IOException, XMLParserException, InvalidConfigurationException, SQLException, InterruptedException {
List<String> warnings = new ArrayList<String>();
booleanoverwrite = true;
InputStream inputStream = AutoCreateDaoAndEntity.class.getClassLoader().getResourceAsStream("generatorConfig.xml");
ConfigurationParser cp = new ConfigurationParser(warnings);
Configuration config = cp.parseConfiguration(inputStream);
DefaultShellCallback callback = new DefaultShellCallback(overwrite);
MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
myBatisGenerator.generate(null);
}
}
右击运行：
产生对应的实体类和dao类
产生mapper sql配置文件：
@MapperScan(basePackages = "com.svw.tbox.tcloud.user.consumer.dao")
publicclass ConsumerApplication {
……
package com.svw.tbox.tcloud.user.provider.vo;
import java.io.Serializable;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
@ApiModel(description="用户VO")
public class UserVO implements Serializable {
private static final long serialVersionUID = 1971651527296698958L;
@ApiModelProperty(value="主键ID")
public Long id;
@ApiModelProperty(value="名称")
public String name;
@ApiModelProperty(value="年龄")
private int age;
@ApiModelProperty(value="性别 1-男 2-女")
private Boolean sex;
@ApiModelProperty(value="级别：1-10（级）")
private String grade;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Boolean getSex() {
return sex;
}
public void setSex(Boolean sex) {
this.sex = sex;
}
public String getGrade() {
return grade;
}
public void setGrade(String grade) {
this.grade = grade;
}
}
package com.svw.tbox.tcloud.user.consumer.service;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.svw.tbox.tcloud.common.vo.PageRequest;
import com.svw.tbox.tcloud.user.consumer.dao.TUserMapper;
import com.svw.tbox.tcloud.user.consumer.entity.TUser;
import com.svw.tbox.tcloud.user.consumer.vo.UserVO;
import tk.mybatis.mapper.entity.Example;
/**
*
* <p>ClassName: UserService</p>
* <p>Description: 用户信息服务类</p>
* <p>Author: liuyunlong</p>
* <p>Date: 2017年11月2日</p>
*/
@Service
publicclass UserService {
@Autowired
TUserMapper userMapper;
/**
*
* <p>Description:添加用户</p>
* @param user 用户VO
* @return影响的条数 1-成功 0-失败
*/
@Transactional
publicint add(TUser user) {
returnuserMapper.insert(user);
}
/**
*
* <p>Description: 根据用户的id更新其他信息</p>
* @param userVO 用户VO
* @return影响的条数 1-成功 0-失败
*/
@Transactional
publicint updateUser(UserVO userVO) {
TUser entity = new TUser();
BeanUtils.copyProperties(userVO, entity);
returnuserMapper.updateByPrimaryKeySelective(entity);
}
/**
*
* <p>Description:根据用户id查询用户信息</p>
* @param id 主键ID
* @return用户VO
*/
public UserVO getUserById(Long id) {
TUser entity = userMapper.selectByPrimaryKey(id);
UserVO userVO = new UserVO();
BeanUtils.copyProperties(entity, userVO);
returnuserVO;
}
/**
*
* <p>Description:根据用户ID删除用户信息</p>
* @param id 主键ID
* @return影响记录条数 1-成功 0-失败
*/
@Transactional
publicint delUser(Long id) {
returnuserMapper.deleteByPrimaryKey(id);
}
/**
* <p>Description: 根据用户名查询用户信息列表(支持模糊查询)</p>
* @param name 用户名
* @return用户对象列表
*/
public List<UserVO> getUserByName(String name) {
Example example = new Example(TUser.class);
example.createCriteria().andLike("name", name);
List<TUser> list = userMapper.selectByExample(example);
List<UserVO> userVoList = new ArrayList<>();
for (TUser entity : list) {
UserVO userVo = new UserVO();
BeanUtils.copyProperties(entity, userVo);
userVoList.add(userVo);
}
returnuserVoList;
}
/**
*
* <p>Description: 根据年龄精确查找用户信息列表</p>
* @param age 年龄
* @param pageRequest 分页请求对象
* @return用户对象列表分页
*/
public PageInfo<UserVO> getUsersByAge(intage, PageRequest pageRequest) {
Example example = new Example(TUser.class);
example.createCriteria().andEqualTo("age", age);
example.orderBy("grade").desc();
PageHelper.startPage(pageRequest.getPageNow(), pageRequest.getPageSize());
List<TUser> list = userMapper.selectByExample(example);
PageInfo<UserVO> pageInfo = new PageInfo(list);
List<UserVO> userVoList = new ArrayList();
for (TUser entity : list) {
UserVO userVo = new UserVO();
BeanUtils.copyProperties(entity, userVo);
userVoList.add(userVo);
}
pageInfo.setList(userVoList);
returnpageInfo;
}
}
package com.svw.tbox.tcloud.user.consumer.controller;
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.svw.tbox.tcloud.common.vo.PageRequest;
import com.svw.tbox.tcloud.common.web.Result;
import com.svw.tbox.tcloud.user.consumer.entity.TUser;
import com.svw.tbox.tcloud.user.consumer.service.UserService;
import com.svw.tbox.tcloud.user.consumer.vo.UserVO;
@RestController
//@Api(description= "用户服务接口")
publicclass UserController {
@Autowired
private UserService userService;
@RequestMapping(value = "/users", method = RequestMethod.POST)
public Result add(String userName, intage, intsex, String grade) {
TUser TUser = new TUser();
TUser.setName(userName);
TUser.setAge(age);
TUser.setSex(age == 1 ? true : false);
TUser.setGrade(grade);
return Result.success(userService.add(TUser));
}
@RequestMapping(value = "/users/{id}", method = RequestMethod.GET)
public Result findUserById(@PathVariable("id") Long id) {
return Result.success(userService.getUserById(id));
}
@RequestMapping(value = "/users/{id}", method = RequestMethod.PUT)
public Result findUserByName(@PathVariable("id") Long id, String userName, intage, intsex, String grade) {
UserVO userVO = new UserVO();
userVO.setId(id);
userVO.setName(userName);
userVO.setAge(age);
userVO.setGrade(grade);
return Result.success(userService.updateUser(userVO));
}
@RequestMapping(value = "/users/{id}", method = RequestMethod.DELETE)
public Result delUser(@PathVariable("id") Long id) {
return Result.success(userService.delUser(id));
}
@RequestMapping(value = "/users/getByAge/{age}", method = RequestMethod.GET)
public Result findUsers(@PathVariable("age") intage, intpageNo, intpageSize) {
PageRequest page = new PageRequest(pageNo, pageSize);
return Result.success(userService.getUsersByAge(age, page));
}
}
实体对象：实体对象跟普通类型参数传递方法一样，只是在用的时候，以 对象名.（点）对象属性名 的方式调用就可以了。
效果：
===========================================================
Mybatis:
Mapper添加查询：
<select id="queryLoginUserVo" parameterType="com.svw.tbox.tcloud.security.auth.vo.UserTokenVo"
resultType="com.svw.tbox.tcloud.security.auth.vo.UserTokenVo">
SELECT
a.refresh_token AS refreshToken,
a.refresh_token_deadline AS
refreshTokenDeadline,
a.access_token AS accessToken,
a.access_token_deadline AS accessTokenDeadline,
a.uid,
b.id,
b.username,
b.password,
b.name,
b.id_card_no AS idCardNo,
b.birthday,
b.address,
b.mobile_phone AS mobilePhone,
b.tel_phone AS telPhone,
b.email,
b.sex,
b.type,
b.status,
b.upd_pwd_time AS
updPwdTime
FROM user_token a inner join user
b on a.uid=b.id
<where>
<if test="vo!=null">
<if
test="vo.username!=null and vo.username!='' and vo.password!=null and vo.password!=''">
and b.username=#{vo.username} and b.password=#{vo.password}
</if>
<if test="vo.refreshToken!=null and vo.refreshToken!=''">
and a.refresh_token=#{vo.refreshToken}
</if>
</if>
</where>
</select>
package com.svw.tbox.tcloud.security.auth.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.svw.tbox.tcloud.security.auth.entity.User;
import com.svw.tbox.tcloud.security.auth.vo.UserTokenVo;
import tk.mybatis.mapper.common.Mapper;
publicinterface UserMapper extends Mapper<User> {
/**
* <p>Title: 关联查询获取票剧信息</p>
* <p>Description: vo 对应sql中的参数#{vo}</p>
* @param uo
* @return
*/
public List<UserTokenVo> queryLoginUserVo(@Param("vo") UserTokenVo uo);
}
//调用：
@Service
publicclass AuthService {
@Autowired
private UserMapper userMapper;
@Value("gateway.token.expiration")
privateintexpiration;
@Value("gateway.token.skey")
private String skey;
/**
* <p>Title: 查询要登录的用户信息</p>
* <p>Description: </p>
* @param username
* @param password
*/
public UserTokenVo queryLoginUserVo(String username, String password) {
// 从数据库找，目前给一个固定的
UserTokenVo uo = new UserTokenVo(username, password);
returnuserMapper.queryLoginUserVo(uo).get(0);
}
Vo:
publicclass UserTokenVo implements Serializable{
privatestaticfinallongserialVersionUID = -6795714101384945276L;
/**
* 用户id
*/
private Long id;
/**
* 用户名（登录名）
*/
private String username;
/**
* 密码
*/
private String password;
/**
* 姓名
*/
private String name;
/**
* 身份证号
*/
private String idCardNo;
/**
* 账户状态（0：已注销、1：正常）
*/
private String status;
/**
* 密码修改时间
*/
private Date updPwdTime;
/**
* 身份凭据
*/
private String refreshToken;
/**
* 身份凭据过期时间
*/
private String refreshTokenDeadline;
/**
* 访问凭据
*/
private String accessToken;
/**
* 访问凭据过期时间
*/
private String accessTokenDeadline;
……
publicinterface TmAccountMapper extends Mapper<TmAccount> {
/**
* <p>Title: 关联查询获取用户信息</p>
* <p>Description: uid 对应sql中的参数#{uid}</p>
* @param uid 用户代码
* @return
*/
public List<UserInfoVo> queryUserInfoVo(@Param("uids") List<String> uids);
}
<select id="queryUserInfoVo" parameterType="java.util.List"
resultType="com.svw.tbox.tcloud.mds.vo.UserInfoVo">
SELECT
b.`uid`,
b.`name`,
b.`spin`,
b.`urgent_contact` AS urgentContact,
b.`urgent_mobile` AS urgentMobile,
a.`username`,
a.`password`,
a.`nick_name` AS nickName,
a.`id_card_no` AS idCardNo,
a.`birthday`,
a.`address`,
a.`mobile`,
a.`tel_phone` AS telPhone,
a.`mail_addr` AS
mailAddr,
a.`gender`,
a.`auth`,
a.`type`,
a.`avatar`
FROM
`svw_mds`.`tm_user` a INNER JOIN `svw_mds`.`tm_account` b on a.uid =
b.uid
<where>
<if test="uids!=null">
and a.uid in
<foreach collection="uids" index="index" item="item" open="("
separator="," close=")">
#{item}
</foreach>
</if>
</where>
</select>
