Java使用PreparedStatement接口及ResultSet结果集的方法示例

本文实例讲述了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();//中间不用传入sql
    dbutil.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);// 获取第一列的值id
      string 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");// 获取第一列的值id
      string 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");// 获取第一列的值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);//添加到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);
    }
  }
}

希望本文所述对大家java程序设计有所帮助。

原文链接：https://blog.csdn.net/u010986080/article/details/51813056