数据库连接和关闭的java最佳实践
假设我有一个jsp页面，在加载时需要先加载类别，然后加载产品，我有一个数据库连接类，其中包含listCategories和getProducts函数。我的问题是，我应该在调用的每个函数中连接并关闭db连接，还是连接到jsp页面上方的db，然后在页面关闭时关闭加载的示例代码：
public class DbConnection {
private Connection conn = null;
private PreparedStatement ps = null;
private ResultSet rs = null;
private void connect() {
try {
DriverManager.registerDriver(new Driver());
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/shop", "", "");
} catch (SQLException ex) {
Logger.getLogger(DbConnection.class.getName()).log(Level.SEVERE, null, ex);
}
}
private void close() {
try {
if (!conn.isClosed()) {
conn.close();
}
} catch (SQLException ex) {
Logger.getLogger(DbConnection.class.getName()).log(Level.SEVERE, null, ex);
}
}
public List<Category> listCategories() {
connect();
List<Category> listCategory = new ArrayList<>();
try {
ps = conn.prepareStatement("select * from Category where sub_category=0");
rs = ps.executeQuery();
while (rs.next()) {
Category u = new Category(rs.getInt("id"), rs.getString("name"), rs.getInt("sub_category"));
listCategory.add(u);
}
return listCategory;
} catch (SQLException ex) {
Logger.getLogger(DbConnection.class.getName()).log(Level.SEVERE, null, ex);
}finally{
close();
}
return null;
}
public List<Products> getProducts() {
connect();
List<Products> listProducts = new ArrayList<>();
try {
ps = conn.prepareStatement("select * from products");
rs = ps.executeQuery();
while (rs.next()) {
Products p = new Products(rs.getInt("id"), rs.getString("name"));
listCategory.add(p);
}
return listCategory;
} catch (SQLException ex) {
Logger.getLogger(DbConnection.class.getName()).log(Level.SEVERE, null, ex);
}finally{
close();
}
return null;
}
}
jsp代码示例：
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<%for(Category c: dc.listCategories()){%>
<h1><%=c.getName()%></h1>
<%}%>
<%for(Products p: dc.getProducts()){%>
<h1><%=p.getName()%></h1>
<%}%>
<body>
<h1>Hello World!</h1>
</body>
</html>
# 1 楼答案
最好在开始渲染页面之前打开一次连接，查询所有需要的模型数据，然后关闭连接并开始渲染
这样：