JDBC----PrepareStatement

package java3.crud;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

import org.junit.Test;

import java3.util.JDBCUtils;

public class PreparedStatementUpdateTest {
	
	@Test
	public void testCommonUpdate(){
//		String sql="delete from customers where id=?";
//		update(sql,3);
		//order这个表名和关键字冲突，要用``包起来
		String sql="update `order` set order_name=? where order_id=?";
		update(sql,"qq",2);
	}
	
	//通用的增删改操作
	//sql中占位符的个数与可变形参的长度相同
	public void update(String sql,Object ...args)  {
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			//1.获取数据库的连接
			conn = JDBCUtils.getConnedtion();
			//2.预编译sql语句，返回PrepareStatement的实例
			ps = conn.prepareStatement(sql);
			//3.填充占位符
			for(int i=0;i<args.length;i++){
				ps.setObject(i+1, args[i]);
			}
			//4.执行
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			//5.资源的关闭
			JDBCUtils.closeResource(conn, ps);
			
		}
		
	}
	
	//修改customer表的一条记录
	@Test
	public void testUpdate()  {
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			//1.数据库的连接
			conn = JDBCUtils.getConnedtion();
			//2.预编译sql语句，返回PreparedStatement的实例
			String sql="update customers set name=? where id=?";
			ps = conn.prepareStatement(sql);
			
			//3.填充占位符
			ps.setObject(1, "莫扎特");
			ps.setObject(2, 18);
			//4.执行
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			//5.资源的关闭
			JDBCUtils.closeResource(conn, ps);
			
		}
		
	}
	
	
	//向Customer表中添加一条记录
	@Test
	public void testInsert() {
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			//1.读取配置文件中的4个基本信息
			InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
			Properties pros = new Properties();
			pros.load(is);
			
			String user=pros.getProperty("user");
			String password=pros.getProperty("password");
			String url=pros.getProperty("url");
			String driverClass=pros.getProperty("driverClass");
			
			//2.加载驱动
			Class.forName(driverClass);
			
			//3.获取连接
			conn = DriverManager.getConnection(url,user,password);
			
			//4.预编译sql语句，返回PreparedStatement的实例
			//?:占位符
			String sql="insert into customers(name,email,birth)values(?,?,?)";
			ps = conn.prepareStatement(sql);
			
			//5.填充占位符
			ps.setString(1, "孙悟空");
			ps.setString(2, "abd@huaguoshan.com");
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Date date = sdf.parse("1000-01-01");
			ps.setDate(3, new java.sql.Date(date.getTime()));
			
			//6.执行操作
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			//7.资源的关闭
			if(ps!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
		}
		
		
	}
}

package java3.crud;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import org.junit.Test;

import java3.bean.Customer;
import java3.util.JDBCUtils;

/*
 * 	针对于Customer表的查询操作
 */
public class CustomerForQuery {
	
	@Test
	public void testQueryForCustomers() throws Exception{
		String sql="select id,name,birth,email from customers where id=?";
		Customer customer = queryForCustomers(sql, 13);
		System.out.println(customer);
		
		sql="select name,email from customers where name=?";
		Customer queryForCustomers = queryForCustomers(sql, "周杰伦");
		System.out.println(queryForCustomers);
		
	}
	
	/*
	 * 针对Customers表的通用查询操作
	 */
	public Customer queryForCustomers(String sql,Object...args) throws Exception{
		
		Connection conn = JDBCUtils.getConnedtion();
		PreparedStatement ps = conn.prepareStatement(sql);
		for(int i=0;i<args.length;i++){
			ps.setObject(i+1, args[i]);
		}
		ResultSet rs = ps.executeQuery();
		//获取结果集的元数据：ResultSetMetaData
		ResultSetMetaData rsmd = rs.getMetaData();
		//通过ResultSetMetaData获取结果集中的列数
		int columnCount = rsmd.getColumnCount();
		
		if(rs.next()){
			Customer cust = new Customer();
			//处理结果集一行数据中的每一个列
			for(int i=0;i<columnCount;i++){
				//获取列值
				Object columnValue = rs.getObject(i+1);
				//获取每个列的列名
				//getColumnName():列名
				//getColumnLabel():列的别名（如果没有别名，那么就返回列名）
				String columnLabel = rsmd.getColumnLabel(i+1);
				
				//给cusst对象指定的columnName属性，赋值为columnValue:通过反射
				Field field = Customer.class.getDeclaredField(columnLabel);
				field.setAccessible(true);
				field.set(cust, columnValue);
			}
			return cust;
		}
		
		JDBCUtils.closeResource(conn, ps, rs);
		
		return null;
	}
	
