编程

当前位置:永利皇宫463登录 > 编程 > SpringBoot集成Mybatis(四)

SpringBoot集成Mybatis(四)

来源:http://www.makebuLuo.com 作者:永利皇宫463登录 时间:2019-10-11 09:54

图片 1

Pom

<!-- 移除 tomcat-jdbc, Spring Boot 将会自动使用 HikariCP -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>2.6.3</version>
</dependency>

<!-- 数据库选用 Mariadb -->
<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>2.1.0</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.1</version>
    <!-- 移除 Tomcat 的jdbc连接池,使用 HikariCP  -->
    <exclusions>
        <exclusion>
            <groupId>org.apache.tomcat</groupId>
            <artifactId>tomcat-jdbc</artifactId>
        </exclusion>
    </exclusions>
</dependency>

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.6</version>
</dependency>

MyBatis-Spring-Boot-Starter依赖将会:

  • 自动物检疫验现存的DataSource
  • 将创造并登记SqlSessionFactory的实例,该实例使用SqlSessionFactoryBean将该DataSource作为输入举行传递
  • 将开创并登记从SqlSessionFactory中猎取的SqlSessionTemplate的实例。
  • 活动扫描mappers,将它们链接到SqlSessionTemplate并将其注册到Spring上下文,以便将其注入到你的bean中。

实属,使用了该Starter之后,只供给定义二个DataSource就可以(application.properties中可安插),它会自动创制使用该DataSource的SqlSessionFactoryBean以致SqlSessionTemplate。会活动扫描你的Mappers,连接受SqlSessionTemplate,并登记到Spring上下文中。


SpringBoot Mybatis 集成

八个简短的SpringBoot项目,用到了有的和好常用的本领。
数据库连接池为HiKariCP
Mybatis集成了通用Mapper和PageHelper
Lombok

Mybatis是一款扶植复杂的SQL语句,存款和储蓄进程及高端映射的长久层的框架。使用Mybatis有三种方法,XML和注释。

Mybatis早期使用相比辛劳,供给过多配备文件、实体类、dao层映射、还应该有为数不菲任何的陈设。早期开垦使用generator能够依照表结构自动生产实体类、dao层代码,那样是足以缓和部分开荒量;早先时期mybatis进行大批量的优化,今后能够利用证明版本,自动管理dao层和配置文件。 mybatis-spring-boot-starte纵使SpringBoot集成Mybatis的jar包,能够完全使用评释,不供给配置文件,轻松布置轻便上手。

自定义数据源

@SpringBootApplication
public class SbmybatisApplication {

    @Autowired
    Environment environment;

    public static void main(String[] args) {
        SpringApplication.run(SbmybatisApplication.class, args);
    }

    @Bean(destroyMethod = "shutdown")
    public DataSource dataSource() {
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setJdbcUrl(environment.getProperty("spring.datasource.hikari.jdbc-url"));
        hikariDataSource.setUsername(environment.getProperty("spring.datasource.hikari.username"));
        hikariDataSource.setPassword(environment.getProperty("spring.datasource.hikari.password"));
        hikariDataSource.setMaximumPoolSize(Integer.parseInt(environment.getProperty("spring.datasource.hikari.maximum-pool-size")));
        hikariDataSource.setMinimumIdle(Integer.parseInt(environment.getProperty("spring.datasource.hikari.minimum-idle")));
        hikariDataSource.setConnectionTestQuery(environment.getProperty("spring.datasource.hikari.connection-test-query"));
        hikariDataSource.setIdleTimeout(Long.parseLong(environment.getProperty("spring.datasource.hikari.idle-timeout")));
        hikariDataSource.setMaxLifetime(Long.parseLong(environment.getProperty("spring.datasource.hikari.max-lifetime")));
        hikariDataSource.setConnectionTimeout(Long.parseLong(environment.getProperty("spring.datasource.hikari.connection-timeout")));
        return hikariDataSource;
    }
}

一、用Gradle创设项目,并在IDEA中展开

常常来讲为build.gradle文件内容:

