|
midrange.com
So I guess the best thing for me to do then is to use the try catch
finally and put the close connection in the finally in case of any errors?
Here's the RecordLock code to just for verification:
/*
* Created on Mar 18, 2005
*
* To change the template for this generated file go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
package com.csj.bulkgarbage.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.text.*;
import javax.sql.DataSource;
/**
* @author RPower
*
* To change the template for this generated type comment go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
public class RecordLock {
/**
* @param ds
* @param conn
* @param table
* @param fieldname
* @param lockfield
* @param recordKey
* @param lib
* @param userid
* @return
*/
//check if record is locked, if not, lock it for current user.
public synchronized static boolean isLocked(
DataSource ds,
Connection conn,
String lib,
String table,
String fieldname,
String recordKey,
String lockfield,
String timefield,
String userid) {
boolean result = false;
PreparedStatement checkLock = null;
ResultSet rs = null;
try {
java.sql.DatabaseMetaData dmd =
conn.getMetaData();
String query =
"SELECT "
+ lockfield
+ " FROM "
+ lib
+ dmd.getCatalogSeparator()
+ table
+ " WHERE "
+ fieldname
+ "='"
+ recordKey.trim()
+ "' FOR UPDATE OF "
+ lockfield.trim()
+ ", "
+ timefield.trim();
checkLock = conn.prepareStatement(query);
rs = checkLock.executeQuery();
rs.next();
String cursorName = rs.getCursorName();
String checkField = rs.getString(lockfield);
//field is not blank, so it is in use and not by
the current user
if (!checkField.trim().equals("")
&& !checkField.trim().equals(userid)) {
result = true;
} else {
result = false;
query =
"UPDATE "
+ lib
+
dmd.getCatalogSeparator()
+ table
+ " SET "
+ lockfield.trim()
+ " = ?,"
+ timefield.trim()
+ " = ? WHERE CURRENT OF "
+ cursorName;
PreparedStatement getLock =
conn.prepareStatement(query);
getLock.setString(1, userid);
Date today = new Date();
SimpleDateFormat formatter = new
SimpleDateFormat("yyyyMMdd");
int callRecDate =
Integer.parseInt(formatter.format(today));
formatter = new
SimpleDateFormat("yyyy/MM/dd");
String callRecDateString =
formatter.format(today);
formatter = new SimpleDateFormat("h:mm
a");
String callRecTime = (String)
formatter.format(today);
getLock.setString(
2,
callRecDateString.trim() + " " +
callRecTime.trim());
getLock.executeUpdate();
if (getLock != null){
getLock.close();
getLock = null;
}
}
conn.commit();
if (checkLock != null){
checkLock.close();
checkLock = null;
}
if (rs != null){
rs.close();
rs = null;
}
} catch (SQLException e) {
result = true;
e.printStackTrace();
} finally {
if (checkLock != null){
try { checkLock.close(); } catch
(SQLException e) { ; }
checkLock = null;
}
if (rs != null){
try { rs.close(); } catch (SQLException e)
{ ; }
rs = null;
}
}
return result;
}
/**
* @param ds
* @param conn
* @param table
* @param fieldname
* @param lockfield
* @param recordKey
* @param lib
* @param userid
* @return
*/
//unlock the record that was in use by the user.
public synchronized static boolean unlock(
DataSource ds,
Connection conn,
String lib,
String table,
String fieldname,
String recordKey,
String lockfield,
String timefield,
String userid,
boolean forceUnlock) {
boolean result = false;
PreparedStatement checkLock = null;
ResultSet rs = null;
try {
java.sql.DatabaseMetaData dmd =
conn.getMetaData();
String query =
"SELECT "
+ lockfield
+ " FROM "
+ lib
+ dmd.getCatalogSeparator()
+ table
+ " WHERE "
+ fieldname
+ "='"
+ recordKey.trim()
+ "' FOR UPDATE OF "
+ lockfield.trim()
+ ", "
+ timefield.trim();
checkLock = conn.prepareStatement(query);
rs = checkLock.executeQuery();
if (rs.next()) {
String cursorName = rs.getCursorName();
String checkField =
rs.getString(lockfield);
if (!checkField.trim().equals(userid) &&
!forceUnlock) {
result = false;
} else {
//set field to blanks to unlock
record
result = true;
query =
"UPDATE "
+ lib
+
dmd.getCatalogSeparator()
+ table
+ " SET "
+ lockfield.trim()
+ " = ?,"
+ timefield.trim()
+ " = ? WHERE
CURRENT OF "
+ cursorName;
PreparedStatement resetLock =
conn.prepareStatement(query);
resetLock.setString(1, "");
resetLock.setString(2, "");
resetLock.executeUpdate();
if (resetLock != null){
resetLock.close();
resetLock = null;
}
}
}
conn.commit();
if (checkLock != null){
checkLock.close();
checkLock = null;
}
if (rs != null){
rs.close();
rs = null;
}
} catch (SQLException e) {
result = false;
System.out.println("Error with " + lockfield);
e.printStackTrace();
} finally {
if (checkLock != null){
try { checkLock.close(); } catch
(SQLException e) { ; }
checkLock = null;
}
if (rs != null){
try { rs.close(); } catch (SQLException e)
{ ; }
rs = null;
}
}
return result;
}
/**
* @param ds
* @param conn
* @param table
* @param fieldname
* @param lockfield
* @param recordKey
* @param lib
* @param userid
* @return
*/
//returns user name of user who locked the record
public synchronized static String whoLocked(
DataSource ds,
Connection conn,
String lib,
String table,
String fieldname,
String recordKey,
String lockfield,
String timefield,
String userid) {
String result = "";
PreparedStatement checkLock = null;
ResultSet rs = null;
try {
//March 24, 2006 Removed code for create new
connection - Ron Power
//Connection conn = ds.getConnection();
java.sql.DatabaseMetaData dmd =
conn.getMetaData();
String query =
"SELECT "
+ lockfield
+ ","
+ timefield
+ " FROM "
+ lib
+ dmd.getCatalogSeparator()
+ table
+ " WHERE "
+ fieldname
+ "='"
+ recordKey.trim()
+ "'";
checkLock = conn.prepareStatement(query);
rs = checkLock.executeQuery();
rs.next();
result = rs.getString(lockfield) + " " +
rs.getString(timefield);
if (checkLock != null){
checkLock.close();
checkLock = null;
}
if (rs != null){
rs.close();
rs = null;
}
//March 24, 2006 Removed code for create new
connection - Ron Power
//conn.close();
} catch (SQLException e) {
result = "";
e.printStackTrace();
} finally {
if (checkLock != null){
try { checkLock.close(); } catch
(SQLException e) { ; }
checkLock = null;
}
if (rs != null){
try { rs.close(); } catch (SQLException e)
{ ; }
rs = null;
}
}
return result;
}
}
Ron Power
Programmer
Information Services
City Of St. John's, NL
P.O. Box 908
St. John's, NL
A1C 5M2
709-576-8132
rpower@xxxxxxxxxx
http://www.stjohns.ca/
___________________________________________________________________________
Success is going from failure to failure without a loss of enthusiasm. -
Sir Winston Churchill
"Joe Sam Shirah" <jshirah@xxxxxxxxxxxxx>
Sent by: java400-l-bounces@xxxxxxxxxxxx
2006/04/28 02:00 AM
Please respond to
Java Programming on and around the iSeries / AS400
<java400-l@xxxxxxxxxxxx>
To
"Java Programming on and around the iSeries / AS400"
<java400-l@xxxxxxxxxxxx>
cc
Subject
Re: Connection Pooling
Hi Ron,
Thread safety for the connection itself, as indicated by Dan, is
handled
by ( and you are dependedent on ) the connection pool. You are handling
thread safety for the reference by your use of a local variable.
I expect most of your problems would go away if you ensure that the
connection is closed when you are done with it. A defensive programmer
will
also close ResultSets and Statements, although closing the connection
*should* close these as well. You also want to make sure that happens in
all circumstances. Otherwise you risk starving the pool, and gc will not
help you here. BTW, "close" is overridden by connection pools to just
return the connection to the available set. I don't see anything in your
code that explicitly closes the connection.
If it happens that RecordLock is supposed to do the close for you,
then
you may have a thread safety issue, since it appears to use a static
method.
We, of course, can't see the code from here. Even if that works, as it
stands, if you get an exception, the connection is not closed.
Last, you can always have a valid case where the connection gets
closed
for any number of reasons ( what about system shutdown, for an extreme
example, ) and you should always be prepared. The pool can't help you
while
you have the connection.
Joe Sam
Joe Sam Shirah - http://www.conceptgo.com
conceptGO - Consulting/Development/Outsourcing
Java Filter Forum: http://www.ibm.com/developerworks/java/
Just the JDBC FAQs: http://www.jguru.com/faq/JDBC
Going International? http://www.jguru.com/faq/I18N
Que Java400? http://www.jguru.com/faq/Java400
----- Original Message -----
From: <RPower@xxxxxxxxxx>
To: "Java Programming on and around the iSeries / AS400"
<java400-l@xxxxxxxxxxxx>
Sent: Thursday, April 27, 2006 2:41 PM
Subject: Connection Pooling
> Question.
> I have the following servlet:
> /*
> * Created on Mar 18, 2005
> *
> * To change the template for this generated file go to
> * Window>Preferences>Java>Code Generation>Code and Comments
> */
> package com.csj.bulkgarbage;
>
> import java.io.IOException;
> import java.sql.*;
> import java.util.*;
> import javax.naming.InitialContext;
> import javax.servlet.*;
> import javax.servlet.http.*;
> import javax.sql.DataSource;
>
> import com.csj.bulkgarbage.util.*;
>
> /**
> * @author RPower
> *
> * To change the template for this generated type comment go to
> * Window>Preferences>Java>Code Generation>Code and Comments
> */
> public class CancelGarbage extends HttpServlet {
> /************************************
> * Put in the parameters and variables
> * for datasource.
> *
> * @author RPower Jun 2, 2005
> ************************************/
> private static DataSource ds = null;
> private static String collectionName;
> private static String garbschedmstTable;
> private String dsname;
> private DatabaseMetaData dmd;
> private ServletContext context;
> private String jdbc_source;
>
> public void doGet(HttpServletRequest req, HttpServletResponse
> resp)
> throws ServletException, IOException {
> performTask(req, resp);
> }
>
> public void doPost(HttpServletRequest req, HttpServletResponse
> resp)
> throws ServletException, IOException {
> performTask(req, resp);
> }
>
> private void getDS() {
> try {
> Hashtable parms = new Hashtable();
> parms.put(
> "java.naming.factory.initial",
> "com.ibm.websphere.naming.WsnInitialContextFactory");
> InitialContext cxt = new InitialContext(parms);
> ds = (DataSource) cxt.lookup("jdbc/" + dsname);
> } catch (Exception e) {
> e.printStackTrace();
> }
> }
>
> public void init(ServletConfig config) throws ServletException {
> super.init(config);
> try {
> context = getServletContext();
> garbschedmstTable =
> context.getInitParameter("bulk.garbmastertable");
> collectionName =
> context.getInitParameter("pubwrk.lib");
> dsname = context.getInitParameter("ds.name");
> } catch (Exception e) {
> System.err.println(e);
> }
> }
>
> public void performTask(HttpServletRequest req,
> HttpServletResponse resp) {
> HttpSession sess;
> Connection conn = null;
> Customer customer;
> String type;
> String date;
> String counter;
> String url;
> try {
> sess = req.getSession(true);
> //!---New code put in Jun 2, 2005
> if (ds == null)
> getDS();
> conn = ds.getConnection();
> dmd = conn.getMetaData();
> // -----End New Code------->
> BulkGarbageSchedMaster b =
> (BulkGarbageSchedMaster) sess.getAttribute("bBulkGarbage");
> customer = (Customer)
> sess.getAttribute("customer");
> RecordLock.unlock(ds, conn, collectionName,
> garbschedmstTable, "WGSCHDID", b.getGarbageId(), "WGINUSBY", "WGINUSTM",
> req.getRemoteUser(), false);
> req.setAttribute("newCustomer", "no");
> sess.setAttribute("customer", customer);
> sess.setAttribute("disabled", "disabled");
> if (req.getParameter("counter")!=null){
> counter = req.getParameter("counter");
> date = req.getParameter("date");
> type = req.getParameter("type");
> url = "Edit_Pickupdate_Apps?type=" +
type
> + "&date=" + date + "&counter=" + counter;
> } else {
> url = "LoadCustomer?" +
> customer.getCustId();
> }
> getServletConfig().getServletContext().getRequestDispatcher(
> url).forward(
> req,
> resp);
> } catch (Exception e) {
> e.printStackTrace();
> }
> }
> }
>
> When it's being run on the server, sometimes I get the error that the
> connection is closed exception. Basically, I think that the threads are
> modifying each other as they are running. How do I make the connection
> thread safe?
>
> Ron Power
> Programmer
> Information Services
> City Of St. John's, NL
> P.O. Box 908
> St. John's, NL
> A1C 5M2
> 709-576-8132
> rpower@xxxxxxxxxx
> http://www.stjohns.ca/
>
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
