PreparedStatement 与 ResultSet

1. TestPreparedStatement类

package day17;

import java.sql.ResultSet;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

/*
 * PreparedStatement比Statement速度快，而且安全性高
 * username="a" and psw="123"
 * username="a1" or 1=1 or 1="" and psw="123"
 */
public class TestPreparedStatement {

	public static void main(String[] args) throws Exception {
		
		 DBManager dbManager=null;
			Connection conn=null;
			PreparedStatement pstmt=null;
			ResultSet rs=null;
			//注册驱动
			dbManager=new DBManager();
			//获取连接
			conn=dbManager.getConnection();
			String sql="select * from students";
			pstmt=(PreparedStatement) conn.prepareStatement(sql);
			
			rs=pstmt.executeQuery();//rs永远不为空
			while(rs.next()){
				System.out.println(rs.getString(1)+" "+
						rs.getString(2)+" "+rs.getInt(3));
			}
			
			dbManager.closeResource(conn,pstmt,rs);
		
	}
   public void PreparedStatementInsert() throws Exception{
	   DBManager dbManager=null;
		Connection conn=null;
		PreparedStatement pstmt=null;
		//注册驱动
		dbManager=new DBManager();
		//获取连接
		conn=dbManager.getConnection();
		//组织sql语句:?从1开始一次增加
		String sqlinsert="insert into students(姓名,专业)values(?,?)";
		/*
		 * PreparedStatement对象将sql语句发送到数据库，表示预编译的SQL语句的对象。
		 */
		pstmt=(PreparedStatement) conn.prepareStatement(sqlinsert);
		pstmt.setString(1, "小熊");
		pstmt.setString(2, "计算机");
		
		//执行sql
		int k=pstmt.executeUpdate();
		System.out.println("k== "+k);
		//关闭资源
		dbManager.closeResource(conn,pstmt,null);
	   
   }
public static void PreparedStatementupdate() throws Exception{
	DBManager dbManager=null;
	Connection conn=null;
	PreparedStatement pstmt=null;
	//注册驱动
	dbManager=new DBManager();
	//获取连接
	conn= dbManager.getConnection();
	//组织sql语句:?从1开始一次增加
	String sqlupdate="update students set 姓名=?,专业=? where id=?";
	/*
	 * PreparedStatement对象将sql语句发送到数据库，表示预编译的SQL语句的对象。
	 */
	pstmt=(PreparedStatement) conn.prepareStatement(sqlupdate);
	pstmt.setString(1, "小bai");
	pstmt.setString(2, "计算机");
	pstmt.setInt(3, 2);
	
	//执行sql
	int k=pstmt.executeUpdate();
	System.out.println("k== "+k);
	//关闭资源
	dbManager.closeResource(conn,pstmt,null);
	
	   
   }
}

2. DBManager.java

package day17;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

public class DBManager {
	private String url="jdbc:mysql://localhost:3306/test";
	Connection conn=null;
	public DBManager() throws Exception{
		Class.forName("com.mysql.jdbc.Driver");
	}
	public Connection getConnection() throws SQLException{
		conn=(Connection) DriverManager.getConnection(url, "root", "");
		return conn;
	}
	public void closeResource(Connection conn, PreparedStatement pstmt,
			ResultSet rs) throws SQLException {
		if(rs!=null){
			rs.close();
		}
		if(pstmt!=null){
			pstmt.close();
		}
		if(conn!=null){
			conn.close();
		}
		
	}
	
}