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

时间:2022-09-24 18:52:08

本文实例讲述了java使用preparedstatement接口及resultset结果集的方法。分享给大家供大家参考,具体如下:

说明:

1.preparedstatement接口继承statement,它的实例包含已编译的sql语句,执行速度要快于statement。

2.preparedstatement继承了statement的所有功能,三种方法executeupdateexecutequeryexecute不再需要参数。

3.在jdbc应用中,一般都用preparedstatement,而不是statement。

便于操作,先做一些封装:

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
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接口实现增的操作:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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结果集:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
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