1, Statement interface introduction
Function: object used to execute a static SQL statement and return the result it generates
int executeUpdate(String sql) executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement, or an SQL statement that does not return anything, such as a SQLDDL statement.
void close() immediately releases the database and JDBC resources for this Statement object, rather than waiting for the object to close automatically.
2, Using Statement interface to add data
demo1:
public class Demo1 {
public static void main(String[] args) throws Exception {
DbUtil dbUtil = new DbUtil();
String sql = "insert into t_book values(null,'java Fucking great',888,'B Brother',1)";
Connection con = dbUtil.getCon();//Get database connection
Statement stmt = con.createStatement();//Get Statement
int result = stmt.executeUpdate(sql);
System.out.println("Result of operation:"+result+"data");
stmt.close();//Close statement
con.close();//Close connection
}
}demo2:
//Tool class
public class DbUtil {
//Database address
private static String dbUrl="jdbc:mysql://localhost:3306/db_book";
//User name
private static String dbUserName="root";
//Password
private static String dbPassword="root";
//Driver name
private static String jdbcName="com.mysql.jdbc.Driver";
/**
* Get database connection
* 1.Load database driver
* 2.Get database connection
*/
public Connection getCon() throws Exception {
Class.forName(jdbcName);//Load database driver
Connection con = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
return con;
}
/**
* Close connection
*/
public void close(Statement stmt,Connection con) throws Exception {
if(stmt!=null) {
stmt.close();
}
if(con!=null) {
con.close();
}
}
}
//Demo2
public class Demo2 {
private static DbUtil dbUtil = new DbUtil();
/**
* Add books
*/
private static int addBook(String bookName,float price,String author,int bookTypeId) throws Exception {
Connection con = dbUtil.getCon();
String sql = "insert into t_book values(null,'"+bookName+"',"+price+",'"+author+"',"+bookTypeId+")";
Statement stmt = con.createStatement();//Create Statement
int result = stmt.executeUpdate(sql);
dbUtil.close(stmt, con);//Close Statement and connection
return result;
}
public static void main(String[] args) throws Exception {
String bookName = "Java You're awesome";
float price = 100;
String author = "Niu Niu";
int bookTypeId = 1;
int result = addBook(bookName, price, author, bookTypeId);
if(result == 1) {
System.out.println("Added successfully!");
}else {
System.out.println("Failed to add!");
}
}
}demo3: in object mode
//Tool class
public class DbUtil {
//Database address
private static String dbUrl="jdbc:mysql://localhost:3306/db_book";
//User name
private static String dbUserName="root";
//Password
private static String dbPassword="root";
//Driver name
private static String jdbcName="com.mysql.jdbc.Driver";
/**
* Get database connection
* 1.Load database driver
* 2.Get database connection
*/
public Connection getCon() throws Exception {
Class.forName(jdbcName);//Load database driver
Connection con = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
return con;
}
/**
* Close connection
*/
public void close(Statement stmt,Connection con) throws Exception {
if(stmt!=null) {
stmt.close();
}
if(con!=null) {
con.close();
}
}
}
//book object
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;
}
. . . //get and set methods omitted
}
//demo3
public class Demo3 {
private static DbUtil dbUtil = new DbUtil();
/**
* Add book 2 as object
*/
private static int addBook2(Book book) throws Exception{
Connection con = dbUtil.getCon();
String sql = "insert into t_book values(null,'"+book.getBookName()+"',"+book.getPrice()+",'"+book.getAuthor()+"',"+book.getBookTypeId()+")";
Statement stmt = con.createStatement();//Create Statement
int result = stmt.executeUpdate(sql);
dbUtil.close(stmt, con);//Close Statement and connection
return result;
}
public static void main(String[] args) throws Exception {
Book book = new Book("Java Niu Niu 2", 99, "Niu Niu 2", 2);
int result = addBook2(book);
}
}3, Using Statement interface to update data
//Tool class
public class DbUtil {
//Database address
private static String dbUrl="jdbc:mysql://localhost:3306/db_book";
//User name
private static String dbUserName="root";
//Password
private static String dbPassword="root";
//Driver name
private static String jdbcName="com.mysql.jdbc.Driver";
/**
* Get database connection
* 1.Load database driver
* 2.Get database connection
*/
public Connection getCon() throws Exception {
Class.forName(jdbcName);//Load database driver
Connection con = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
return con;
}
/**
* Close connection
*/
public void close(Statement stmt,Connection con) throws Exception {
if(stmt!=null) {
stmt.close();
}
if(con!=null) {
con.close();
}
}
}
//book object
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;
}
}
//demo test class
public class Demo1 {
private static DbUtil dbUtil = new DbUtil();
private static int updateBook(Book book) throws Exception {
Connection conn = dbUtil.getCon();//Get connection
String sql = "update t_book set bookName='"+book.getBookName()
+"',price="+book.getPrice()+",author='"+book.getAuthor()
+"',bookTypeId="+book.getBookTypeId()+" where id="
+book.getId();
Statement stmt = conn.createStatement();
int result = stmt.executeUpdate(sql);
dbUtil.close(stmt, conn);//Shut down the state and connection
return result;
}
public static void main(String[] args) throws Exception {
Book book = new Book(3, "java Niu Niu 222", 123, "Niu", 1);
int result = updateBook(book);
System.out.println("Updated"+result+"data");
if(result==1) {
System.out.println("Update success");
}else {
System.out.println("Update failed");
}
}
}
4, Using Statement interface to delete data
public class Demo2 {
private static DbUtil dbUtil = new DbUtil();
private static int deleteBook(int id) throws Exception{
Connection con = dbUtil.getCon();//Get connection
String sql = "delete from t_book where id = "+id;
Statement stmt = con.createStatement();//Create Statement
int result = stmt.executeUpdate(sql);
dbUtil.close(stmt, con);
return result;
}
public static void main(String[] args) throws Exception {
int result = deleteBook(3);
if(result ==1) {
System.out.println("Delete successfully!");
}else {
System.out.println("Delete failed!");
}
}
}