最佳实践：在 Java 中使用 DB

Best practices: working with DB in Java

提问by binaryLV

First of all, I'm new to Java.

首先，我是 Java 新手。

I'm trying to figure out what would be a good/handy way to work with DB from Java. I'm using c3p0 for connection pooling. Hibernate or other ORM is not an option this time, we decided to stick with "plain SQL" for now.

我试图找出从 Java 使用 DB 的好/方便的方法。我使用 c3p0 进行连接池。这次不选择 Hibernate 或其他 ORM，我们决定暂时坚持使用“普通 SQL”。

Currently basic retrieval of data looks like this:

目前数据的基本检索如下所示：

private int getUserID(int sessionID, String userIP) {
 int result = 0;
 Connection conn = null;
 PreparedStatement st = null;
 ResultSet rs = null;
 try {
  // Application.cpds is an instance of c3p0's ComboPooledDataSource
  conn = Application.cpds.getConnection();
  st = conn.prepareStatement("SELECT user_id, user_ip, is_timed_out FROM g_user.user_session WHERE id = ?");
  st.setInt(1, sessionID);
  rs = st.executeQuery();
  if ( rs.next() ) {
   if ( !rs.getBoolean("is_timed_out") && userIP.equals(rs.getString("user_ip")) ) {
    result = rs.getInt("user_id");
   }
  }
 }
 catch (SQLException 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 result;
}

The code looks very long for such a basic operation. Another problem is that most of the code would have to be repeated in many places (declaring Connection, PreparedStatement, ResultSet, closing them, catching exceptions). Though, this is what I see in most examples when googling.

对于这样一个基本操作，代码看起来很长。另一个问题是大部分代码必须在很多地方重复（声明 Connection、PreparedStatement、ResultSet、关闭它们、捕获异常）。尽管如此，这是我在谷歌搜索时在大多数示例中看到的。

In PHP I would create a wrapper class that would have method select() that accepts 2 arguments (string)sqlQuery and (array)parameters and would return simple array of data. Wrapper class would also have few more specific methods, like:

在 PHP 中，我将创建一个包装类，该类具有方法 select()，该类接受 2 个参数（字符串）sqlQuery 和（数组）参数，并返回简单的数据数组。Wrapper 类还有一些更具体的方法，例如：

• selectValue() for single value (e.g., select count(*) from user)
• selectRow() for single row (e.g., select name, surname from user where id = :user_id)
• selectColumn for single column (e.g., select distinct remote_address from user)

• selectValue() 用于单个值（例如，select count(*) from user）
• selectRow() 用于单行（例如，select name, surname from user where id = :user_id）
• selectColumn 用于单列（例如，select distinct remote_address from user）

Is anything like this practiced in Java? Or is there anything better / handier? Or should I use same style as in getUserID()example above? As I said, ORM is not an option this time.

在 Java 中有这样的做法吗？或者有什么更好/更方便的吗？或者我应该使用与getUserID()上面示例相同的样式？正如我所说，这次 ORM 不是一个选择。

Thanks in advance :)

提前致谢 ：）

---

---

edit:Currently DBConnectionclass is written. It gets connection from c3p0 connection pool in constructor. It has few public methods for working with DB: select()for tabular data, selectValue()for single value, selectRow()and selectColumn()for single row or column, as well as insert(), update(), delete()and ddl(). Methods accept String query, Object[] paramsarguments, with paramsbeing optional. insert(), update()and delete()return Integerwhich is result of PreparedStatement.executeUpdate(). selectmethods return different results:

编辑：当前DBConnection类已编写。它从构造函数中的 c3p0 连接池获取连接。它具有与DB工作的几个公共方法：select()表格数据，selectValue()单一价值，selectRow()并selectColumn()为单个行或列，以及insert()，update()，delete()和ddl()。方法接受String query, Object[] params参数，并且params是可选的。insert()，update()并delete()返回Integer结果PreparedStatement.executeUpdate()。select方法返回不同的结果：

