一般的查询操作如下:
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方法中,然后进行资源的释放。完成查询操作以及代码优化,重用。