This article describes the method of Java using the PreparedStatement interface and the ResultSet result set. Share it for your reference, as follows:
illustrate:
1. The PreparedStatement interface inherits Statement, and its instances contain compiled SQL statements, and the execution speed is faster than Statement.
2. PreparedStatement inherits all the functions of Statement. The three methods executeUpdate , executeQuery , and execute no longer require parameters.
3. In JDBC applications, PreparedStatement is generally used instead of Statement.
For easy operation, make some packaging first:
For connection database, close connection encapsulation, DbUtil.java has been mentioned in previous blogs;
Encapsulate the database table, here is the operation of the comp table in my database, so the encapsulation is 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; }}Then use the PreparedStatement interface to achieve the incremental operation:
package com.mysqltest.jdbc.xiao1;import java.sql.Connection;import java.sql.PreparedStatement;import com.mysqltest.jdbc.modelComp.CompMember;import com.mysqltest.jdbc.util.DbUtil;public class PstatementTest { private static DbUtil dbUtil = new DbUtil(); /** * Add member with PreparedStatement* @param mem * @return * @throws Exception */ private static int addMember(CompMember mem) throws Exception{ Connection con = dbUtil.getCon(); String sql = "insert into comp values(null,?,?,?)"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, mem.getName()); pstmt.setInt(2, mem.getAge()); pstmt.setDouble(3, mem.getSalary()); int result = pstmt.executeUpdate();//No need to pass in sql in the middle dbUtil.close(pstmt, con); //preparedStatement is a subclass, and it is OK to close it with the parent class. Return result; } public static void main(String[] args) throws Exception { CompMember mem = new CompMember("Liu Xiang", 24, 8000.00); int result = addMember(mem); if (result==1) { System.out.println("Add successfully"); } else { System.out.println("Add failed"); } }}Then use the PreparedStatement interface to implement query and use the ResultSet result set:
package com.mysqltest.jdbc.xiao2;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.List;import com.mysqltest.jdbc.modelComp.CompMember;import com.mysqltest.jdbc.util.DbUtil;public class ResultsetTest { private static DbUtil dbUtil = new DbUtil(); /** * Traversing query results* @throws Exception */ @SuppressWarnings("unused") private static void listMem1() throws Exception { Connection con = dbUtil.getCon();// Get the connection String sql = "select * from comp"; PreparedStatement pstmt = con.prepareStatement(sql); ResultSet rs = pstmt.executeQuery();// Return the result set// next() Turn the cursor to the next row while (rs.next()) { int id = rs.getInt(1);// Get the value of the first column id String name = rs.getString(2);// int age = rs.getInt(3); double salary = rs.getDouble(4); System.out.println("No.:" + id + "Name:" + name + "Age:" + age + "Salary:" + salary); System.out.println("+====================================================================================================================================================================================================================================================================================================================================================================================================================================== listMem2() throws Exception { Connection con = dbUtil.getCon();// Get the connection String sql = "select * from comp"; PreparedStatement pstmt = con.prepareStatement(sql); ResultSet rs = pstmt.executeQuery();// Return the result set// next() Turn the cursor to the next row while (rs.next()) { int id = rs.getInt("id");// Get the value of the first column id String name = rs.getString("name");// int age = rs.getInt("age"); double salary = rs.getDouble("salary"); System.out.println("number:" + id + "name:" + name + "age:" + age + "salary); System.out.println("+================================================================================================================================================================================================================================================================================================================================================================================================================================================================= dbUtil.getCon();// Get the connection String sql = "select * from comp"; PreparedStatement pstmt = con.prepareStatement(sql); ResultSet rs = pstmt.executeQuery();// Return the result set// next() Turn the cursor to the back row while (rs.next()) { int id = rs.getInt("id");// Get the value of the first column id String name = rs.getString("name");// int age = rs.getInt("age"); double salary = rs.getDouble("salary"); CompMember mem = new CompMember(id, name, age, salary); memList.add(mem);//Add to List} return memList; } public static void main(String[] args) throws Exception {// listMem1();// listMem2(); List<CompMember> memList = listMem3(); for (CompMember mem: memList) { //Tranquility through each element of the collection System.out.println(mem); } }}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.