buildscript {
    repositories {
        mavenLocal()
        maven { url 'https://plugins.gradle.org/m2/' }  // 用于下载Gradle插件
        maven { url 'http://jcenter.bintray.com/' }
        maven { url 'http://repo1.maven.org/maven2/' }
        maven { url 'http://repo.spring.io/snapshot' }
        maven { url 'http://repo.spring.io/milestone' }
        maven { url 'http://repo.spring.io/libs-milestone/' }
        maven { url 'http://repo.spring.io/release' }
    }
    dependencies {
        classpath 'org.springframework.boot:spring-boot-gradle-plugin:1.5.2.RELEASE' // org.springframework.boot的构建版本控制
        classpath 'io.spring.gradle:dependency-management-plugin:1.0.1.RELEASE' // 用于管理jar包之间的依赖问题
    }
}

apply plugin: 'java'                            //  java插件,同时也声明是一个java项目
apply plugin: 'idea'                            //  idea插件,可直接让IDEA集成能力
apply plugin: 'org.springframework.boot'        //  管理这个组织的jar包插件
apply plugin: 'io.spring.dependency-management' //  管理jar包之间的依赖关系


idea {
    module {
        inheritOutputDirs = false
        outputDir = file("$buildDir/classes/main/")             //  在idea中build后输出的class目录
    }
}

jar {
    baseName = 'springboot'
    version = new Date().format("yyyy-MM-dd_HH:mm").toString()  //  根据当前时间控制版本
    // e.g. Gradle是基于Groovy的构建语言,本身也是JVM语言
}

bootRun {
    addResources = false
    systemProperties = System.properties
    main = 'com.avcdata.Application'        // 程序运行的主文件,每个项目需指定一个运行入口
}

repositories {
    mavenLocal()
    maven { url 'http://jcenter.bintray.com/' }
    maven { url 'http://repo1.maven.org/maven2/' }
    maven { url 'http://repo.spring.io/snapshot' }
    maven { url 'http://repo.spring.io/libs-milestone/' }
    maven { url 'http://repo.spring.io/release' }
    maven { url 'http://repo.spring.io/milestone' }
}

dependencies {
    compile 'com.zaxxer:HikariCP:2.6.1'
    compile 'org.projectlombok:lombok:1.16.12'
    compile 'mysql:mysql-connector-java:6.0.5'

    compile 'org.springframework.boot:spring-boot-starter-web'

    compile 'org.mybatis.spring.boot:mybatis-spring-boot-starter:1.2.0'
    compile 'tk.mybatis:mapper-spring-boot-starter:1.0.0'
    compile 'com.github.pagehelper:pagehelper-spring-boot-starter:1.1.0'

    testCompile ( 'org.springframework.boot:spring-boot-starter-test' )
}

1、添加pom.xml文件

伊始化脚本

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint(11) NOT NULL,
  `username` varchar(9) NOT NULL,
  `password` varchar(10) NOT NULL,
  `uri` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '冀永光', 'jyg0723', 'http://www.jiyongguang.xin/');
INSERT INTO `user` VALUES ('2', '李亚男', 'lyn0723', 'http://www.jiyongguang.xin/');

二、在类型中合而为一一些做事

 <!--mybatis jar--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <!--数据库--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.26</version> </dependency>

Model

@Data
@AllArgsConstructor
public class User {

    private long id;
    private String username;
    private String password;
    private String uri;
}

①引进通用mapper,在钦命地点。

图片 2

如图所示:在com.avcdata.util下
当中MyMapper的源代码为:

package com.avcdata.util;/*
 * The MIT License (MIT)
 *
 * Copyright (c) 2014-2016 abel533@gmail.com
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 */


import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

/**
 * 继承自己的MyMapper
 *
 * @author liuzh
 * @since 2015-09-06 21:53
 */
public interface MyMapper<T> extends Mapper<T>, MySqlMapper<T> {
    //TODO
    //FIXME 特别注意,该接口不能被扫描到,否则会出错
}

2、配置application.properties

Controller

@Controller
@Log4j2
@RequestMapping(path = "/user")
public class UserController {

    @Autowired
    UserService userService;

