SpringBoot使用MyBatis连接MySQL

[!NOTE]

本教程是《SpringBoot系列基础教程》之一,教程目录:https://blog.csdn.net/laisc7301/article/details/135918617

首先按照图示新建项目:

添加以下依赖:

找到application.properties文件,并写入下面内容:

server.port=8001

#设置数据库名为testdata
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/testdata?characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#下面两行设置数据库用户名和密码
spring.datasource.username=root
spring.datasource.password=root1234


mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.configuration.map-underscore-to-camel-case=true

注意 testdata 就是数据库名。

整个项目的文件结构如下图所示:

先把代码贴出来:

UserController.java

package com.laisc.example3.controller;

import com.laisc.example3.entity.User;
import com.laisc.example3.mapper.UserMapper;
import com.laisc.example3.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
@RequestMapping("/test")
public class UserController {

    @Autowired
    UserService userService;

    @ResponseBody
    @RequestMapping("/insert")
    public String insert() { // 插入数据
        User u = new User();
        u.setUsername("admin");
        u.setPassword("admin123");
        u.setRole("root");
        int num = userService.insertUser(u);
        return "成功添加" + num + "条记录";
    }

    @ResponseBody
    @RequestMapping("/findone")
    public String findone() { //仅搜索一条结果,结果多于一条时,会报错
        User u = new User();
        u.setUsername("admin");
        User ret = userService.findOneUser(u);
        return ret.toString();
    }

    @ResponseBody
    @RequestMapping("/findmany")
    public String findMany() { //搜索所有符合条件的结果
        User u = new User();
        u.setUsername("admin");
        List<User> ret = userService.findManyUser(u);
        return ret.toString();
    }

    @ResponseBody
    @RequestMapping("/findmany2")
    public String findMany2() { // 和 findMany() 方法一样,只是这次在mapper配置文件里的resultType属性上使用了User类
        User u = new User();
        u.setUsername("admin");
        List<User> ret = userService.findManyUser2(u);
        return ret.toString();
    }

    @ResponseBody
    @RequestMapping("/findmany3")
    public String findMany3() { // 和 findMany() 方法一样,只是这次ret的类型是List<Map>。
        User u = new User();
        u.setUsername("admin");
        List<Map> ret = userService.findManyUser3(u);
        return ret.toString();
    }

    @ResponseBody
    @RequestMapping("/findmany4")
    public String findMany4() { // 和 findMany() 方法一样,只是这次传入的参数的类型是Map,ret的类型也是List<Map>。
        Map<String, String> map = new HashMap<String, String>();
        map.put("username", "admin");

        List<Map> ret = userService.findManyUser4(map);
        return ret.toString();
    }

    @ResponseBody
    @RequestMapping("/update1")
    public String update1() { // 更新数据库
        User u = new User();
        u.setUsername("admin");
        u.setPassword("abcd1234");
        u.setRole("user");
        int num = userService.updateUserPasswordAndRole(u);
        return "成功更新" + num + "条记录";
    }

    @ResponseBody
    @RequestMapping("/update2")
    public String update2() { // 更新数据库,但是不更新对象u中值为空的数据
        User u = new User();
        u.setUsername("admin");
        //u.setPassword("abcd1234");
        u.setRole("user");
        int num = userService.updateUser(u);
        return "成功更新" + num + "条记录";
    }

    @ResponseBody
    @RequestMapping("/delete")
    public String delete() { // 删除记录
        int num = userService.deleteUser("admin");
        return "成功删除" + num + "条记录";
    }
}

UserService.java

package com.laisc.example3.service;

import com.laisc.example3.entity.User;

import java.util.List;
import java.util.Map;

public interface UserService {
    public int insertUser(User user);

    public User findOneUser(User user);//仅搜索一条结果,结果多于一条时,会报错

    public List<User> findManyUser(User user);

    public List<User> findManyUser2(User user);

    public List<Map> findManyUser3(User user);

    public List<Map> findManyUser4(Map map);

    public int updateUserPasswordAndRole(User user);

