JDBC-用PreparedStatement针对不用表的通用查询操作

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import com.atguigu.bean.Customers;
import com.atguigu.bean.Order;
import com.atguigu.util.JDBCUtil;
	public <T> List<T> getForList(Class<T> clazz,String sql,Object...args)  {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			//获取连接
			conn = JDBCUtil.getConnection();
			//获取一个预编译sql语句对象
			ps = conn.prepareStatement(sql);
			//根据输入占位符的个数做一个for循环分别给占位符赋值
			for(int i = 0;i < args.length;i++) {
				ps.setObject(i + 1, args[i]);
			}
			//执行操作语句得到一个rs的结果集
			rs = ps.executeQuery();
			//获取rs结果集的元数据rsmd
			ResultSetMetaData rsmd = rs.getMetaData();
			//根据元数据的getcolumnCount的方法来获取列数
			int columnCount = rsmd.getColumnCount();
			//创建一个t类型的数组
			ArrayList<T> arrayList = new ArrayList<T>();
			//判断结果集是否有值
			while(rs.next()) {
				//创建一个t类型的对象
				T t = clazz.newInstance();
				
				//根据列的个数来遍历每列的值和对应的列名
				for(int i = 0;i <columnCount;i++) {
					//通过元数据rsmd的getcolumnLabel的方法来获得列的别名
					String columnLabel = rsmd.getColumnLabel(i+1);
					//获取当前列的值
					Object columnValue = rs.getObject(i+1);
					//通过反射得到一个order表中 名字为 （得到的别名的名字） 的属性
					Field field = clazz.getDeclaredField(columnLabel);
					//设置属性的权限
					field.setAccessible(true);
					//将columnValue的值放入order对象中的相对属性
					field.set(t, columnValue);
				}
				arrayList.add(t);
			}
			return arrayList;
		}catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.closeResourse(conn, ps, rs);
			
		}
		return null;
	}
		@Test 
	public void test2() {
		String sql = "select id,name,email from customers where id < ?";
		List<Customers> forList = getForList(Customers.class, sql, 8);
		forList.forEach(System.out::println);
		
		sql="SELECT order_name orderName,order_id orderId FROM `order` WHERE order_id <  ?";
		List<Order> forList2 = getForList(Order.class, sql, 4);
		forList2.forEach(System.out::println);
	}

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtil {
	public static Connection getConnection() throws Exception {
		InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
		Properties prop = new Properties();
		prop.load(is);
		
		String user = prop.getProperty("user");
		String password = prop.getProperty("password");
		String url = prop.getProperty("url");
		String driverclass = prop.getProperty("driverclass");
		
		
		Class.forName(driverclass);
		Connection conn = DriverManager.getConnection(url, user, password);
		return conn;
	}
	
	public static void closeResourse(Connection conn,Statement ps) {
		try {
			if(conn != null) {
				conn.close();
			}
		
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if(ps != null) {
				ps.close();
			}
	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void closeResourse(Connection conn,Statement ps,ResultSet rs) {
		try {
			if(conn != null) {
				conn.close();
			}
		
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if(ps != null) {
				ps.close();
			}
	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if(rs != null) {
				rs.close();
			}
	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

```java

import java.sql.Date;

public class Order {
	private int orderId;
	private String orderName;
	private Date orderDate;
	public Order(int orderId, String orderName, Date orderDate) {
		super();
		this.orderId = orderId;
		this.orderName = orderName;
		this.orderDate = orderDate;
	}
	public Order() {
		super();
	}
	public int getOrderId() {
		return orderId;
	}
	public void setOrderId(int orderId) {
		this.orderId = orderId;
	}
	public String getOrderName() {
		return orderName;
	}
	public void setOrderName(String orderName) {
		this.orderName = orderName;
	}
	public Date getOrderDate() {
		return orderDate;
	}
	public void setOrderDate(Date orderDate) {
		this.orderDate = orderDate;
	}
	@Override
	public String toString() {
		return "Order [orderId=" + orderId + ", orderName=" + orderName + ", orderDate=" + orderDate + "]";
	}
}


public class Customers {
	private int id;
	private String name;
	private String email;
	private Date birth;
	public Customers() {
		super();
	}
	public Customers(int id, String name, String email, Date birth) {
		super();
		this.id = id;
		this.name = name;
		this.email = email;
		this.birth = birth;
	}
	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 String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public Date getBirth() {
		return birth;
	}
	public void setBirth(Date birth) {
		this.birth = birth;
	}
	@Override
	public String toString() {
		return "Customers [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
	}	
}	

为什么preparestatement可以解决SQL的注入问题？

在预编译的时候，已经知道了查询语句中的 谁 and 谁 或者 谁 or 谁 的关系，无法再改变，并且认为 ？（占位符）就是一个值，无论填什么都相当于是给占位符赋值。