problem connecting to the database

[knowitnot]

i'm running tomcat5.0 mysql4.0

in my applications deployment descriptor i've got:

<servlet>
<servlet-name>ControllerServlet</servlet-name>
<servlet-class>ControllerServlet</servlet-class>

<!-- Define initial parameters that will be loaded into
the Servlet Context object in the controller servlet -->
<init-param>
<param-name>base</param-name>
<param-value>

http://localhost/foode/servlet/ControllerServlet</param-value>

</init-param>
<init-param>
<param-name>jdbcDriver</param-name>
<param-value>com.mysql.jdbc.Driver</param-value>
</init-param>
<init-param>
<param-name>imageUrl</param-name>
<param-value>

http://localhost/foode/images/</param-value>

</init-param>
<init-param>
<param-name>dbUrl</param-name>
<param-value>jdbc:mysql://localhost/Foode</param-value>
</init-param>
<init-param>
<param-name>dbUserName</param-name>
<param-value>****</param-value>
</init-param>
<init-param>
<param-name>dbPassword</param-name>
<param-value>******</param-value>
</init-param>
</servlet>

i have a bean called dbBean that contains all the methods used by the presentation pages and a servlet that receives and fowards requests to the jsp pages.

my default.jsp page contains a menu.jsp that retrieves the elements from the database. however, when i load the page, the elements do not appear. menu.jsp page compiles but i can't open the connection. this is a portion of the code from the bean:

public class DbBean {
public String dbUrl = "";
public String dbUserName = "";
public String dbPassword = "";

public void setDbUrl(String url){
dbUrl = url;
}

public void setDbUserName(String user){
dbUserName = user;
}

public void setDbPassword(String password){
dbPassword = password;
}

public Hashtable getCategories(){
Hashtable categories = new Hashtable();
try {
Connection conn = DriverManager.getConnection(dbUrl);
Statement s = conn.createStatement();
String sql = "Select CategoryId, Category From Categories" + " ";
ResultSet rs = s.executeQuery(sql);
while (rs.next()){
categories.put(rs.getString(1), rs.getString(2));
}
rs.close();
s.close();
conn.close();
}
catch (SQLException e){}
return categories;
}


and the following is the initializing part of the controller servlet:

/* Initialize global variables */
public void init (ServletConfig config) throws ServletException {

System.out.println("initializing controller servlet.");

ServletContext context = config.getServletContext();
context.setAttribute("base", config.getInitParameter("base"));
context.setAttribute("imageUrl", config.getInitParameter("imageUrl"));

//instantiating the DbBean
DbBean dbBean = new DbBean();
//initializing the DbBean's variables
dbBean.setDbUrl(config.getInitParameter("dbUrl"));
dbBean.setDbUserName(config.getInitParameter("dbUserName"));
dbBean.setDbPassword(config.getInitParameter("dbPassword"));

//put the bean in the servlet context
//the bean will be accessed from the jsp
context.setAttribute("dbBean", dbBean);

try {
//loading the database driver
Class.forName(config.getInitParameter("jdbcDriver"));
}
catch (ClassNotFoundException e) {
System.out.println(e.toString());
}
super.init(config);
}

any help would be appreciated.

 

[venur]

Hi,

I hope this is what you are looking for.

http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-examples-howto.html

Cheers
Venu

 

[knowitnot]

hi venur,

actually i'm trying to run this application without utilizing connection pooling even though this method is reccomended. i'm doing this for my own benefit; I just don't understand why it's not connecting... =( i suspect it has to do with the way mysql is reading the getConnection parameters. any thoughts?

 

[venur]

Hi,

Try one of these

in DbBean.getCategories() use this Constructor

DriverManager.getConnection(URL url, String userName, String password)

or

String dbURL = config.getInitParameter("dbUrl")+"?user="+ config.getInitParameter("dbUserName")+"password="+ config.getInitParameter("dbPassword");

dbBean.setDbUrl(dbURL);

Cheers
Venu

 

[venur]

Hi,

Try one of these

in DbBean.getCategories() use this Constructor

DriverManager.getConnection(URL url, String userName, String password)

or

String dbURL = config.getInitParameter("dbUrl")+"?user="+ config.getInitParameter("dbUserName")+"&password="+ config.getInitParameter("dbPassword");

dbBean.setDbUrl(dbURL);

Cheers
Venu

 

[sedj]

You are missing the port in your URL.
The connection syntax for mysql should be :

DriverManager.getConnection("jdbc:mysql://myserver:3306/myDBName", "myuser", "mypasswd");

However you implement that is up to to, but I would use conn pooling if I were you, its easy to do, and very worthwhile.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[knowitnot]

thanks for the replies venur and sedj,

i am aware of the available constructors and have attempted both the constructor you mention and the contructor:

getConnection (String url) referencing the servletcontext config param <parm-name>url<param-name>
<pram-value>jdbc:mysql://localhost:3306/myDBName?user=***&password=****>

but to no avail, i am still getting nowhere. the page is loading my header but i cannot make the connection to the database.

i'm attempting to find a solution using connection pooling but i am a little confused about how to create a method that returns the DataSource information. can the method be included in the ControllerServlet? How can I reference this in my jsp files?

 

[knowitnot]

excuse me if i'm confusing the situation more for you guys. i'm just beginning to familiarize myself with the model-2 apprach of web app design. from my reading, i'm beginning to wonder if i need to use an ejb. terminology aside i just want to make the db connection as resource efficient as possible.

 

[byam]

EJB is only necessary if your business logic require Distributed Transaction, i.e. A transaction happens across multiple server. Otherwise using EJB is over kill and down grade the over performance.