    @RequestMapping(path = "/query", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
    @ResponseBody
    public String queryAllUser() {
        log.info("UserController -/ queryAllUser");
        String userString = userService.queryAllUser().toString();
        log.info("userList", userService.queryAllUser());
        return JsonUtil.getJsonString(JsonUtil.REQUEST_SUCCESS, userString);
    }

    @RequestMapping(path = "/add", method = RequestMethod.POST)
    @ResponseBody
    public String addUser(@RequestParam String username,
                          @RequestParam String password,
                          @RequestParam String uri) {
        log.info("UserController -/ addUser");
        log.info("username:" + username + " - password:" + password + " - uri:" + uri);
        return JsonUtil.getJsonString(userService.addUser(username, password, uri));
    }

    @RequestMapping(path = "/update", method = RequestMethod.POST)
    @ResponseBody
    public String updateUser(@RequestParam String username,
                             @RequestParam String password,
                             @RequestParam String uri,
                             @RequestParam long id) {
        log.info("UserController -/ updateUser");
        log.info("username:" + username + " - password:" + password + " - uri:" + uri + " - id:" + id);
        return JsonUtil.getJsonString(userService.updateUser(username, password, uri, id));
    }

    @RequestMapping(path = "/delete", method = RequestMethod.POST)
    @ResponseBody
    public String deleteUser(@RequestParam String id) {
        log.info("UserController -/ deleteUser - id: " + id, id);
        return JsonUtil.getJsonString(userService.deleteUser(Long.parseLong(id)));
    }

    @RequestMapping(path = "/queryone", method = RequestMethod.GET)
    public String queryUserById(@RequestParam String id,
                                Model model) {
        log.info("UserController -/ queryUserById - id: " + id, id);
        User user = userService.queryUserById(Long.parseLong(id));
        model.addAttribute("user", user);
        return "update";
    }
}

②在resources下开创叁个mapper目录

图片 3

image.png

#指定服务端口server.port=8089## 数据源配置spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8spring.datasource.username=rootspring.datasource.password=rootspring.datasource.driver-class-name=com.mysql.jdbc.Driver

声明情势

@Mapper
@Component
@SuppressWarnings("ALL")
public interface UserDao {

    @Select("select * from user where id = #{id}")
    User queryUserById(@Param("id") Long id);

