【JavaEE案例】商品的类别

川长思鸟来 2024-03-25 19:20 94阅读 0赞

**【案例介绍】**

现有一个商品表product和一个商品类别表category，其中，商品类别表category和商品表product是一对多的关系。商品表product和商品类别表category分别如下所示：

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td colspan="4" style="text-align:center;">商品表（product）</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">商品编号（id）</td> 
   <td style="text-align:center;">商品名称（goodsname）</td> 
   <td style="text-align:center;">商品单价（price）</td> 
   <td style="text-align:center;">商品类别（typeid）</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">1</td> 
   <td style="text-align:center;">电视机</td> 
   <td style="text-align:center;">5000</td> 
   <td style="text-align:center;">1</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">2</td> 
   <td style="text-align:center;">冰箱</td> 
   <td style="text-align:center;">4000</td> 
   <td style="text-align:center;">2</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">3</td> 
   <td style="text-align:center;">空调</td> 
   <td style="text-align:center;">3000</td> 
   <td style="text-align:center;">2</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">4</td> 
   <td style="text-align:center;">洗衣机</td> 
   <td style="text-align:center;">2000</td> 
   <td style="text-align:center;">2</td> 
  </tr> 
 </tbody> 
</table>

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td colspan="2" style="text-align:center;">商品类别表（category）</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">商品类别编号（id）</td> 
   <td style="text-align:center;">商品类别名称（typename）</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">1</td> 
   <td style="text-align:center;">黑色家电</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">2</td> 
   <td style="text-align:center;">白色家电</td> 
  </tr> 
 </tbody> 
</table>

本案例具体要求如下：根据商品表和商品类别表在数据库分别创建一个商品表product和商品类别表category，并通过MyBatis查询商品类别为白色家电的商品的所有信息。

**【案例实现】**

![46146d08d8f741e9982c43395121bd05.png][]

**1.数据准备：**在MySQL中创建一个名称为mybatis的数据库。

CREATE DATABASE mybatis;

①在数据库中创建product表，并插入测试数据：

create table product(
    	id int primary key auto_increment,
    	goodsname varchar(20) not null,
    	price int not null,
    	typeid int not null
    );

insert into product values(1,'电视机',5000,1);
    insert into product values(2,'冰箱',4000,2);
    insert into product values(3,'空调',3000,2);
    insert into product values(4,'洗衣机',2000,2);

② 在数据库中创建category表，并插入测试数据：

create table category(
    	id int(32) primary key auto_increment,
    	typename varchar(40)
    );

insert into category values(1,'黑色家电');
    insert into category values(2,'白色家电');

**2.引入相关依赖：**在项目的pom.xml文件中导入MySQL驱动包、JUnit测试包、MyBatis的核心包等相关依赖。

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
     
        <groupId>org.xinhua</groupId>
        <artifactId>MyBatisTest</artifactId>
        <version>1.0-SNAPSHOT</version>
     
        <properties>
            <maven.compiler.source>17</maven.compiler.source>
            <maven.compiler.target>17</maven.compiler.target>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        </properties>
     
        <dependencies>
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.5.11</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.28</version>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
                <scope>compile</scope>
            </dependency>
        </dependencies>
        <build>
            <resources>
                <resource>
                    <directory>src/main/java</directory>
                    <includes>
                        <include>**/*.properties</include>
                        <include>**/*.xml</include>
                    </includes>
                    <filtering>true</filtering>
                </resource>
            </resources>
        </build>
     
    </project>

**3.POJO类准备：①**创建持久化类Product，并在类中定义相关属性和方法。

package com.itheima.pojo;
    
    public class Product {
        private int id;
        private String goodsname;
        private double price;
        private int typeid;
    
        public void setId(int id) {
            this.id = id;
        }
        public void setGoodsname(String goodsname) {
            this.goodsname = goodsname;
        }
        public void setPrice(double price) {
            this.price = price;
        }
        public void setTypeid(int typeid) {
            this.typeid = typeid;
        }
        public int getId() {
            return id;
        }
        public String getGoodsname() {
            return goodsname;
        }
        public double getPrice() {
            return price;
        }
        public int getTypeid() {
            return typeid;
        }
    
        @Override
        public String toString(){
            return "Product{" + "id=" + id + ",goodsname=" + goodsname +",price=" + price + '}' + '\n';
        }
    }

