SpringBoot入门建站全系列(三)Mybatis操作数据库

SpringBoot入门建站全系列(三)Mybatis操作数据库

SpringBoot操作数据库有多种方式,如

JDBC直接操作:太古老了,没人愿意这样玩

Mybatis插件:比较时髦,比较适合sql复杂,或者对性能要求高的应用,因为sql都是自己写的。

Spring-data-jpa: 使用hibernate作为实现,基本上不需要写sql,因为sql都是统计的,总是会产生多余的查询,性能上相对而言会低,但不绝对,影响性能的因素是多种的,这里说的性能是 从最终的查询的sql来对比的,毕竟生成的sql没有经过深思熟虑写出来的性能好。

JdbcTemplate:spring在jdbc上面做了深层次的封装,使用spring的注入功能,可以把DataSource注册到JdbcTemplate之中。Spring-data-jpa引入的时候,JdbcTemplate必然会被引入的。

当然还有其他中间件,主流使用的就是Mybatis和Spring-data-jpa。索引本篇先讲Mybatis。

品茗IT-SpringBoot专题-同步发布

品茗IT 提供在线支持:

一键快速构建Spring项目工具

一键快速构建SpringBoot项目工具

一键快速构建SpringCloud项目工具

一站式Springboot项目生成

Mysql一键生成Mybatis注解Mapper

一、引入依赖

需要同时引入数据库的connector和数据源datasource,当然也可以使用mybatis自己实现的数据源,但是还是以第三方数据源最好,毕竟经过大家的认可。

<dependency>

    <groupId>mysql</groupId>

    <artifactId>mysql-connector-java</artifactId>

</dependency>

<dependency>

    <groupId>org.mybatis.spring.boot</groupId>

    <artifactId>mybatis-spring-boot-starter</artifactId>

    <version>1.3.2</version>

</dependency>

<dependency>

    <groupId>org.apache.commons</groupId>

    <artifactId>commons-dbcp2</artifactId>

</dependency>

完整的依赖如下所示:

<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>cn.pomit</groupId>

    <artifactId>testboot</artifactId>

    <version>0.0.1-SNAPSHOT</version>

    <packaging>jar</packaging>

    <parent>

        <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-parent</artifactId>

        <version>2.0.4.RELEASE</version>

        <relativePath /> <!-- lookup parent from repository -->

    </parent>

    <name>testboot</name>

    <url>http://maven.apache.org</url>



    <properties>

        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

    </properties>



    <dependencies>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-web</artifactId>

        </dependency>

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

        </dependency>

        <dependency>

            <groupId>org.mybatis.spring.boot</groupId>

            <artifactId>mybatis-spring-boot-starter</artifactId>

            <version>1.3.2</version>

        </dependency>

        <dependency>

            <groupId>org.apache.commons</groupId>

            <artifactId>commons-dbcp2</artifactId>

        </dependency>

    </dependencies>

    <build>

        <plugins>

            <plugin>

                <groupId>org.springframework.boot</groupId>

                <artifactId>spring-boot-maven-plugin</artifactId>

            </plugin>

        </plugins>

    </build>

</project>

二、配置数据库连接信息

spring.datasource.driverClassName = com.mysql.jdbc.Drive

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/boot?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC

spring.datasource.username=cff

spring.datasource.password=123456



spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource

spring.datasource.dbcp2.max-wait-millis=60000

spring.datasource.dbcp2.min-idle=20

spring.datasource.dbcp2.initial-size=2

spring.datasource.dbcp2.validation-query=SELECT 1

spring.datasource.dbcp2.connection-properties=characterEncoding=utf8

spring.datasource.dbcp2.validation-query=SELECT 1

spring.datasource.dbcp2.test-while-idle=true

spring.datasource.dbcp2.test-on-borrow=true

spring.datasource.dbcp2.test-on-return=false



mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

三、使用Mybatis的Mappe

3.1 表与Java实体

假设我们有一张这个表user_role :

在这里插入图片描述

实体:

import java.io.Serializable;



public class UserRole implements Serializable {

    private static final long serialVersionUID = 1L;



    private Integer id;



    private String role;



    private String userName;



    private String phone;



    public String getUserName() {

        return userName;

    }



    public void setUserName(String userName) {

        this.userName = userName;

    }



    public UserRole() {

    }



    public Integer getId() {

        return id;

    }



    public void setId(Integer id) {

        this.id = id;

    }



    public String getRole() {

        return this.role;

    }



    public void setRole(String role) {

        this.role = role;

    }



    @Override

    public String toString() {

        return "UserRole [id=" + id + ", role=" + role + ", userName=" + userName + "]";

    }



    public String getPhone() {

        return phone;

    }



    public void setPhone(String phone) {

        this.phone = phone;

    }



}

3.2 使用注解方式写sql

import java.util.List;



import org.apache.ibatis.annotations.Delete;

import org.apache.ibatis.annotations.Insert;

import org.apache.ibatis.annotations.Mapper;

import org.apache.ibatis.annotations.Options;

import org.apache.ibatis.annotations.Param;

import org.apache.ibatis.annotations.Select;

import org.apache.ibatis.annotations.Update;



