jdbc 与 mysql 连接 - 增删改查

最新推荐文章于 2023-07-10 13:06:57 发布

sunqianLOL

最新推荐文章于 2023-07-10 13:06:57 发布

阅读量277

点赞数

分类专栏： Mysql JDBC 文章标签： mysql jdbc

本文链接：https://blog.csdn.net/sunqianLOL/article/details/120249027

版权

Mysql 同时被 2 个专栏收录

16 篇文章 3 订阅

订阅专栏

JDBC

9 篇文章 0 订阅

订阅专栏

package com.atguigu2.statement.crud;

public class User {
 private String user;
 private String password;

 public User() {
 }

 public User(String user, String password) {
     this.user = user;
     this.password = password;
 }

 public String toString() {
     return "User [user=" + this.user + ", password=" + this.password + "]";
 }

 public String getUser() {
     return this.user;
 }

 public void setUser(String user) {
     this.user = user;
 }

 public String getPassword() {
     return this.password;
 }

 public void setPassword(String password) {
     this.password = password;
 }
}

package com.atguigu2.statement.crud;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
import org.junit.Test;

public class StatementTest {
 public StatementTest() {
 }

 // 使用 Statement 的弊端，需要拼写 sql 语句，并且存在 SQL 注入的问题
 // 如何避免出现 sql 注入：只要用 PreparedStatement (从 Statement 扩展而来) 取代 Statement
 @Test
 public void testLogin() {
     Scanner scanner = new Scanner(System.in);
     System.out.print("请输入用户名：");
     String user = scanner.nextLine();
     System.out.print("请输入密码：");
     String password = scanner.nextLine();
     
     String sql = "SELECT user,password FROM user_table WHERE user = '" + user + "' AND password = '" + password + "'";
     User returnUser = (User)this.get(sql, User.class);
     if (returnUser != null) {
         System.out.println("登录成功");
     } else {
         System.out.println("用户名不存在或密码错误");
     }

 }
 
// 使用 Statement 实现对数据表的查询操作
 public <T> T get(String sql, Class<T> clazz) {
     T t = null;
     Connection conn = null;
     Statement st = null;
     ResultSet rs = null;

     try {
    	 //1.加载配置文件
         InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
         Properties pros = new Properties();
         pros.load(is);
         
         //2.读取配置信息
         String user = pros.getProperty("user");
         String password = pros.getProperty("password");
         String url = pros.getProperty("url");
         String driverClass = pros.getProperty("driverClass");
         
         //3.加载驱动
         Class.forName(driverClass);
         
         conn = DriverManager.getConnection(url, user, password);
         st = conn.createStatement();
         rs = st.executeQuery(sql);
         ResultSetMetaData rsmd = rs.getMetaData();
         int columnCount = rsmd.getColumnCount();
         if (!rs.next()) {
             return null;
         } else {
             t = clazz.newInstance();

             for(int i = 0; i < columnCount; ++i) {
                 String columnName = rsmd.getColumnLabel(i + 1);
                 Object columnVal = rs.getObject(columnName);
                 Field field = clazz.getDeclaredField(columnName);
                 field.setAccessible(true);
                 field.set(t, columnVal);
             }

             Object var20 = t;
             return (T) var20;
         }
     } catch (Exception var39) {
         var39.printStackTrace();
         return null;
     } finally {
         if (rs != null) {
             try {
                 rs.close();
             } catch (SQLException var38) {
                 var38.printStackTrace();
             }
         }

         if (st != null) {
             try {
                 st.close();
             } catch (SQLException var37) {
                 var37.printStackTrace();
             }
         }

         if (conn != null) {
             try {
                 conn.close();
             } catch (SQLException var36) {
                 var36.printStackTrace();
             }
         }

     }
 }
}

package com.atguigu3.preparedstatement.crud;

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

import org.junit.Test;

import com.atguigu1.connection.ConnectionTest;
import com.atguigu3.util.JDBCUtils;

