JDBC篇-第三章：使用PreparedStatement实现CRUD操作

谁践踏了优雅 2021-06-10 20:40 422阅读 0赞

# 第三章：使用PreparedStatement实现CRUD操作 #

## 3.1 使用Statement操作数据表的弊端 ##

*  通过调用 Connection 对象的 createStatement() 方法创建该对象。该对象用于执行静态的 SQL 语句，并且返回执行结果。
 *  Statement 接口中定义了下列方法用于执行 SQL 语句：
    
        int excuteUpdate(String sql)：执行更新操作INSERT、UPDATE、DELETE
        ResultSet executeQuery(String sql)：执行查询操作SELECT
 *  但是使用Statement操作数据表存在弊端：
    
     *  **问题一：存在拼串操作，繁琐**
     *  **问题二：存在SQL注入问题**
 *  SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段或命令(如：SELECT user, password FROM user\_table WHERE user=‘a’ OR 1 = ’ AND password = ’ OR ‘1’ = ‘1’) ，从而利用系统的 SQL 引擎完成恶意行为的做法。
 *  对于 Java 而言，要防范 SQL 注入，只要用 PreparedStatement(从Statement扩展而来) 取代 Statement 就可以了。
 *  代码演示：
    
        public class StatementTest { 
        
        	// 使用Statement的弊端：需要拼写sql语句，并且存在SQL注入的问题
        	@Test
        	public void testLogin() { 
        		Scanner scan = new Scanner(System.in);
        
        		System.out.print("用户名：");
        		String userName = scan.nextLine();
        		System.out.print("密 码：");
        		String password = scan.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 = '" + userName + "' AND PASSWORD = '" + password
        				+ "'";
        		User user = get(sql, User.class);
        		if (user != 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);
        
        			// 4.获取连接
        			conn = DriverManager.getConnection(url, user, password);
        
        			st = conn.createStatement();
        
        			rs = st.executeQuery(sql);
        
        			// 获取结果集的元数据
        			ResultSetMetaData rsmd = rs.getMetaData();
        
        			// 获取结果集的列数
        			int columnCount = rsmd.getColumnCount();
        			if (rs.next()) { 
        				t = clazz.newInstance();
        				for (int i = 0; i < columnCount; i++) { 
        					// //1. 获取列的名称
        					// String columnName = rsmd.getColumnName(i+1);
        
        					// 1. 获取列的别名
        					String columnName = rsmd.getColumnLabel(i + 1);
        
        					// 2. 根据列名获取对应数据表中的数据
        					Object columnVal = rs.getObject(columnName);
        
        					// 3. 将数据表中得到的数据，封装进对象
        					Field field = clazz.getDeclaredField(columnName);
        					field.setAccessible(true);
        					field.set(t, columnVal);
        				}
        				return t;
        			}
        		} catch (Exception e) { 
        			e.printStackTrace();
        		} finally { 
        			// 关闭资源
        			if (rs != null) { 
        				try { 
        					rs.close();
        				} catch (SQLException e) { 
        					e.printStackTrace();
        				}
        			}
        			if (st != null) { 
        				try { 
        					st.close();
        				} catch (SQLException e) { 
        					e.printStackTrace();
        				}
        			}
        			if (conn != null) { 
        				try { 
        					conn.close();
        				} catch (SQLException e) { 
        					e.printStackTrace();
        				}
        			}
        		}
        		return null;
        	}
        }

## 3.2 PreparedStatement的使用 ##

### 3.2.1 PreparedStatement介绍 ###

*  可以通过调用 Connection 对象的 **preparedStatement(String sql)** 方法获取 PreparedStatement 对象
 *  **PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句**
 *  PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始)，第二个是设置的 SQL 语句中的参数的值

### 3.2.2 PreparedStatement vs Statement ###

*  代码的可读性和可维护性。
 *  **PreparedStatement 能最大可能提高性能：**
    
     *  DBServer会对**预编译**语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。 (详情代码见批量插入)
     *  在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。
     *  (语法检查，语义检查，翻译成二进制命令，缓存)
 *  PreparedStatement 可以防止 SQL 注入

### 3.2.3 Java与SQL对应数据类型转换表 ###

<table> 
 <thead> 
  <tr> 
   <th>Java类型</th> 
   <th>SQL类型</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>boolean</td> 
   <td>BIT</td> 
  </tr> 
  <tr> 
   <td>byte</td> 
   <td>TINYINT</td> 
  </tr> 
  <tr> 
   <td>short</td> 
   <td>SMALLINT</td> 
  </tr> 
  <tr> 
   <td>int</td> 
   <td>INTEGER</td> 
  </tr> 
  <tr> 
   <td>long</td> 
   <td>BIGINT</td> 
  </tr> 
  <tr> 
   <td>String</td> 
   <td>CHAR,VARCHAR,LONGVARCHAR</td> 
  </tr> 
  <tr> 
   <td>byte array</td> 
   <td>BINARY , VAR BINARY</td> 
  </tr> 
  <tr> 
   <td>java.sql.Date</td> 
   <td>DATE</td> 
  </tr> 
  <tr> 
   <td>java.sql.Time</td> 
   <td>TIME</td> 
  </tr> 
  <tr> 
   <td>java.sql.Timestamp</td> 
   <td>TIMESTAMP</td> 
  </tr> 
 </tbody> 
