JDBC增删改查练习

 

查

public class test {


    @Test
    public void select() throws Exception {
        Properties prop=new Properties();

        prop.load(new FileInputStream("C:\\Users\\Administrator\\IdeaProjects\\JDBC\\JDB\\src\\druid1.properties"));
        //获取连接池对象
        DataSource datasource= DruidDataSourceFactory.createDataSource(prop);
        //获取数据库连接
        Connection connection=datasource.getConnection();

        String sql="SELECT * FROM student;";
        //获取psmt对象
        PreparedStatement psmt=connection.prepareStatement(sql);
        //执行sql语句
        ResultSet res=psmt.executeQuery();
            student stu=new student();
            List<student> students=new ArrayList<>();
        while(res.next()){
            String name=res.getString("name");
            String id=res.getString("id");
            String cellphone=res.getString("cellphone");

            stu.setName(name);
            stu.setId(id);
            stu.setCellphone(cellphone);

            students.add(stu);

        }
        System.out.println(students);
        psmt.close();
        res.close();
        connection.close();

    }

}

public class Insert {
     //增
    @Test
    public void insert() throws Exception{

        //用户输入
        String name1="lolita的都是SB";
        String id1="44444";
        String cellphone1="886886886";

        //1.获取数据库和连接池连接
        Properties prop=new Properties();
        prop.load(new FileInputStream("C:\\Users\\Administrator\\IdeaProjects\\JDBC\\JDB\\src\\druid1.properties"));
        //获取连接池对象
        DataSource datasource= DruidDataSourceFactory.createDataSource(prop);
        //获取数据库连接
        Connection connection=datasource.getConnection();


        //2.定义sql语句
        String sql="INSERT INTO student(name,id,cellphone) VALUES(?,?,?);";


        //3.获取psmt对象
        PreparedStatement psmt=connection.prepareStatement(sql);

        //设置参数
        psmt.setString(1,name1);
        psmt.setString(2,id1);
        psmt.setString(3,cellphone1);

        //4.执行sql语句
        int count=psmt.executeUpdate();
        System.out.println(count>0);
        psmt.close();
        //res.close();
        connection.close();
    }
    //改
    @Test
    public void update() throws Exception{

        //用户输入.通过ID修改内容
        String name1="lolita的都是SBa";
        String id1="44444";
        String cellphone1="886886886";

        //1.获取数据库和连接池连接
        Properties prop=new Properties();
        prop.load(new FileInputStream("C:\\Users\\Administrator\\IdeaProjects\\JDBC\\JDB\\src\\druid1.properties"));
        //获取连接池对象
        DataSource datasource= DruidDataSourceFactory.createDataSource(prop);
        //获取数据库连接
        Connection connection=datasource.getConnection();


        //2.定义sql语句
        String sql="UPDATE student SET name=?,cellphone=? WHERE id=?;";


        //3.获取psmt对象
        PreparedStatement psmt=connection.prepareStatement(sql);

        //设置参数
        psmt.setString(1,name1);
        psmt.setString(2,cellphone1);
        psmt.setString(3,id1);
        //4.执行sql语句
        int count=psmt.executeUpdate();
        System.out.println(count>0);
        psmt.close();
        //res.close();
        connection.close();
    }

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

        //用户输入.通过ID修改内容
        //String name1="lolita的都是SBa";
        String id1="44444";
        //String cellphone1="886886886";

        //1.获取数据库和连接池连接
        Properties prop=new Properties();
        prop.load(new FileInputStream("C:\\Users\\Administrator\\IdeaProjects\\JDBC\\JDB\\src\\druid1.properties"));
        //获取连接池对象
        DataSource datasource= DruidDataSourceFactory.createDataSource(prop);
        //获取数据库连接
        Connection connection=datasource.getConnection();


        //2.定义sql语句
        String sql="DELETE  FROM student WHERE id=?;";


        //3.获取psmt对象.用于预编译。直接拼写SQL是很容易出错的且难用的，
        // PreparedStatement带有模版的思想，减少了出错的机率。
        PreparedStatement psmt=connection.prepareStatement(sql);

        //设置参数
        //psmt.setString(1,name1);
       // psmt.setString(2,cellphone1);
        psmt.setString(1,id1);
        //4.执行sql语句
        int count=psmt.executeUpdate();
        System.out.println(count>0);
        psmt.close();
        //res.close();
        connection.close();
    }
}