SpringBoot 操作数据库实战

超、凢脫俗 2022-05-19 09:19 201阅读 0赞

**简介：本博客建立在已经搭好SpringBoot框架的基础上，开发一个添加邮件用户、查询用户的接口。其中，添加和查询用户都要进行数据库的操作**

## 首先，在数据库中创建一个数据库表 ##

这里以新建emails表为例，包括id、email、phoneNum三个字段：

CREATE TABLE `emails` ( `id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(255) NOT NULL, `phoneNum` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) )

## 打开SpringBoot项目，添加pom.xml依赖 ##

<dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.1.1</version>
                
            </dependency>
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.0.15</version>
                
            </dependency>
            
            <dependency>
                <groupId> mysql</groupId>
                <artifactId> mysql-connector-java</artifactId>
                <version> 5.0.5</version>
            </dependency>

这里加入了一个阿里的druid驱动，druid是一个可监控的驱动，可以说是一个为监控而生的数据库驱动

## 配置数据库 ##

在application.properties中添加数据库的相关配置，如下：

# 驱动配置信息
    spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
    spring.datasource.url = jdbc:mysql://localhost:3306/eunomia
    spring.datasource.username = root
    spring.datasource.password = root
    spring.datasource.driverClassName = com.mysql.jdbc.Driver
    
    # MyBatis 配置
    mybatis.mapper-locations=classpath:mapper/*.xml mybatis.type-aliases-package=com.chinawayltd.bean

## 代码 ##

1、在bean目录下，新建一个实体类User.class，

/** * Created by pc on 2018/6/30. */
    public class User { 
        private int id;
        private String email;
        private String phoneNum;
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public String getEmail() {
            return email;
        }
    
        public void setEmail(String email) {
            this.email = email;
        }
    
        public String getPhoneNum() {
            return phoneNum;
        }
    
        public void setPhoneNum(String phoneNum) {
            this.phoneNum = phoneNum;
        }
    }

2、在项目的resources目录下新建一个mapper文件夹，在mapper文件夹里新建一个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.chinawayltd.mapper.UserMapper">
        
    
        
        <resultMap id="UserResultMap" type="com.chinawayltd.bean.User">
            <id property="id" column="id" />
            <result property="email" column="email" />
            <result property="phoneNum" column="phoneNum" />
        </resultMap>
    
        <insert id="insertUser" >
            INSERT INTO emails(email, phoneNum)
            VALUES (#{user.email}, #{user.phoneNum})
        </insert>
    
        <select id="findByEmail" resultType="User">
            SELECT * FROM emails WHERE email = #{email}
        </select>
    
        <select id="findPhoneByEmail" resultType="java.lang.String">
            SELECT phoneNum FROM emails WHERE email = #{email}
        </select>
    
    </mapper>

3、新建一个操作数据库接口类，UserMapper.class：

package com.chinawayltd.mapper;
    import com.chinawayltd.bean.User;
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.annotations.Mapper;
    /** * Created by zggdczfr on 2017/1/22. */
    @Mapper
    public interface UserMapper { 
    
        /** * 插入用户 * @param user * @return */
        int insertUser(@Param("user") User user);
    
        /** * 查询某个用户 * @param email * @return */
        User findByEmail(String email);
    
        //通过邮件查询用户的手机号
        String findPhoneByEmail(String email);
    }

4、新建一个数据服务类，UserService.class:

import com.chinawayltd.bean.User;
    import com.chinawayltd.mapper.UserMapper;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    /** * Created by pc on 2018/6/30. */
    @Service
    public class UserService { 
    
        @Autowired
        private UserMapper userMapper;
    
        public int add(User user) {
            return userMapper.insertUser(user);
        }
    
        public User findByEmail(String email){
            return userMapper.findByEmail(email);
        }
    
        public String findPhoneByEmail(String email){
            return userMapper.findPhoneByEmail(email);
        }
    
    }

5、新建一个Controller类，TestController.class：

import com.chinawayltd.bean.User;
    import com.chinawayltd.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.*;
    
    /** * Created by pc on 2018/6/30. */
    @RestController
    @RequestMapping("/test")
    public class TestController { 
    
        //引入userService
        @Autowired
        private UserService userService;  
    
        //添加用户
        @RequestMapping(value="/add",method= RequestMethod.POST)
        public String add(@RequestBody User user){
            userService.add(user);
            return "添加成功！";
        }
    
        //通过邮件查询用户
        @RequestMapping("/findUserByEmail")
        public User findUserByEmail(@RequestParam(value="email") String email){
            User user = userService.findByEmail(email);
            return user;
        }
    
        //通过邮件查询用户的手机号
        @RequestMapping("/findPhoneByEmail")
        public String findPhoneByEmail(@RequestParam(value="email") String email){
            return userService.findPhoneByEmail(email);
        }
    
    }

## 接口测试 ##

1、运行Application.class，启动服务  
2、在postman里进行接口测试：  
（1）发送post请求添加邮件用户，如下图：  
![这里写图片描述][70]

（2）通过邮件查询用户，如下图：  
![这里写图片描述][70 1]

（3）通过邮件查询用户的手机号，如下图：  
![这里写图片描述][70 2]

**完…**

[70]: /images/20220519/dfbf9da9d6374d8b85491e9b6ab5a767.png
[70 1]: /images/20220519/ce7d424a20924cdc80cccc09751ac6f9.png
[70 2]: /images/20220519/ebf1d76885cb439b8e4e0f7dd93fd6ce.png