</table>

### 3.2.4 使用PreparedStatement实现增、删、改操作 ###

//通用的增、删、改操作（体现一：增、删、改 ； 体现二：针对于不同的表）
    	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) { 
    			e.printStackTrace();
    		}finally{ 
    			//5.资源的关闭
    			JDBCUtils.closeResource(conn, ps);
    		}
    	}

### 3.2.5 使用PreparedStatement实现查询操作 ###

> 返回一条记录,用泛型方法

/** * * @Description 针对于不同的表的通用的查询操作，返回表中的一条记录 * @author cVzhanshi * @date 2021年4月2日上午11:13:26 * @param sql * @param args * @return */
    	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 = 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;
    	}

> 返回多条记录,用泛型方法

/** * * @Description 针对于不同的表的通用的查询操作，返回表中的多条记录 * @author cVzhanshi * @date 2021年4月2日上午11:27:47 * @param clazz * @param sql * @param args * @return */
    	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 = 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;
    	}

### 3.2.6PreparedStatement的查询流程 ###

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HE3G03h4-1617463743390)(JDBC.assets/1617349446567.png)\]

## 3.3 ResultSet与ResultSetMetaData ##

### 3.3.1 ResultSet ###

*  查询需要调用PreparedStatement 的 executeQuery() 方法，查询结果是一个ResultSet 对象
 *  ResultSet 对象以逻辑表格的形式封装了执行数据库操作的结果集，ResultSet 接口由数据库厂商提供实现
 *  ResultSet 返回的实际上就是一张数据表。有一个指针指向数据表的第一条记录的前面。
 *  ResultSet 对象维护了一个指向当前数据行的**游标**，初始的时候，游标在第一行之前，可以通过 ResultSet 对象的 next() 方法移动到下一行。调用 next()方法检测下一行是否有效。若有效，该方法返回 true，且指针下移。相当于Iterator对象的 hasNext() 和 next() 方法的结合体。
 *  当指针指向一行时, 可以通过调用 getXxx(int index) 或 getXxx(int columnName) 获取每一列的值。
    
     *  例如: getInt(1), getString(“name”)
     *  **注意：Java与数据库交互涉及到的相关Java API中的索引都从1开始。**
 *  ResultSet 接口的常用方法：
    
     *  boolean next()
     *  getString()
     *  …

示意图:

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDA4Mzkw_size_16_color_FFFFFF_t_70]

### 3.3.2 ResultSetMetaData ###

*  可用于获取关于 ResultSet 对象中列的类型和属性信息的对象
 *  ResultSetMetaData meta = rs.getMetaData();
    
     *  **getColumnName**(int column)：获取指定列的名称
     *  **getColumnLabel**(int column)：获取指定列的别名
     *  **getColumnCount**()：返回当前 ResultSet 对象中的列数。
     *  getColumnTypeName(int column)：检索指定列的数据库特定的类型名称。
     *  getColumnDisplaySize(int column)：指示指定列的最大标准宽度，以字符为单位。
     *  **isNullable**(int column)：指示指定列中的值是否可以为 null。
     *  isAutoIncrement(int column)：指示是否自动为指定列进行编号，这样这些列仍然是只读的。

**问题1：得到结果集后, 如何知道该结果集中有哪些列 ？ 列名是什么？**

需要使用一个描述 ResultSet 的对象， 即 ResultSetMetaData

**问题2：关于ResultSetMetaData**

1.  **如何获取 ResultSetMetaData**： 调用 ResultSet 的 getMetaData() 方法即可
2.  **获取 ResultSet 中有多少列**：调用 ResultSetMetaData 的 getColumnCount() 方法
3.  **获取 ResultSet 每一列的列的别名是什么**：调用 ResultSetMetaData 的getColumnLabel() 方法

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDA4Mzkw_size_16_color_FFFFFF_t_70 1]

## 3.4 JDBCUtils工具类 ##

/** * * @Description 操作数据库的工具类 * @author cVzhanshi Email:cvzhanshi@qq.com * @version * @date 2021年4月2日上午10:00:56 * */
    public class JDBCUtils { 
    	/** * * @Description 获取数据库的连接 * @author cVzhanshi * @date 2021年4月2日上午10:01:19 * @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和ResultSet的操作 * @author cVzhanshi * @date 2021年4月2日上午10:02:59 * @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();
    		}
    	}
    }

## 3.5 JDBC API小结 ##

*  两种思想
    
     *  面向接口编程的思想
     *  ORM思想(object relational mapping)
        
         *  一个数据表对应一个java类
         *  表中的一条记录对应java类的一个对象
         *  表中的一个字段对应java类的一个属性
    
    > sql是需要结合列名和表的属性名来写。注意起别名。
 *  两种技术
    
     *  JDBC结果集的元数据：ResultSetMetaData
        
         *  获取列数：getColumnCount()
         *  获取列的别名：getColumnLabel()
     *  通过反射，创建指定类的对象，获取指定的属性并赋值

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDA4Mzkw_size_16_color_FFFFFF_t_70]: /images/20210516/1b2914e5bb0840c69c55f7f5d0429dc2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDA4Mzkw_size_16_color_FFFFFF_t_70 1]: /images/20210516/df8382c178c4442c9f1842435a4a399e.png