jdbc优化（select）

一般的查询操作如下：

public class Select {

	public static void main(String[] args) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
			statement = connection.createStatement();
			String sql = "select * from user_info";
			resultSet = statement.executeQuery(sql);
			while(resultSet.next()) {
				String id = resultSet.getString("id");
				String userName = resultSet.getString("user_name");
				String password = resultSet.getString("password");
				System.out.println(id+","+userName+","+password);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if (resultSet!=null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			try {
				if (statement!=null) {
					statement.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			try {
				if (connection!=null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

我们对上述代码进行优化，首先想到的是将查询操作单独定义出一个方法，方法返回结果集，然后在main函数中对结果集进行遍历。如下：

public class Select {

	public static ResultSet query(String sql) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
			statement = connection.createStatement();
			return statement.executeQuery(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if (resultSet!=null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			try {
				if (statement!=null) {
					statement.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			try {
				if (connection!=null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	public static void main(String[] args) {
		String sql = "select * from user_info";
		try {
			ResultSet resultSet = query(sql);
			while(resultSet.next()) {
				String id = resultSet.getString("id");
				String userName = resultSet.getString("user_name");
				String password = resultSet.getString("password");
				System.out.println(id+","+userName+","+password);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

执行结果为：

出错了，原因是在执行main函数中的while语句时resultSet资源已经被释放，不能再使用resultSet进行遍历。当然也可以在resultSet资源使用完后再释放，如下：

public class Select {
	static Connection connection = null;
	static Statement statement = null;
	
	public static ResultSet query(String sql) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
			statement = connection.createStatement();
			return statement.executeQuery(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static void main(String[] args) {
		String sql = "select * from user_info";
		ResultSet resultSet =null;
		try {
			resultSet = query(sql);
			while(resultSet.next()) {
				String id = resultSet.getString("id");
				String userName = resultSet.getString("user_name");
				String password = resultSet.getString("password");
				System.out.println(id+","+userName+","+password);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if (resultSet!=null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (statement!=null) {
					statement.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (connection!=null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

这种代码可以完成查询，但不算是优化，main函数太长，代码不能重用。
下面我们使用命令模式进行代码优化：

interface IRowMapper{
	void rowMapper(ResultSet rs);
}
public class Select {

	public static void query(String sql,IRowMapper rowMapper) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			rowMapper.rowMapper(resultSet);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if (resultSet!=null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}	
			try {
				if (statement!=null) {
					statement.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			try {
				if (connection!=null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}	
	public static void main(String[] args) {
		String sql = "select * from user_info";	
		class RowMapper implements IRowMapper{
			@Override
			public void rowMapper(ResultSet rs) {
				try {
					while(rs.next()) {
						String id = rs.getString("id");
						String userName = rs.getString("user_name");
						String password = rs.getString("password");
						System.out.println(id+","+userName+","+password);
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}	
		RowMapper rowMapper = new RowMapper();
		query(sql,rowMapper);
	}
}

在上面代码中我们在query方法中使用rowMapper.rowMapper(resultSet);该语句在在资源释放前执行，到此程序执行权转到main函数中的内部类的构造方法，对结果集的遍历操作在该构造方法内完成，完成之后执行权又回到query方法中，然后进行资源的释放。完成查询操作以及代码优化，重用。