db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Stephan van Loendersloot (LIST)" <step...@republika.nl>
Subject Re: Tomcat derby JSP "java.sql.SQLException: No suitable driver"
Date Mon, 08 Dec 2008 19:35:58 GMT

Below is the forwarded message that Mike sent to my e-mail address. I 
wanted to reply on the list, so others may benefit from it as well:

<quote>

mikehmurray@gmail.com wrote:
 thanks for all the replys,
 Steven, at this point i am simply trying to set up an tomcat webserver, 
and run a derby database on it. i will then write .jsp webpages that 
will contain sql commands that will operate on the derby database.

 i have managed to set up the webserver to then extent which it runs jsp 
pages that i can access.
 i have managed to set up the derby database to the extent which runs 
commands from the command line.

 i cannot however, get code in .jsp files to connect to the derby database.

 thanks.

</quote>



This may be a long post and it's mostly Tomcat-related configuration, so 
please bear with me:

The directories you posted for configuration files seem to be totally 
mixed up, so I suggest you start with a fresh Tomcat 
(http://tomcat.apache.org/download-60.cgi).

You unpacked Tomcat in the directory 
home/applications/apache-tomcat-6.0.18, from now on I'll refer to this 
as CATALINA_HOME.

Please note! All given paths and filenames in the following section are 
case-sensitive!



The first step is to copy derbyclient.jar to CATALINA_HOME/lib

Leave CATALINA_HOME/conf/server.xml alone for now, you don't need it 
(for this example).

In the directory CATALINA_HOME/webapps there's a directory ROOT. This is 
the default website that comes with Tomcat out-of-the-box (e.g. 
http://localhost:8080).

Make sure Tomcat is NOT started and delete everything in this directory, 
but not the directory itself.

Create a directory: CATALINA_HOME/webapps/ROOT/META-INF
Create a file here: CATALINA_HOME/webapps/ROOT/META-INF/context.xml

The content of context.xml file may look like this:

<?xml version="1.0" encoding="UTF-8"?>
<Context reloadable="true">
    <Resource name="jdbc/db" auth="Container"
        type="javax.sql.DataSource"
        driverClassName="org.apache.derby.jdbc.ClientDriver"
        url="jdbc:derby://localhost:1527/db"
        username="DATABASEUSERNAME"
        password="DATABASEUSERPASSWORD"
        maxActive="10"
        maxIdle="5"
        removeAbandoned="true"
        removeAbandonedTimeout="60"
        logAbandoned="true"
        testOnBorrow="true"
        validationQuery="VALUES(1)"
    />
</Context>

This will give you connection-pooling and a lot of other configurable 
options from the start and you don't have to rewrite your application 
when you change these options or databases or... etc. etc.


Create another directory: CATALINA_HOME/webapps/ROOT/WEB-INF
Next, create the file CATALINA_HOME/webapps/ROOT/WEB-INF/web.xml

The minimal content of web.xml for this example:

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.5"
    xmlns="http://java.sun.com/xml/ns/javaee"
    xmlns:j2ee="http://java.sun.com/xml/ns/javaee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
http://java.sun.com/xml ns/javaee/web-app_2_5.xsd">
    <display-name>root</display-name>
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
        <welcome-file>index.html</welcome-file>
    </welcome-file-list>
    <resource-ref>
        <description>JNDI www.mydomain.com Connection</description>
        <res-ref-name>jdbc/db</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
        <res-sharing-scope>Shareable</res-sharing-scope>
    </resource-ref>
</web-app>


Finally, your index.jsp should be something like this:

<%@page language="java" import="java.sql.*, javax.sql.*, javax.naming.*" %>
<%
    Context ctx = new InitialContext();
    DataSource dataSource = (DataSource) 
ctx.lookup("java:comp/env/jdbc/db");
    if (dataSource == null) {
        throw new JspException(
            "java:comp/env/jdbc/db returned NULL");
    }

try {
    Connection conn = dataSource.getConnection();
    out.println("CONNECTED<br>");
    //body of code to go here

}  catch (Exception e)  {
   out.println(e);
   e.printStackTrace();
} finally {

}

%>


Now, start Derby, then start Tomcat.


This is tested, working code, but please drop us a line if doesn't work 
for you.


Regards,

    Stephan.





Mime
View raw message