JDBC简介（PreparedStatement接口）

PreparedStatement操作

PreparedStatement属于Statement的子接口，实例化对象要使用Connection接口下的的方法：

public PrepareStatement preparedStatement（String sql）throws SQLException方法

范例1：改进数据增加

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Date;

public class OracleDemo {
	private static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DBURL = "jdbc:oracle:thin:@localhost:1521:acehzh";
	private static final String USER = "scott";
	private static final String PASSWORD = "tiger";

	public static void main(String[] args) throws Exception {
		String name = "Mr'Smith";
		Date birthday = new Date();
		int age = 18;
		String note = "外国人";
		// 1.加载数据库驱动程序
		Class.forName(DBDRIVER);
		// 2.连接数据库
		Connection conn = DriverManager.getConnection(DBURL, USER, PASSWORD);
		String sql = "INSERT INTO member(mid,name,birthday,age,note) VALUES " + " (myseq.nextval,?,?,?,?)";
		// 3.数据库操作
		PreparedStatement pre = conn.prepareStatement(sql);
		pre.setString(1, name);
		pre.setDate(2, new java.sql.Date(birthday.getTime()));
		pre.setInt(3, age);
		pre.setString(4, note);
		int len = pre.executeUpdate();
		System.out.println("更新行数:" + len);
		// 4.关闭数据库
		conn.close();
	}

}

==============分割线===============

范例2：查询全部数据

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

public class OracleDemo {
	private static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DBURL = "jdbc:oracle:thin:@localhost:1521:acehzh";
	private static final String USER = "scott";
	private static final String PASSWORD = "tiger";

	public static void main(String[] args) throws Exception {
		// 1.加载数据库驱动
		Class.forName(DBDRIVER);
		// 2.连接数据库
		Connection conn = DriverManager.getConnection(DBURL, USER, PASSWORD);
		// 3.操作数据库
		String sql = "SELECT mid,name,birthday,age,note FROM member ORDER BY mid";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			int mid = rs.getInt(1);
			String name = rs.getString(2);
			Date birthday = rs.getDate(3);
			int age = rs.getInt(4);
			String note = rs.getString(5);
			System.out.println(mid + "," + name + "," + birthday + "," + age + "," + note);
		}
		conn.close();
		ps.close();
		rs.close();
	}

}

==============分割线===============

范例3：分页显示

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

public class OracleDemo {
	private static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DBURL = "jdbc:oracle:thin:@localhost:1521:acehzh";
	private static final String USER = "scott";
	private static final String PASSWORD = "tiger";

	public static void main(String[] args) throws Exception {
		String keyWord = "";
		int currentPage = 2;
		int lineSize = 5;
		// 1.加载数据库驱动
		Class.forName(DBDRIVER);
		// 2.连接数据库
		Connection conn = DriverManager.getConnection(DBURL, USER, PASSWORD);
		// 3.操作数据库
		String sql = "SELECT * FROM (" 
					+ " SELECT mid,name,birthday,age,note,ROWNUM rn "
					+ " FROM member "
					+ " WHERE name LIKE ? AND ROWNUM<=?)temp " 
					+ " WHERE temp.rn>? ";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, "%" + keyWord + "%");
		ps.setInt(2, currentPage * lineSize);
		ps.setInt(3, (currentPage - 1) * lineSize);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			int mid = rs.getInt(1);
			String name = rs.getString(2);
			Date birthday = rs.getDate(3);
			int age = rs.getInt(4);
			String note = rs.getString(5);
			System.out.println(mid + "," + name + "," + birthday + "," + age + "," + note);
		}			
		conn.close();
		rs.close();

	}

}

==============分割线===============

范例4：统计数量，使用COUNT（）

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class OracleDemo {
	private static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DBURL = "jdbc:oracle:thin:@localhost:1521:acehzh";
	private static final String USER = "scott";
	private static final String PASSWORD = "tiger";

	public static void main(String[] args) throws Exception {
		String keyWord = "";
		// 1.加载数据库驱动
		Class.forName(DBDRIVER);
		// 2.连接数据库
		Connection conn = DriverManager.getConnection(DBURL, USER, PASSWORD);
		// 3. 操作数据库
		String sql = "SELECT COUNT(mid) FROM member WHERE name LIKE ?";
		PreparedStatement psta = conn.prepareStatement(sql);
		psta.setString(1, "%" + keyWord + "%");
		ResultSet rs = psta.executeQuery();
		if (rs.next()) {
			int count = rs.getInt(1);
			System.out.println(count);
		}
		conn.close();
		psta.close();
	}

}