c***c
发帖数: 6234 | 1 【 以下文字转载自 Java 讨论区 】
发信人: cubic (黑猫), 信区: Java
标 题: 求救：Connection Pooling 没有close resultset 怎么解决
发信站: BBS 未名空间站 (Thu Oct 1 00:11:25 2015, 美东)
我们有了大麻烦。以前的code没有close resultset 和 preparestatemen。但call了
conn.close()。
1.是不是用了pooling就不能close connection？
2.查了log 貌似有connection leak。很严重吗？
我们10月12号go love
有没有什么变通办法让多用户不exhausted connections。这样多出1周可以scan 整个
project改所有地方
我们已经改了主要模块，但还有5-60个不重要模块没改。 |
|
t*****s
发帖数: 124 | 2 谨慎怀疑还是有connection没有close掉吧
因为一般driver的实现在close connection时，
会自动close相关连的statement和resultset
当然，手动close相关的statement和resultset是良好的编程习惯
很想知道楼主用的是什么样的driver
memory
整个 |
|
c***c
发帖数: 6234 | 3 统一谢谢各位了。
是的，用connection pool，如果是conn.close()，就是放回pooling。如果statement
或 resultset 没有close，conn是不会没其request用的。
争取了2 周，彻底fix这个。
难道不能有办法强行关掉所有statement 和resultset在本connection里吗？
以前用c#没这么大问题。
再次感谢回复 |
|
S*******s
发帖数: 13043 | 4 almost every search engine can return the result in the middle of the whole
resultset. say one query returns total 10 k rows. while the use can choose to
have a look only to the rows of number 5000 to 5010.
how to implement this in the databse? is it a must to generate all the top
5010 rows first? |
|
i*****t
发帖数: 220 | 5 When I second time call function to query something, the things I queried
previously are still overthere. Even I deleted the old stuff from the
database, they are still in the resultset. Why? How to delete the old stuff?
Thanks. |
|
c***c
发帖数: 6234 | 6 我们有了大麻烦。以前的code没有close resultset 和 preparestatemen。但call了
conn.close()。
1.是不是用了pooling就不能close connection？
2.查了log 貌似有connection leak。很严重吗？
我们10月12号go love
有没有什么变通办法让多用户不exhausted connections。这样多出1周可以scan 整个
project改所有地方
我们已经改了主要模块，但还有5-60个不重要模块没改。 |
|
r**i
发帖数: 1222 | 7 应该close statement和resultset，connection是release让pool来close。弄个memory
dump来看看吧。 |
|
f**********t
发帖数: 1001 | 8 这题应该不难，以前还做过，但是就是不能全pass。。。
我的思路是这样的，外面是一个二重循环，i从0到n - 2, j 从i+1到n-1。然后里面也
有个二重循环，把所有小于i的两个数的加和都存进去。
不知我的代码哪里有不对？或者谁能提供一个正确的版本给我看看。。。多谢！:)
这是我的代码：
vector > fourSum(vector &num, int target) {
// Start typing your C/C++ solution below
// DO NOT write int main() function
vector > result;
map > mp;
set > resultset;
sort(num.begin(), num.end());
for (int i = 0; i < (int)num.size() - 1;... 阅读全帖 |
|
d**e
发帖数: 6098 | 9 说起这个，我想搭车问一个问题。
interface不能instantiate，我的理解是不能直接
intfc myobj = new intfc();
但如果有个class
public class abc implements intfc
{
...
}
那 intfc myobj = new abc() 就可以。
我一直以来有个疑问是java里的一些interface，比如java.sql里的connection,
preparedstatement, resultset之类。我发现很多其它的interface在java doc里都会
提哪些class用到这些interface。但诸如connection, resultset这些却没提到哪个
class用到它。是否就说没class用它？如果是，但为什么又可以直接到，比如
Connection conn = XXX.getConnection(...)
Statement stmt = conn.createStatement(...)
ResultSet rs = stmt.executeQuery(...);
还是实际有cl |
|
n********a
发帖数: 68 | 10
PreparedStatement ps = conn.prepareStatement("select col from table where
col1=?");
ps.setString(1, "foo");
ResultSet rs = ps.executeQuery(sql);
// Now iterate through the ResultSet.
// After you finished iteration, close ResultSet object.
rs.close();
// Don't close ps yet if you have another variable to bind
// You can reuse the PreparedStatement.
ps.setString(1, "bar");
rs = ps.executeQuery(sql);
PreparedStatement ps = conn.prepareStatement("update table set col1=? where
col2=?");
ps.setStrin |
|
c***c
发帖数: 6234 | 11 谢谢啊
MLGB的，才和DBA聊完。
所有open cursor都是从query来的，没一个从我的stored procedure里来的。是原来的
老code。这帮人在 for loop 里execute query。不close resultset。仅仅在finally
里close resultset。
我觉得是这个原因。
所以，close resultset确实close cursor。 |
|
w*******e
发帖数: 285 | 12 我的一个server程序似乎总有内存泄漏，我用sun自己的profiling连续监控了2个礼拜
，heap size从40兆涨到了400兆，然后我看了内存的使用情况，发现有几项是数据库的
statement和resultset
percent live alloc'ed stack class
self accum bytes objs bytes objs trace name
10.76% 25.34% 52615656 243591 359835048 1665903 305016 com.mysql.jdbc.
ResultSet
10.17% 35.52% 49732488 230243 49732920 230245 305033 com.mysql.jdbc.
ResultSet
7.97% 53.57% 38974560 243591 38974560 243591 309126 com.mysql.jdbc.
Statement
7.53% 61.10% 36838880 230243 36838 |
|
w*******e
发帖数: 285 | 13 说了这么多是不是就是大家都认为执行完executeQuery返回ResultSet以后，
在对ResultSet操作完并关闭ResultSet以后，一定还要手动关闭Statement,
否则就算它没有了reference，也会一直残存在内存中？ |
|
|
S*******0
发帖数: 198 | 15 1. Deadlock describes a situation that two or more threads (processes) are
blocked forever, waiting for each other.
Causes: one thread needs to visit the resource that another thread is
possessing and vice versa.
Effects: If deadlock happens, the threads involved will hang there forever
in an undesired status. Deadlock should be avoided.
2.
public ArrayList getToyotas(ArrayList cars)
{
if(cars == null) return null;
ArrayList toyotas = new ArrayList();
for ... 阅读全帖 |
|
N********g
发帖数: 132 | 16 3. there are different kind of databases; given a query, system will tell
you which database you should connect(system gives you a string like "Oracle
" or "MySQL"). Design a class that could handle any query.
这题考的是separation of interface and implementation，code against interface
，not against implementation，如果做过JDBC开发，立马就知道什么意思。
好比你写了一些DAO（Database access object），就是用来把数据从数据库里面读出
来，然后交给中间层处理的工具类，但是你们开发组在不久的将来，打算把数据库由
oracle转成mysql。如果这些DAO里面的具体选取数据的方法，都跟oracle数据库相关，
都跟oralce对应的java class相关，那转数据库的时... 阅读全帖 |
|
N********g
发帖数: 132 | 17 3. there are different kind of databases; given a query, system will tell
you which database you should connect(system gives you a string like "Oracle
" or "MySQL"). Design a class that could handle any query.
这题考的是separation of interface and implementation，code against interface
，not against implementation，如果做过JDBC开发，立马就知道什么意思。
好比你写了一些DAO（Database access object），就是用来把数据从数据库里面读出
来，然后交给中间层处理的工具类，但是你们开发组在不久的将来，打算把数据库由
oracle转成mysql。如果这些DAO里面的具体选取数据的方法，都跟oracle数据库相关，
都跟oralce对应的java class相关，那转数据库的时... 阅读全帖 |
|
i*****t
发帖数: 220 | 18 I use two database accessing methods in a loop like:
while ()
{
...
query = "select ...";
ResultSet r = statement.executeQuery(query);
update = "update...";
int ud = statement.executeUpdate(update);
...
}
The first query has no problem. No compiling errors. But when it runs, it says
the update method does not work because "ResultSet is closed." What is wrong
with it and how to fix it? Thanks. |
|
S*********d
发帖数: 119 | 19 sql server 里面update一个table with a resultset，要根据被update的column来决
定怎么update，比如如果column1的value如果跟resultset的那个对应的value一样的话
，就不update这个column了
问题是除了CURSOR外，是否可以用一句UPDATE做呢 |
|
c***c
发帖数: 6234 | 20 我写java。使用了大量的Stored Procedure。很多是open cursor，return java一个
list。
同组另一个人只会一般query，也只用一般statement。现在测试发现，每个session 有
30-40个 held unclosed cursor。
我们都是确保在使用cursor fetch data 后，都close resultset 和statement的
会不会cursor从一般query来的？
难道不能使用SP的 out parameter 是cursor？我这么用了10年了，一直没问题。很疑惑
code类似
stmPersonMaster.registerOutParameter(5, OracleTypes.CURSOR);
stmPersonMaster.execute();
rs = (ResultSet) stmPersonMaster.getObject(5);
while(rs.next()){
rs.getString(colName)
}
rs.close
stmPersonMaster.close
谢谢 |
|
w*r
发帖数: 2421 | 21 I do not think oracle jdbc closes cursor just because you close resultset or
statement. The cursor will remain open until the connection is closed. If
your application is expecting to use connection pool and reuse connection,
then you might want to issue commit after resultset is fully processed. Give
that a shot. If you keep getting the ORA error with maximum cursor limit,
you can temporary elevate your number of open_cursors in the ora.ini file..
however, that will not solve your problem if yo... 阅读全帖 |
|
a*********e
发帖数: 35 | 22
1.1
我已经下了1.3,在设置Scrolling Resultset时的出错是不一样了,1.2里用
rs.setFetchDirection(ResultSet.FETCH_REVERSE);
要错的,1.3可以正常了,但是我无论怎么设,Direction,他还是出现
"java.sql.SQLException: Result set type is TYPE_FORWARD_ONLY"
不知道了,这次. |
|
r**e
发帖数: 57 | 23 当一个query返回0项结果时，resultset里面是什么？
为什么我用:
ResultSet rs;
rs = .... (should return nothing)
rs.equals(null) 或者 rs.wasNull()都给我返回false?
谢谢！ |
|
m******t
发帖数: 2416 | 24
发
Make your ResultSet scrollable, and use absolute() to move to the rows that
need to be shown and edited.
The javadoc of ResultSet has more details. |
|
c*****s
发帖数: 214 | 25 完整的例子太长，程序大概是这样。
写（假设用commons-fileupload）：
org.apache.commons.fileupload.FileItem fileItem = ...;
//我的程序很多是在jakarta
turbine框架里，拿到FileItem很容易。如果你也想用commons-fileupload请参考文档，
应该不难。
InputStream fileIn = fileItem.getInputStream();
...
Statement statement = dbcon.createStatement(...)//ResultSet类型不要READ_ONLY
ResultSet rs = statement.execute(...);
...
java.sql.Blob blob = rs.getBlob("file_content"); //假设字段是Blob类型
OutputStream blobOut = blob.setBinaryStream();
org.apache.commons.io.CopyUtils.copy(fileIn, |
|
w*******e
发帖数: 285 | 26 我试过了，执行10000条查询再profiling之后比较，确实不执行statement.close(）的
话statement和ResultSet都占很大比例的内存，而且明显创建过10000个对象，结束的
时候内存也还存在10000个对象。要是每次都执行statement.close()或者总是同一个
statement就没有这个问题。不过同一个statement如果执行了下一个executeQuery那么
前一个命令中获得的ResultSet就会自动关闭. |
|
s***e
发帖数: 122 | 27 1. In the JDBC API version 2.0 the ResultSet object can be used to UPDATE a
data value in the database.
Which of these describes how this is done?
A Use the updateXXX() methods to change the value of a given column.
B Use the getXXX() methods from the ResultSet class to get the row that
requires updating and create a new SQL UPDATE statement to perform
the update.
C Use the updateXXX() method to perform the update passing it the row
that requires updating.
D Use the updateXXX() method to perform |
|
T****U
发帖数: 3344 | 28 String getString(int columnIndex)
Retrieves the value of the designated column in the current row of
this ResultSet object as a String in the Java programming language.
String getString(String columnLabel)
Retrieves the value of the designated column in the current row of
this ResultSet object as a String in the Java programming language. |
|
S*******0
发帖数: 198 | 29 Web development职位，不是很大的公司，先发邮件来做题，第一次碰到。
刚刚做完，过会发我的sample答案上来
1. Describe a deadlock. What causes it? What are the effects?
2. Consider class Car with method getMake() -> String. Write a function to
retrieve all Toyotas from the following data structure:
ArrayList cars
3. List the bugs/problems in the following code snippets:
a. select * from claim where diagnosis_id in (739.1) and where patient_id in
(select patient_id from patient where name is ‘SMITH’)
b.with a as (select * from claim)
s... 阅读全帖 |
|
S*******C
发帖数: 822 | 30 package com.cdd.jdbc;
import java.sql.*;
public class CreateConnection {
public Connection conn=null;
public ResultSet rs=null;
public Statement stmt=null;
public CreateConnection() { //构造方法
}
//获取数据库连接方法
public Connection getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection("jdbc:mysql://mydbinstance.XXXXXXX.
us-east-1.rds.amazonaws.com:3306/db_database05","root","11111");
//不管用上... 阅读全帖 |
|
|
|
m**r
发帖数: 13 | 33 Java 2 的resultset已经支持absolute(int row)了吧? |
|
g****z
发帖数: 1135 | 34 I think it's because mysqli_query returns a resultset (in case of select
statement) regardless of rows not being found. So "if($res)" always return
true. Use "if($res->num_rows >0)"
出"
good
good" |
|
b****e
发帖数: 1275 | 35 this is no good.. what if i click on "next" and
want the resultset 100-200? :) |
|
a*********e
发帖数: 35 | 36 想要把Oracle里的table里的数据在Web上显示出来.
当然用JDBC了.现在的问题是,我想象一些网站的表示一样,可以分页
显示,就是显示了一页的数据后下面有文字联接"上一页""下一页""最
后一页",象你现在看到的这样.
用了JDBC的ResultSet后,好象是不能够直接定位的,只能next()的这
样来顺序查,取数据.
我怎样可以创建一个数据页窗口,每次可以选出制定的页的那些recor
d,来显示.
怎样显示我是会的.
这个问题对于作网站的Servlet来说,应该都会碰到的情况. |
|
e***s
发帖数: 1397 | 37 You may want to customize your own ResultSet class
implemented
by array or something else, so that you can display the
tuples
you want by specifying the index of the selected tuples.
When
the user click the links to "next" or to specified page
number,
you can either recall the servlet to do the query again or
store
the query reselt in session. I am not sure if this is what
you
want but hope it will help. |
|
m**i
发帖数: 89 | 38
JDBC 2's ResultSet has function method setFetchSize() and getFetchSize().
See Oracle's Manual Oracle Row Prefetching for details. |
|
J*L
发帖数: 2659 | 39 ResultSet rset = _conn.prepareStmt( "select seq.currval
from dual" );
rset.open(null);
rset.getNextRow();
id = rset.getColumnInt(1);
rset.close();
From this simple statement, I even got that id = 7 or the
integer I got, how come I got the error message of 00922?
missed or invalid option? |
|
a*p
发帖数: 62 | 40 In some top-level API, like in PHP, you can specify which
line to return when you call function like
mysql_fetch_array
or
odbc_fetch_row
check API documentation. I specified row number in ODBC and mySQL before but
just no source code on hand right now.
but when you select, all the 10k rows should already be in the server's cache.
this method just saves the time to transmit, if say, your mySQL server is remote. |
|
a*****i
发帖数: 4391 | 41 Well, that would be EXTREMELY slow if you have to be able to grab all
returnsets first.
For easier implementation; you can use Limit $start, $page functionality.
select * from test_table where test_id > 10 limit 10, 10
will give you 10 results starting from the 10th one. |
|
a*p
发帖数: 62 | 42 I don't think the original author doesn't know this solution.
his problem is probably far more complicated.
basically, if you want to query a search engine using sth like
where str like '%you_want%'
and you want the output to be paged.
how can you generate the second/third/etc... pages? |
|
a*****i
发帖数: 4391 | 43 Not sure what you meant, but you can keep track of the pages by specifying
the parameter like dummypage.php?start=0&page=15&total=100.(I have done this,
I know.)
And searching engine is a different story. I think they have used c code
to optimize the middle layer between web and the db. |
|
S*******s
发帖数: 13043 | 44 i think I mentioned "search engine" in my original post.
it is not a problem how the middle layer deal with the
parameter of start and end. the prolem is just as the title.
I don't know the limit function (so atp, what you assumed is
wrong ;). and i still don't understand how it can be used. I
can't find the description of this function from online
document.
the solution I can figure out is with cursor, only those
rows falls into that range was returned, which eleminate the
unnecessary traffice |
|
a*p
发帖数: 62 | 45 definitely search engine should use more effective code like
C. but probably this is common problem in implementing
search engine.
Well, what I mean is as the following. here in this bbs, you
want to query posts whose titles contain the letter "a" and
definitely you will get lots of results, say, 1000 records
and you defintely don't want all results to be outputted in
one page, so you page it. say, if you want 50 records per
page
It's fairly easy to generate the first page, just using
"limit 50" |
|
S*******s
发帖数: 13043 | 46 1. u both mean that search engine would rather do its own
query algorism than request against a RDBMS ? I strongly
doubt.
if it is not what you meant, I don't see too much difference
of the coding in client side, no matter it is C or not.
2. and do you know if there is the counterpart in MS SQL
Server of the "limit" function you mentioned? is this
function part of ANSI?
3. i think such function helps to those query that can be do
by a single select statment. but to those more complicated
query I |
|
S*******s
发帖数: 13043 | 47 now i got a solution:
temp table. we can do the index in the temp table. then only
return those in the range back to client. according to the
DBA, such operation runs very fast.
so is the solution to my other question. insert the rows
created by cursor into the temp table. return this table at
last.
so simple, hmm? |
|
c********x
发帖数: 93 | 48 我通过jdbc执行一条语句：
INSERT INTO OBJECT VALUES ( 'login' , 'pass' , 1)
结果每次都产生下面的exception:
java.sql.SQLException: [Microsoft][SQLServer JDBC Driver]No ResultSet set was
pr
oduced.
不过察看数据库，发现我的insert操作是成功的！！但是我的executeQuery是最后一条语
句啊!
请大侠给个提示是怎么回事，还有为什么会有这个exception, 谢谢先。 |
|
x*****n
发帖数: 98 | 49 problem: When I update some rows, how can I get the rows that have just been
updated, genericly?
Solution: 1.I've meant to use the JDBC 2.0's new feasures: updatable Resultset.
Unfortunatly the underlying JDBC DB2 driver didn't support it. Then I have to
write a parser to analyze the value expression. That's use select to get the
rows that will be updated. Then compute the new values according the expression
and update the corresponding columns.
The original purpose of it is: build a layer above |
|
j***w
发帖数: 1 | 50 I am not familiar with DB2. But the easiest ways I could think are
1. using rollback a transaction to undo update(s)
2. using triggers to store updated values and old values
actually, I have no idea why you need to redo update(s).
Resultset.
to
expression
any
value
the |
|
