SpringBoot入门建站全系列（三）Mybatis操作数据库

迈不过友情╰ 2022-04-25 02:56 202阅读 0赞

## 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-SpringBoot_-]

[品茗IT][IT] 提供在线支持：

[一键快速构建Spring项目工具][Spring]

[一键快速构建SpringBoot项目工具][SpringBoot]

[一键快速构建SpringCloud项目工具][SpringCloud]

[一站式Springboot项目生成][Springboot]

**如果大家正在寻找一个java的学习环境，或者在开发中遇到困难，可以[加入我们的java学习圈，点击即可加入][java]，共同学习，节约学习时间，减少很多在学习中遇到的难题。**

### 一、引入依赖 ###

需要同时引入数据库的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 /> 
    	</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.Driver
    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的Mapper ###

#### 3.1 表与Java实体 ####

假设我们有一张这个表user\_role ：

![在这里插入图片描述][aHR0cHM6Ly93d3cucG9taXQuY24vdXBsb2FkL3BpY3R1cmUvMjAxOTA0MjgvMTcwODY1NTc5NDUwNjI0LnBuZw]

实体：

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;
    
    @Mapper
    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;
    
    @Mapper
    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;
    
    @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技术吧！  
![品茗IT交流群][IT 1]

[IT-SpringBoot_-]: https://www.pomit.cn/p/170859946381568
[IT]: https://www.pomit.cn
[Spring]: https://www.pomit.cn/java/spring/spring.html
[SpringBoot]: https://www.pomit.cn/java/spring/springboot.html
[SpringCloud]: https://www.pomit.cn/java/spring/springcloud.html
[Springboot]: https://www.pomit.cn/java/java/sqlToSpringboot.html
[java]: https://jq.qq.com/?_wv=1027&k=52sgH1J
[aHR0cHM6Ly93d3cucG9taXQuY24vdXBsb2FkL3BpY3R1cmUvMjAxOTA0MjgvMTcwODY1NTc5NDUwNjI0LnBuZw]: /images/20220210/41568d014fa64c9e965768c96da6f0dc.png
[IT 1]: /images/20220210/91a2bd36be0f4ee1b2ac463684f387d7.png