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);
		}
	}
}