BaseDao封装增删改查

BaseDao的好处:

减少代码量

采用泛型设计

工具类

获取连接和关闭连接

package Books;

import java.sql.*;

public class BooksDB {

    private String driver="com.mysql.jdbc.Driver";
    private String url="jdbc:mysql://localhost:3306/BooksTab";
    private String user="root";
    private String password="root";
    Connection conn=null;

    //    获取数据库链接对象
    public Connection getConn(){
        if(conn==null){
            try {
                Class.forName(driver);
                conn= DriverManager.getConnection(url,user,password);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
        return conn;
    }


    //    关闭数据库链接
    public void closeAll(Connection conn, Statement sta, ResultSet res){
        //关闭conn
        if(conn!=null){
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

//        关闭sta
        if(sta!=null){
            try {
                sta.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


//        关闭res
        if(res!=null){
            try {
                res.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    }



    //    增删改操作
    public int excuteUpdate(String sql,Object [] objs){
        int row=0;
//        获取链接
        Connection conn=getConn();
//        编写sql语句
        try {
//            预处理对象
            PreparedStatement pst= conn.prepareStatement(sql);
//            数据注入
            for (int i = 0; i < objs.length ; i++) {
                pst.setObject(i+1,objs[i]);
            }
//            处理数据
            row=pst.executeUpdate();
            closeAll(conn,pst,null);
//            释放资源
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return row;
    }

}

增删改方法

package Books;

public class BooksTest {
    BooksDB book=new BooksDB();
    public int Update(String name, String author, String price, String id){
        String sql= "UPDATE books set name=?,author=?,price=? where id=?";
        Object[] objects={name,author,price,id};
        int row= book.excuteUpdate(sql,objects);
        if(row>0){
            System.out.println("修改成功");
        }
        return row;
    }


    public int del(int id){
        String sql="DELETE from books where id=?";
        Object[] objects={id};
        int row=book.excuteUpdate(sql,objects);
        if(row>0){
            System.out.println("删除成功");
        }
        return row;
    }

    public int add(String name, String author, String price){
        String sql="insert into books(name,author,price)VALUES(?,?,?)";
        Object[] objects={name,author,price};
        int row=book.excuteUpdate(sql,objects);
        if(row>0){
            System.out.println("新增成功");
        }
        return row;
    }

}

实体类

package Books;

public class books {
    private int id;
    private String name;
    private String author;
    private int price;

    public books(int id, String name, String author, int price) {
        this.id = id;
        this.name = name;
        this.author = author;
        this.price = price;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }
}

测试代码

写SQL语句

  String sql= "UPDATE books set name=?,author=?,price=? where id=?";
        Object[] objects={name,author,price,id};
        int row= book.excuteUpdate(sql,objects);
        if(row>0){
            System.out.println("修改成功");
        }
        return row;

数据库

CREATE database BooksTab
use BooksTab

CREATE TABLE Books(
id int AUTO_INCREMENT primary key not null, 
name VARCHAR(20) not null,
author VARCHAR(20) not null,
price int not null
)

insert into books(name,author,price)values
("Java入门到入狱","唐某某",159),("python入门到入土","孙某某",129),
("半小时漫画计算机","刘欣",89),("偷心记忆体","工作室",167)

select * from books