mybatis的select方式的实现

项目搭建基于前面两篇文章。
先建立两张数据库表：
user_first表：

image.png

user_product表：

image.png

建立对应实体类：
UserModel:

package com.lml.helloworld3.pojo;

import java.util.List;

public class UserModel {
    private int id;
    private String  userName;
    private int userAge;
    private String userPassw;

    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 int getUserAge() {
        return userAge;
    }

    public void setUserAge(int userAge) {
        this.userAge = userAge;
    }

    public String getUserPassw() {
        return userPassw;
    }

    public void setUserPassw(String userPassw) {
        this.userPassw = userPassw;
    }
}

ProductModel:

package com.lml.helloworld3.pojo;

public class ProductModel {

    private int id;
    private int userId;
    private String book;
    private String userClass;

    public int getId() {
        return id;
    }

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

    public int getUserId() {
        return userId;
    }

    public void setUserId(int userId) {
        this.userId = userId;
    }

    public String getBook() {
        return book;
    }

    public void setBook(String book) {
        this.book = book;
    }

    public String getUserClass() {
        return userClass;
    }

    public void setUserClass(String userClass) {
        this.userClass = userClass;
    }
}

StudentModel:

package com.lml.helloworld3.pojo;

import java.util.List;

public class StudentModel {
    private String  userName;
    private List<ProductModel> products;
    private String userPassw;

    public String getUserPassw() {
        return userPassw;
    }

    public void setUserPassw(String userPassw) {
        this.userPassw = userPassw;
    }

    public String getUserName() {
        return userName;
    }

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


    public List<ProductModel> getProducts() {
        return products;
    }

    public void setProducts(List<ProductModel> products) {
        this.products = products;
    }
}

在mapper文件夹下的UserDAO.xml中添加代码,对应各类查询：

<select id="findById" parameterType="java.lang.Integer" resultType="com.lml.helloworld3.pojo.UserModel">
        SELECT * FROM mytest.user_first where id = #{id}
    </select>

    <select id="findNameById" parameterType="java.lang.Integer" resultType="Map">
        SELECT a.user_name FROM mytest.user_first a where id = #{id}
    </select>

    <resultMap type="com.lml.helloworld3.pojo.UserModel" id="userAgeMap">
        <result property="userAge" column="user_age"/>
    </resultMap>

    <select id="findAgeById" resultMap="userAgeMap">
        SELECT a.user_age FROM mytest.user_first a where id = #{id}
    </select>

    <select id="findUserInfos" parameterType="java.lang.Integer" resultType="Map">
            SELECT a.user_name, a.user_passw, b.book FROM user_first a, user_product b WHERE a.id = b.user_id AND a.id = #{id}
    </select>

    <resultMap type="com.lml.helloworld3.pojo.StudentModel" id="userMap">
        <result property="userName" column="user_name"/>
        <result property="userPassw" column="user_passw"/>
        <!--collection定义关联集合类型的属性的封装规则
            ofType:指定集合里面元素的类型  -->
        <collection property="products" ofType="Map">
            <!-- 定义这个集合元素的封装规则 -->

            <result column="book" property="book"/>

        </collection>
    </resultMap>

    <select id="getStudentInfo" resultMap="userMap">
        SELECT a.user_name, a.user_passw, b.book FROM user_first a, user_product b WHERE a.id = b.user_id AND a.id = #{id}
    </select>

UserDao接口类：

public interface UserDao {

    UserModel findById(int id);
    Map findNameById(int id);
    UserModel findAgeById(int id);
    List findUserInfos(int id);
    StudentModel getStudentInfo(int id);
}

UserController实现如下：

package com.lml.helloworld3.controller;

import com.lml.helloworld3.dao.UserDao;
import com.lml.helloworld3.pojo.StudentModel;
import com.lml.helloworld3.pojo.UserModel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

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

@RestController
@RequestMapping(value = "/myuser")
public class UserController {

    @Autowired(required = false)
    private UserDao userDao;

    /**
     * 单表所有列
     * @return
     */
    @RequestMapping(value = "/getUser",method = RequestMethod.GET)
    public UserModel getUserById(){
        return userDao.findById(1);
    }

    /**
     * 单表部分列(Map)
     * @return
     */
    @RequestMapping(value = "/getUserName", method = RequestMethod.GET)
    public Map findNameById(){
        return userDao.findNameById(1);
    }

    /**
     * 单表部分列(实体类)
     * @return
     */
    @RequestMapping(value = "/getUserAge", method = RequestMethod.GET)
    public UserModel findAgeById(){
        return userDao.findAgeById(1);
    }

    /**
     * 多表无集合类
     * @return
     */
    @RequestMapping(value = "/getUserInfos", method = RequestMethod.GET)
    public List findUserInfos(){
        return userDao.findUserInfos(1);
    }

    /**
     * 多表有集合类(手动编辑)
     * @return
     */
    @RequestMapping(value = "/getUserInfosWithCollection", method = RequestMethod.GET)
    public Map getUserInfosWithCollection(){
        List data = userDao.findUserInfos(1);
        Map mapData = new HashMap();
        List bookList = new ArrayList();

        if(data.size() >0) {
            String passw = (String) ((Map) data.get(0)).get("user_passw");
            mapData.put("user_passw", passw);
            String name = (String) ((Map) data.get(0)).get("user_name");
            mapData.put("user_name", name);
        }
        for (int i = 0; i < data.size(); i++) {
            String bookStr = (String)((Map)data.get(i)).get("book");
            bookList.add(bookStr);
        }
        mapData.put("book", bookList);
        return mapData;
    }

    /**
     * 多表有集合类(自动编辑)
     * @return
     */
    @RequestMapping(value = "/getStudentInfo", method = RequestMethod.GET)
    public StudentModel getStudentInfo(){
        StudentModel data = userDao.getStudentInfo(1);
        return  data;
    }
}

返回json如果需要过滤空值，可返回Map。如要全部，可返回对应实体类。