• ArrayCollection<HashMap<String, Object>> select()
• Object selectValue()
• HashMap<String, Object> selectRow()
• ArrayCollection<Object> selectColumn()

• ArrayCollection<HashMap<String, Object>> select()
• Object selectValue()
• HashMap<String, Object> selectRow()
• ArrayCollection<Object> selectColumn()

The last problem is with compiler warnings - "warning: [unchecked] unchecked cast". This is because all methods call single private method that returns Objectand cast its result to mentioned types. As I am new to Java, I'm also not sure if I have chosen appropriate types for selects. Other than that, everything seems to work as expected.

最后一个问题是编译器警告 - "warning: [unchecked] unchecked cast". 这是因为所有方法都调用单个私有方法，该方法返回Object并将其结果转换为提到的类型。由于我是 Java 新手，我也不确定我是否为选择选择了合适的类型。除此之外，一切似乎都按预期工作。

回答by Tim Büthe

If the an ORM is no option, you could still use Spring's JDBC helper classes: http://docs.spring.io/spring-framework/docs/4.1.0.RELEASE/spring-framework-reference/html/jdbc.html

如果 ORM 没有选项，您仍然可以使用 Spring 的 JDBC 帮助程序类：http: //docs.spring.io/spring-framework/docs/4.1.0.RELEASE/spring-framework-reference/html/jdbc.html

Or you could simply write some helper methods on your own. Maybe a DBUtil.close(conn, st, rs);would be nice.

或者您可以简单地自己编写一些辅助方法。也许DBUtil.close(conn, st, rs);会很好。

And by the way, you really should use a logging framework instead of "e.printStackTrace()"

顺便说一句，你真的应该使用日志框架而不是“ e.printStackTrace()”

EDIT:One more thing: I think it's kind of hard to add ORM afterwards, when you have all the SQL already written in plain JDBC. You can't refactor that stuff, you have to throw it away and do it again.

编辑：还有一件事：当您已经用普通 JDBC 编写了所有 SQL 时，我认为之后添加 ORM 有点困难。你不能重构那些东西，你必须把它扔掉然后再做一次。

EDIT:You don't have to close the resultSet if you are closing the statement anyway. The Java ResultSet APIreads:

编辑：如果您要关闭语句，则不必关闭 resultSet。在Java的API的ResultSet读取：

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

当生成它的 Statement 对象关闭、重新执行或用于从多个结果的序列中检索下一个结果时，ResultSet 对象将自动关闭。

Beside that, C3P0 does resource management as well and and closes Statements when you return a connection. You might to look that up too.

除此之外，C3P0 还进行资源管理，并在您返回连接时关闭 Statements。你也可以查一下。

回答by zcourts

To avoid the repetition of code and perhaps makes things simpler. What you could do is create a Database class.

避免代码的重复，也许可以让事情变得更简单。您可以做的是创建一个 Database 类。

In the class you could then create general purpose methods for access to the database. For eg.

在该类中，您可以创建通用方法来访问数据库。例如。

if the class is called DBManager.java then inside create methods

如果该类称为 DBManager.java，则在 create 方法中

private connect()
public boolean update()
public ResultSet query()

Reason for connect method is obvious, you use it get your connection. Since its private you call it in the constructor of DBManager.

连接方法的原因很明显，你使用它来获得你的连接。因为它是私有的，所以你在 DBManager 的构造函数中调用它。

You then use your update() method to allow you to perform SQL inserts,update,delete and the like, basically any SQL operation that doesn't return any data except for maybe a status of its success is done with the update method.

然后，您可以使用 update() 方法来允许您执行 SQL 插入、更新、删除等操作，基本上任何不返回任何数据的 SQL 操作除了可能成功的状态是使用 update 方法完成的。

Your query method is used when you want to do a select query. You can thn return the resultset and then iterate through the results in the calling method/class

当您想要执行选择查询时，将使用您的查询方法。您可以返回结果集，然后在调用方法/类中迭代结果

How you handle exceptions is up to you. It may be nicer on you to handle exceptions in the DBManager class that way you won't have to handle them in the various classes that you make a query from.

您如何处理异常取决于您。在 DBManager 类中处理异常可能对您更好，这样您就不必在从中进行查询的各种类中处理它们。

So instead of

所以代替

public ResultSet query() Throws SQLException{

you would use a try catch inside the query method like you did in your examples above. The obvious advantage of handling it in the dbmanager class is that you won't have to worry about it in all the other classes that make use of your sql connection.

您可以像在上面的示例中一样在查询方法中使用 try catch 。在 dbmanager 类中处理它的明显优势是您不必在使用 sql 连接的所有其他类中担心它。

Hope that's helpful

希望这有帮助

in response to your comment:

回应你的评论：

Its up to you what you return, the ResultSet being return is only an idea but maybe it'd be best to return a collection of some sort instead of an array, maybe? depending on what you need. The resultset needn't be closed.

返回什么取决于您，返回的 ResultSet 只是一个想法，但也许最好返回某种集合而不是数组，也许？取决于你需要什么。结果集不需要关闭。

public ResultSet query(String strSql) {

        try {
            Statement tmpStatement = connection.createStatement();
            ResultSet resultSet = tmpStatement.executeQuery(strSql);
            return resultSet;
        } catch (java.sql.SQLException ex) {
           //handle exception here
            return null;
        }
    }

your update can then look like so

然后你的更新看起来像这样

public boolean updateSql(String strSQL) {
         try {
            Statement tmpStatement = connection.createStatement();
            tmpStatement.executeUpdate(strSQL);
           return true;
        } catch (java.sql.SQLException ex) {
            //handle exception
            return false;
        }
}

erm, you can then use your query method like so

嗯，然后你可以像这样使用你的查询方法

 ResultSet r = query(sql);

        try {

            while (r.next()) {
                someVar[i] = r.getString("columnName");           
            }
        } catch (SomeException ex) {
           //handle exception etc
        }

But then again as you said instead of returning a result set you could change the query method to copy your results to an array or collection and then return the collection and close the statement with

但是，正如您所说，您可以更改查询方法以将结果复制到数组或集合，然后返回集合并关闭语句，而不是返回结果集

tmpStatement.close();

But when a Statement object is closed, its current ResultSet object, if one exists, is also closed.(from api docs)

但是当一个 Statement 对象关闭时，它当前的 ResultSet 对象（如果存在）也会关闭。（来自 api docs）

Its good practice to free up database resources as soon as so copying your result to a collection object and then closing your statement is probably best. Again its up to you.

最好尽快释放数据库资源，将结果复制到集合对象然后关闭语句可能是最好的做法。再次取决于你。

回答by Greg

" Hibernate or other ORM is not an option this time, we decided to stick with "plain SQL" for now." Out of curiosity, what was the reason to stick with plain SQL? Looking at the example and question you mentioned first obvious answer would be use ORM and don't bother - in most cases standard ORM feature list would be sufficient.

“这次 Hibernate 或其他 ORM 不是一种选择，我们决定暂时坚持使用“普通 SQL”。” 出于好奇，坚持使用普通 SQL 的原因是什么？查看您提到的示例和问题，第一个明显的答案是使用 ORM，不要打扰-在大多数情况下，标准 ORM 功能列表就足够了。

Obviously there are plenty of reasons not to use ORM's, so I'm interested in yours?

显然有很多理由不使用 ORM，所以我对你的感兴趣？

回答by 4NDR01D3

I think the level o granularity is always a developer decision. I mean, the great thing of having so many exceptions and validations is that you can capture the specific error and act according to it, however if what you need doesn't require that level of robustness and as you are showing it is just to print out the stack trace, I think a wrapper method can be useful in your case.

我认为级别 o 粒度始终是开发人员的决定。我的意思是，拥有如此多的异常和验证的好处在于您可以捕获特定的错误并根据它采取行动，但是如果您需要的不需要那种级别的稳健性并且正如您所展示的那样只是打印在堆栈跟踪之外，我认为包装器方法对您的情况很有用。