File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes using datasource help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "using datasource help" Watch "using datasource help" New topic
Author

using datasource help

billo bailey
Ranch Hand

Joined: Dec 02, 2002
Posts: 50
Hi,
I configured a datasource and got it to work following the apache tutorial here http://tomcat.apache.org/tomcat-5.0-doc/jndi-datasource-examples-howto.html. For my application I guessed I needed to put this code in the init() method of my default servlet so it would be run before any requests come in.



I want to know if I need to reuse this code, with different query strings, anytime I want to do db access. I tried putting only the first 4 lines in init() thinking this would allow me to use conn from different classes to do database access, i.e. from a separate class doing SomeServlet.conn.createStatement() etc. This did not work, conn was null and I'm not sure why. Am I going about this the right way??

thanks


only users lose drugs
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30136
    
150

Billo,
Yes, you can reuse most of that code. Try to take it out of the servlet though. For example, you could write a helper class that gets a connection. If you use a disconnected result set, you could pass in the SQL string and turn that resultset. For a connected result set, the data would be gone when you close the connection. Also, note that you will want to close your Statement object to avoid a resource leak.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
billo bailey
Ranch Hand

Joined: Dec 02, 2002
Posts: 50
OK, so if I use RowSet rather than ResultSet to return values. I've been trying out some things but just can't get it working.

The start servlet:


DataAccessObject:



I'm getting a null pointer exception for this DataSource ds = new DataAccessObject().getTestDS();. Am I going about this the right way? Should I be doing db access differently, (well clearly) but generally ?

This is getting very frustrating now, I decided to use a datasource as I believed it was a better solution to a single connection but didn't think it would be this much trouble. Any more help greatly appreciated.
billo bailey
Ranch Hand

Joined: Dec 02, 2002
Posts: 50
OK all seems well now. Here's what I have. I use CachedRowSetImpl instead of ResultSet so to avoid connection problems, here's the article I read to help me with this http://www.onjava.com/pub/a/onjava/2004/06/23/cachedrowset.html.

DataAccessObject


Calling servlet, though as mentioned above I will take this out of the servlet...in the interests of MVC.

import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.ServletConfig;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;


import com.sun.rowset.CachedRowSetImpl;
import com.tbookerireland.www.model.DataAccessObject;


public class InitServlet extends javax.servlet.http.HttpServlet implements javax.servlet.Servlet {
String location, url, category;
DataAccessObject dao;
Vector wsil = new Vector();
List categoryList = new ArrayList();
int size = 0;

public InitServlet() {
super();
}

public void init(ServletConfig config) throws ServletException
{
super.init(config);
DataAccessObject.initDataSource();//init datasource
}

public void getWSILDetails() throws SQLException{
CachedRowSetImpl rs = DataAccessObject.connect("select * from wsildetails");
try {
while(rs.next()) {
category = rs.getString("category");
location = rs.getString("location");
url = rs.getString("url");
}
}
catch(SQLException sqle) {}
}

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
//this entry servlet reads the currently listed wsil locations
//to find services that can be used

//create a session
HttpSession session = request.getSession();

//if new session set wsil attribute
if(session.isNew()) {
try {
getWSILDetails();//do this at start of a session only
}catch(SQLException sqle) {}
session.setAttribute("categories", category);
session.setAttribute("url", url);
session.setAttribute("location", location);

}

RequestDispatcher view = request.getRequestDispatcher("/first.jsp");
view.forward(request, response);
}

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
}
}

first.jsp (very basic)


I hope this is adequate, are there any loose ends I should tie up? I'm using this in conjunction with tomcat so hopefully there will be no concurrency issues.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30136
    
150

Looks good to me.
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
I would strongly recommend programming to interfaces, so your connect method should return javax.sql.RowSet. Also, your getWSILDetails method should declare rs to be of type javax.sql.RowSet. Your code will need minimal changes should you later change implementation.


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
billo bailey
Ranch Hand

Joined: Dec 02, 2002
Posts: 50
OK I've done as you suggested and used RowSet as opposed to CachedRowSetImpl. thanks.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: using datasource help
 
Similar Threads
Closing The connection (Database)
DataSource Invalid
Configuring connection pooling in tomcat
Web application goes extremely slow with more than 5 users logged in
Database connectivity issue