	@Test
	public void testQuery1(){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet resultSet=null;
		try {
			conn = JDBCUtils.getConnedtion();
			String sql="select id,name,email,birth from customers where id=?";
			ps = conn.prepareStatement(sql);
			ps.setObject(1, 1);
			
			//执行，并返回结果集
			resultSet = ps.executeQuery();
			
			//next():判断结果集的下一条是否有数据，如果有数据返回true，并指针下移；如果返回false，指针不会下移
			if(resultSet.next()){
				//获取当前这条数据的各个字段值
				int id=resultSet.getInt(1);
				String name = resultSet.getString(2);
				String email = resultSet.getString(3);
				Date birth = resultSet.getDate(4);
				
				Customer customer = new Customer(id, name, email, birth);
				System.out.println(customer);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			
			JDBCUtils.closeResource(conn, ps,resultSet);
		}
		
	}
}

package java3.crud;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Date;

import org.junit.Test;

import java3.bean.Order;
import java3.util.JDBCUtils;

/*
 * 	针对于order表的通用的查询操作
 */
public class OrderForQuery {
	/*
	 * 	针对于表的字段与类的属性名不相同的情况下
	 * 1.必须声明sql时，使用类的属性名来命名字段的别名
	 * 2.使用ResultSetMetaData时，需要使用getColumnLabel()来替换getColumnName()来获取列的别名
	 * 说明：如果sql中没有给字段其别名，getColumnLabel()获取的就是列名
	 */
	
	
	
	@Test
	public void testOrderForQuery()  {
		String sql="select order_id orderId,order_name orderName,order_date orderDate from `order` where  order_id=?";
		Order orderForQuery = orderForQuery(sql, 1);
		System.out.println(orderForQuery);
		
	}
	
	/*
	 * 	通用的针对于order表的查询操作
	 */
	public Order orderForQuery(String sql,Object ...args)  {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn = JDBCUtils.getConnedtion();
			ps = conn.prepareStatement(sql);
			for(int i=0;i<args.length;i++){
				ps.setObject(i+1, args[i]);
			}
			
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			if(rs.next()){
				Order order = new Order();
				for(int i=0;i<columnCount;i++){
					//列值
					Object columnValue = rs.getObject(i+1);
					//列的别名
					String columnLabel = rsmd.getColumnLabel(i+1);
					
					//通过反射
					Field field = Order.class.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(order, columnValue);
				}
				return order;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.closeResource(conn, ps,rs);
			
		}
		
		return null;
		
	}
	
	@Test
	public void testQuery1() throws Exception{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn = JDBCUtils.getConnedtion();
			String sql="select order_id,order_name,order_date from `order` where order_id=?";
			ps = conn.prepareStatement(sql);
			ps.setObject(1, 1);
			
			rs = ps.executeQuery();
			
			if(rs.next()){
				int id = (int) rs.getObject(1);
				String name = (String) rs.getObject(2);
				Date date = (Date) rs.getObject(3);
				
				Order order = new Order(id, name, date);
				System.out.println(order);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.closeResource(conn, ps,rs);
			
		}
		
		
	}
}

package java3.crud;

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

import org.junit.Test;

import java3.bean.Customer;
import java3.bean.Order;
import java3.util.JDBCUtils;
/*
使用PreparedStatement实现针对于不同表的通用的查询操作
*/
public class PrepareStatementQueryTest {
	
