Thread: Postgres Issue with JDBC
Hi All
I am a novice in using Postgres.I have been mainly using Postgres with Zope
installed on Mandrake Linux.PLEASE SPARE A LITTLE TIME TO READ MY MAIL since
it is a bit long.
I am currently having some Major difficulties in running my application
developed in java using Postgres as the back end.
I will explain the system I am using.The main application is devloped in
Java on Mandrake Linux8.2 <database is Postgres 7.2.2 and the pgjdbc2.jar
Driver.
My application is using connection pooling to get the connection from the
Connection Pool Class(This is my own class DBConnection() with a few
functions defined --
getConnection()
freeConnection(objConnection)
)
and then using the connection to execute the query.
Let me give a brief description about the application architecture.
I am trying to implement MVC in a way.
I have Java class files(MODEL) which are actually doing the actual select
and insert queries.
I have a control Servlet(CONTROLLER) which is the Servlet that is called by
all the JSp Pages .The servlet gets a connection form the Pool in the init()
and then calls the function based on the request from the JSP Page and
passes the connection object to the Class File which does the query.
========================================================================
public RequestDispatcher objRD=null;
public void init(ServletConfig config) throws ServletException
{
super.init(config);
objConnMgr = new DBConnection();
}
public void doPost(HttpServletRequest objRequest, HttpServletResponse
objResponse) throws ServletException, IOException
{
objConnection = objConnMgr.getConnection();
}
public ArrayList someFunction()
{
..........................
........................
//calls to the functions in the MODEL java classes for:
//select * from table
//insert into table
// delete from table
// update table where
ArrayList objArrayList = objAdmin.showAll();
objRequest.setAttribute("COUNT", objArrayList.get(0));
objRequest.setAttribute("USERS", objArrayList.get(1));
objRequest.setAttribute("PageNum", objArrayList.get(2));
objConnMgr.freeConnection(objConnection);
objRD = getServletContext().getRequestDispatcher("/Admin/index.jsp");
objRD.forward(objRequest, objResponse);
}
=========================================================================
NOw the jsp page requests the values from the Request Object and does the
coresponding action.
The Problem:
The Server shows too many Clients Exception everytimw within half an hour or
two of starting Tomcat and Postgres.
Althought the number of users accessing the Site would be at the most 30
concurrent users.
THE Connection doesnt seem to be getting closed when I call the connection
Free Manager.The reason would be that the ResultSet is not null since I may
have done a select.But in this case the JSP page is dispalyed by requesting
the ResultSet
and then looping through.BUt I am not able to clse the cnnectionnhere either
as I do not know if that Connection has been threaded and is being used by
any other function.
SO in Short I am not able to close the connection in the class Files(MODEL)
because the Result set is to be sent back after the select .
I am not able to close the connection in the Control Servlet( CONTROL)
because I have to send the Result Set to the jSP page to be shown.So even if
I write connection.close() it will nto since the Result Se is not empty.
I am not being able to close the Connection in the JSP page(VIEW) since the
connection Object cannot be sent forward foimr the Servlet since I do not
know if the Object has been threaded and a thread is being used by another
function.CO I cannot close it in the JSP Page either.Also if I wanted to
close a particular connection I cannot gewt the connection Object by knwoing
the ResulSert obj.I can only get the statement.IN the JSP Page I am setting
the ResultSE and Statement Object to null.But stil I cannot close the
connection.
So in short the number of connections keep increasing and the Sytem crashes
or else Postgers shows an error of Too many Clients and too Many
Connections.Increasing the number oc connections is not a solution since I
have kept it at 256 but still with 5 users in a few hours the application
comes back to the same situation.
I am sure you may have come across such a problem may times.Please help me
out and guide me as to how I should go about this isse .Where exactly to
close the connection.How to pass the cnnection Object to the JSP page and
make sure the right connection is closed.Or if it is POstgres which somehow
is holding connections persistently then how do I stop it.Thank s in
Advancew for reading this whole letter .Please point me in the right
direction.
Thanks and Best Regards
John Kunchandy
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.476 / Virus Database: 273 - Release Date: 4/24/2003
On Mon, 2003-05-05 at 10:41, John Kunchandy wrote: > Or if it is POstgres which somehow > is holding connections persistently then how do I stop it.Thank s in > Advancew for reading this whole letter .Please point me in Bad JSP design - nothing to do with PostgreSQL. </html> <% connection_name.close(); Connconnection_name.close(); %> I show I do it Cheers Tony Grant -- www.tgds.net Library management software toolkit, redhat linux on Sony Vaio C1XD, Dreamweaver MX with Tomcat and PostgreSQL
Hi Tony
Thanks for your prompt Reply.Could you pease guide me in closing the
connection without affecting the functioning of my application elsewhere.
what should I do??I am giving the step by step procedure followed.please
mark against the step where I shoudl close the connection and how I shoudl
close the correct connection .
1)The JSP Page sends a request
2)The Control Servlet understands the Request retrives a Connection from the
Connection class and calls teh Particular Java Class and passes the
Connection Object as a parameter.
3)The Java Class executes the Queries and sends back the Result or ResultSet
to the Control Servlet
4) The Control Servlet now makes a call to the ConnectionManager to Free the
Connection .(but since the Result Set still has values Connection is not
freed).
5)The JSp Page retrieves the values from the Request Object and Displays the
values .
IN between these steps where can I close the connection and whne .If I am to
close the connection in a JSP page how will I sned the correct Connection
Object to the JSP page and how will I close it in the JSP Page.
Kindly help me out.
Thanks once again for your Prompt reply
Best Regards
John
----- Original Message -----
From: "Tony Grant"
To: "John Kunchandy"
Sent: Monday, May 05 2003 2:35 PM
Subject: Re: [JDBC] Postgres Issue with JDBC
: On Mon, 2003-05-05 at 10:41, John Kunchandy wrote:
:
: > Or if it is POstgres which somehow
: > is holding connections persistently then how do I stop it.Thank s in
: > Advancew for reading this whole letter .Please point me in
:
: Bad JSP design - nothing to do with PostgreSQL..
:
: </html>
: <%
: connection_name.close();
: Connconnection_name.close();
: %>
:
: I show I do it
:
: Cheers
:
: Tony Grant
: --
============================================================================
===
Hi All
I am a novice in using Postgres.I have been mainly using Postgres with Zope
installed on Mandrake Linux.PLEASE SPARE A LITTLE TIME TO READ MY MAIL since
it is a bit long.
I am currently having some Major difficulties in running my application
developed in java using Postgres as the back end.
I will explain the system I am using.The main application is devloped in
Java on Mandrake Linux8.2 <database is Postgres 7.2.2 and the pgjdbc2.jar
Driver.
My application is using connection pooling to get the connection from the
Connection Pool Class(This is my own class DBConnection() with a few
functions defined --
getConnection()
freeConnection(objConnection)
)
and then using the connection to execute the query.
Let me give a brief description about the application architecture.
I am trying to implement MVC in a way.
I have Java class files(MODEL) which are actually doing the actual select
and insert queries.
I have a control Servlet(CONTROLLER) which is the Servlet that is called by
all the JSp Pages .The servlet gets a connection form the Pool in the init()
and then calls the function based on the request from the JSP Page and
passes the connection object to the Class File which does the query.
========================================================================
public RequestDispatcher objRD=null;
public void init(ServletConfig config) throws ServletException
{
super.init(config);
objConnMgr = new DBConnection();
}
public void doPost(HttpServletRequest objRequest, HttpServletResponse
objResponse) throws ServletException, IOException
{
objConnection = objConnMgr.getConnection();
}
public ArrayList someFunction()
{
..........................
........................
//calls to the functions in the MODEL java classes for:
//select * from table
//insert into table
// delete from table
// update table where
ArrayList objArrayList = objAdmin.showAll();
objRequest.setAttribute("COUNT", objArrayList.get(0));
objRequest.setAttribute("USERS", objArrayList.get(1));
objRequest.setAttribute("PageNum", objArrayList.get(2));
objConnMgr.freeConnection(objConnection);
objRD = getServletContext().getRequestDispatcher("/Admin/index.jsp");
objRD.forward(objRequest, objResponse);
}
=========================================================================
NOw the jsp page requests the values from the Request Object and does the
coresponding action.
The Problem:
The Server shows too many Clients Exception everytimw within half an hour or
two of starting Tomcat and Postgres.
Althought the number of users accessing the Site would be at the most 30
concurrent users.
THE Connection doesnt seem to be getting closed when I call the connection
Free Manager.The reason would be that the ResultSet is not null since I may
have done a select.But in this case the JSP page is dispalyed by requesting
the ResultSet
and then looping through.BUt I am not able to clse the cnnectionnhere either
as I do not know if that Connection has been threaded and is being used by
any other function.
SO in Short I am not able to close the connection in the class Files(MODEL)
because the Result set is to be sent back after the select .
I am not able to close the connection in the Control Servlet( CONTROL)
because I have to send the Result Set to the jSP page to be shown.So even if
I write connection.close() it will nto since the Result Se is not empty.
I am not being able to close the Connection in the JSP page(VIEW) since the
connection Object cannot be sent forward foimr the Servlet since I do not
know if the Object has been threaded and a thread is being used by another
function.CO I cannot close it in the JSP Page either.Also if I wanted to
close a particular connection I cannot gewt the connection Object by knwoing
the ResulSert obj.I can only get the statement.IN the JSP Page I am setting
the ResultSE and Statement Object to null.But stil I cannot close the
connection.
So in short the number of connections keep increasing and the Sytem crashes
or else Postgers shows an error of Too many Clients and too Many
Connections.Increasing the number oc connections is not a solution since I
have kept it at 256 but still with 5 users in a few hours the application
comes back to the same situation.
I am sure you may have come across such a problem may times.Please help me
out and guide me as to how I should go about this isse .Where exactly to
close the connection.How to pass the cnnection Object to the JSP page and
make sure the right connection is closed.Or if it is POstgres which somehow
is holding connections persistently then how do I stop it.Thank s in
Advancew for reading this whole letter .Please point me in the right
direction.
Thanks and Best Regards
John Kunchandy
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.476 / Virus Database: 273 - Release Date: 4/24/2003
On Mon, 2003-05-05 at 11:53, John Kunchandy wrote: > Hi Tony > > Thanks for your prompt Reply.Could you pease guide me in closing the > connection without affecting the functioning of my application elsewhere. > what should I do??I am giving the step by step procedure followed.please > mark against the step where I shoudl close the connection and how I shoudl > close the correct connection . > 1)The JSP Page sends a request > 2)The Control Servlet understands the Request retrives a Connection from the > Connection class and calls teh Particular Java Class and passes the > Connection Object as a parameter. > 3)The Java Class executes the Queries and sends back the Result or ResultSet > to the Control Servlet > 4) The Control Servlet now makes a call to the ConnectionManager to Free the > Connection .(but since the Result Set still has values Connection is not > freed). > 5)The JSp Page retrieves the values from the Request Object and Displays the > values . Just close it at the end of the page. If you don't each time you open your page it will create a new occurrence of the connection. Cheers Tony Grant -- www.tgds.net Library management software toolkit, redhat linux on Sony Vaio C1XD, Dreamweaver MX with Tomcat and PostgreSQL
Hi John,
I return object wrappers to the JSP. F.e. my Java class has a method:
public MyBean getMyBean() {
// JDBC stuff to do SQL query, read out ResultSet and put values in MyBean
myBean.setField(rs.getString(1));
// close result set, statement and connection
return myBean;
}
When Controller puts MyBean in request (setAttribute) In JSP page I use
<jsp:useBean name="..." class="MyBean">
Without any JDBC stuff.
Jeroen
Example Code
(Note: My ConnectionProvider implementation uses a JNDI DataSource but could
also be implemented using pooling or the good old class.forName and
DriverManager.getConnection)
public class UserBalanceProvider implements TransTypeConstants {
private static UserBalanceProvider singleton = new
UserBalanceProvider();
private UserBalanceProvider() {
}
public static UserBalanceProvider getInstance() { return singleton; }
private final static String BALANCE_TABLE_ALIAS = "bal";
private final static String BALANCE_TABLE = "user_balance";
private final static String BALANCE_COLUMN_PREFIX =
BALANCE_TABLE_ALIAS+".";
private final static String BALANCE_COLUMNS =
BALANCE_COLUMN_PREFIX+"user_id,"
+ BALANCE_COLUMN_PREFIX+"credits100,"
+ BALANCE_COLUMN_PREFIX+"megapoints,"
+ BALANCE_COLUMN_PREFIX+"requested_pay_out_credits100,"
+ BALANCE_COLUMN_PREFIX+"confirmed_pay_out_credits100,"
+ BALANCE_COLUMN_PREFIX+"completed_pay_out_credits100,"
+ BALANCE_COLUMN_PREFIX+"games_played,"
+ BALANCE_COLUMN_PREFIX+"last_trans_id,"
+ BALANCE_COLUMN_PREFIX+"modify_date";
private UserBalance fillUserBalance(ResultSet rs) throws SQLException {
return new UserBalance(rs.getInt(1), // userId
rs.getInt(2), // credits100
rs.getInt(3), // megapoints
rs.getInt(4), // requested_pay_out_credits100
rs.getInt(5), // confirmed_pay_out_credits100
rs.getInt(6), // completed_pay_out_credits100
rs.getInt(7), // games_played
rs.getInt(8), // last_trans_id
rs.getTimestamp(9) // modify_date
);
}
/**
* returns current {@link UserBalance user balance} from database.
* @param userId ID of balance owner.
* @return UserBalance
*/
public UserBalance getUserBalance(int userId) throws AccountingException
{
UserBalance result = null;
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = ConnectionProvider.getReadOnlyConnection();
ps = con.prepareStatement("SELECT "+BALANCE_COLUMNS+" FROM
"+BALANCE_TABLE+" "+BALANCE_TABLE_ALIAS+" WHERE
"+BALANCE_COLUMN_PREFIX+"user_id=?");
ps.setInt(1, userId);
rs = ps.executeQuery();
if (rs.first()) {
result = fillUserBalance(rs);
}
rs.close();
rs = null;
ps.close();
ps = null;
con.close();
con = null;
} catch (Exception e) {
throw new
AccountingException("AccountingProvider.getUserBalance("+userId+"):
caught="+e, e);
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException rsCloseE) { }
}
if (ps != null) {
try {
ps.close();
} catch (SQLException psCloseE) { }
}
if (con != null) {
try {
con.close();
} catch (SQLException conCloseE) {
}
}
}
return result;
}
On Mon, 5 May 2003, John Kunchandy wrote: > 4) The Control Servlet now makes a call to the ConnectionManager to Free the > Connection .(but since the Result Set still has values Connection is not > freed). That sounds to me like a problem with your pooling. Your pooling code should return a pooled connection to a properly usable state when Connection.close() is called, regardless of the current state of the connection. If it can't do so, it should do a "real" close of the connection. Pooling code is very difficult to write. I suggest that unless you are a real expert, you just use someone else's. Or, if it's a low-volume site, you can get by without any pooling at all. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC