SImple Servlet with Issues with MySQL Database Connenctivity using JNDI

Dev Articles Community Forums Sponsor:

Ajax Application Generator Generate database and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!

April 25th, 2008, 10:16 PM

~Antonio

Registered User

Join Date: Apr 2008

Location: San Francisco, CA, USA

Posts: 2

Time spent in forums: 13 m 56 sec
Reputation Power: 0

Servlet with Issues writing to MySQL Database using JNDI

I'm hung on one servlet for my site, which compiles fine, and is accessed fine by the JSP, but doesn't do as I intended: write my blog entries to the MySQL database!

As mentioned in the title, I'm using JNDI for connection pooling, via META-INF/context.xml.
I'm also using JDBC Realm with a form, and that's working just fine, so I'm sure my issue isn't context.xml, as it seems to be overriding Tomcat's context file just fine, at least for authentication.

Below is the code from the servlet, to include the annotations:

Code:

package projectEgress.web.blog;

import java.io.*;
import java.text.*;
import java.sql.*;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

import javax.sql.*;

public final class blogInput {

/* bean properties */

private String blogHeader;
private String blogSubheader;
private String blogBody;
private String externalLinks;
Connection conn;
Statement stmt;
ResultSet result;

/* getters & setters */

public String getBlogHeader() {
return blogHeader;
}

public void setBlogHeader(String blogHeader) {
this.blogHeader = blogHeader;
}

public String getBlogSubheader() {
return blogSubheader;
}

public void setBlogSubheader(String blogSubheader) {
this.blogSubheader = blogSubheader;
}

public String getBlogBody() {
return blogBody;
}

public void setBlogBody(String blogBody) {
this.blogBody = blogBody;
}

public String getExternalLinks() {
return externalLinks;
}

public void setExternalLinks(String externalLinks) {
this.externalLinks = externalLinks;
}

/* like it says, a void which writes to the database */

public void writeToDatabase() {

/* create the query string to fill the table */

String query = "insert into blogEntry (blogHeader, blogSubheader, blogBody, externalLinks) values (\"" + this.blogHeader + "\", \"" + this.blogSubheader + "\", \"" + this.blogBody + "\", \"" + this.externalLinks + "\")";

try {

/*establish the datasource and make the connection */

Context ctxt = new InitialContext();
DataSource dsrc = (DataSource)ctxt.lookup("java:comp/env/jdbc/projectEgress");

conn = dsrc.getConnection();
stmt = conn.createStatement();

/* Add data to table 'blogEntry' in our database */

int result = stmt.executeUpdate(query);

/* close the connections */

stmt.close();
conn.close();

/* check for exceptions, ensure connections are closed */

} catch (SQLException sqlx) {
sqlx.printStackTrace();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlx) {}
}

if (conn != null) {
try {
conn.close();
} catch (SQLException sqlx) {}
}
}
}
}

Here are the settings I have in META-INF/context.xml (sans Realm stuff, which works):

Code:

<Context debug="1" reloadable="true">

<Resource name="jdbc/projectEgress"
auth="Container"
type="javax.sql.DataSource"
username="********"
password="********"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/projectEgress?autoReconnect=true"
validationQuery="select 1"
maxActive="15"
maxIdle="8"
removeAbandoned="true"
removeAbandonedTimeout="120" />

<WatchedResource>WEB-INF/web.xml</WatchedResource>
<WatchedResource>META-INF/context.xml</WatchedResource>

</Context>

And, the code I'm using in the JSP which calls the method (the form action is directed to this file's URL):

Code:

<jsp:useBean id="blogInput" class="projectEgress.web.blog.blogInput">
<jsp:setProperty name="blogInput" property="*" />
<jsp:scriptlet>blogInput.writeToDatabase();</jsp:scriptlet>
</jsp:useBean>

-YES, I know I'm using a scriptlet in a JSP... I really don't want to create a custom tag to call the method, at least not until I'm far along enough in the project to justify creating a library... let's make it all work, first! )

FINALLY, the form:

Code:

