Hi Martin,
You could also use a simple class called DBUtilsHelper to simplify
your database access code:
// This is the name of a pre-configured datasource attached to a JNDI name.
String jndiName = "mysqlDS";
DBUtilsHelper helper = DBUtilsHelperFactory.getInstance(jndiName);
/* If you don't want to use JDNI, you could instead give the connection details
* when calling the getInstance method, like this:
* DBUtilsHelper helper = DBUtilsHelperFactory.getInstance("driver.class", "
* connectURL", "user name", "password");
*
*/
String sql = "select * from TABLE_NAME where TABLE_FIELD like ${param_1{";
Map params = new HashMap();
params.put("param_1", "satisf%");
// Each element of the list is an array of Object, which can be
accessed by an index.
// The first field is at index 0, the second field is at index 1, and so on.
java.util.List recordsAsArray = helper.selectAsArrayList(sql, params);
// Each element of the list is a Map.
// You access the fields by its name.
java.util.Map recordsAsMap = helper.selectAsMapList(sql, params);
As you can see, using DBUtilsHelper, you don't have to remember to
open the connection, nor to close it, and you dont have to create a
prepared statement either. All of this is done automatically for you
(and each thread will get its own connection).
I can send you a working example so you can see how simple it is.
Regards,
Elifarley
On 11/29/05, Martin Grogan <[EMAIL PROTECTED]> wrote:
> Hi Allistair,
> Thanks for the help. I've downloaded and installed all the relevant
> JAR's and edited the server.xml (correctly I hope, I can forward if you
> like?).
> I've also created a simple database just to test the app.
> However, I'm lost when it comes to writing a basic servet that just
> opens the connection, executes a query and closes the connection. There
> are several (conflicting) resources on the web describing how to do this
> (some use JSP's which I don't use). In particular, the init() and
> destroy() events seem to be causing me confusion,
> In my main doGet method, I have something like :
> try
> {
> Connection connection= ??? need to get a connection from the
> pool here
> Statement statement=connection.createStatement();
> String query="SELECT name,age FROM test";
> ResultSet r=statement.execute(query);
> while(r.next())
> out.println(r.getString(1)+", "+r.getString(2));
> statement.close();
> }
> catch(Exception e) { out.println(e.getMessage()); }
>
> Any ideas?
> Thanks,
> Martin
>
>
>
> Allistair Crossley wrote:
>
> >There's a really nice way to get Tomcat to manage your database pool for
> >you with it's built-in DBCP support. This method is detailed in the
> >Tomcat pages
> >
> >http://tomcat.apache.org/tomcat-5.0-doc/jndi-datasource-examples-howto.h
> >tml
> >
> >In a nuthell, you put your database driver JAR into tomcat/common/lib
> >and take it out of your webapp's WEB-INF/lib. You then declare a
> >configuration block for a Resource which tells Tomcat to create an
> >instance of a DBCP database pool. Then, in your servlets you would have
> >a static member of type DataSource which you would set with a JNDI
> >lookup for the Resource in the servlet init method. Then, anytime you
> >needed a connection you call dataSource.getConnection(). You still need
> >to close your connections however to return them to the pool. Your
> >servlet destroy method would need to close the dataSource, or at least
> >set it to null as it will leak being static otherwise on reloads of the
> >webapp.
> >
> >Cheers, Allistair
> >
> >-----Original Message-----
> >From: Martin Grogan [mailto:[EMAIL PROTECTED]
> >Sent: 29 November 2005 09:31
> >To: Jakarta Commons Users List
> >Subject: Re: [DBCP] Beginner how-to
> >
> >Yes, using Tomcat 5.0.28,
> >
> >
> >Allistair Crossley wrote:
> >
> >
> >
> >>Are you using Tomcat?
> >>
> >>-----Original Message-----
> >>From: Martin Grogan [mailto:[EMAIL PROTECTED]
> >>Sent: 29 November 2005 09:27
> >>To: Jakarta Commons Users List
> >>Subject: [DBCP] Beginner how-to
> >>
> >>Hi all,
> >>I'm a complete beginner with commons DBCP and unsure where to start. I
> >>require connection pooling to increase performance of my web
> >>application.
> >>All elements of my application are servlets (no EJB's, no JSP's) and
> >>for each servlet, I basically do the old-fashioned thing...
> >>1. Open connection to data source (mySQL) 2. Create statement 3.
> >>Execute a pile of queries 4. Close statement 5. Close connection I am
> >>using Tomcat 5.0.28 and mysql-connector-java-3.1.8. We are not using
> >>any dedicated web-server like Apache, just Tomcat.
> >>Can anyone tell my what I need to do to start using connection pooling
> >>in my applications? Maybe some sample servlet code, and server
> >>configuration, JAR's I need, etc.
> >>I'm basically an old ASP programmer that's picked up Java and now am
> >>trying to do things the Java way.
> >>Thanks,
> >>Martin
> >>
> >>
> >>-----------
> >>Martin Grogan
> >>Keizen Software
> >>
> >>[EMAIL PROTECTED]
> >>www.keizensoftware.com
> >>
> >>
> >>---------------------------------------------------------------------
> >>To unsubscribe, e-mail: [EMAIL PROTECTED]
> >>For additional commands, e-mail: [EMAIL PROTECTED]
> >>
> >>
> >>
> >>
> >>
> >><FONT SIZE=1 FACE="VERDANA,ARIAL" COLOR=BLUE>
> >>-------------------------------------------------------
> >>QAS Ltd.
> >>Registered in England: No 2582055
> >>Registered in Australia: No 082 851 474
> >>-------------------------------------------------------
> >></FONT> <FONT SIZE=1 FACE="VERDANA,ARIAL" COLOR=BLACK>
> >>Disclaimer: The information contained within this e-mail is
> >>
> >>
> >confidential and may be privileged. This email is intended solely for
> >the named recipient only; if you are not authorised you must not
> >disclose, copy, distribute, or retain this message or any part of it. If
> >you have received this message in error please contact the sender at
> >once so that we may take the appropriate action and avoid troubling you
> >further. Any views expressed in this message are those of the
> >individual sender. QAS Limited has the right lawfully to record,
> >monitor and inspect messages between its employees and any third party.
> >Your messages shall be subject to such lawful supervision as QAS Limited
> >deems to be necessary in order to protect its information, its interests
> >and its reputation.
> >
> >
> >>Whilst all efforts are made to safeguard Inbound and Outbound emails,
> >>
> >>
> >QAS Limited cannot guarantee that attachments are virus free or
> >compatible with your systems and does not accept any liability in
> >respect of viruses or computer problems experienced.
> >
> >
> >></FONT>
> >>
> >>
> >>---------------------------------------------------------------------
> >>To unsubscribe, e-mail: [EMAIL PROTECTED]
> >>For additional commands, e-mail: [EMAIL PROTECTED]
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >---------------------------------------------------------------------
> >To unsubscribe, e-mail: [EMAIL PROTECTED]
> >For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
> >
> >
> >
> ><FONT SIZE=1 FACE="VERDANA,ARIAL" COLOR=BLUE>
> >-------------------------------------------------------
> >QAS Ltd.
> >Registered in England: No 2582055
> >Registered in Australia: No 082 851 474
> >-------------------------------------------------------
> ></FONT> <FONT SIZE=1 FACE="VERDANA,ARIAL" COLOR=BLACK>
> >Disclaimer: The information contained within this e-mail is confidential
> >and may be privileged. This email is intended solely for the named recipient
> >only; if you are not authorised you must not disclose, copy, distribute, or
> >retain this message or any part of it. If you have received this message in
> >error please contact the sender at once so that we may take the appropriate
> >action and avoid troubling you further. Any views expressed in this message
> >are those of the individual sender. QAS Limited has the right lawfully to
> >record, monitor and inspect messages between its employees and any third
> >party. Your messages shall be subject to such lawful supervision as QAS
> >Limited deems to be necessary in order to protect its information, its
> >interests and its reputation.
> >
> >Whilst all efforts are made to safeguard Inbound and Outbound emails, QAS
> >Limited cannot guarantee that attachments are virus free or compatible with
> >your systems and does not accept any liability in respect of viruses or
> >computer problems experienced.
> ></FONT>
> >
> >
> >---------------------------------------------------------------------
> >To unsubscribe, e-mail: [EMAIL PROTECTED]
> >For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
> >
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
