spring boot:访问数据库

      <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>

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

<?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>

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);

    }

}

@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));

    }

}

实体对象：实体对象跟普通类型参数传递方法一样，只是在用的时候，以 对象名.（点）对象属性名 的方式调用就可以了。

<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>