MyBatis数据查询

导航

回到mybatis导航页

MyBatis数据查询步骤

1. 创建实体类
2. 创建Mapper XML
3. 编写<select>sql标签
4. 开启驼峰命名映射
5. 新增<mapper>
6. SqlSession执行select语句

目录结构

创建实体

CREATE TABLE `t_goods`  (
  `goods_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品编号',
  `title` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品名称',
  `sub_title` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '子标题',
  `original_cost` float NOT NULL COMMENT '原价',
  `current_price` float NOT NULL COMMENT '折后价',
  `discount` float NOT NULL COMMENT '折扣(0~1)',
  `is_free_delivery` int(11) NOT NULL COMMENT '是否包邮',
  `category_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`goods_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5676 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
INSERT INTO `t_goods` VALUES (2000, '五羊婴儿抑菌洗衣皂 80g*14块', '特含艾草抑菌成分，有效抑制黄色葡萄球菌和大肠杆菌，温和去污，呵护双手，易漂柔顺。', 78, 36, 0.461538, 1, 28);
INSERT INTO `t_goods` VALUES (2001, '五羊婴儿抑菌洗衣液2.2kg+宝宝洗衣皂组合', '【热卖1.7万套】为宝宝肌肤设计，特含洋甘菊提取物、艾叶提取物、清洁+抑菌，去污洁净、不含荧光剂，温和不伤手', 69, 42, 0.608696, 1, 29);
INSERT INTO `t_goods` VALUES (2002, '五羊 倍柔型防溢乳垫独立包装 30片X2盒', '贝壳型倍柔设计，不易变形、不移位；多层轻薄防溢纤维立体吸收放心安睡；绒毛棉柔，柔软更舒适。', 72, 33, 0.458333, 1, 30);
INSERT INTO `t_goods` VALUES (2003, '五羊bibi拉拉裤 XL码90片 特级棉柔婴儿成长裤 18片/包*5包', '绵柔材质结合打孔热风工艺，干爽柔软不湿小屁屁；5重锁湿芯体，吸收多达一瓶大可乐的尿量，劲吸不反渗；5cm加高防漏隔边设计，锁住尿液不反渗；呼吸透气PE膜，隔尿透气两不误。', 295, 139, 0.471186, 1, 31);

package com.torey.mybatis.entity;

/**
 * 
 *
 * @author torey torey6061@qq.com
 * @since 1.0.0 2021-04-17
 */
public class TGoodsEntity {
	private static final long serialVersionUID = 1L;

    /**
     * 商品编号
     */
	private Integer goodsId;
    /**
     * 商品名称
     */
	private String title;
    /**
     * 子标题
     */
	private String subTitle;
    /**
     * 原价
     */
	private Float originalCost;
    /**
     * 折后价
     */
	private Float currentPrice;
    /**
     * 折扣(0~1)
     */
	private Float discount;
    /**
     * 是否包邮
     */
	private Integer isFreeDelivery;
    /**
     * 
     */
	private Integer categoryId;

	public static long getSerialVersionUID() {
		return serialVersionUID;
	}

	public Integer getGoodsId() {
		return goodsId;
	}

	public void setGoodsId(Integer goodsId) {
		this.goodsId = goodsId;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getSubTitle() {
		return subTitle;
	}

	public void setSubTitle(String subTitle) {
		this.subTitle = subTitle;
	}

	public Float getOriginalCost() {
		return originalCost;
	}

	public void setOriginalCost(Float originalCost) {
		this.originalCost = originalCost;
	}

	public Float getCurrentPrice() {
		return currentPrice;
	}

	public void setCurrentPrice(Float currentPrice) {
		this.currentPrice = currentPrice;
	}

	public Float getDiscount() {
		return discount;
	}

	public void setDiscount(Float discount) {
		this.discount = discount;
	}

	public Integer getIsFreeDelivery() {
		return isFreeDelivery;
	}

	public void setIsFreeDelivery(Integer isFreeDelivery) {
		this.isFreeDelivery = isFreeDelivery;
	}

	public Integer getCategoryId() {
		return categoryId;
	}

	public void setCategoryId(Integer categoryId) {
		this.categoryId = categoryId;
	}

	@Override
	public String toString() {
		return "TGoodsEntity{" +
				"goodsId=" + goodsId +
				", title='" + title + '\'' +
				", subTitle='" + subTitle + '\'' +
				", originalCost=" + originalCost +
				", currentPrice=" + currentPrice +
				", discount=" + discount +
				", isFreeDelivery=" + isFreeDelivery +
				", categoryId=" + categoryId +
				'}';
	}
}

2 创建Mapper xml和编写select标签

<?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="goods">
<select id="selectAll" resultType="com.torey.mybatis.entity.TGoodsEntity">
   SELECT * FROM t_goods ORDER BY goods_id limit 10
</select>
</mapper>

4 编写select sql标签

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <!--设置开启驼峰命名-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <!--default="dev" 设置默认指向的数据库-->
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"/>
            <!--采用连接池的方式-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://0.0.0.0:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/TGoodsDao.xml"></mapper>
    </mappers>
</configuration>

sqlSession执行select语句

package com.torey.mybatis;

import com.torey.mybatis.entity.TGoodsEntity;
import com.torey.mybatis.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;


/**
 * @ClassName：MybatisTestor
 * @Description：
 * @author: Torey
 */
public class MybatisTestor {
    @Test
    public void testSelectAll() throws Exception {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.openSession();
            List<TGoodsEntity> objects = sqlSession.selectList("goods.selectAll");
            for (TGoodsEntity object : objects) {
                System.out.println(object.toString());
            }
        } catch (Exception ex) {
            throw ex;
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }
}

导航，上一页，下一页

3初始化工具类MyBatisUtil
5sql传参l

支持我-微信扫一扫-加入微信公众号

赞赏作者