SpringBoot入門建站全系列（三）Mybatis操作數(shù)據(jù)庫(kù)

時(shí)間：2023-08-01 06:06:02 | 來源：網(wǎng)站運(yùn)營(yíng)

時(shí)間：2023-08-01 06:06:02 來源：網(wǎng)站運(yùn)營(yíng)

SpringBoot入門建站全系列（三）Mybatis操作數(shù)據(jù)庫(kù)：

SpringBoot入門建站全系列（三）Mybatis操作數(shù)據(jù)庫(kù)

SpringBoot操作數(shù)據(jù)庫(kù)有多種方式，如

JDBC直接操作: 玩法太古老了，而且難免會(huì)忘記關(guān)閉連接。沒人愿意這樣玩

Mybatis插件：比較時(shí)髦，比較適合sql復(fù)雜，或者對(duì)性能要求高的應(yīng)用，因?yàn)閟ql都是自己寫的。

Spring-data-jpa： 使用hibernate作為實(shí)現(xiàn)，基本上不需要寫sql，因?yàn)閟ql都是統(tǒng)計(jì)的，總是會(huì)產(chǎn)生多余的查詢，性能上相對(duì)而言會(huì)低，但不絕對(duì)，影響性能的因素是多種的，這里說的性能是 從最終的查詢的sql來對(duì)比的，畢竟生成的sql沒有經(jīng)過深思熟慮寫出來的性能好。

JdbcTemplate：spring在jdbc上面做了深層次的封裝，使用spring的注入功能，可以把DataSource注冊(cè)到JdbcTemplate之中。Spring-data-jpa引入的時(shí)候，JdbcTemplate必然會(huì)被引入的。

當(dāng)然還有其他中間件，主流使用的就是Mybatis和Spring-data-jpa。索引本篇先講Mybatis。

品茗IT-SpringBoot專題-同步發(fā)布

品茗IT 提供在線支持：

一鍵快速構(gòu)建Spring項(xiàng)目工具

一鍵快速構(gòu)建SpringBoot項(xiàng)目工具

一鍵快速構(gòu)建SpringCloud項(xiàng)目工具

一站式Springboot項(xiàng)目生成

Mysql一鍵生成Mybatis注解Mapper

如果大家正在尋找一個(gè)java的學(xué)習(xí)環(huán)境，或者在開發(fā)中遇到困難，可以加入我們的java學(xué)習(xí)圈，點(diǎn)擊即可加入，共同學(xué)習(xí)，節(jié)約學(xué)習(xí)時(shí)間，減少很多在學(xué)習(xí)中遇到的難題。

一、引入依賴

需要同時(shí)引入數(shù)據(jù)庫(kù)的connector和數(shù)據(jù)源datasource，當(dāng)然也可以使用mybatis自己實(shí)現(xiàn)的數(shù)據(jù)源，但是還是以第三方數(shù)據(jù)源最好，畢竟經(jīng)過大家的認(rèn)可。

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

二、配置數(shù)據(jù)庫(kù)連接信息

spring.datasource.driverClassName = com.mysql.jdbc.Driverspring.datasource.url=jdbc:mysql://127.0.0.1:3306/boot?useUnicode=true&characterEncoding=utf8&serverTimezone=UTCspring.datasource.username=cffspring.datasource.password=123456spring.datasource.type=org.apache.commons.dbcp2.BasicDataSourcespring.datasource.dbcp2.max-wait-millis=60000spring.datasource.dbcp2.min-idle=20spring.datasource.dbcp2.initial-size=2spring.datasource.dbcp2.validation-query=SELECT 1spring.datasource.dbcp2.connection-properties=characterEncoding=utf8spring.datasource.dbcp2.validation-query=SELECT 1spring.datasource.dbcp2.test-while-idle=truespring.datasource.dbcp2.test-on-borrow=truespring.datasource.dbcp2.test-on-return=falsemybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

三、使用Mybatis的Mapper

3.1 表與Java實(shí)體

假設(shè)我們有一張這個(gè)表user_role ：







實(shí)體：

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;@Mapperpublic 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);}其中，插入操作中的語(yǔ)句：

@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")是用來回顯ID的，方便保存后回查。

3.3 使用xml方式寫sql

使用xml方式寫sql，需要先在SpringBoot讀取的配置文件（可以放在環(huán)境相關(guān)的配置文件中，也可以直接放在application.properties文件）中加入：

mybatis.mapper-locations=classpath:mapper/*.xml這條配置指定了xml的配置在classpath下的mapper文件夾下。

首先建立一個(gè)mapper接口：

import java.util.List;import org.apache.ibatis.annotations.Mapper;import cn.pomit.testboot.domain.UserRole;@Mapperpublic 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>這里，多參數(shù)傳遞的時(shí)候，剛開始用#{0}，#{1}這種形式取值，出現(xiàn)：

nested exception is org.apache.ibatis.binding.BindingException: Parameter '1' not found. Available parameters are [arg1, arg0, param1, param2]這種異常，可能是版本升級(jí)，取參方式改變了，那就按照它說的換成param或者arg就行，如果怕出問題，就直接在接口的參數(shù)前加上@Param注解，xml中以名稱來取變量即可。

四、service邏輯調(diào)用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;@Servicepublic 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); }}

五、開放接口調(diào)用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;@RestController@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技術(shù)吧!