    public int updateUser(User user);

    public int deleteUser(String username);
}

UserServiceImpl.java

package com.laisc.example3.service.impl;

import com.laisc.example3.entity.User;
import com.laisc.example3.mapper.UserMapper;
import com.laisc.example3.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    UserMapper userMapper;
    @Override
    public int insertUser(User user) {
        return userMapper.insertUser(user);
    }

    @Override
    public User findOneUser(User user) {
        return userMapper.findOneUser(user);
    }

    @Override
    public List<User> findManyUser(User user) {
        return userMapper.findManyUser(user);
    }

    @Override
    public List<User> findManyUser2(User user) {
        return userMapper.findManyUser2(user);
    }

    @Override
    public List<Map> findManyUser3(User user) {
        return userMapper.findManyUser3(user);
    }

    @Override
    public List<Map> findManyUser4(Map map) {
        return userMapper.findManyUser4(map);
    }

    @Override
    public int updateUserPasswordAndRole(User user) {
        return userMapper.updateUserPasswordAndRole(user);
    }

    @Override
    public int updateUser(User user) {
        return userMapper.updateUser(user);
    }

    @Override
    public int deleteUser(String username) {
        return userMapper.deleteUser(username);
    }
}

UserMapper.java

package com.laisc.example3.mapper;


import com.laisc.example3.entity.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;
import java.util.Map;

@Mapper
public interface UserMapper {

    public int insertUser(User user);

    public User findOneUser(User user);//仅搜索一条结果,结果多于一条时,会报错

    public List<User> findManyUser(User user);

    public List<User> findManyUser2(User user);

    public List<Map> findManyUser3(User user);

    public List<Map> findManyUser4(Map map);

    public int updateUserPasswordAndRole(User user);

    public int updateUser(User user);

    public int deleteUser(String username);
}

User.java

package com.laisc.example3.entity;

public class User {
    private int id = -1;
    private String username= "";
    private String password= "";
    private String role= "";

    public User() {
    }

    public User(int id, String username, String password, String role) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.role = role;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getRole() {
        return role;
    }

