Java - how to connect mysql with jsp using eclipse

Asked By Anto Bilson on 11-Jan-12 04:08 AM

hi friends..

im new to mysql and jsp..
how connect mysql with jsp using eclipse IDE ...
now im trying to do login from database..
where i've to design and develop code..
pls kindly help me to do

thanks
kalpana aparnathi replied to Anto Bilson on 11-Jan-12 04:15 AM

A common practice is to configure this as a DataSource in the webserver in question. It will provide you connection pooling facilities which will greatly improve performance. Also a common practice is to externalize the raw settings in some configuration file which is been placed in the classpath.

It's unclear which webserver (servletcontainer/appserver) you're using, so I'll just give a Tomcat example. You need to configure the datasource as per the webserver-supplied JNDI documentation. In case of Tomcat it's here: JNDI Resources HOW-TO. You'll see that there are several ways. Easiest way is to create a /META-INF/context.xml in the webcontent of your dynamic web project (to be clear, the /META-INF is at the same level as the /WEB-INF of the webapp) and fill it with something like:


<?xml version="1.0" encoding="UTF-8"?>

<Context>
    <Resource
        name="jdbc/db" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
        url="jdbc:mysql://hostname.com/db"
        driverClassName="com.mysql.jdbc.Driver"
        username="user" password="pass"
    />
</Context>


This roughly means that the webapplication context should create a datasource with the JNDI name jdbc/db with a maximum of 100 active connections, a maximum of 30 idle connections and a maximum wait time of 10000 milliseconds before a connection should be returned from your application (actually: closed by your application, so your application has 10 seconds time between acquiring the connection and closing the connection). The remnant of the settings should be familiar and self-explaining enough to you; those are the JDBC settings.


Finally in your web project, edit the file /WEB-INF/web.xml to add the following entry:

<resource-env-ref>
    <resource-env-ref-name>jdbc/db</resource-env-ref-name>
    <resource-env-ref-type>javax.sql.DataSource</resource-env-ref-type>
</resource-env-ref>


This roughly means that the webapplication should use the datasource with the name jdbc/db.

Then change your connection manager to something like this:

private DataSource dataSource;

public Database(String jndiname) {
    try {
        dataSource = (DataSource) new InitialContext().lookup("java:comp/env/" + jndiname);
    } catch (NamingException e) {
        // Handle error that it's not configured in JNDI.
        throw new SomeRuntimeException(jndiname + " is missing in JNDI!", e);
    }
}

public Connection getConnection() {
    return dataSource.getConnection();
}

..and replace all DriverManager.getConnection() calls by new Database("jdbc/db") and use database.getConnection() to acquire the Connection. You can obtain the value jdbc/db from some config file (Properties file?).


That should be it. Just deploy your webapplication with the above changes and run it. Don't forget to place the database JDBC driver in the Tomcat/lib or to add its path to the shared.loader property of Tomcat/conf/catalina.properties, because the responsibility of loading the driver is now moved to Tomcat. For more hints and other basic JDBC/JNDI examples you may find this article useful as well.

Sri K replied to Anto Bilson on 11-Jan-12 05:12 AM
You need to learn JDBC first - http://download.oracle.com/javase/tutorial/jdbc/index.html - and then you need to add the appropriate Java MySQL driver to your application so that the JDBC connection creation string can locate it.
Anto Bilson replied to Sri K on 11-Jan-12 06:02 AM
 pls provide elaborate