Background
In my current project - a server product with no GUI front-end, I'm trying to write in better error handling support. Errors currently are outputted to the logs and are typically not read by users.
在我目前的项目中 - 没有GUI前端的服务器产品,我正在尝试编写更好的错误处理支持。当前错误输出到日志,通常不被用户读取。
We use PostgreSQL as our database backend and we access it using direct JDBC calls and DAOs via a database pooler. Most database related exceptions are wrapped in a generic DatabaseException class that implements RuntimeException and attempts to pull out debugging and state information from the exception it was passed. In our particular case, it will access the underlying PostgreSQL database driver - PSQLException. So far this approach has worked well for getting more verbose information about what caused the database error, with the notable exception described below.
我们使用PostgreSQL作为我们的数据库后端,我们使用直接JDBC调用和DAO通过数据库池来访问它。大多数与数据库相关的异常都包含在一个实现RuntimeException的通用DatabaseException类中,并尝试从传递的异常中提取调试和状态信息。在我们的特定情况下,它将访问底层的PostgreSQL数据库驱动程序 - PSQLException。到目前为止,这种方法很适合获取有关导致数据库错误的更详细信息,下面将介绍明显的例外情况。
Furthermore, since we have very specific performance and legacy support requirements we have a lot of custom SQL magic that makes the following the stack trace back a bit more time intensive but not impossible or difficult.
此外,由于我们有非常具体的性能和遗留支持要求,我们有很多自定义SQL魔术,使得跟踪堆栈跟踪的时间更加集中,但并非不可能或困难。
Problem Described
I have noticed that when we get a SQLException as a result of a faulty SQL statement, the driver's implementation does not return the SQL statement that caused the error. After doing a little searching, I found out that there is a way to drop the PostgreSQL driver into a debug mode on startup and have it display properties about its internal query. However, it is undesirable for us to run the driver in debug mode in our production environment (and honestly I haven't been able to figure out how to get it into the freakin mode!).
我注意到,当我们因错误的SQL语句而得到SQLException时,驱动程序的实现不会返回导致错误的SQL语句。在做了一点搜索后,我发现有一种方法可以在启动时将PostgreSQL驱动程序放入调试模式,并让它显示有关其内部查询的属性。但是,我们不希望在我们的生产环境中以调试模式运行驱动程序(老实说,我还没弄清楚如何让它进入freakin模式!)。
Question
Has anyone else dealt with this same issue before and found a solution? If not, is there some OOP pattern out there for storing query information before execution and then assigning that information to the exception thrown? Or do most developers just feel that they don't need the full query to troubleshoot database issues? Honestly, I don't need it because I have the full stack trace and I can look up the invoking query, but it definitely speeds up my debugging by having it be the first thing that I see in the error logs.
有没有其他人之前处理过同样的问题,并找到了解决方案?如果没有,是否有一些OOP模式用于在执行之前存储查询信息,然后将该信息分配给抛出的异常?或者大多数开发人员只是觉得他们不需要完整的查询来解决数据库问题?老实说,我不需要它,因为我有完整的堆栈跟踪,我可以查找调用查询,但它肯定加快我的调试,因为它是我在错误日志中看到的第一件事。
6 个解决方案
#1
I am assuming that when you make the call to execute the query you have the statement, and you receive the Exception, so at that point you have both. It seems like you could do your analysis there.
我假设当你调用执行查询时你有语句,并且你收到了Exception,所以那时你有两个。看起来你可以在那里进行分析。
However, maybe you're catching things further up. So, what you might do is on your own custom subclass of Exception, DatabaseException, add a triggeringSQLStatement member with a getter and setter, and then at the place where you attempt to execute the statement, catch the original Exception from PostgreSQL, create a new DatabaseException, set the triggeringSQLStatement to be the statement you just executed, and call initCause() on the DatabaseException to set the Exception caught from PostgreSQL as the cause of your exception; then throw your DatabaseException and the calling code which catches it will have an object that prints out a very decent stack trace of what happened plus provides access to the SQL statement that caused the problem. For more information on this approach, you might want to research Java Exception chaining. Even if you don't use all of what I just described, I think you should definitely be using Java Exception chaining already.
但是,也许你正在进一步追赶。所以,你可能会做的是你自己的自定义子类Exception,DatabaseException,添加一个带有getter和setter的triggeringSQLStatement成员,然后在你尝试执行语句的地方,从PostgreSQL中捕获原始的Exception,创建一个新的DatabaseException,将triggeringSQLStatement设置为刚刚执行的语句,并在DatabaseException上调用initCause()以将从PostgreSQL捕获的Exception设置为异常的原因;然后抛出你的DatabaseException,捕获它的调用代码将有一个对象打印出一个非常不错的堆栈跟踪发生的事情,并提供对导致问题的SQL语句的访问。有关此方法的更多信息,您可能希望研究Java Exception链。即使你没有使用我刚才描述的所有内容,我认为你肯定应该使用Java Exception链接。
If there's not a spot anywhere in the code where you have access to both the SQL statement that caused the problem and the Exception that gets thrown, I'd be very curious as to why, and how that is possible. And I'd suggest you redesign your code so that you do have such a spot.
如果代码中没有任何地方可以访问导致问题的SQL语句和抛出异常的SQL语句,那么我会非常好奇为什么以及如何可能。我建议你重新设计你的代码,这样你就有了这样的地方。
Edit: Since you're wanting to see the SQL statement first thing in the log, you could probably also override your DatabaseException's toString() method (or other appropriate methods; I'm not sure what gets called when an Exception is printed out) to print out the included SQL statement, assuming you included it as I described above.
编辑:由于您希望在日志中首先看到SQL语句,您可能还可以覆盖DatabaseException的toString()方法(或其他适当的方法;我不确定打印出Exception时会调用什么)打印出包含的SQL语句,假设您按上述方法包含它。
#2
I used to add the SQL query in my custome Exception object when ever there is an SQLException. In the code where ever I am logging the exception details in log file, I used to log the SQL also.
当有SQLException时,我曾经在我的客户Exception对象中添加SQL查询。在我在日志文件中记录异常详细信息的代码中,我也用来记录SQL。
#3
The easiest way to do this I think is to use a third party product like p6spy. It gets in between your jdbc driver and your database and reports the exact queries that are run. It's very easy to run on demand as it's implemented as another JDBC driver that will delegate to your actual JDBC driver. Very powerful tool that I can't imagine working without.
我认为最简单的方法是使用像p6spy这样的第三方产品。它介于您的jdbc驱动程序和数据库之间,并报告运行的确切查询。它很容易按需运行,因为它是作为另一个JDBC驱动程序实现的,它将委托给您的实际JDBC驱动程序。非常强大的工具,我无法想象没有工作。
#4
Why not add a file logger around all JDBC calls (log4j)?
为什么不在所有JDBC调用(log4j)周围添加文件记录器?
Whenever you make a SQL call you log the SQL and how long it took to execute. Simple stuff.
无论何时进行SQL调用,都要记录SQL以及执行所需的时间。简单的东西。
We do this for any call to an external system E.g. SOAP calls, RMI, Corba, etc. Its proved invaluable almost every day and if it affects performance.. I haven't noticed!
我们这样做是为了对外部系统的任何调用。 SOAP调用,RMI,Corba等。它几乎每天都被证明是非常宝贵的,如果它影响性能......我没有注意到!
If you have security concerns i.e. don't want it to go to a log file on a client machine, you could use a SocketAppender and send it to a remote machine for centralised logging purposes. This won't be totally secure but would stop the casual snooper.
如果您有安全问题,即不希望它转到客户端计算机上的日志文件,您可以使用SocketAppender并将其发送到远程计算机以进行集中式日志记录。这不会是完全安全的,但会阻止随便的窥探者。
#5
The stacktrace can point you to the DAO method that caused the problem, isn't that the case?
堆栈跟踪可以指向导致问题的DAO方法,是不是这样?
Edit after comment: If your SQL query is complex and dynamically generated from previous parts of the code then you could log (level TRACE or DEBUG) these statements before executing them. In the logging configuration you could enable logs only for the DAO(s).
注释后编辑:如果您的SQL查询很复杂并且是从代码的前面部分动态生成的,那么您可以在执行它们之前记录(级别TRACE或DEBUG)这些语句。在日志记录配置中,您只能为DAO启用日志。
#6
Another solution to spy on the queries you are executing is pgFouine which analyses and reports on the logs generated by Postgres
监视正在执行的查询的另一个解决方案是pgFouine,它分析并报告Postgres生成的日志
#1
I am assuming that when you make the call to execute the query you have the statement, and you receive the Exception, so at that point you have both. It seems like you could do your analysis there.
我假设当你调用执行查询时你有语句,并且你收到了Exception,所以那时你有两个。看起来你可以在那里进行分析。
However, maybe you're catching things further up. So, what you might do is on your own custom subclass of Exception, DatabaseException, add a triggeringSQLStatement member with a getter and setter, and then at the place where you attempt to execute the statement, catch the original Exception from PostgreSQL, create a new DatabaseException, set the triggeringSQLStatement to be the statement you just executed, and call initCause() on the DatabaseException to set the Exception caught from PostgreSQL as the cause of your exception; then throw your DatabaseException and the calling code which catches it will have an object that prints out a very decent stack trace of what happened plus provides access to the SQL statement that caused the problem. For more information on this approach, you might want to research Java Exception chaining. Even if you don't use all of what I just described, I think you should definitely be using Java Exception chaining already.
但是,也许你正在进一步追赶。所以,你可能会做的是你自己的自定义子类Exception,DatabaseException,添加一个带有getter和setter的triggeringSQLStatement成员,然后在你尝试执行语句的地方,从PostgreSQL中捕获原始的Exception,创建一个新的DatabaseException,将triggeringSQLStatement设置为刚刚执行的语句,并在DatabaseException上调用initCause()以将从PostgreSQL捕获的Exception设置为异常的原因;然后抛出你的DatabaseException,捕获它的调用代码将有一个对象打印出一个非常不错的堆栈跟踪发生的事情,并提供对导致问题的SQL语句的访问。有关此方法的更多信息,您可能希望研究Java Exception链。即使你没有使用我刚才描述的所有内容,我认为你肯定应该使用Java Exception链接。
If there's not a spot anywhere in the code where you have access to both the SQL statement that caused the problem and the Exception that gets thrown, I'd be very curious as to why, and how that is possible. And I'd suggest you redesign your code so that you do have such a spot.
如果代码中没有任何地方可以访问导致问题的SQL语句和抛出异常的SQL语句,那么我会非常好奇为什么以及如何可能。我建议你重新设计你的代码,这样你就有了这样的地方。
Edit: Since you're wanting to see the SQL statement first thing in the log, you could probably also override your DatabaseException's toString() method (or other appropriate methods; I'm not sure what gets called when an Exception is printed out) to print out the included SQL statement, assuming you included it as I described above.
编辑:由于您希望在日志中首先看到SQL语句,您可能还可以覆盖DatabaseException的toString()方法(或其他适当的方法;我不确定打印出Exception时会调用什么)打印出包含的SQL语句,假设您按上述方法包含它。
#2
I used to add the SQL query in my custome Exception object when ever there is an SQLException. In the code where ever I am logging the exception details in log file, I used to log the SQL also.
当有SQLException时,我曾经在我的客户Exception对象中添加SQL查询。在我在日志文件中记录异常详细信息的代码中,我也用来记录SQL。
#3
The easiest way to do this I think is to use a third party product like p6spy. It gets in between your jdbc driver and your database and reports the exact queries that are run. It's very easy to run on demand as it's implemented as another JDBC driver that will delegate to your actual JDBC driver. Very powerful tool that I can't imagine working without.
我认为最简单的方法是使用像p6spy这样的第三方产品。它介于您的jdbc驱动程序和数据库之间,并报告运行的确切查询。它很容易按需运行,因为它是作为另一个JDBC驱动程序实现的,它将委托给您的实际JDBC驱动程序。非常强大的工具,我无法想象没有工作。
#4
Why not add a file logger around all JDBC calls (log4j)?
为什么不在所有JDBC调用(log4j)周围添加文件记录器?
Whenever you make a SQL call you log the SQL and how long it took to execute. Simple stuff.
无论何时进行SQL调用,都要记录SQL以及执行所需的时间。简单的东西。
We do this for any call to an external system E.g. SOAP calls, RMI, Corba, etc. Its proved invaluable almost every day and if it affects performance.. I haven't noticed!
我们这样做是为了对外部系统的任何调用。 SOAP调用,RMI,Corba等。它几乎每天都被证明是非常宝贵的,如果它影响性能......我没有注意到!
If you have security concerns i.e. don't want it to go to a log file on a client machine, you could use a SocketAppender and send it to a remote machine for centralised logging purposes. This won't be totally secure but would stop the casual snooper.
如果您有安全问题,即不希望它转到客户端计算机上的日志文件,您可以使用SocketAppender并将其发送到远程计算机以进行集中式日志记录。这不会是完全安全的,但会阻止随便的窥探者。
#5
The stacktrace can point you to the DAO method that caused the problem, isn't that the case?
堆栈跟踪可以指向导致问题的DAO方法,是不是这样?
Edit after comment: If your SQL query is complex and dynamically generated from previous parts of the code then you could log (level TRACE or DEBUG) these statements before executing them. In the logging configuration you could enable logs only for the DAO(s).
注释后编辑:如果您的SQL查询很复杂并且是从代码的前面部分动态生成的,那么您可以在执行它们之前记录(级别TRACE或DEBUG)这些语句。在日志记录配置中,您只能为DAO启用日志。
#6
Another solution to spy on the queries you are executing is pgFouine which analyses and reports on the logs generated by Postgres
监视正在执行的查询的另一个解决方案是pgFouine,它分析并报告Postgres生成的日志