②创建持久化类Category，并在类中定义商品类别的相关属性和方法。

package com.itheima.pojo;
    
    import java.util.List;
    
    public class Category {
        private Integer id; //主键id
        private String typename; //类别名称
        private List<Product> productList; //商品集合
    
        public void setId(Integer id) {
            this.id = id;
        }
        public void setTypename(String typename) {
            this.typename = typename;
        }
        public void setProductList(List<Product> productList) {
            this.productList = productList;
        }
        public Integer getId() {
            return id;
        }
        public String getTypename() {
            return typename;
        }
        public List<Product> getProductList() {
            return productList;
        }
    
        @Override
        public String toString(){
            return "Category{" + "id=" + id + ",typename=" + typename + ",productList=" + productList + '}' ;
        }
    }

**4.创建映射文件CategoryMapper.xml：**在文件中编写一对多关联映射查询的配置，该文件主要用于配置SQL语句和Java对象之间的映射 。

<?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="mapper.CategoryMapper">
        
    
        <select id="findCategoryWithProduct" parameterType="Integer" resultMap="CategoryWithProductResult">
            select c.*,p.id as category_id,p.goodsname,p.price
            from category c,product p
            where c.id=p.typeid and c.id=#{id}
        </select>
    
        <resultMap id="CategoryWithProductResult" type="com.itheima.pojo.Category">
            <id property="id" column="id"/>
            <result property="typename" column="typename"/>
            <collection property="productList" ofType="com.itheima.pojo.Product">
                <id property="id" column="category_id"/>
                <result property="goodsname" column="goodsname"/>
                <result property="price" column="price"/>
            </collection>
        </resultMap>
    </mapper>

**5.创建数据库连接信息配置文件db.properties：**在该文件中配置数据库连接的参数。

mysql.driver=com.mysql.cj.jdbc.Driver
    mysql.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&\
      characterEncoding=utf8&useUnicode=true&useSSL=false
    mysql.username=root
    mysql.password=root

**6.创建MyBatis的核心配置文件mybatis-config.xml：**该文件主要用于项目的环境配置。

<?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>
        
        
        <properties resource="db.properties"/>
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                
                <dataSource type="POOLED">
                    <property name="driver" value="${mysql.driver}"/>
                    <property name="url" value="${mysql.url}"/>
                    <property name="username" value="${mysql.username}"/>
                    <property name="password" value="${mysql.password}"/>
                </dataSource>
            </environment>
        </environments>
    
        
        <mappers>
            <mapper resource="mapper/CategoryMapper.xml"/>
        </mappers>
    
    </configuration>

**7.编写mybatisUtils工具类：**该类用于封装读取配置文件信息的代码。

package com.itheima.Utils;
     
    import java.io.Reader;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
     
    public class MyBatisUtils {
        private static SqlSessionFactory sqlSessionFactory = null;
        //初始化SQLSessionFactory类加载MyBatis的配置文件
        static {
            try {
                //使用MyBatis提供的Resources类加载MyBatis的配置文件
                Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
                //构建SqlSessionFactory工厂
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            }catch(Exception e){
                e.printStackTrace();
            }
        }
        public static SqlSession getSession(){
            //获取SqlSession对象的静态方法
            return sqlSessionFactory.openSession(true);
            //若传入true表示关闭事务控制，自动提交;false表示开启事务控制
        }
    }

**8.编写测试方法：**在测试类MyBatisTest中，编写测试方法findCategoryTest()。

package Test;
    import com.itheima.pojo.Category;
    import com.itheima.Utils.MyBatisUtils;
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    public class MyBatisTest {
        @Test
        public void findCategoryTest(){
            //通过工具类生成SqlSession对象
            SqlSession session = MyBatisUtils.getSession();
            //查询id为2的商品类别信息
            Category category = session.selectOne("findCategoryWithProduct",2);
            //输出查询结果
            System.out.println(category);
            //关闭SqlSession
            session.close();
        }
    }

9.运行结果：

![e0dd5d9930f547f49847af119f687ff5.png][]

[46146d08d8f741e9982c43395121bd05.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/cc5953ed8caa4092a961cfd6c21130d4.png
[e0dd5d9930f547f49847af119f687ff5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/8e9b1ae8d6f64e49a4c72499d5056693.png