java中PreparedStatement接口及ResultSet结果集

说明：

1.PreparedStatement接口继承Statement,它的实例包含已编译的SQL语句，执行速度要快于Statement。

2.PreparedStatement继承了Statement的所有功能，三种方法executeUpdate、executeQuery、execute不再需要参数。

3.在JDBC应用中，一般都用PreparedStatement，而不是Statement。

便于操作，先做一些封装：

对连接数据库，关闭连接封装，在之前博客中已经提到DbUtil.java;

对数据库表进行封装，这里是对我的数据库中comp表进行操作，因此封装如下：

package com.mysqltest.jdbc.modelComp;public class CompMember {private int id;private String name;private int age;private double salary;/** * 构造函数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;}/** * 重载构造函数 * @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方法 */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/** * 改写toString,使得显示更好 */public String toString() {return "["+this.id+"]"+this.name+","+this.age+","+this.salary;}}

然后利用PreparedStatement接口实现增的操作：

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();/** * 用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();//中间不用传入sqldbUtil.close(pstmt, con); //preparedStatement是子类，用父类关闭也行return result;}public static void main(String[] args) throws Exception {CompMember mem = new CompMember("刘翔", 24, 8000.00);int result = addMember(mem);if (result==1) {System.out.println("添加成功");} else {System.out.println("添加失败");}}}

再利用PreparedStatement接口实现查询，并运用ResultSet结果集：

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();/** * 遍历查询结果 * @throws Exception */@SuppressWarnings("unused")private static void listMem1() throws Exception {Connection con = dbUtil.getCon();// 获取连接String sql = "select * from comp";PreparedStatement pstmt = con.prepareStatement(sql);ResultSet rs = pstmt.executeQuery();// 返回结果集// next()将光标向后一行while (rs.next()) {int id = rs.getInt(1);// 获取第一列的值idString name = rs.getString(2);//int age = rs.getInt(3);double salary = rs.getDouble(4);System.out.println("编号：" + id + "姓名：" + name + "年龄：" + age + "工资：" + salary);System.out.println("+====================================+");}}/** * 遍历查询结果方法2 * @throws Exception */@SuppressWarnings("unused")private static void listMem2() throws Exception {Connection con = dbUtil.getCon();// 获取连接String sql = "select * from comp";PreparedStatement pstmt = con.prepareStatement(sql);ResultSet rs = pstmt.executeQuery();// 返回结果集// next()将光标向后一行while (rs.next()) {int id = rs.getInt("id");// 获取第一列的值idString name = rs.getString("name");//int age = rs.getInt("age");double salary = rs.getDouble("salary");System.out.println("编号：" + id + "姓名：" + name + "年龄：" + age + "工资：" + salary);System.out.println("+====================================+");}}private static List<CompMember> listMem3() throws Exception{List<CompMember> memList = new ArrayList<CompMember>();Connection con = dbUtil.getCon();// 获取连接String sql = "select * from comp";PreparedStatement pstmt = con.prepareStatement(sql);ResultSet rs = pstmt.executeQuery();// 返回结果集// next()将光标向后一行while (rs.next()) {int id = rs.getInt("id");// 获取第一列的值idString 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);//添加到List中}return memList;}public static void main(String[] args) throws Exception {//listMem1();//listMem2();List<CompMember> memList = listMem3();for (CompMember mem : memList) { //遍历集合的每个元素System.out.println(mem);}}}