	@Test
	public void testGetForList(){
		String sql="select id,name,email from customers where id < ?";
		List<Customer> forList = getForList(Customer.class, sql, 12);
//		System.out.println(forList);
		forList.forEach(System.out::println);
		
		System.out.println();
		String sql1="select order_id orderId,order_name orderName from `order`";
		List<Order> forList2 = getForList(Order.class, sql1);
		forList2.forEach(System.out::println);
		
		
	}
	
	
	public <T> List<T> getForList(Class<T> clazz,String sql,Object ...args){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn = JDBCUtils.getConnedtion();
			ps = conn.prepareStatement(sql);
			for(int i=0;i<args.length;i++){
				ps.setObject(i+1, args[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			
			System.out.println(columnCount);
			//创建集合对象
			ArrayList<T> list = new ArrayList<T>();
			while(rs.next()){
				T t=clazz.newInstance();
				
				for(int i=0;i<columnCount;i++){
					Object columnValue = rs.getObject(i+1);
					String columnLabel = rsmd.getColumnLabel(i+1);
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columnValue);
					
				}
				list.add(t);
			}
		return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.closeResource(conn, ps, rs);
		}
		
		return null;
	}
	
	
	@Test
	public void testGetInstance(){
		String sql="select id,name,email,birth from customers where id=?";
		Customer instance = getInstance(Customer.class, sql, 12);
		System.out.println(instance);
		
		String sql1="select order_id orderId,order_name orderName from `order` where order_id=?";
		Order instance2 = getInstance(Order.class, sql1, 1);
		System.out.println(instance2);
	}
	/*
	 * 	针对于不同表的查询操作，返回表中的一条记录
	 */
	public <T> T getInstance(Class<T> clazz,String sql,Object ...args)  {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn = JDBCUtils.getConnedtion();
			ps = conn.prepareStatement(sql);
			for(int i=0;i<args.length;i++){
				ps.setObject(i+1, args[i]);
			}
			rs = ps.executeQuery();
			//获取结果集的元数据
			ResultSetMetaData rsmd = rs.getMetaData();
			//获取列数
			int columnCount = rsmd.getColumnCount();
			if(rs.next()){
				T t=clazz.newInstance();
				//处理结果集一行数据中的每一个列
				for(int i=0;i<columnCount;i++){
					//获取列值
					Object columnValue = rs.getObject(i+1);
					
					//获取每个列的列名
					String columnLabel = rsmd.getColumnLabel(i+1);
					
					//给t对象指定的columnName属性，赋值为columnValue，通过反射
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columnValue);
					
				}
				return t;
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.closeResource(conn, ps, rs);
			
		}
		
		return null;
	}
}

package java3.bean;

import java.util.Date;

/*
 * 		ORM编程思想(object relational mapping)
 * 	一个数据表对应一个java类
 * 	表中的一条记录对应java类的一个对象
 * 	表中的一个字段对应java类的一个属性
 */
public class Customer {
	private int id;
	private String name;
	private String email;
	private Date birth;
	public Customer() {
		super();
	}
	public Customer(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 "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
	}
	
	
}

package java3.bean;

import java.util.Date;

public class Order {
	private int orderId;
	private String orderName;
	private Date orderDate;
	public Order() {
		super();
	}
	public Order(int orderId, String orderName, Date orderDate) {
		super();
		this.orderId = orderId;
		this.orderName = orderName;
		this.orderDate = orderDate;
	}
	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 + "]";
	}
	
	
}

package java3.util;

import java.io.IOException;
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 JDBCUtils {
	//获取数据库的连接
	public static Connection getConnedtion() throws Exception{
		//1.读取配置文件中的4个基本信息
		 InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
		 Properties pros = new Properties();
		 pros.load(is);
		 
		 String user=pros.getProperty("user");
		 String password=pros.getProperty("password");
		 String url=pros.getProperty("url");
		 String driverClass=pros.getProperty("driverClass");
		 
		 //2.加载驱动
		 Class.forName(driverClass);
		 
		 //3.获取连接
		 Connection conn = DriverManager.getConnection(url, user, password);
		 return conn;
		
	}
	
	//关闭连接和statement的操纵
	public static void closeResource(Connection conn,Statement ps){
		if(ps!=null){
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	//关闭带结构集的资源的操作
	public static void closeResource(Connection conn,Statement ps,ResultSet rs){
		if(ps!=null){
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
}