mybatis实现从数据库获取商品列表的信息

mybatis是一个流行的Java持久层框架,它提供了一种简单、可读性高的方式来将SQL语句和Java代码进行映射，因此被广泛使用于各种Java项目，以下是一个简单的示例，展示了mybatis如何从数据库获取商品列表的信息并将信息返回

1.新建数据库表插入商品信息

这里我将id设置为主键自增，使用category_id来区分商品的种类

2.创建实体类Goodist

@Data
@Entity
public class GoodList {
    @Id
    private int goodListId;				//商品编号
    private String goodListName;	//商品名称
    private double goodListPrice;		//商品单价
    private String goodListPicture;
    private String goodListBriefIntroduction;
    private Integer categoryId;

}

3.创建GoodMapper.xml

在resources目录下新建mappers包，在mappers包下新建GoodMapper.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.software.PetShop.mapper.GoodMapper">
    <resultMap id="goodResult" type="com.software.PetShop.entity.GoodList">
            <id column="good_list_id" property="goodListId"/>
            <id column="good_list_name" property="goodListName"/>
            <id column="good_list_price" property="goodListPrice"/>
            <id column="good_list_brief_introduction"         
                        property="goodListBriefIntroduction"/>
            <id column="good_list_picture" property="goodListPicture"/>
            <id column="category_id" property="categoryId"/>
    </resultMap>
   
    </mapper>

4.配置mybatis-config.xml

在<mappers>下新加GoodMapper.xml映射文件

<mappers>
    <mapper resource="mappers/GoodMapper.xml"></mapper>
</mappers>

5.创建GoodController，新建getAllGoodList()方法

将CartMapper中的getAllGoodList方法获取到的数据填充在allGoodList列表里，遍历将商品信息存入jsonArray中，根据判断条件将jsonArray和状态码添加到jsonObject1中并返回数据

package com.software.PetShop.controller;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import com.software.PetShop.entity.GoodList;
import com.software.PetShop.mapper.GoodMapper;
import org.apache.ibatis.session.SqlSession;
import org.springframework.web.bind.annotation.*;

import java.util.List;

import static com.software.PetShop.utils.MybatisUtils.getSqlSession;

@RestController
@RequestMapping("/petShop")
public class GoodController {
/**
     * 获取所有商品列表**/
    @GetMapping("/getAllGoodsList")
    public String getAllGoodList() {
        SqlSession sqlSession = getSqlSession();
        try {
            GoodMapper goodMapper = sqlSession.getMapper(GoodMapper.class);
            List<GoodList> allGoodList = goodMapper.getAllGoodList();
            System.out.println("goodList数据："+allGoodList);
            JSONArray jsonArray = new JSONArray();
            for (GoodList goodList : allGoodList) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("goodId", goodList.getGoodListId());
                jsonObject.put("goodName", goodList.getGoodListName());
                jsonObject.put("goodBriefIntroduction",     
                                 goodList.getGoodListBriefIntroduction());
                jsonObject.put("goodPicture", goodList.getGoodListPicture());
                jsonObject.put("goodPrice", goodList.getGoodListPrice());
                jsonObject.put("goodCategoryId", goodList.getCategoryId());
                System.out.println("goodList数据：" + goodList.getGoodListId() + ": " + 
                                 goodList.getGoodListName());
                jsonArray.put(jsonObject);
            }
                JSONObject jsonObject1 = new JSONObject();
                if (jsonArray!=null){
                    jsonObject1.append("data",jsonArray);
                    jsonObject1.append("status","ok");
                }else{
                    jsonObject1.append("status","no");
                }
                System.out.println("jsonobject: "+jsonObject1);
                return jsonObject1.toString();

        } finally {
            sqlSession.close();
        }
    }
}

6.创建GoodMapper接口，实现getAllGoodList()方法

注意方法名和类型要与GoodController里面一致

public interface GoodMapper extends BaseMapper<GoodList> {

    List<GoodList> getAllGoodList();

}

7.在GoodMapper.xml中实现sql查询语句

注意id要与方法名一致

<?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.software.PetShop.mapper.GoodMapper">
    <resultMap id="goodResult" type="com.software.PetShop.entity.GoodList">
            <id column="good_list_id" property="goodListId"/>
            <id column="good_list_name" property="goodListName"/>
            <id column="good_list_price" property="goodListPrice"/>
            <id column="good_list_brief_introduction" property="goodListBriefIntroduction"/>
            <id column="good_list_picture" property="goodListPicture"/>
            <id column="category_id" property="categoryId"/>
    </resultMap>

     <select id="getAllGoodList" resultMap="goodResult">
        SELECT * FROM good_list
    </select>

</mapper>

8.测试

可以看出，程序成功在数据库表中查询出了商品的数据信息并返回了信息状态