package PRACTICE;

import DUIXIANG.Brand;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class ControlOfBrandNew {
public static void main(String[] args) throws Exception {
//添加
//Insert();
//修改
//Update();
//刪除
Drop();
//查詢
SearchAll();
}
public static void SearchAll() throws Exception {//查詢
Properties prop = new Properties();
prop.load(new FileInputStream("./src/druid.properties"));
DataSource ds = DruidDataSourceFactory.createDataSource(prop);
Connection conn = ds.getConnection();
String sql = "select * from tb_brand";
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rst = pstmt.executeQuery();
List list = new ArrayList<>();
while(rst.next()){
Brand b = new Brand();
b.setId(rst.getInt("id"));
b.setBrand_name(rst.getString("brand_name"));
b.setCompany_name(rst.getString("company_name"));
b.setOrdered(rst.getInt("ordered"));
b.setDescription(rst.getString("description"));
b.setStatus(rst.getInt("status"));
list.add(b);
}
for(int i = 0;i < list.size();i ++){
System.out.println(list.get(i).getId()+" "+list.get(i).getBrand_name()+" "+list.get(i).getCompany_name()+" "+list.get(i).getOrdered()+" "+list.get(i).getDescription()+" "+list.get(i).getStatus());
}
pstmt.close();
conn.close();
rst.close();
}
public static void Insert() throws Exception {//添加
Properties prop = new Properties();
prop.load(new FileInputStream("./src/druid.properties"));
DataSource ds = DruidDataSourceFactory.createDataSource(prop);
Connection conn = ds.getConnection();
String sql = "insert into tb_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"邵陽湘窖酒業");
pstmt.setString(2,"邵陽湘窖酒業有限公司");
pstmt.setInt(3,30);
pstmt.setString(4,"美酒好吃");
pstmt.setInt(5,0);
int count = pstmt.executeUpdate();
if(count>0){
System.out.println("添加數據成功!");
}else{
System.out.println("添加數據失敗!");
}
conn.close();
pstmt.close();
}
public static void Update() throws Exception {//修改
Properties prop = new Properties();
prop.load(new FileInputStream("./src/druid.properties"));
DataSource ds = DruidDataSourceFactory.createDataSource(prop);
Connection conn = ds.getConnection();
String sql = "update tb_brand set brand_name= ?,company_name= ?,ordered=?,description= ?,status=? where id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"邵陽揚子巴士");
pstmt.setString(2,"邵陽揚子巴士有限公司");
pstmt.setInt(3,80);
pstmt.setString(4,"環游邵陽");
pstmt.setInt(5,1);
pstmt.setInt(6,7);
int count = pstmt.executeUpdate();
if(count>0){
System.out.println("修改成功!");
}else{
System.out.println("修改失敗!");
}
pstmt.close();
conn.close();
}
public static void Drop() throws Exception {
Properties prop = new Properties();
prop.load(new FileInputStream("./src/druid.properties"));
DataSource ds = DruidDataSourceFactory.createDataSource(prop);
Connection conn = ds.getConnection();
String sql = "delete from tb_brand where id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,5);
int count = pstmt.executeUpdate();
if(count>0){
System.out.println("刪除數據成功!");
}else{
System.out.println("刪除數據失敗!");
}
pstmt.close();
conn.close();
}
}