项目搭建基于前面两篇文章。
先建立两张数据库表：
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。如要全部，可返回对应实体类。
