usepreparedstatement.java

jsp 应用开发技术光盘 是《jsp应用开发技术》这本书的源代码

package ch13;

import java.sql.*;

public class UsePreparedStatement {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost/sql_test";
		String userName = "root";
		String password = "root";
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch(ClassNotFoundException e) {
			System.out.println("加载驱动器类时出现异常");
		}
		
		try {
			conn = DriverManager.getConnection(url, userName, password);
			
			//创建PreparedStatement语句
			PreparedStatement pstmtDelete = conn.prepareStatement(
					"DELETE FROM student WHERE stu_id>=?");		
			PreparedStatement pstmtInsert = conn.prepareStatement(
					"INSERT INTO student VALUES(?, ?, ?, ?)");	
			PreparedStatement pstmtSelect = conn.prepareStatement(
					"SELECT * FROM student WHERE stu_id>=? " +
					"ORDER BY stu_id");
			
			int id = 15;
			
			//使用setXXX方法设置IN参数
			pstmtSelect.setString(1, Integer.toString(id));
			
			//多次执行同一语句
			for (int i=0; i<3; i++, id++) {
				
                //使用setXXX方法设置IN参数
				pstmtDelete.setString(1, Integer.toString(id));
				
				pstmtInsert.setString(1, Integer.toString(id));
				pstmtInsert.setString(2, "name"+id);
				pstmtInsert.setString(3, "city"+id);
				pstmtInsert.setDate(4, new Date(78, 2, id));
				
                //执行PreparedStatement语句
				pstmtDelete.executeUpdate();
				pstmtInsert.executeUpdate();
				ResultSet rs = pstmtSelect.executeQuery();
				
				System.out.println("");
				System.out.println("第 " + (i+1) + " 次循环后的结果集为：");
                
				//显示返回的结果集
				while (rs.next()) {
					String stuID      = rs.getString(1);
					String name    = rs.getString(2);
					String address = rs.getString(3);
					String birthday= rs.getString(4);
					System.out.println(stuID + "   " + 
							name + "   " + address + "   " + birthday);
				}
			}
			
			pstmtDelete.close();
			pstmtInsert.close();
			pstmtSelect.close();
			
		} catch(SQLException e) {
			System.out.println("出现SQLException异常");
		} finally {
			//关闭语句和数据库连接
			try {
				if (conn != null) conn.close();
			} catch(SQLException e) {
				System.out.println("关闭数据库连接时出现异常");
			}		
		}
		
	}
}