    @Select("select * from user")
    @Results(id = "userList", value = {
            @Result(id = true, column = "id", property = "id", javaType = Long.class, jdbcType = JdbcType.BIGINT),
            @Result(column = "username", property = "username", javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(column = "password", property = "password", javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(column = "uri", property = "uri", javaType = String.class, jdbcType = JdbcType.VARCHAR)
    })
    List<User> queryAllUser();

    // 模糊查询
    @SelectProvider(type = UserSqlBuilder.class, method = "queryUserByParams")
    List<User> queryUserByCondition(Map<String, Object> params);

    @Insert("insert into user(username,password,uri) values(#{username},#{password},#{uri}) ")
    Integer addUser(@Param("username") String username,
                    @Param("password") String password, @Param("uri") String uri);

    @Update("update user set username=#{username},password=#{password},uri=#{uri} where id = #{id}")
    Integer updateUser(@Param("username") String username, @Param("password") String password,
                       @Param("uri") String uri, @Param("id") Long id);

    @Delete("delete from user where id = #{id}")
    Integer deleteUser(@Param("id") Long id);

    @DeleteProvider(type = UserSqlBuilder.class, method = "deleteUserById")
    Integer deleteUserById(@Param("ids") String[] ids);

    @Log4j2
    class UserSqlBuilder {
        public String queryUserByParams(final Map<String, Object> params) {
            StringBuilder sql = new StringBuilder("select * from user where 1=1");
            if (!StringUtils.isEmpty((String) params.get("username"))) {
                sql.append("and username like '%").append((String) params.get("username")).append("'%");
            } else if (!StringUtils.isEmpty((String) params.get("uti"))) {
                sql.append("and uri like '%").append((String) params.get("uri")).append("%'");
            }
            log.info("查询的sql语句:" + sql.toString());
            return sql.toString();
        }

        public String deleteUserById(final String[] ids) {
            StringBuilder sql = new StringBuilder("delete from user where id in(");
            for (int i = 0; i < ids.length; ++i) {
                if (i == ids.length - 1) {
                    sql.append(ids[i]);
                } else {
                    sql.append(ids[i]).append(",");
                }
            }
            sql.append(")");
            log.info("删除的sql语句:" + sql.toString());
            return sql.toString();
        }
    }
}

③书写application.yml文件

spring:
  datasource:
    url:  jdbc:mysql://192.168.2.200/jdbc_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      username: root
      password: new.1234
      max-lifetime: 1765000
      maximum-pool-size: 15
      minimum-idle: 2
      connection-timeout: 1765000
mybatis:
    type-aliases-package: com.avcdata.model  # 配置Bean对应的别名目录
    mapper-locations: classpath:mapper/*.xml  # 配置mapper对应目录
    configuration:
      call-setters-on-nulls: true          # 为null不清除
      map-underscore-to-camel-case: true   #开启驼峰命名
mapper:
    #通用Mapper目录
    mappers:
        - com.avcdata.util.MyMapper # 配置MyMapper目录
    not-empty: false
    identity: MYSQL
pagehelper:
    helperDialect: mysql
    reasonable: true
    supportMethodsArguments: true
    params: count=countSql
logging:
  # 日志打印级别配置
  config: classpath:logback.xml

springboot会自动加载spring.datasource.*相关安顿,数据源就能够自动注入到sqlSessionFactory中,sqlSessionFactory会自动注入到Mapper中。

在起步类中加多对mapper包扫描@MapperScan仍旧在每一种Mapper类中追加地点增加评释@Mapper,推荐在起步类加评释,这样不用在种种Mapper类加申明。

三、最初化业务相关的Dao层

@SpringBootApplication@MapperScan("com.example.mybatis.dao")public class Application { public static void main(String[] args) { SpringApplication.run(Application.class, args); }}

①、依据表结构,去建设构造叁个Bean

图片 4

user表

此为一张表,个中表名称叫user,id为主键,username和password为八个字段。所对应的Bean放在com.avcdata.model下,Bean名称叫User

package com.avcdata.model;

import lombok.*;
import org.apache.ibatis.type.Alias;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 *
 * Created by huangcheng on 2017/3/24.
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Alias(value = "user")
@Table(name = "user")
public class User {
    @Id
    private Integer id;
    @Column(name = "username")
    private String username; // 因为开启了驼峰命名,所以password不需要@column
    private String password;
}

3、Mapper

②创造对应的UserMapper

public interface UserMapper { /** * @Description: 查询用户列表 */ @Select("SELECT * FROM user") @Results({ @Result(property = "userName", column = "user_name"), @Result(property = "passWord", column = "pass_word"), @Result(property = "nickName", column = "nick_name"), @Result(property = "regTime", column = "reg_time") }) List<User> getAll(); /** * @Description: 保存用户信息 */ @Insert("INSERT INTO user(user_name,pass_word,email,nick_name,reg_time) VALUES(#{userName}, #{passWord}, #{email},#{nickName},#{regTime})") void insert(User user); /** * @Description: 修改用户信息 */ @Update("UPDATE user SET user_name=#{userName},nick_name=#{nickName} WHERE id =#{id}") void update(User user); /** * @Description: 删除用户信息 */ @Delete("DELETE FROM user WHERE id =#{id}") void delete;}

1、在com.avcdata.mapper下创设三个UserMapper

UserMapper继承MyMapper<User>
代码如下:

package com.avcdata.dao;

import com.avcdata.model.User;
import com.avcdata.util.MyMapper;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

/**
 * 
 * Created by huangcheng on 2017/3/24.
 */
//继承了通用MyMapper,则可以根据Bean实现简单的增删改查
@Mapper
public interface UserMapper extends MyMapper<User> {
    List<User> query(String s);
}

@Select是查询类的注释,全数的查询均使用那些@Result 修饰再次回到的结果集,关联实体类属性和数据库字段一一对应,要是实体类属性和数据库属性名保持一致,就没有供给这些天性来修饰。@Insert 插入数据库使用,间接传入实体类会自动剖析属性到相应的值@Update 担负修改,也能够平素传入对象@delete 担任删除

2、在resources中成立二个Usermapper.xml

代码如下:

<?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.avcdata.dao.UserMapper">