/*
 * 使用 PreparedStatement 来替换 Statement，实现对数据表的增删改查操作
 * 
 * 增删改：查
 * 
 */

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,"DD","2");
		String sql = "update `order` set order_name = ? where order_id = ?";// "`" 飘号(不是单引号)：在键盘 1 的左边
		update(sql,"DD","2");
	}
	
	// 通用的 增删改查 操作 (增删改)
	public void update(String sql,Object ...args){	// sql 中的占位符的个数与可变形参的长度相同
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//1.获取数据库的连接
			conn = JDBCUtils.getConnection();
			//2.预编译 sql 语句，返回 PreparedStatement 的实例
			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) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			//5.资源的关闭
			JDBCUtils.closeResource(conn, ps);
		}
	}
	
	// 向 customers 表中添加一条记录
	@Test
	public void testInsert() {
		//3.获取链接
		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);
			System.out.println(conn);
			
			//4.预编译 sql 语句，返回 PreparedStatement 的实例
			String sql = "insert into customers(name,email,birth)values(?,?,?)";// ? : 占位符
			ps = conn.prepareStatement(sql);
			
			//5.填充占位符
			ps.setString(1, "哪吒");
			ps.setString(2, "nezha@gmail.com");
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			java.util.Date date = sdf.parse("1000-01-01");
			ps.setDate(3, new Date(date.getTime()));
			
			//6.执行操作
			ps.execute();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			//7.资源的关闭
			try {
				if(ps != null)
					ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	//修改 customers 表的一条记录
	@Test
	public void testUpdate() {
		
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//1.获取数据库的连接
			conn = JDBCUtils.getConnection();
			
			//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 (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) { 
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			//5.资源的关闭
			JDBCUtils.closeResource(conn, ps);
		}
		
	}
	
}

package com.atguigu3.bean;

import java.sql.Date;

/*
 * ORM 编程思想(object relational mapping)
 * 一个数据表对应一个 java 类
 * 表中的一条记录对应 java 类的一个对象
 * 表中的一个字段对应 java 类的一个属性
 * 
 * 
 *     Java 与 SQL 对应数据类型转换表
 *     
 * Java 类型						SQL 类型
 *   boolean					  BIT
 *   byte						  TINYINT
 *   short						  SMALLINT
 *   int 						  INTGER
 *   long						  BIGINT
 *   String						  CHAR,VARCHAR,LONGVARCHAR
 *   byte array					  BINARY,VAR BINARY
 *   java.sql.Date				  DATE
 *   java.sql.Time				  TIME
 *   java.sql.Timestamp			  TIMESTAMP
 * 
 */
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 com.atguigu3.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;

/**
 * 操作数据库的工具类，此工具类避免每次操纵数据库时，连接数据库，关闭资源。
 * @author 21115
 *
 */

public class JDBCUtils {
	
	/**
	 * 获取数据库的连接
	 * @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;
	}
	
	/**
	 * 关闭连接和 Statement 的操作
	 * @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();
		}
	}
	
	// 关闭资源操作
	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.SQLException;

import org.junit.Test;

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

/**
 * 针对于 Customers 表的查询操作
 *
 */
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); 
		//Customer [id=13, name=张学友, email=zhangxy@126.com, birth=1998-12-21]
		
		sql = "select name,email from customers where name = ?";
		Customer customer1 = queryForCustomers(sql,"周杰伦");
		System.out.println(customer1);
		//Customer [id=0, name=周杰伦, email=zhoujl@sina.com, birth=null]
	}
	
	/**
	 * 针对于 customers 表的通用的查询操作
	 * 
	 */
	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 = (ResultSetMetaData) 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);
					
					// 给 cust 对象指定的 columnName 属性，赋值为 columValue：通过反射
					Field field = Customer.class.getDeclaredField(columnName);
					field.setAccessible(true);
					field.set(cust, columValue);
				}
				return cust;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, ps, rs);
		}
		return null;
	}
	
	@Test
	public void testQuery1() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		try {
			//执行，并返回结果集
			conn = JDBCUtils.getConnection();
			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);
				
				// 方式一：
//			System.out.println("id = " + id + ",name = " + name + ",email = " + email + ",birth = " + birth);
				
				// 方式二：
				Object[] data = new Object[] {id,name,email,birth};
				
				// 方式三：将数据封装为一个对象(推荐)
				Customer customer = new Customer(id,name,email,birth);
				System.out.println(customer);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			JDBCUtils.closeResource(conn, ps, resultSet);	
		}
//Customer [id=1, name=汪峰, email=wf@126.com, birth=2010-02-02]
	}
}

package com.atguigu3.bean;

import java.sql.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 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.SQLException;

import org.junit.Test;

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

/**
 * 针对于 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 order = orderForQuery(sql,1);
		System.out.println(order);
		// Order [orderId=1, orderName=AA, orderDate=2010-03-04]
	}
	
	/**
	 * 通用的针对于 order 表的查询操作
	 * 
	 */
	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
			ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
			// 获取列数：通过 ResultSetMetaData 获取结果集中的列数
			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;
	}
	
	@Test
	public void testQuery1() {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			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); 
				//Order [orderId=1, orderName=AA, orderDate=2010-03-04]
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			
			JDBCUtils.closeResource(conn, ps, rs);
		}
		
	}
	
}

package com.atguigu2.statement.crud;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
import org.junit.Test;

public class StatementTest {
 public StatementTest() {
 }

