JDBC学习（第一天）-2-第三章-使用PreparedStatement实现CRUD操作-CSDN博客

本文链接：https://blog.csdn.net/shouhmzhl/article/details/123259553

该博客主要展示了如何使用Java的PreparedStatement进行数据库的增删改查操作。内容包括了通用的更新（update）方法实现，以及针对Customers和Order表的查询（query）方法，这些方法利用了PreparedStatement预编译SQL以提高效率，并通过反射将查询结果映射到对应的Java对象。此外，还提供了JDBCUtils工具类以辅助数据库连接和资源关闭。

摘要由CSDN通过智能技术生成

package com.atguigu3.preparedstatement.crud;

import java.sql.Connection;
import java.sql.PreparedStatement;

import org.junit.Test;

import com.atguigu3.util.JDBCUtils;

public class preparedstatementHJR {
	@Test
	public void testCommonUpdate() {
		/*
		//增
		String sqlString = "insert into customers(name,email)"
			            	+ "value(?,?)";
		Update(sqlString,"我的林志玲","lzl@qq.com");
		 */

		/*
		//删
		String sqlString = "delete from customers "
				             + "where id = ?";
		Update(sqlString, 3);
		 */
		
		//改
		String sql = "update `order` set order_name = ? where order_id = ?"; //说明可以对任何表进行增删改操作
		Update(sql,"DD","2");
	}
	
	
	public void Update(String sql,Object ...args){
		//1.获取数据库的连接
		Connection connection = null;
		//2.预编译sql语句，返回PreparedStatement的实例
		PreparedStatement pStatement =null;
		try {
			connection = JDBCUtils.getConnection();
			pStatement = connection.prepareStatement(sql);
			//3.填充占位符
			for (int i = 0; i < args.length; i++) {
				pStatement.setObject(i + 1, args[i]);
			}
			//4.执行
			pStatement.execute();
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			//5.资源的关闭
			JDBCUtils.closeResource(connection, pStatement);	
		}
	}
}

package com.atguigu3.util;

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;

/**
 * 
 * @Description 操作数据库的工具类
 * @author shkstart Email:shkstart@126.com
 * @version
 * @date 上午9:10:02
 *
 */
public class JDBCUtils {
	
	/**
	 * 
	 * @Description 获取数据库的连接
	 * @author shkstart
	 * @date 上午9:11:23
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection() 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;
	}
	/**
	 * 
	 * @Description 关闭连接和Statement的操作
	 * @author shkstart
	 * @date 上午9:12:40
	 * @param conn
	 * @param ps
	 */
	public static void closeResource(Connection conn,Statement ps){
		try {
			if(ps != null)
				ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 
	 * @Description 关闭资源操作
	 * @author shkstart
	 * @date 上午10:21:15
	 * @param conn
	 * @param ps
	 * @param rs
	 */
	public static void closeResource(Connection conn,Statement ps,ResultSet rs){
		try {
			if(ps != null)
				ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(rs != null)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

package com.atguigu3.preparedstatement.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 com.atguigu3.bean.Customer;
import com.atguigu3.util.JDBCUtils;

/**
 * 
 * @Description 针对于Customers表的查询操作
 * @author shkstart  Email:shkstart@126.com
 * @version 
 * @date 上午10:04:55
 *
 */
public class CustomerForQuery {
	
	@Test
	public void testQueryForCustomers(){
		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 customer1 = queryForCustomers(sql,"周杰伦");
		System.out.println(customer1);
	}
	
	/**
	 * 
	 * @Description 针对于customers表的通用的查询操作
	 * @author shkstart
	 * @throws Exception 
	 * @date 上午10:23:40
	 */
	public Customer queryForCustomers(String sql,Object...args){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			
			ps = conn.prepareStatement(sql);
			for(int i = 0;i < args.length;i++){
				ps.setObject(i + 1, args[i]);
			}
			
			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 columValue = rs.getObject(i + 1);
					
					//获取每个列的列名
//					String columnName = rsmd.getColumnName(i + 1);
					String columnLabel = rsmd.getColumnLabel(i + 1);
					
					//给cust对象指定的columnName属性，赋值为columValue：通过反射
					Field field = Customer.class.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(cust, columValue);
				}
				return cust;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, ps, rs);
			
		}
		
		return null;
		
		
	}
	
}

package com.atguigu3.preparedstatement.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 com.atguigu3.bean.Order;
import com.atguigu3.util.JDBCUtils;

/**
 * 
 * @Description 针对于Order表的通用的查询操作
 * @author shkstart  Email:shkstart@126.com
 * @version 
 * @date 上午10:43:58
 *
 */
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 order = orderForQuery(sql,1);
		System.out.println(order);
	}
	