    public void setRole(String role) {
        this.role = role;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", role='" + role + '\'' +
                '}';
    }
}

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.laisc.example3.mapper.UserMapper">

    <insert id="insertUser" parameterType="com.laisc.example3.entity.User">
        insert into myuser (username, password, role)
        VALUES (#{username}, #{password}, #{role})
    </insert>


    <resultMap id="myfinduser" type="com.laisc.example3.entity.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="role" column="role"/>
    </resultMap>
    <select id="findOneUser" resultMap="myfinduser" parameterType="com.laisc.example3.entity.User">
        select myuser.id       id,
               myuser.username username,
               myuser.password password,
               myuser.role     role
        from myuser
        where username = #{username}
    </select>

    <select id="findManyUser" resultMap="myfinduser" parameterType="com.laisc.example3.entity.User">
        select myuser.id       id,
               myuser.username username,
               myuser.password password,
               myuser.role     role
        from myuser
        where username = #{username}
    </select>

    <select id="findManyUser2" resultType="com.laisc.example3.entity.User" parameterType="com.laisc.example3.entity.User">
        select myuser.id       id,
               myuser.username username,
               myuser.password password,
               myuser.role     role
        from myuser
        where username = #{username}
    </select>

    <select id="findManyUser3" resultType="Map" parameterType="com.laisc.example3.entity.User">
        select myuser.id       id,
               myuser.username username,
               myuser.password password,
               myuser.role     role
        from myuser
        where username = #{username}
    </select>

    <select id="findManyUser4" resultType="Map" parameterType="Map">
        select myuser.id       id,
               myuser.username username,
               myuser.password password,
               myuser.role     role
        from myuser
        where username = #{username}
    </select>

    <update id="updateUserPasswordAndRole" parameterType="com.laisc.example3.entity.User">
        update myuser
        <set>
            myuser.password=#{password},
            myuser.role=#{role}
        </set>
        where username = #{username}
    </update>


    <update id="updateUser" parameterType="com.laisc.example3.entity.User">-- 不更新值为空的数据
        update myuser
        <set>
            <if test="id != null and id != -1 ">id=#{id},</if>
            <if test="password != null and password != '' ">password=#{password},</if>
            <if test="role != null and role != '' ">role=#{role},</if>

        </set>
        where username = #{username}
    </update>


    <delete id="deleteUser" parameterType="String">
        delete
        from myuser
        where username = #{username}
    </delete>

</mapper>

数据库(myuser @testdata):

idusernamepasswordrole
adminabc123root
admin123root

看UserController.java:

@Autowired
UserService userService;

UserService.java 里调用了UserServiceImpl.java

UserServiceImpl.java

@Autowired
UserMapper userMapper;

打上这段代码后,我们就可以使用UserMapper了。

另外,不要忘记在UserMapper.java里写上这一行:

@Mapper

在UserServiceImpl.java里写上这一行:

@Service

然后,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.laisc.example3.mapper.UserMapper">
</mapper>

看这一段:

UserController.java

@ResponseBody
@RequestMapping("/insert")
public String insert() { // 插入数据
    User u = new User();
    u.setUsername("admin");
    u.setPassword("admin123");
    u.setRole("root");
    int num = userService.insertUser(u);
    return "成功添加" + num + "条记录";
}

UserService.java、UserMapper.java

public int insertUser(User user);

UserServiceImpl.java

@Override
public int insertUser(User user) {
    return userMapper.insertUser(user);
}

UserMapper.xml

<insert id="insertUser" parameterType="com.laisc.example3.entity.User">
    insert into myuser (username, password, role)
    VALUES (#{username}, #{password}, #{role})
</insert>

运行代码,访问 http://localhost:8001/test/insert ,得到文字:成功添加1条记录。

看这几段:

@ResponseBody
@RequestMapping("/findone")
public String findone() { //仅搜索一条结果,结果多于一条时,会报错
    User u = new User();
    u.setUsername("admin");
    User ret = userService.findOneUser(u);
    return ret.toString();
}

@ResponseBody
@RequestMapping("/findmany")
public String findMany() { //搜索所有符合条件的结果
    User u = new User();
    u.setUsername("admin");
    List<User> ret = userService.findManyUser(u);
    return ret.toString();
}

@ResponseBody
@RequestMapping("/findmany2")
public String findMany2() { // 和 findMany() 方法一样,只是这次在mapper配置文件里的resultType属性上使用了User类
    User u = new User();
    u.setUsername("admin");
    List<User> ret = userService.findManyUser2(u);
    return ret.toString();
}

@ResponseBody
@RequestMapping("/findmany3")
public String findMany3() { // 和 findMany() 方法一样,只是这次ret的类型是List<Map>。
    User u = new User();
    u.setUsername("admin");
    List<Map> ret = userService.findManyUser3(u);
    return ret.toString();
}

@ResponseBody
@RequestMapping("/findmany4")
public String findMany4() { // 和 findMany() 方法一样,只是这次传入的参数的类型是Map,ret的类型也是List<Map>。
    Map<String, String> map = new HashMap<String, String>();
    map.put("username", "admin");

    List<Map> ret = userService.findManyUser4(map);
    return ret.toString();
}
public User findOneUser(User user);//仅搜索一条结果,结果多于一条时,会报错

public List<User> findManyUser(User user);

public List<User> findManyUser2(User user);

public List<Map> findManyUser3(User user);

public List<Map> findManyUser4(Map map);
<resultMap id="myfinduser" type="com.laisc.example3.entity.User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <result property="password" column="password"/>
    <result property="role" column="role"/>
</resultMap>
<select id="findOneUser" resultMap="myfinduser" parameterType="com.laisc.example3.entity.User">
    select myuser.id       id,
           myuser.username username,
           myuser.password password,
           myuser.role     role
    from myuser
    where username = #{username}
</select>

<select id="findManyUser" resultMap="myfinduser" parameterType="com.laisc.example3.entity.User">
    select myuser.id       id,
           myuser.username username,
           myuser.password password,
           myuser.role     role
    from myuser
    where username = #{username}
</select>

<select id="findManyUser2" resultType="com.laisc.example3.entity.User" parameterType="com.laisc.example3.entity.User">
    select myuser.id       id,
           myuser.username username,
           myuser.password password,
           myuser.role     role
    from myuser
    where username = #{username}
</select>

<select id="findManyUser3" resultType="Map" parameterType="com.laisc.example3.entity.User">
    select myuser.id       id,
           myuser.username username,
           myuser.password password,
           myuser.role     role
    from myuser
    where username = #{username}
</select>

<select id="findManyUser4" resultType="Map" parameterType="Map">
    select myuser.id       id,
           myuser.username username,
           myuser.password password,
           myuser.role     role
    from myuser
    where username = #{username}
</select>

此时访问 findmany、findmany2、findmany3、findmany4 分别会返回查询结果。访问 findone会出错,因为符合条件的结果不止一个。

看下面代码:

@ResponseBody
@RequestMapping("/update1")
public String update1() { // 更新数据库
    User u = new User();
    u.setUsername("admin");
    u.setPassword("abcd1234");
    u.setRole("user");
    int num = userService.updateUserPasswordAndRole(u);
    return "成功更新" + num + "条记录";
}
public int updateUserPasswordAndRole(User user);
<update id="updateUserPasswordAndRole" parameterType="com.laisc.example3.entity.User">
    update myuser
    <set>
        myuser.password=#{password},
        myuser.role=#{role}
    </set>
    where username = #{username}
</update>

访问 http://localhost:8001/test/update1 ,将得到下面信息:成功更新2条记录。

看一下数据库,数据库会变成下面这样:

idusernamepasswordrole
adminabcd1234user
adminabcd1234user

看这一段:

@ResponseBody
@RequestMapping("/update2")
public String update2() { // 更新数据库,但是不更新对象u中值为空的数据
    User u = new User();
    u.setUsername("admin");
    //u.setPassword("abcd1234");
    u.setRole("user");
    int num = userService.updateUser(u);
    return "成功更新" + num + "条记录";
}
public int updateUser(User user);
<update id="updateUser" parameterType="com.laisc.example3.entity.User">-- 不更新值为空的数据
    update myuser
    <set>
        <if test="id != null and id != -1 ">id=#{id},</if>
        <if test="password != null and password != '' ">password=#{password},</if>
        <if test="role != null and role != '' ">role=#{role},</if>

    </set>
    where username = #{username}
</update>

访问 http://localhost:8001/test/update2 ,将得到下面信息:成功更新2条记录。

看一下数据库,数据库会变成下面这样:

idusernamepasswordrole
adminabc123user
admin123user

最后看这一段:

@ResponseBody
@RequestMapping("/delete")
public String delete() { // 删除记录
    int num = userService.deleteUser("admin");
    return "成功删除" + num + "条记录";
}
public int deleteUser(String username);
<delete id="deleteUser" parameterType="String">
    delete
    from myuser
    where username = #{username}
</delete>

这一段比较简单,就是删除所有username值为admin的记录。

项目源代码下载:https://pan.baidu.com/s/1w0yKDOJsbOazc27OLM0WUQ?pwd=zvx3

上一篇:SpringBoot使用Thymeleaf:https://laisc7301.github.io/blog/2024/01/04/202401040000SpringBoot%E4%BD%BF%E7%94%A8Thymeleaf/

下一篇:SpringBoot使用MongoDB:https://laisc7301.github.io/blog/2024/01/12/202401120000SpringBoot%E4%BD%BF%E7%94%A8MongoDB/

原文:https://laisc7301.github.io/blog/2024/01/07/202401070000SpringBoot%E4%BD%BF%E7%94%A8MyBatis%E8%BF%9E%E6%8E%A5MySQL/

  • 33
    点赞
  • 38
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值