作业6：实现商品信息管理并测试

package com.study.spring.dao;

import com.study.spring.model.Product;

import java.util.List;

public interface IProductDao {

    /**
     * 添加商品
     * @param product 商品信息
     * @return 增加数据返回影响的行数
     */
    public int addProduct(Product product);

    /**
     *
     * @param product_id 主键编号
     * @return 删除影响的行数
     */
    public int deleteProduct(int product_id);

    /**
     * 修改商品信息
     * @param product 封装要修改的数据
     * @return 修改数据影响的行数
     */
    public int updateProduct(Product product);

    /**
     * 查询所有商品
     * @return 查询到的集合
     */
    public List<Product> findAllProduct();

    /**
     * 根据商品编号查询商品
     * @param product_id 商品编号
     * @return 商品编号对应的商品
     */
    public Product findProductById(int product_id);

    /**
     * 商品数量
     * @return
     */
    public int count();

}

package com.study.spring.dao;


import com.study.spring.model.Product;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import java.util.List;

public class ProductDaoImpl implements IProductDao {
    // 属性注入
    private JdbcTemplate jdbcTemplate;
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public int addProduct(Product product) {
        //1.sql语句
        String sql = "insert into t_product(category,product_name,price,stock) values(?,?,?,?)";
        //2.指定参数，存入object[]
        Object[] params = {product.getCategory(),product.getProduct_name(),product.getPrice(),product.getStock()};
        //3.执行 增删该统一用update
        return jdbcTemplate.update(sql,params);
    }

    @Override
    public int deleteProduct(int product_id) {
        //sql语句
        String sql = "delete from t_product where product_id =?";
        //参数的顺序和？的顺序对应
        Object[] params = {product_id};
        //执行
        return jdbcTemplate.update(sql, params);
    }

    @Override
    public int updateProduct(Product product) {
        String sql = "update t_product set category=?,product_name=?,price=?,stock=? where product_id =?";
        Object[] params = {product.getCategory(),product.getProduct_name(),product.getPrice(),product.getStock(),product.getProduct_id()};
        return jdbcTemplate.update(sql,params);
    }


    @Override
    public List<Product> findAllProduct() {
        //sql语句
        String sql = "select * from t_product";
        //将查询到的数据行信息封装成实体类对象
        //数据库表的行数据给实体类的属性赋值
        //注意数据表字段和实体类属性要对应
        RowMapper<Product> rowMapper =
                new BeanPropertyRowMapper<Product>(Product.class);
        //执行（SQL语句和要封装的RowMapper 数据行和实体类的映射）
        return jdbcTemplate.query(sql, rowMapper);
    }

    @Override
    public Product findProductById(int product_id) {
        //1.sql语句
        String sql = "select * from t_product where product_id=?";
        //2.行映射
        RowMapper<Product> rowMapper =
                new BeanPropertyRowMapper<Product>(Product.class);
        //3.参数
        Object[] params = {product_id};
        //4.执行
        return jdbcTemplate.queryForObject(sql,params,rowMapper);
    }

    @Override
    public int count() {
        String sql = "select count(*) from t_product";
        SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
        if(rs.next()) {
            return rs.getInt(1);
        }
        return 0;
    }
}

package com.study.spring.model;

public class Product {
    private Integer product_id;
    private String category;
    private String product_name;
    private Double price;
    private Integer stock;
    private Integer state;

    public Integer getProduct_id() {
        return product_id;
    }

    public void setProduct_id(Integer product_id) {
        this.product_id = product_id;
    }

    public String getCategory() {
        return category;
    }

    public void setCategory(String category) {
        this.category = category;
    }

    public String getProduct_name() {
        return product_name;
    }

    public void setProduct_name(String product_name) {
        this.product_name = product_name;
    }

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }

    public Integer getStock() {
        return stock;
    }

    public void setStock(Integer stock) {
        this.stock = stock;
    }

    public Integer getState() {
        return state;
    }

    public void setState(Integer state) {
        this.state = state;
    }

    @Override
    public String toString() {
        return "Product{" +
                "product_id=" + product_id +
                ", category='" + category + '\'' +
                ", product_name='" + product_name + '\'' +
                ", price=" + price +
                ", stock=" + stock +
                ", state=" + state +
                '}';
    }
}

jdbc.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssmdb?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
jdbc.username=root
jdbc.password=123456

applicationContext.xml

<!-- 注册属性文件 -->
    <context:property-placeholder location="classpath:jdbc.properties"/>
    <!-- 配置数据源 给连接池配置-->
    <bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>
    <!-- 配置jdbcTemplate模板，注入dataSource -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!-- 配置DAO 注入jdbcTemplate-->

    <bean id="productDao" class="com.study.spring.dao.ProductDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>

pom.xml

<?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>spring.study</groupId>
    <artifactId>spring07</artifactId>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <!--设定spring版本-->
        <spring.version>5.2.11.RELEASE</spring.version>
    </properties>
    <!-- 添加依赖-->
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-expression</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <!--添加log4j依赖-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <!-- junit测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <!--spring测试-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <!--Spring数据库访问-->
        <!--数据库驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.21</version>
        </dependency>

       <!-- spring JDBC支持-JDBCTemplate-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <!--数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.4</version>
        </dependency>
        <!--日志-->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>1.2.3</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.3</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-access</artifactId>
            <version>1.2.3</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>