import cn.pomit.testboot.domain.UserRole;



@Mappe

public interface UserRoleMapper {

    @Insert({"<script> ",

        "INSERT INTO user\_role",

        "( phone,",

        "userName ,",

        "role",

         ") ",

        " values ",

         "( #{phone},",

         "#{userName},",

         "#{role}",

        " ) ",

         "</script>"})

    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")

    int saveTest(UserRole userRole);

    

    @Select({

        "<script>",

            "SELECT ",

            "id as id,",

            "userName as userName,",

            "role as role",

            "FROM user\_role",

       "</script>"})

    List<UserRole> selectAll();

    

    @Update({

        "<script>",

        " update user\_role set",

        " phone = #{phone, jdbcType=VARCHAR}",

        " where id=#{id}",

        "</script>"

    })

    int update(@Param("id") Integer id, @Param("phone") String phone);

    

    @Delete({

        "<script>",

        " delete from user\_role",

        " where id=#{id}",

        "</script>"

    })

    int delete(@Param("id") Integer id);

}

其中,插入操作中的语句:

@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")

是用来回显ID的,方便保存后回查。

3.3 使用xml方式写sql

使用xml方式写sql,需要先在SpringBoot读取的配置文件(可以放在环境相关的配置文件中,也可以直接放在application.properties文件)中加入:

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

这条配置指定了xml的配置在classpath下的mapper文件夹下。

首先建立一个mapper接口:

import java.util.List;



import org.apache.ibatis.annotations.Mapper;



import cn.pomit.testboot.domain.UserRole;



@Mappe

public interface UserRoleInfoMapper {

    int saveTest(UserRole userRole);

    

    List<UserRole> selectAll();

    

    int update(Integer id, String phone);



    int delete(Integer id);

}

在配置文件mapper文件夹下建立UserRoleInfoMapper.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="cn.pomit.testboot.mapper.UserRoleInfoMapper" >

    <resultMap id="BaseResultMap" type="cn.pomit.testboot.domain.UserRole" >

        <id property="id" column="id" />

        <result column="role" jdbcType="VARCHAR" property="role" />

        <result column="userName" jdbcType="VARCHAR" property="userName" />

        <result column="phone" jdbcType="VARCHAR" property="phone" />

    </resultMap>

    

     <select id="selectAll" resultMap="BaseResultMap">

      SELECT \* FROM user\_role

    </select>

    

    <insert id="saveTest" useGeneratedKeys="true" keyProperty="id">

        INSERT INTO user\_role( phone,userName ,role )  values  ( #{phone}, #{userName}, #{role} )

    </insert>

    

    <update id="update">

      update user\_role set

        phone = #{param2}

      where id = #{param1}

    </update>



    <delete id="delete" parameterType="java.lang.Integer">

      delete from user\_role where id = #{id}

    </delete>

</mapper>

这里,多参数传递的时候,刚开始用#{0},#{1}这种形式取值,出现:

nested exception is org.apache.ibatis.binding.BindingException: Parameter '1' not found. Available parameters are [arg1, arg0, param1, param2]

这种异常,可能是版本升级,取参方式改变了,那就按照它说的换成param或者arg就行,如果怕出问题,就直接在接口的参数前加上@Param注解,xml中以名称来取变量即可。

四、service逻辑调用Mapper。

UserRoleService:

import java.util.List;



import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;



import cn.pomit.testboot.domain.UserRole;

import cn.pomit.testboot.mapper.UserRoleInfoMapper;



@Service

public class UserRoleService {

    @Autowired

    UserRoleInfoMapper userRoleMapper;



    // @Autowired

    // UserRoleMapper userRoleMapper;



    public List<UserRole> selectAll() {

        return userRoleMapper.selectAll();

    }



    public int saveTest(UserRole userRole) {

        return userRoleMapper.saveTest(userRole);

    }



    public int update(Integer id, String phone) {

        return userRoleMapper.update(id, phone);

    }



    public int delete(Integer id) {

        return userRoleMapper.delete(id);

    }

}

五、开放接口调用service

MybatisRest:

import java.util.List;



import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;



import cn.pomit.testboot.domain.UserRole;

import cn.pomit.testboot.service.UserRoleService;



@RestControlle

@RequestMapping("/db")

public class MybatisRest {

    @Autowired

    UserRoleService userRoleService;



    @RequestMapping(value = "/query")

    public List<UserRole> query() {

        return userRoleService.selectAll();

    }

    

    @RequestMapping(value = "/save")

    public Object save() {

        UserRole userRole = new UserRole();

        userRole.setRole("TEST");

        userRole.setUserName("TEST");

        userRole.setPhone("3424234");

        return userRoleService.saveTest(userRole );

    }

    

    @RequestMapping(value = "/update")

    public Object update() {

        return userRoleService.update(4,"454");

    }

    

    @RequestMapping(value = "/delete")

    public Object delete() {

        return userRoleService.delete(4);

    }

    

}

喜欢这篇文章么,喜欢就加入我们一起讨论SpringBoot技术吧!

    ![品茗IT技术交流群](https://upload-images.jianshu.io/upload\_images/15296705-b024ad1fce668926.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券