Mybatis实践教程（三）-数据查询

本章节介绍如何利用spring boot+mybatis从数据库中查询数据并返回。

上一章节我们已经创建好了数据库和表（用户表t_user）及内容

本章查询的需求：

• 查询所有用户信息
• 根据id查询单条用户信息

 

首先在工程中创建domain

User.java

package com.example.springbootmybatis.domain;

public class User {
    private Long id;
    private String userName;
    private Integer age;
    private String mobilePhone;

    public Long getId() {
        return id;
    }

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

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public Integer getAge() {
        return age;
    }


    public void setAge(Integer age) {
        this.age = age;
    }


    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", age=" + age +
                ", mobilePhone=" + mobilePhone +
                '}';
    }

    public void setMobilePhone(String mobilePhone) {
        this.mobilePhone = mobilePhone;
    }
}

创建UserService接口

package com.example.springbootmybatis.service;

import com.example.springbootmybatis.domain.User;

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

public interface UserService {

    List<User> getAllUsers();

    User selectUserById(Integer id);

    List<User> getAllUsersByCond(Map<String,Object> map);
}

创建UserServiceImpl实体类

package com.example.springbootmybatis.service.impl;

import com.example.springbootmybatis.domain.User;
import com.example.springbootmybatis.mapper.UserMapper;
import com.example.springbootmybatis.service.UserService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

@Service
public class UserServiceImpl implements UserService {

    @Resource
    private UserMapper userMapper;
    @Override
    public List<User> getAllUsers() {
        return userMapper.selectUserList();
    }

    @Override
    public User selectUserById(Integer id) {
        return userMapper.selectUserById(id);
    }

    @Override
    public List<User> getAllUsersByCond(Map<String,Object> map) {
        return userMapper.getAllUsersByCond(map);
    }
}

创建UserMapper接口

package com.example.springbootmybatis.mapper;

import com.example.springbootmybatis.domain.User;

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

public interface UserMapper {

    List<User> selectUserList ();

    User selectUserById(Integer id);

    List<User> getAllUsersByCond(Map<String,Object> map);


}

创建UserMapper.xml

在该文件中完成需求中的sql查询

<?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.example.springbootmybatis.mapper.UserMapper">
   <!-- 该resultMap用于实体对象User与表映射-->
    <resultMap id="userResult" type="com.example.springbootmybatis.domain.User">
        <id property="id" column="id"/>
        <result property="userName" column="user_name" />
        <result property="age" column="age" />
        <result property="mobilePhone" column="mobile_phone"/>

    </resultMap>
    <!--查询所有用户信息-->
   <select id="selectUserList" resultMap="userResult">
      select * from t_user
   </select>

 <!--   根据id查询单条用户信息-->
    <select id="selectUserById" resultMap="userResult">
      select * from t_user
      <where>
          id =#{id}
      </where>
   </select>

  <!-- 模糊查询 like查询-->
    <select id="getAllUsersByCond" parameterType="map" resultMap="userResult">
        select * from t_user
        <where>
           user_name like concat('%', #{userName}, '%')
        </where>
    </select>
</mapper>

测试数据是否正常查询,

创建测试类进行代码测试

package com.example.springbootmybatis;


import com.example.springbootmybatis.domain.User;
import com.example.springbootmybatis.service.UserService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RunWith(SpringRunner.class)
@SpringBootTest
public class MybatisTest {

    @Resource
    private UserService userService;

    /**
     *查询所有用户信息
     */
    @Test
    public  void  getAllUsers(){
        List<User> userList = userService.getAllUsers();
        for (User user : userList) {
            System.out.println(user);
        }
    }

    /**
     * 根据id查询单条用户信息
     */
    @Test
    public void selectUserById(){
      User user =  userService.selectUserById(1);
        System.out.println(user);
    }

    /**
     * 模糊查询
     */
    @Test
    public void getAllUsersByCond(){
        Map<String,Object> map = new HashMap<>();
        map.put("userName","a");
      List<User> userList =  userService.getAllUsersByCond(map);
        for (User user : userList) {
            System.out.println(user);
        }
    }
}

测试数据是否正常查询？

根据之前的需求我们依次执行测试代码

• 查询所有用户信息

User{id=1, userName='admin', age=25, mobilePhone=13001092823}
User{id=2, userName='testuser', age=34, mobilePhone=18888888888}
User{id=3, userName='zhangsan', age=21, mobilePhone=13666666666}
User{id=4, userName='lisi', age=15, mobilePhone=13111111111}

• 根据id查询单条用户信息

User{id=1, userName='admin', age=25, mobilePhone=13001092823}

• 模糊查询

User{id=1, userName='admin', age=25, mobilePhone=13001092823}
User{id=3, userName='zhangsan', age=21, mobilePhone=13666666666}

以上就是关于Mybatis数据查询的代码，在这里我们需要了解一个小知识点如下：

条件查询中参数中#和$的区别？

#获取参数时，默认会把参数当做字符串处理，在参数之间会默认添加单引号，$获取参数时默认至输出原样参数。

#获取参数时，可以防止sql注入，$不能够防止sql注入。

 

本章主要讲解了如何利用Mybatis查询数据并返回结果，大家有什么不明白的欢迎留言