 // 使用 Statement 的弊端，需要拼写 sql 语句，并且存在 SQL 注入的问题
 // 如何避免出现 sql 注入：只要用 PreparedStatement (从 Statement 扩展而来) 取代 Statement
 @Test
 public void testLogin() {
     Scanner scanner = new Scanner(System.in);
     
     System.out.print("请输入用户名：");
     String user = scanner.nextLine();
     System.out.print("请输入密码：");
     String password = scanner.nextLine();
     
     String sql = "SELECT user,password FROM user_table WHERE user = '" + user + "' AND password = '" + password + "'";
     User returnUser = (User)this.get(sql, User.class);
     if (returnUser != null) {
         System.out.println("登录成功");
     } else {
         System.out.println("用户名不存在或密码错误");
     }

 }
 
// 使用 Statement 实现对数据表的查询操作
 public <T> T get(String sql, Class<T> clazz) {
     T t = null;
     Connection conn = null;
     Statement st = null;
     ResultSet rs = null;

     try {
    	 //1.加载配置文件
         InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
         Properties pros = new Properties();
         pros.load(is);
         
         //2.读取配置信息
         String user = pros.getProperty("user");
         String password = pros.getProperty("password");
         String url = pros.getProperty("url");
         String driverClass = pros.getProperty("driverClass");
         
         //3.加载驱动
         Class.forName(driverClass);
         
         conn = DriverManager.getConnection(url, user, password);
         st = conn.createStatement();
         rs = st.executeQuery(sql);
         ResultSetMetaData rsmd = rs.getMetaData();
         int columnCount = rsmd.getColumnCount();
         if (!rs.next()) {
             return null;
         } else {
             t = clazz.newInstance();

             for(int i = 0; i < columnCount; ++i) {
                 String columnName = rsmd.getColumnLabel(i + 1);
                 Object columnVal = rs.getObject(columnName);
                 Field field = clazz.getDeclaredField(columnName);
                 field.setAccessible(true);
                 field.set(t, columnVal);
             }

             Object var20 = t;
             return (T) var20;
         }
     } catch (Exception var39) {
         var39.printStackTrace();
         return null;
     } finally {
         if (rs != null) {
             try {
                 rs.close();
             } catch (SQLException var38) {
                 var38.printStackTrace();
             }
         }

         if (st != null) {
             try {
                 st.close();
             } catch (SQLException var37) {
                 var37.printStackTrace();
             }
         }

         if (conn != null) {
             try {
                 conn.close();
             } catch (SQLException var36) {
                 var36.printStackTrace();
             }
         }

     }
 }
}

package com.atguigu2.statement.crud;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

import org.junit.Test;

import com.atguigu3.util.JDBCUtils;
import com.mysql.cj.jdbc.result.ResultSetMetaData;

/**
 * 演示使用 PreparedStatement 替换 Statement，解决 SQL 注入问题
 *
 * 除了解决 Statement 的拼串、sql 问题之外，PreparedStatement 还有哪些好处呢？
 *     1.PreparedStatement 操作 Blob 的数据，而 Statement 做不到。
 * 	   2.PreparedStatement 可以实现更高效的批量操作。
 * 
 */

public class PreparedStatementTest {
	
	@Test
	 public void testLogin() {
	     Scanner scanner = new Scanner(System.in);
	     
	     System.out.print("请输入用户名：");
	     String user = scanner.nextLine();
	     System.out.print("请输入密码：");
	     String password = scanner.nextLine();
	     
	     // SELECT user,password FROM user_table WHERE user = '1' or ' AND password = '=1 or '1' = '1'
	     String sql = "SELECT user,password FROM user_table WHERE user = ? and password = ?";
	     User returnUser = getInstance(User.class, sql,user,password);
	     if (returnUser != null) {
	         System.out.println("登录成功");
	     } else {
	         System.out.println("用户名不存在或密码错误");
	     }

	 }
	
	/**
	 * 针对于不同的表的通用的查询操作，返回表中的一条记录
	 * 
	 */
	public <T> T getInstance(Class<T> clazz,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 = (ResultSetMetaData) rs.getMetaData();
			// 通过 ResultSetMetaData 获取结果集中的列数
			int columnCount = rsmd.getColumnCount();
			
			if(rs.next()) {
				T t = clazz.newInstance();
				// 处理结果集一行数据中的每一个列
				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);
					
					// 给 t 对象指定的 columnName 属性，赋值为 columValue：通过反射
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				return t;
			}
		} 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.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

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

/**
 * 使用 PreparedStatement 实现针对于不同表的通用的查询操作
 * 
 */
public class PreparedStatementQueryTest {
	