    <select id="query" parameterType="java.lang.String"
            resultType="user">
        SELECT
            username ,
            password
        FROM user
    </select>
</mapper>

至此,实现了Dao层。

4、测试 运行项目成功后输入查询全体顾客的接口。

四、轻易达成Controller和瑟维斯

图片 5

①、初阶化叁个Service命名称为UserService

源代码如下:

package com.avcdata.service;

import com.avcdata.dao.UserMapper;
import com.avcdata.model.PageInfoParam;
import com.avcdata.model.User;
import com.github.pagehelper.PageHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 *
 * Created by huangcheng on 2017/3/24.
 */
@Service
public class UserService {
    @Autowired
    private UserMapper userMapper;

    public List<User> query(PageInfoParam pageInfoParam){
        PageHelper.startPage(pageInfoParam.getPageSize(), pageInfoParam.getCurrentPage());
        return userMapper.selectAll();
    }
}

5、常见难题

②、伊始化二个Controller,命名字为:UserController

源代码如下:

package com.avcdata.controller;

import com.avcdata.model.PageInfoParam;
import com.avcdata.model.User;
import com.avcdata.service.UserService;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;


/**
 *
 * Created by huangcheng on 2017/3/24.
 */
@RestController
@RequestMapping(value = "/user")
@Slf4j
public class UserController {
    @Autowired
    UserService userService;

    @RequestMapping(value = "/query", method = RequestMethod.POST)
    public PageInfo<User> query(@RequestBody PageInfoParam pageInfoParam){
        log.info("currentPage:{}, pageSize:{}", pageInfoParam.getCurrentPage(), pageInfoParam.getPageSize());
        return new PageInfo<>(userService.query(pageInfoParam));
    }
}

图片 6

五、运维服务并测量试验

xml版本保持映射文件的老守旧,优化首要反映在无需完成dao的是完成层,而是在绚烂的xml文件中找到相应的sql语句。

①、运维服务

1、application.properties文件中加进安插

②、用postman发送伏乞

中间pageSize为每页数量,currentPage为当前页

图片 7

呼吁相关参数

回到拿到相对应的ResponseBody,带领了分页新闻的整整查询:

{
    "pageNum": 0,
    "pageSize": 0,
    "size": 1,
    "startRow": 1,
    "endRow": 1,
    "total": 1,
    "pages": 0,
    "list": [
        {
            "id": 1,
            "username": "hc",
            "password": "1234"
        }
    ],
    "prePage": 0,
    "nextPage": 0,
    "isFirstPage": false,
    "isLastPage": true,
    "hasPreviousPage": false,
    "hasNextPage": false,
    "navigatePages": 8,
    "navigatepageNums": [],
    "navigateFirstPage": 0,
    "navigateLastPage": 0,
    "firstPage": 0,
    "lastPage": 0
}

## Mybatis 配置##注意:对应实体类的路径mybatis.typeAliasesPackage=com.example.mybatis.bean.User##注意:一定要对应mapper映射xml文件的所在路径mybatis.mapperLocations=classpath:mapper/*.xml

上述就是和谐实现的一个简单易行的SpringBoot Web瑟维斯项目

源代码地址:https://github.com/OrangesHuang/springboot.git

2、Mapper接口和Mapper XML的投射关系

图片 8 3、Dao层

public interface UserMapper { /** * 通过用户id查询User **/ User selectUserByUserId; /** * 保存用户信息 **/ int createUser(User record); /** * 删除用户信息 **/ int deleteByUserId; /** * 修改用户信息 **/ int modifyUserByUserId(User record);}

Dao层未来拾分简洁,具体的sql语句都在mapper.xml文件中,具体代码小编这里就不贴了。

4、使用 新添效率,在url页面包车型地铁测量检验。

图片 9

注明版符合轻易急迅的形式。xml版比较切合大型项目,能够灵活的动态生成SQL,方便调解SQL

注解版:

本文由永利皇宫463登录发布于编程,转载请注明出处:SpringBoot集成Mybatis(四)

关键词: