JDBC--小案例

package com.JDBC;


import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class jdbcDame {
    @Test
    public void add() throws Exception {
        String brandName = "鸿星尔克";
        String companyName = "鸿星尔克";
        int ordered = 100;
        String description = "土逼南玻万";
        int status = 1;
        Properties pro = new Properties();
        pro.load(new FileInputStream("src/druid.properties"));
        DataSource data = DruidDataSourceFactory.createDataSource(pro);
        Connection conn = data.getConnection();
        String sql = "insert into tb_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, brandName);
        ps.setString(2, companyName);
        ps.setInt(3, ordered);
        ps.setString(4, description);
        ps.setInt(5, status);
        int i = ps.executeUpdate();
        System.out.println(i > 0);
    }

    @Test
    public void deleteById() throws Exception {
        int id = 4;
        Properties pro = new Properties();
        pro.load(new FileInputStream("src/druid.properties"));
        DataSource data = DruidDataSourceFactory.createDataSource(pro);
        Connection conn = data.getConnection();
        String sql = "delete from tb_brand where id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        System.out.println(i > 0);
    }

    @Test
    public void modifyById() throws Exception {
        int id = 4;
        String brandName = "鸿星尔克";
        String companyName = "鸿星尔克股份有限公司";
        int ordered = 100;
        String description = "To be number one";
        int status = 1;
        Properties pro = new Properties();
        pro.load(new FileInputStream("src/druid.properties"));
        DataSource data = DruidDataSourceFactory.createDataSource(pro);
        Connection conn = data.getConnection();
        String sql = "update tb_brand set brand_name = ?,company_name = ?,ordered = ?,description = ?,status = ? where id = ? ";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, brandName);
        ps.setString(2, companyName);
        ps.setInt(3, ordered);
        ps.setString(4, description);
        ps.setInt(5, status);
        ps.setInt(6, id);
        int i = ps.executeUpdate();
        System.out.println(i > 0);
    }

    @Test
    public void query() throws Exception {
        List<Brand> brands = new ArrayList<>();
        Brand brand = null;
        Properties pro = new Properties();
        pro.load(new FileInputStream("src/druid.properties"));
        DataSource data = DruidDataSourceFactory.createDataSource(pro);
        Connection conn = data.getConnection();
        String sql = "select * from tb_brand";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            brand = new Brand();
            brand.setId(rs.getInt("id"));
            brand.setBrand_name(rs.getString("brand_name"));
            brand.setCompany_name(rs.getString("company_name"));
            brand.setOrdered(rs.getInt("ordered"));
            brand.setDescription(rs.getString("description"));
            brand.setStatus(rs.getInt("status"));
            brands.add(brand);
        }
        System.out.println(brands);
    }
}