JDBC2增删改查

一，实体类：

1.无参构造方法：

//无参构造函数
    public Books() {

    }

2.有参构造方法：

//构造方法

    public Books(int bookId, String bookName, String bookAuthor, int bookNum, double bookPrice, String introduce) {
        this.bookId = bookId;
        this.bookName = bookName;
        this.bookAuthor = bookAuthor;
        this.bookNum = bookNum;
        this.bookPrice = bookPrice;
        this.introduce = introduce;
    }

3.getter和setter方法

 //getter和setter方法
    public int getBookId() {
        return bookId;
    }

    public void setBookId(int bookId) {
        this.bookId = bookId;
    }

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public String getBookAuthor() {
        return bookAuthor;
    }

    public void setBookAuthor(String bookAuthor) {
        this.bookAuthor = bookAuthor;
    }

    public int getBookNum() {
        return bookNum;
    }

    public void setBookNum(int bookNum) {
        if (0 <= bookNum && bookNum <= 100) {
            this.bookNum = bookNum;
        }
    }

    public double getBookPrice() {
        return bookPrice;
    }

    public void setBookPrice(double bookPrice) {
        this.bookPrice = bookPrice;
    }

    public String getIntroduce() {
        return introduce;
    }

    public void setIntroduce(String introduce) {
        this.introduce = introduce;
    }

4.toString方法

 //toString 方法
    @Override
    public String toString() {
        return "Books{" +
                "bookId=" + bookId +
                ", bookName='" + bookName + '\'' +
                ", bookAuthor='" + bookAuthor + '\'' +
                ", bookNum=" + bookNum +
                ", bookPrice=" + bookPrice +
                ", introduce='" + introduce + '\'' +
                '}';
    }

二，1.全查

 //全部查询
    @Test
    public void selectAll() throws Exception {
        //1.调用jar包
        //2.加载驱动
        Class.forName(driver);
        //3.调用Connection 获取连接
        Connection con = DriverManager.getConnection(url, username, password);
        //4.编写sql语句
        String sql="select*from teacher";
        PreparedStatement ps = con.prepareStatement(sql);
        //5.执行sql语句
        ResultSet rs = ps.executeQuery();
        //创建list集合
        List<Teacher> teacherList=new ArrayList<>();
        //6.遍历
        while(rs.next()){
            //创建teacher对象
            Teacher teacher=new Teacher();
            teacher.settId(rs.getInt("tId"));
            teacher.settName(rs.getString("tName"));
            teacher.settSex(rs.getString("tSex"));
            teacher.settAge(rs.getInt("tAge"));
            teacher.settAddr(rs.getString("tAddr"));
            //添加到list集合中
            teacherList.add(teacher);
        }
        System.out.println(teacherList);
        //7.关闭资源
        if(rs!=null){
            rs.close();
        }
        if(ps!=null){
              ps.close();
          }
        if(con!=null){
            con.close();
        }
    }

2.添加

//添加操作
    @Test
    public void testAdd() throws Exception {
        //1.调用jar包
        //2.加载管理器驱动
        Class.forName(driver);
        //3.通过驱动管理器获取Connection对象
        Connection con = DriverManager.getConnection(url, username, password);
        //4.编写sql语句
        String sql="insert into teacher(tName,tSex,tAge,tAddr) values(?,?,?,?);";
        //通过Connection 获取PreparedStatement对象
        PreparedStatement ps = con.prepareStatement(sql);
        //创建teacher对象将值存入
        Teacher teacher=new Teacher();
        teacher.settName("王五");
        teacher.settSex("男");
        teacher.settAge(21);
        teacher.settAddr("河北");
        //5.传参
        ps.setObject(1,teacher.gettName());
        ps.setObject(2,teacher.gettSex());
        ps.setObject(3,teacher.gettAge());
        ps.setObject(4,teacher.gettAddr());
        //6.执行sql语句
        int n=ps.executeUpdate();
        //判断结果
        if(n>0){
            System.out.println("插入成功");
        }else{
            System.out.println("插入失败");
        }
        //7.关闭资源
        if(ps!=null){
            ps.close();
        }
        if(con!=null){
            con.close();
        }

    }

3.删除

//删除操作
    @Test
    public void testDel() throws Exception {
        //1.调用jar包
        //2.加载管理器驱动
        Class.forName(driver);
        //3.通过管理器驱动获取Connection对象
        Connection con = DriverManager.getConnection(url, username, password);
        //4.编写sql语句
        String sql="delete from teacher where tId=?";
        //获取PreparedStatement对象
        PreparedStatement ps = con.prepareStatement(sql);
        //5.传参
        int tId=5;
        ps.setObject(1,tId);
        //6.执行sql语句
        int n=ps.executeUpdate();
        //判断结果
        if(n>0){
            System.out.println("删除成功");
        }else{
            System.out.println("删除失败");
        }
        //7.回收资源
        if(ps!=null){
            ps.close();
        }
        if(con!=null){
            con.close();
        }
    }

