jdbc 与 mysql 连接 - 增删改查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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;
 }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
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();
             }
         }

     }
 }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
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);
		}
		
	}
	
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
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 + "]";
	}
	
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
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();
		}
	}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
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]
	}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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 + "]";
	}
	

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
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);
		}
		
	}
	
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
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();
             }
         }

     }
 }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
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;
	}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
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;
	}
	
}