<form action="/projectEgress/area51/blogInput" method="post" id="adminForm" enctype="application/x-www-form-urlencoded">
<div>
<span class="inputheader">Blog Header</span><br/>
<input type="text" name="blogHeader" size="35" class="form" /><br/>
<span class="inputheader">Blog SubHeader</span><br/>
<input type="text" name="blogSubheader" size="45" class="form" /><br/>
<span class="inputheader">Blog Body</span><br/>
<textarea class="content" name="blogBody" rows="9" cols="60"></textarea><br/>
<span class="inputheader">External Links</span><br/>
<input type="text" name="externalLinks" size="45" class="form" /><br/>
<input type="submit" value="Post!" class="submit" />
</div>
</form>

As far as I can tell, it should work, and it doesn't throw any errors (in fact it shows the success message rather than the configured error page), but when I check the blogEntry table from the MySQL prompt, it responds with "Empty set".
I've double checked to ensure that the table columns are present in MySQL and all the naming conventions line up and they do, so I figure it's my servlet that's broken.

Advice? Ideas?
Thanks in advance.

April 26th, 2008, 03:22 PM

~Antonio

Registered User

Join Date: Apr 2008

Location: San Francisco, CA, USA

Posts: 2

Time spent in forums: 13 m 56 sec
Reputation Power: 0

Okay, I changed a few things in the servlet code.
For one, I'm trying a PreparedStatement in place of Statement. Don't ask me what made me think it would work any better, it just stores the sql query in cache, but I thought I'd just try something else.
One thing this is allowing me to do is make the connection and statement (now PreparedStatement pStmt) objects local variables. It wouldn't allow me to do so before without giving me compile errors.

Code:

package projectEgress.web.blog;

import java.io.*;
import java.text.*;
import java.sql.*;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

import javax.sql.*;

public final class blogInput {
    
     /* bean properties */
    
    private String blogHeader;
    private String blogSubheader;
    private String blogBody;
    private String externalLinks;
    
        /* getters & setters */
        
        public String getBlogHeader() {
            return blogHeader;
        }
        
        public void setBlogHeader(String blogHeader) {
            this.blogHeader = blogHeader;
        }
        
        public String getBlogSubheader() {
            return blogSubheader;
        }
        
        public void setBlogSubheader(String blogSubheader) {
            this.blogSubheader = blogSubheader;
        }
        
        public String getBlogBody() {
            return blogBody;
        }
        
        public void setBlogBody(String blogBody) {
            this.blogBody = blogBody;
        }
        
        public String getExternalLinks() {
            return externalLinks;
        }
        
        public void setExternalLinks(String externalLinks) {
            this.externalLinks = externalLinks;
        }
        
        /* like it says, a void which writes to the database */
        
        public synchronized void writeToDatabase() {
            
            Connection conn = null;
            PreparedStatement pStmt = null;
            
            /* create the query string to fill the table */
            
            String Query = "INSERT INTO blogEntry (blogHeader, blogSubheader, blogBody, externalLinks) VALUES (\"" + this.blogHeader + "\", \"" + this.blogSubheader + "\", \"" + this.blogBody + "\", \""  + this.externalLinks + "\")";
            
            try {
                
                /*establish the datasource and make the connection */
        
                Context ctxt =  new InitialContext();
                DataSource dsrc = (DataSource)ctxt.lookup("java:comp/env/jdbc/projectEgress");
        
                conn = dsrc.getConnection();
                pStmt = conn.prepareStatement(Query);
        
                /* Add data to table 'blogEntry' in our database */
            
                pStmt.executeUpdate();
                pStmt.clearParameters();
                
                /* close the connections */
                
                pStmt.close();
                conn.close();
        
                /* check for exceptions, ensure connections are closed */
        
                } catch (SQLException sqlx) {
                    sqlx.printStackTrace();
                } catch (Exception ex) {
                    ex.printStackTrace();
                } finally {
                    if (pStmt != null) {
                        try {
                            pStmt.close(); 
                    } catch (SQLException sqlx) {}
                    }
                     
                    if (conn != null) {
                        try {
                            conn.close();
                    } catch (SQLException sqlx) {}
                    } 
                }
        }
}

Someone out there has to have a thought on this.
Even if it's just something they think probably won't work, so long as it gives me another angle to see this from.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: