java连接数据库的一些增删改查操作-CSDN博客

package example;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javafx.util.converter.IntegerStringConverter;
import org.testng.annotations.Test;
import pojo.brand;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.PipedInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Properties;
import java.util.zip.DeflaterInputStream;
import pojo.brand;


//品牌数据的增删改查操作
public class brandTest {


//    @Test
    //查询所有 1.select *from tb_brand 2.参数:不需要 3.结果:list<brand>
    public void getAlldata()throws Exception {

        Properties prop = new Properties();
        prop.load(new FileInputStream("G:\\javapack\\jdbc\\jdbc_demo\\src\\druid_ini.txt"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        Connection con = dataSource.getConnection();
        String sql = "select *from tb_brand";
        PreparedStatement execute = con.prepareStatement(sql);
        ResultSet rs = execute.executeQuery();
        ArrayList<brand> array = new ArrayList<>();
        while (rs.next()) {
            int id = rs.getInt(1);
            String brandName = rs.getString(2);
            String companyName = rs.getString(3);
            int ordered = rs.getInt(4);
            String description = rs.getString(5);
            int status = rs.getInt(6);
            brand sign = new brand(id, brandName, companyName, ordered, description, status);
            array.add(sign);
        }
        for (int i = 0; i < array.size(); i++) {
            System.out.println(array.get(i).toString());
        }
        rs.close();
        execute.close();
        con.close();
    }

//    @Test
//    添加数据
    public void add()throws Exception {

        Properties prop = new Properties();
        prop.load(new FileInputStream("G:\\javapack\\jdbc\\jdbc_demo\\src\\druid_ini.txt"));

        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        Connection con = dataSource.getConnection();

        String sql = "insert into tb_brand (brand_name,company_name,ordered,description,status) values(?,?,?,?,?)";
        PreparedStatement execute = con.prepareStatement(sql);

        //接收页面提交的参数
        String brand_name = "香飘飘";
        String company_name = "香飘飘";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;

        execute.setString(1,brand_name);
        execute.setString(2,company_name);
        execute.setInt(3,ordered);
        execute.setString(4,description);
        execute.setInt(5,status);

        int count = execute.executeUpdate();//影响的行数
        //处理结果
        System.out.println(count>0);
        execute.close();
        con.close();
    }

//    @Test
    //根据id修改
    public void replace()throws Exception {

        Properties prop = new Properties();
        prop.load(new FileInputStream("G:\\javapack\\jdbc\\jdbc_demo\\src\\druid_ini.txt"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        Connection con = dataSource.getConnection();

        String brand_name = "香飘飘";
        String company_name = "香飘飘";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;
        int id = 2;

        String sql = "update tb_brand set brand_name=?,company_name=?,ordered=?,description=?,status=? where id = ?";
        PreparedStatement execute = con.prepareStatement(sql);

        execute.setInt(6,id);
        execute.setString(1,brand_name);
        execute.setString(2,company_name);
        execute.setInt(3,ordered);
        execute.setString(4,description);
        execute.setInt(5,status);

        int count = execute.executeUpdate();
        System.out.println(count>0);

        execute.close();
        con.close();
    }

    @Test
//    删除数据
    public void delete()throws Exception {

        Properties prop = new Properties();
        prop.load(new FileInputStream("G:\\javapack\\jdbc\\jdbc_demo\\src\\druid_ini.txt"));

        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        Connection con = dataSource.getConnection();

        String sql = "delete from tb_brand where id = ?";
        PreparedStatement execute = con.prepareStatement(sql);

        //接收页面提交的参数
        int id = 4;

        execute.setInt(1,id);

        int count = execute.executeUpdate();//影响的行数
        //处理结果
        System.out.println(count>0);
        execute.close();
        con.close();
    }
}