springboot-27-整合mybatis,druid连接池

sprinboot整合mybatis, 有2种方式, 第一种完全使用注解的方式, 还有一种就是使用xml文件的方式

项目使用gradle + idea, 数据源使用druid, 多使用groovy编写

环境配置

1, 依赖

dependencies {
    compile("org.springframework.boot:spring-boot-devtools")
    compile("org.springframework.boot:spring-boot-starter")
    compile("org.springframework.boot:spring-boot-starter-web")
    compile("org.springframework.boot:spring-boot-starter-log4j2")
    compile("com.fasterxml.jackson.dataformat:jackson-dataformat-yaml")
    compile("org.codehaus.groovy:groovy-all:2.4.11")

    compile 'mysql:mysql-connector-java'
    compile 'com.alibaba:druid-spring-boot-starter:1.1.2'
    compile 'org.mybatis.spring.boot:mybatis-spring-boot-starter:1.2.0'

    compile 'javax.inject:javax.inject:1'

    testCompile group: 'junit', name: 'junit', version: '4.12'
    testCompile("org.springframework.boot:spring-boot-starter-test")

}

2, user-schame.sql

在springboot的配置文件中, 增加 schema, 可以在程序启动时创建数据表, 插入数据等操作

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `userName` varchar(32) DEFAULT NULL COMMENT '用户名',
  `passWord` varchar(32) DEFAULT NULL COMMENT '密码',
  `user_sex` varchar(32) DEFAULT NULL,
  `nick_name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;

3, application.yml

spring: 
  profiles: 
    active: dev

  http: 
        encoding:
          charset: UTF-8
          force: true
          enabled: true

server:
  tomcat:
    uri-encoding: UTF-8

---
# 开发环境
spring: 
  profiles: dev
  datasource:
    url: jdbc:mysql://localhost:3306/springboot
    username: root
    password: root
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
#    schema: classpath:user-schema.sql
#    data: classpath:user-data.sql

server: 
  context-path: /security
  port: 8010

---
# 测试环境配置
spring: 
  profiles: qa

---
# 生产环境配置
spring: 
  profiles: prod

4, Main.groovy

package com.wenbronk.security

import org.mybatis.spring.annotation.MapperScan
import org.springframework.boot.SpringApplication
import org.springframework.boot.autoconfigure.SpringBootApplication
/**
 * Created by wenbronk on 2017/8/14.
 */
@SpringBootApplication
@MapperScan("com.wenbronk.security.mapper")
class SecurityApplication {
    static void main(String[] args) {
        SpringApplication.run(SecurityApplication.class)
    }
}

 

5, UserEntity.groovy

package com.wenbronk.security.entity
/**
 * Created by wenbronk on 2017/8/14.
 */
class UserEntity {
    def id
    def userName
    def passWord
    def userSex
    def nickName

    @Override
    public String toString() {
        return "UserEntity{" +
                "id=" + id +
                ", userName=" + userName +
                ", passWord=" + passWord +
                ", userSex=" + userSex +
                ", nickName=" + nickName +
                '}';
    }
}

6, UserSexEnum

package com.wenbronk.security.enums;

/**
 * Created by wenbronk on 2017/8/14.
 */
public enum UserSexEnum {
    MAN,
    WOMAN
}

 

完全使用注解的方式: 

1, usermapper

package com.wenbronk.security.mapper;

import com.wenbronk.security.entity.UserEntity;
import com.wenbronk.security.enums.UserSexEnum;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * Created by wenbronk on 2017/8/14.
 */
public interface UserMapper {

    @Select("SELECT * FROM users")
    @Results({
            @Result(property = "userSex",  column = "user_sex", javaType = UserSexEnum.class),
            @Result(property = "nickName", column = "nick_name")
    })
    List<UserEntity> findAll();

    @Select("SELECT * FROM users WHERE id = #{id}")
    @Results({
            @Result(property = "userSex",  column = "user_sex", javaType = UserSexEnum.class),
            @Result(property = "nickName", column = "nick_name")
    })
    UserEntity findOne(Long id);

    @Insert("INSERT INTO users(userName,passWord,user_sex) VALUES(#{userName}, #{passWord}, #{userSex})")
    void insert(UserEntity user);

    @Update("UPDATE users SET userName=#{userName},nick_name=#{nickName} WHERE id =#{id}")
    void update(UserEntity user);

    @Delete("DELETE FROM users WHERE id =#{id}")
    void delete(Long id);

}

2, 测试类

package com.wenbronk.security.test

import com.wenbronk.security.entity.UserEntity
import com.wenbronk.security.enums.UserSexEnum
import com.wenbronk.security.mapper.UserMapper
import org.junit.Test
import org.junit.runner.RunWith
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner

import javax.inject.Inject
/**
 * Created by wenbronk on 2017/8/14.
 */
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
class MybatisTest {

    @Inject
    UserMapper userMapper;

    @Test
    void insert() {
        userMapper.insert(new UserEntity(userName: 'vini', passWord: '123', userSex: UserSexEnum.WOMAN, nickName: 'H'))
        userMapper.insert(new UserEntity(userName: 'bronk', passWord: '123', userSex: UserSexEnum.MAN, nickName: 'H'))
    }

    @Test
    void query() {
        def find = userMapper.findAll()
        println find
    }

}

更多注解请移步: http://www.mybatis.org/mybatis-3/zh/java-api.html 

使用xml的方式

1, application.yml中添加

mybatis.config-locations=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml

2, mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <typeAlias alias="Integer" type="java.lang.Integer" />
        <typeAlias alias="Long" type="java.lang.Long" />
        <typeAlias alias="HashMap" type="java.util.HashMap" />
        <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" />
        <typeAlias alias="ArrayList" type="java.util.ArrayList" />
        <typeAlias alias="LinkedList" type="java.util.LinkedList" />
        <package name="com.wenbronk.security.entity"/>
    </typeAliases>
</configuration>

3, user的映射文件

<?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.wenbronk.security.mapper.UserEntityMapper" >
    <resultMap id="BaseResultMap" type="UserEntity" >
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="userName" property="userName" jdbcType="VARCHAR" />
        <result column="passWord" property="passWord" jdbcType="VARCHAR" />
        <result column="user_sex" property="userSex" javaType="com.wenbronk.security.enums.UserSexEnum"/>
        <result column="nick_name" property="nickName" jdbcType="VARCHAR" />
    </resultMap>

    <sql id="Base_Column_List" >
        id, userName, passWord, user_sex, nick_name
    </sql>

    <select id="getAll" resultMap="BaseResultMap"  >
        SELECT
        <include refid="Base_Column_List" />
        FROM users
    </select>

    <select id="getOne" parameterType="java.lang.Long" resultMap="BaseResultMap" >
        SELECT
        <include refid="Base_Column_List" />
        FROM users
        WHERE id = #{id}
    </select>

    <insert id="insert" parameterType="UserEntity" >
        INSERT INTO
        users
        (userName,passWord,user_sex)
        VALUES
        (#{userName}, #{passWord}, #{userSex})
    </insert>

    <update id="update" parameterType="UserEntity" >
        UPDATE
        users
        SET
        <if test="userName != null">userName = #{userName},</if>
        <if test="passWord != null">passWord = #{passWord},</if>
        nick_name = #{nickName}
        WHERE
        id = #{id}
    </update>

    <delete id="delete" parameterType="java.lang.Long" >
        DELETE FROM
        users
        WHERE
        id =#{id}
    </delete>
</mapper>

4, dao代码

public interface UserMapper {

    List<UserEntity> getAll();

    UserEntity getOne(Long id);

    void insert(UserEntity user);

    void update(UserEntity user);

    void delete(Long id);

}

 

代码地址: https://github.com/wenbronk/springboot-test/tree/master/security-mybatis/src

druid 的更多配置: https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter 

 

原博客地址: http://blog.csdn.net/gebitan505/article/details/54929287