	/**
	 * 
	 * @Description 通用的针对于order表的查询操作
	 * @author shkstart
	 * @date 上午10:51:12
	 * @return
	 * @throws Exception 
	 */
	public Order orderForQuery(String sql,Object...args){
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			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++){
					//获取每个列的列值:通过ResultSet
					Object columnValue = rs.getObject(i + 1);
					//通过ResultSetMetaData
					//获取列的列名：getColumnName() --不推荐使用
					//获取列的别名：getColumnLabel()
//					String columnName = rsmd.getColumnName(i + 1);
					String columnLabel = rsmd.getColumnLabel(i + 1);
					
					//通过反射，将对象指定名columnName的属性赋值为指定的值columnValue
					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;
		
	}
	
	
}

总结：查询操作的代码流程

package com.atguigu3.preparedstatement.HJR;

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

import org.junit.Test;

import com.atguigu3.bean.Customer;
import com.atguigu3.bean.Order;
import com.mysql.cj.jdbc.result.ResultSetMetaData;

public class PreparedStatementQueryTest {
	@Test
	public void Querytest() throws Exception{	
		String sql = "select id,name,email from customers where id < ?";
		List<Customer> list = Query(Customer.class,sql,12);
		list.forEach(System.out::println);
		
		String sql1 = "select order_id orderId,order_name orderName from `order`";
		List<Order> orderList = Query(Order.class, sql1);
		orderList.forEach(System.out::println);
	}
	
	public <T> List<T> Query(Class<T> clazz,String sql,Object...args) throws Exception {
		//1.建立连接
		Connection conn = JDBCutilHJR.getConnection();
		//2.ps的实例化
		PreparedStatement ps = conn.prepareStatement(sql);
		//3.填充占位符
		for(int i = 0;i<args.length;i++) {
			ps.setObject(i+1, args[i]);
		}
		//4.执行
		ResultSet rs = ps.executeQuery();
		//4-1创建RSMD
		ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
		//4-2获取列数
		int col = rsmd.getColumnCount();
		//数据导入阶段
		//创建List
		ArrayList<T> List = new ArrayList<>();
		while(rs.next()) {
			T t = clazz.newInstance();
			for(int i =0;i<col;i++) {
				//获取所在列的值
				Object colVaule = rs.getObject(i+1);
				//获取所在列的名字或者别名
				String colLabel = rsmd.getColumnLabel(i+1);
				//反射
				Field field = clazz.getDeclaredField(colLabel);
				field.setAccessible(true);
				field.set(t, colVaule);
			}
			List.add(t);
		}
		return List;		
	}

	//Query1: 老师的写法
	public <T> List<T> Query1(Class<T> clazz,String sql,Object...args) throws Exception {
		//1.获取数据库的连接
		Connection conn = JDBCutilHJR.getConnection();
		//2.预编译sql语句，返回PreparedStatement的实例
		PreparedStatement ps = conn.prepareStatement(sql);
		//3.填充占位符
		for(int i =0;i<args.length;i++) {
			ps.setObject(i+1, args[i]);
		}
		//4.执行，获取结果集
		ResultSet rs = ps.executeQuery();
		//4-1获取结果集的元数据
		ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
		//4-2获取列数
		int columnCount = rsmd.getColumnCount();
		//4-3创建集合对象
		ArrayList<T> list = new ArrayList<T>();
		while(rs.next()){
			T t = clazz.newInstance();
			//处理结果集一行数据中的每一个列:给t对象指定的属性赋值
			for(int i = 0;i < columnCount;i++){
				//获取每个列的列值:通过ResultSet
				Object columnValue = rs.getObject(i + 1);
				String columnLabel = rsmd.getColumnLabel(i + 1);	
				//通过反射，将对象指定名columnName的属性赋值为指定的值columnValue
				Field field = clazz.getDeclaredField(columnLabel);
				field.setAccessible(true);
				field.set(t, columnValue);
				}
				list.add(t);
	       }
		return list;
     }
	
}