PreparedStatement封装与查询封装简介及案例呈现

1.PreparedStatement封装。

使用PreparedStatement主要就是为了防止SQL注入问题，PreparedStatement比statement多了一个SQL语句预处理的功能，下边我将PreparedStatement进行了封装处理。前边的JDBC简易封装那篇博客有将加载驱动、获取连接对象的方法进行过封装。

		public PreparedStatement getPreparedStatement(String sql,Object...args) throws SQLException{
			conn = getConnection();         //获取连接对象
			ps = conn.prepareStatement(sql);//预处理
			for(int i=0;i<args.length;i++){ 
				ps.setObject(i+1, args[i]); //获取完整的SQL语句，无通配符
			}
			return ps;
		}

2.查询封装。

	    public ArrayList<Object> execQueryList(String sql, Object[] data)
	    {
	        int colCount = 0;             //定义字段个数初始值0
	        ResultSetMetaData rsmd = null;//定义ResultSetMetaData对象 

	        try {
				ps = getPreparedStatement(sql,data);  //获取PreparedStatement对象
				rs = ps.executeQuery();//因为是PreparedStatement已经进行过预处理，获取Resultset对象
				rsmd = rs.getMetaData();              //获取ResultSetMetaData对象
	            colCount = rsmd.getColumnCount();     //获取字段个数
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
	        
	        ArrayList<Object> list = new ArrayList<Object>();//创建链表
	        try {
	            while(rs.next())
	            {
	                Map<String, Object> map = new HashMap<String, Object>();//创建map映射
	                for(int i=1; i<=colCount; i++)
	                {
	                    //getColumnLabel(i):第i个字段，getObject(i):第i个字段对应的记录
	                    map.put(rsmd.getColumnLabel(i), rs.getObject(i));
	                }
	                list.add(map);  //将对象放入链表
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	        	jdbcClose(conn,ps,rs); //释放资源
	        }
	        return list;
	    }

3.JDBC封装工具类代码(驱动加载、connection、preparedstatement、增删改、查询...)。

package com.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class PureJDBCUtils {

	private static String driver = null;
	private static String url = null;
	private static String username = null;
	private static String password = null;
	private Connection conn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private static PureJDBCUtils ju = null;
	
	public static PureJDBCUtils buildPureJDBCUtils(){
		if(ju==null){
			ju = new PureJDBCUtils();
		}
		return ju;
	}
	
	//因为加载驱动这件事情 在程序启动的时候就被加载且只执行一次
		static{
			try {
				Properties properties = new Properties();
				//如何 通过类获取流的对象
				InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
				properties.load(in);
				
				driver = properties.getProperty("driverClassName");
				url = properties.getProperty("url");
				username = properties.getProperty("username");
				password = properties.getProperty("password");
				
				Class.forName(driver);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		/**
		 * 获取连接对象
		 * @return
		 * @throws SQLException
		 */
		public static Connection getConnection() throws SQLException{
			return DriverManager.getConnection(url, username,password);
		}
		
		/**
		 * 获取PreparedStatement对象
		 * @param sql
		 * @param args
		 * @return
		 * @throws SQLException
		 */
		public PreparedStatement getPreparedStatement(String sql,Object...args) throws SQLException{
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			for(int i=0;i<args.length;i++){
				ps.setObject(i+1, args[i]);
			}
			return ps;
		}
		
		/**
		 * PreparedStatement,增删改
		 * @param sql
		 * @param args
		 * @throws SQLException 
		 */
		public void update(String sql,Object...args) throws SQLException{
			ps = getPreparedStatement(sql,args);
			int bRet = ps.executeUpdate();
			if(bRet>0){
				System.out.println("数据更新成功,更新记录数："+bRet+"条");
			}else{
				System.out.println("数据更新失败,更新记录数："+bRet+"条");
			}
			jdbcClose(conn,ps);
		}
		
		//查询,并以链表形式返回结果
	    public ArrayList<Object> execQueryList(String sql, Object[] data)
	    {
	        int colCount = 0;             //定义字段个数初始值0
	        ResultSetMetaData rsmd = null;//定义ResultSetMetaData对象 

	        try {
				ps = getPreparedStatement(sql,data);  //获取PreparedStatement对象
				rs = ps.executeQuery();//因为是PreparedStatement已经进行过预处理，获取Resultset对象
				rsmd = rs.getMetaData();              //获取ResultSetMetaData对象
	            colCount = rsmd.getColumnCount();     //获取字段个数
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
	        
	        ArrayList<Object> list = new ArrayList<Object>();//创建链表
	        try {
	            while(rs.next())
	            {
	                Map<String, Object> map = new HashMap<String, Object>();//创建map映射
	                for(int i=1; i<=colCount; i++)
	                {
	                    //getColumnLabel(i):第i个字段，getObject(i):第i个字段对应的记录
	                    map.put(rsmd.getColumnLabel(i), rs.getObject(i));
	                }
	                list.add(map);  //将对象放入链表
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	        	jdbcClose(conn,ps,rs); //释放资源
	        }
	        return list;
	    }
		
		public static void jdbcClose(Connection conn,PreparedStatement ps){
			
			try {
				ps.close();
				if(ps!=null){
					ps = null;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				try {
				conn.close();
				if(conn!=null){
					conn = null;
				}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		public static void jdbcClose(Connection conn,PreparedStatement ps,ResultSet rs){
			
			try {
				rs.close();
				if(rs != null){
					rs = null;
			    }
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				
				try {
					ps.close();
					if(ps!=null){
						ps = null;
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					try {
					conn.close();
					if(conn!=null){
						conn = null;
					}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
		}
}

4.测试类。

package com.openlab.test;

import static org.junit.Assert.*;

import java.util.ArrayList;

import org.junit.Test;

import com.openlab.utils.PureJDBCUtils;

public class PureTestCase {

	@Test
	public void test() {
		PureJDBCUtils ju = PureJDBCUtils.buildPureJDBCUtils();
		Object[] data = {};
		ArrayList<Object> list = ju.execQueryList("select * from tb7 where username=?", new Object[]{"qiweifeng"});
		System.out.println(list);
	}

	@Test
	public void test1() {
		PureJDBCUtils ju = PureJDBCUtils.buildPureJDBCUtils();
		Object[] data = {};
		ArrayList<Object> list = ju.execQueryList("select * from tb7 where username=?", new Object[]{"'or 1 or'"});
		System.out.println(list);
	}
}

显示结果：

可以看出，SQL注入使用封装好的PreparedStatement已解决。