This article describes the method of Java using the Statement interface to perform SQL statement operations. Share it for your reference, as follows:
Statement executes SQL statement:
1. When deleting and modifying the database, use stmt.executeUpdate(sql) to execute the given SQL statements, namely insert , update , and delete .
2. When querying the database, use stmt.executeQuery(sql) directly, and the result can be returned as a resultSet result set.
First do some preparation:
① Encapsulate the database table to be operated, such as the aistu table in my data in mydata, encapsulate it with AiMember.java for subsequent operations. The details are as follows:
package com.mysqltest.jdbc.model;/** * Define a model * Member model* @author AI_STU * */public class AiMember { private String name; private int id; private int age; private String email; private String tel; private double salary; private String riqi; /** * alt+shift+s Add a constructor generating constructor using fields. * @param name * @param id * @param age * @param email * @param tel * @param salary * @param riqi */ public AiMember(String name, int id, int age, String email, String tel, double salary, String riqi) { super(); this.name = name; this.id = id; this.age = age; this.email = email; this.tel = tel; this.salary = salary; this.riqi = riqi; } //Refactor public AiMember(int id) { super(); this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getId() { return id; } public void setId(int id) { this.id = id; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getTel() { return tel; } public void setTel(String tel) { this.tel = tel; } public double getSalary() { return salary; } public void setSalary(double salary) { this.salary = salary; } public String getRiqi() { return riqi; } public void setRiqi(String riqi) { this.riqi = riqi; }}② Encapsulate the connection to the MySQL database and close the connection method. Here it is encapsulated with DbUtil.java, as follows:
package com.mysqltest.jdbc.modelComp;public class CompMember { private int id; private String name; private int age; private double salary; /** * Constructor 1 * @param name * @param age * @param salary */ public CompMember(String name, int age, double salary) { super(); this.name = name; this.age = age; this.salary = salary; } /** * Overload constructor* @param id * @param name * @param age * @param salary */ public CompMember(int id, String name, int age, double salary) { super(); this.id = id; this.name = name; this.age = age; this.salary = salary; } /** * get,set method*/ 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 int getAge() { return age; } public void setAge(int age) { this.age = age; } public double getSalary() { return salary; } public void setSalary(double salary) { this.salary = salary; } @Override /** * Rewrite toString to make the display better*/ public String toString() { return "["+this.id+"]"+this.name+","+this.age+","+this.salary; }}The preparations are done, and now we will start using the Statement interface to execute SQL statements to implement addition, deletion and modification:
① Add:
package com.mysqltest.jdbc.two2;import java.sql.Connection;import java.sql.Statement;import com.mysqltest.jdbc.model.AiMember;import com.mysqltest.jdbc.util.DbUtil;public class Demo3 { /** * Add member to table 1 * @param name * @param id * @param age * @param email * @param tel * @param salary * @param riqi * @return * @throws Exception */ @SuppressWarnings("unused") private static int addMember(String name,int id,int age,String email,String tel,double salary,String riqi) throws Exception{ DbUtil dbUtil = new DbUtil();//Connection con = dbUtil.getCon(); //Get database connection String sql = "insert into aistu values('"+name+"',"+id+",'"+age+"','"+email+"','"+tel+"','"+salary+"','"+riqi+"')"; Statement stmt = con.createStatement();//Get statement int result = stmt.executeUpdate(sql); dbUtil.close(stmt, con); return result; } /** * Add member to table 2 methods* @param mem * @return * @throws Exception */ private static int addMember2(AiMember mem) throws Exception{ //AiMember is also encapsulated before// mem.getName(); DbUtil dbUtil = new DbUtil();//Previously encapsulated Connection con = dbUtil.getCon(); //Get the database connection String sql = "insert into aistu values('"+mem.getName()+"',"+mem.getId()+",'"+mem.getAge()+"','"+mem.getEmail()+"','"+mem.getTel()+"','"+mem.getSalary()+"','"+mem.getRiqi()+"'); Statement stmt = con.createStatement();//Get statement int result = stmt.executeUpdate(sql); dbUtil.close(stmt, con); return result; }// private static int addMenber2() public static void main(String[] args) throws Exception { /*int result = addMember("Liu Xiang", 4, 28, "[email protected]", "13411957776", 8000.00, "2015-09-10"); if(result==1){ System.out.println("Add successful"); }else{ System.out.println("Add failed"); }*/ //Multi-line comments, ctrl+shift+/ AiMember mem = new AiMember("Li Na", 6, 25, "[email protected]", "13411957775", 8000.00, "2015-09-03"); int result = addMember2(mem); if(result==1){ System.out.println("Add successful"); }else{ System.out.println("Add failed"); } }}②Change:
package com.mysqltest.jdbc.two3;import java.sql.Connection;import java.sql.Statement;import com.mysqltest.jdbc.model.AiMember;import com.mysqltest.jdbc.util.DbUtil;public class Demo4 { private static DbUtil dbUtil = new DbUtil();// @SuppressWarnings("unused") /** * Modify member* @param mem * @return * @throws Exception */ private static int updateMember(AiMember mem) throws Exception { Connection con = dbUtil.getCon(); // Get database connection String sql = "update aistu set name='" + mem.getName() + "',id=" + mem.getId() + ",age='" + mem.getAge() + "',email='" + mem.getEmail() + "',tel='" + mem.getTel() + "',salary='" + mem.getSalary() + "',riqi='" + mem.getRiqi() + "' where id=" + mem.getId(); //Format, select ctrl+a, and then ctrl+shift+f Format Statement stmt = con.createStatement();// Get statement int result = stmt.executeUpdate(sql); dbUtil.close(stmt, con); return result;// return 0; } public static void main(String[] args) throws Exception { AiMember mem = new AiMember("Rauer", 6, 24, "[email protected]", "13411957770", 18000.00, "2014-09-03"); int result = updateMember(mem); if (result==1) { System.out.println("Update Success"); } else { System.out.println("Update Failed"); } }}③Delete:
package com.mysqltest.jdbc.two4;import java.sql.Connection;import java.sql.Statement;import com.mysqltest.jdbc.model.AiMember;import com.mysqltest.jdbc.util.DbUtil;public class Demo5 { private static DbUtil dbUtil = new DbUtil(); public static int deleteMember(AiMember mem) throws Exception{ Connection con = dbUtil.getCon(); // Get database connection String sql = "delete from aistu where id="+mem.getId(); Statement stmt = con.createStatement();// Get statement int result = stmt.executeUpdate(sql); dbUtil.close(stmt, con); return result; } public static void main(String[] args) throws Exception { AiMember mem = new AiMember(5); int result = deleteMember(mem); if (result==1) { System.out.println("Successfully deleted member"); } else { System.out.println("Delete member failed"); } }}For more information about Java-related content, please check out the topics of this site: "Java+MySQL database programming summary", "Java data structure and algorithm tutorial", "Java file and directory operation skills summary", "Java operation DOM node skills summary" and "Java cache operation skills summary"
I hope this article will be helpful to everyone's Java programming.