	@Test
	public void testGetForList() {
		
		String sql = "select id,name,email from customers where id < ?";
		List<Customer> list = getForList(Customer.class,sql,12);
		list.forEach(System.out::println);
//		Customer [id=1, name=汪峰, email=wf@126.com, birth=null]
//		Customer [id=2, name=王菲, email=wangf@163.com, birth=null]
//		Customer [id=4, name=汤唯, email=tangw@sina.com, birth=null]
//		Customer [id=5, name=成龙, email=Jackey@gmai.com, birth=null]
//		Customer [id=6, name=迪丽热巴, email=reba@163.com, birth=null]
//		Customer [id=7, name=刘亦菲, email=liuyifei@qq.com, birth=null]
//		Customer [id=8, name=陈道明, email=bdf@126.com, birth=null]
//		Customer [id=10, name=周杰伦, email=zhoujl@sina.com, birth=null]
		
		System.out.println("** ** ** ** ** ** ** ** ** ** ** ** **");
		
		// 特殊情况，没有解决 sql 注入问题，不建议使用(和 statement 效果差不多，没有解决 sql 注入问题)
		String sql1 = "select id,name,email from customers where id < 12";
		List<Customer> list1 = getForList(Customer.class,sql1);
		list1.forEach(System.out::println);
//		Customer [id=1, name=汪峰, email=wf@126.com, birth=null]
//		Customer [id=2, name=王菲, email=wangf@163.com, birth=null]
//		Customer [id=4, name=汤唯, email=tangw@sina.com, birth=null]
//		Customer [id=5, name=成龙, email=Jackey@gmai.com, birth=null]
//		Customer [id=6, name=迪丽热巴, email=reba@163.com, birth=null]
//		Customer [id=7, name=刘亦菲, email=liuyifei@qq.com, birth=null]
//		Customer [id=8, name=陈道明, email=bdf@126.com, birth=null]
//		Customer [id=10, name=周杰伦, email=zhoujl@sina.com, birth=null]
		
		System.out.println("** ** ** ** ** ** ** ** ** ** ** ** **");
		
		String sql2 = "select order_id orderId,order_name orderName from `order` where order_id < ?";
		List<Order> orderList = getForList(Order.class,sql2,5);
		orderList.forEach(System.out::println);
//		Order [orderId=1, orderName=AA, orderDate=null]
//		Order [orderId=2, orderName=DD, orderDate=null]
//		Order [orderId=4, orderName=GG, orderDate=null]
		
		System.out.println("** ** ** ** ** ** ** ** ** ** ** ** **");
		
		// 特殊情况
		String sql3 = "select order_id orderId,order_name orderName from `order`";
		List<Order> orderList1 = getForList(Order.class,sql3);
		orderList1.forEach(System.out::println);
//		Order [orderId=1, orderName=AA, orderDate=null]
//		Order [orderId=2, orderName=DD, orderDate=null]
//		Order [orderId=4, orderName=GG, orderDate=null]
//		Customer [id=12, name=黎明, email=LiM@126.com, birth=null]
//		Order [orderId=1, orderName=AA, orderDate=null]
	}
	
	public <T> List<T> getForList(Class<T> clazz,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 = (ResultSetMetaData) rs.getMetaData();
			// 通过 ResultSetMetaData 获取结果集中的列数
			int columnCount = rsmd.getColumnCount();
			// 创建集合对象
			ArrayList<T> list = new ArrayList<T>();
			
			while (rs.next()) {
				T t = clazz.newInstance();
				// 处理结果集一行数据中的每一个列:给 t 对象指定的属性赋值
				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);
					
					// 给 t 对象指定的 columnName 属性，赋值为 columValue：通过反射
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				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 from customers where id = ?";
		
		Customer customer = getInstance(Customer.class,sql,12);
		System.out.println(customer);
		// Customer [id=12, name=黎明, email=LiM@126.com, birth=null]
		
		String sql1 = "select order_id orderId,order_name orderName from `order` where order_id = ?";
		Order order = getInstance(Order.class,sql1,1);
		System.out.println(order);
		// Order [orderId=1, orderName=AA, orderDate=null]
	}
	
	/**
	 * 针对于不同的表的通用的查询操作，返回表中的一条记录
	 * 
	 */
	
	public <T> T getInstance(Class<T> clazz,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 = (ResultSetMetaData) rs.getMetaData();
			// 通过 ResultSetMetaData 获取结果集中的列数
			int columnCount = rsmd.getColumnCount();
			
			if(rs.next()) {
				T t = clazz.newInstance();
				// 处理结果集一行数据中的每一个列
				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);
					
					// 给 t 对象指定的 columnName 属性，赋值为 columValue：通过反射
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, ps, rs);
		}
		return null;
	}
	
}

sunqianLOL

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
jdbc 与 mysql 连接 - 增删改查

package com.atguigu2.statement.crud;public class User { private String user; private String password; public User() { } public User(String user, String password) { this.user = user; this.password = password; } public String toString()
复制链接

扫一扫