4.修改

//修改操作
    public Teacher selectById(int id) throws Exception{
        //1.调用jar包
        //2.加载管理器驱动
        Class.forName(driver);
        //3.通过管理器驱动程序获取Connection
        Connection con = DriverManager.getConnection(url, username, password);
        //4.编写sql语句
        String sql="select*from teacher where tId=?";
        //获取PreparedStatement
        PreparedStatement ps = con.prepareStatement(sql);
        //5.执行sql语句
        ps.setObject(1,id);
        ResultSet rs = ps.executeQuery();
        //创建teacher对象
        Teacher teacher=null;
        //6.操作判断
        if(rs.next()){
            teacher=new Teacher();
            teacher.settName(rs.getString("tName"));
            teacher.settSex(rs.getString("tSex"));
            teacher.settAge(rs.getInt("tAge"));
            teacher.settAddr(rs.getString("tAddr"));
        }
        //回收资源
        if(rs!=null){
            rs.close();
        }
        if(ps!=null){
            ps.close();
        }
        if(con!=null){
            con.close();
        }
        return teacher;
    }
    @Test
    public void testUpdate() throws Exception {
        Teacher teacher=selectById(2);
        //获取修改前的
        System.out.println("修改前的："+teacher);
        //创建Scanner对象
        Scanner sc=new Scanner(System.in);
        //提示用户输入
        System.out.println("请输入修改后的姓名：");
        //获取用户输入的数据
        String newName=sc.next();
        teacher.settName(newName);
        //提示用户输入
        System.out.println("请输入修改后的性别：");
        //获取用户输入的数据
        String newSex=sc.next();
        teacher.settSex(newSex);
        //提示用户输入
        System.out.println("请输入修改后的年龄：");
        //获取用户输入的数据
        int newAge=sc.nextInt();
        teacher.settAge(newAge);
        //提示用户输入
        System.out.println("请输入修改后的地址：");
        //获取用户输入的数据
        String newAddr=sc.next();
        teacher.settAddr(newAddr);
        System.out.println("修改后："+teacher);

        //1.调用jar包
        //2.加载管理器驱动
        Class.forName(driver);
        //3.通过管理驱动程序获取
        Connection con = DriverManager.getConnection(url, username, password);
        //4.编写sql语句
        String sql="update teacher set tName=?,tSex=?,tAge=?,tAddr=? where tId=?";
        //获取PreparedStatement
        PreparedStatement ps = con.prepareStatement(sql);
        //5.传参
        ps.setObject(1,teacher.gettName());
        ps.setObject(2,teacher.gettSex());
        ps.setObject(3,teacher.gettAge());
        ps.setObject(4,teacher.gettAddr());
        ps.setObject(5,teacher.gettId());
        //6.执行sql语句
        int n=ps.executeUpdate();
        //判断结果
        if(n>0){
            System.out.println("修改成功");
        }else{
            System.out.println("修改失败");
        }
        //7.回收资源
        if(ps!=null){
            ps.close();
        }
        if(con!=null){
            con.close();
        }
    }

5.模糊查询

//模糊查询
    @Test
    public void selectTest() throws Exception {
        //1.
        //2.加载驱动
        Class.forName(driver);
        //3.获取Connection
        Connection con = DriverManager.getConnection(url, username, password);
        //4.编写sql语句
        String sql="select*from teacher where tName like ?";
        PreparedStatement ps = con.prepareStatement(sql);
        //5.传参
        String a="刘";
        ps.setObject(1,"%"+a+"%");
        //6.执行sql语句
        ResultSet rs = ps.executeQuery();
        List<Teacher> teacherList=new ArrayList<>();
        //遍历
        while(rs.next()){
            Teacher teacher=new Teacher();
            teacher.settId(rs.getInt("tId"));
            teacher.settName(rs.getString("tName"));
            teacher.settSex(rs.getString("tSex"));
            teacher.settAge(rs.getInt("tAge"));
            teacher.settAddr(rs.getString("tAddr"));
            teacherList.add(teacher);
        }
        System.out.println(teacherList);
        //回收资源
        if(rs!=null){
            rs.close();
        }
        if(ps!=null){
            ps.close();
        }
        if(con!=null){
            con.close();
        }
    }