使用Statement接口实现增，删，改操作

1. Statement接口引入

作用：用于执行静态SQL语句并返回它所生成结果的对象。

int executeUpdate(String sql)执行给定SQL语句，该语句可能为INSERT、UPDATE或DELETE语句，或者不返回任何内容的SQL语句（如SQL DDL语句）。
void close()立即释放此Statement对象的数据库和JDBC资源，而不是等待该对象自动关闭时发生此操作。

2. 使用Statement接口实现添加数据操作

3. 使用Statement接口实现更新数据操作

4. 使用Statement接口实现删除数据操作

创建数据库db_jdbc
create table t_book(
    id int primary key auto_increment,
    bookName char(20),
    price decimal(8,2),
    author char(20),
    bookTypeId int
)

Book.java

package com.andrew.jdbc.model;

public class Book {
    private int id;
    private String bookName;
    private float price;
    private String author;
    private int bookTypeId;
    public Book(String bookName, float price, String author, int bookTypeId) {
        super();
        this.bookName = bookName;
        this.price = price;
        this.author = author;
        this.bookTypeId = bookTypeId;
    }
    public Book(int id, String bookName, float price, String author, int bookTypeId) {
        super();
        this.id = id;
        this.bookName = bookName;
        this.price = price;
        this.author = author;
        this.bookTypeId = bookTypeId;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getBookName() {
        return bookName;
    }
    public void setBookName(String bookName) {
        this.bookName = bookName;
    }
    public float getPrice() {
        return price;
    }
    public void setPrice(float price) {
        this.price = price;
    }
    public String getAuthor() {
        return author;
    }
    public void setAuthor(String author) {
        this.author = author;
    }
    public int getBookTypeId() {
        return bookTypeId;
    }
    public void setBookTypeId(int bookTypeId) {
        this.bookTypeId = bookTypeId;
    }
}

DbUtil.java

package com.andrew.jdbc.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class DbUtil {
    private static String dbUrl = "jdbc:mysql://localhost:3306/db_jdbc";
    private static String dbUserName = "root";
    private static String dbPassword = "root";
    private static String jdbcName = "com.mysql.jdbc.Driver";

    public Connection getConnection() throws Exception {
        Class.forName(jdbcName);
        Connection connection = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
        return connection;
    }

    public void close(Statement stmt, Connection con) throws Exception {
        if (stmt != null) {
            stmt.close();
            if (con != null) {
                con.close();
            }
        }
    }
}

TestInsert01.java

package com.andrew.jdbc.chap03;

import java.sql.Connection;
import java.sql.Statement;

import com.andrew.jdbc.util.DbUtil;

public class TestInsert01 {
    public static void main(String[] args) throws Exception {
        DbUtil dbUtil = new DbUtil();
        String sql = "insert into t_book values(null, 'java学习', 666, '张三', 1)";
        Connection connection = dbUtil.getConnection(); // 获取数据连接
        Statement statement = connection.createStatement(); // 获取Statement
        int result = statement.executeUpdate(sql);
        System.out.println("操作的结果：" + result + "数据");
        statement.close(); // 关闭statement
        connection.close(); // 关闭连接
    }
}

操作的结果：1数据

TestInsert02.java

package com.andrew.jdbc.chap03;

import java.sql.Connection;
import java.sql.Statement;
import com.andrew.jdbc.model.Book;
import com.andrew.jdbc.util.DbUtil;

public class TestInsert02 {
    private static DbUtil dbUtil = new DbUtil();
    private static int addBook(String bookName, float price, String author, int bookTypeId) throws Exception {
        Connection connection = dbUtil.getConnection(); // 获取连接
        String sql = "insert into t_book values(null,'" + bookName + "'," + price + ",'" + author + "'," + bookTypeId + ")";
        Statement stmt = connection.createStatement(); // 创建Statement
        int result = stmt.executeUpdate(sql);
        dbUtil.close(stmt, connection); // 关闭Statement和连接
        return result;
    }
    private static int addBook2(Book book) throws Exception {
        Connection connection = dbUtil.getConnection(); // 获取连接
        String sql = "insert into t_book values(null,'" + book.getBookName() + "'," + book.getPrice() + ",'"
                + book.getAuthor() + "'," + book.getBookTypeId() + ")";
        Statement statement = connection.createStatement(); // 创建Statement
        int result = statement.executeUpdate(sql);
        dbUtil.close(statement, connection); // 关闭Statement和连接
        return result;
    }

    public static void main(String[] args) throws Exception {
        int result = addBook("Java学习1", 101, "牛哥1", 1); 
        if (result == 1) {
            System.out.println("添加成功！"); 
        } else {
            System.out.println("添加失败！"); 
        }
        Book book = new Book("Java学习2", 102, "牛哥2", 2);
        int result2 = addBook2(book);
        if (result2 == 1) {
            System.out.println("添加成功！");
        } else {
            System.out.println("添加失败！");
        }
    }
}

添加成功！
添加成功！

TestUpdate.java

package com.andrew.jdbc.chap03;

import java.sql.Connection;
import java.sql.Statement;

import com.andrew.jdbc.model.Book;
import com.andrew.jdbc.util.DbUtil;

public class TestUpdate {
    private static DbUtil dbUtil = new DbUtil();
    private static int updateBook(Book book) throws Exception {
        Connection con = dbUtil.getConnection(); // 获取连接
        String sql = "update t_book set bookName='" + book.getBookName() + "',price=" + book.getPrice() + ",author='"
                + book.getAuthor() + "',bookTypeId=" + book.getBookTypeId() + " where id=" + book.getId();
        Statement stmt = con.createStatement(); // 创建Statement
        int result = stmt.executeUpdate(sql);
        dbUtil.close(stmt, con); // 关闭Statement和连接
        return result;
    }
    public static void main(String[] args) throws Exception {
        Book book = new Book(2, "Java修改", 666, "修改人2", 1);
        int result = updateBook(book);
        if (result == 1) {
            System.out.println("更新成功！");
        } else {
            System.out.println("更新败！");
        }
    }
}

更新成功！

TestDelete.java

package com.andrew.jdbc.chap03;

import java.sql.Connection;
import java.sql.Statement;

import com.andrew.jdbc.util.DbUtil;

public class TestDelete {
    private static DbUtil dbUtil = new DbUtil();
    private static int deleteBook(int id) throws Exception {
        Connection connection = dbUtil.getConnection(); // 获取连接
        String sql = "delete from t_book where id=" + id;
        Statement statement = connection.createStatement(); // 创建Statement
        int result = statement.executeUpdate(sql);
        dbUtil.close(statement, connection); // 关闭Statement和连接
        return result;
    }
    public static void main(String[] args) throws Exception {
        int result = deleteBook(3);
        if (result == 1) {
            System.out.println("删除成功！");
        } else {
            System.out.println("删除失败！");
        }
    }
}

删除成功！