how to deploy java web application with mysql database on tomcat server

There are two main ways to obtain JDBC connections within a Java Web Application.

1. Retrieve a connection from a DataSource registered in a JNDI directory service within the container.
2. Creating a Connection manually within your application code.

JNDI

Using JNDI requires a connection pool to be created within tomcat. This can be done within the context.xml file in tomcat's config directory.

Example Context.xml Entry

  <Resource name="jdbc/EmployeeDB"
            auth="Container"
            type="javax.sql.DataSource"
            username="dbusername"
            password="dbpassword"
            driverClassName="org.hsql.jdbcDriver"
            url="jdbc:HypersonicSQL:database"
            maxActive="8"
            maxIdle="4"/>

This connection would then be retrieved in your code as follows:

Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource)
  envCtx.lookup("jdbc/EmployeeDB");

Connection conn = ds.getConnection();
... use this connection to access the database ...
conn.close();

Manual Creation

Manually creating the connection within your code is simpler, however JNDI is recommended for its portability.

Manual Example

public class MysqlConnect{
   public static void main(String[] args) {

      Connection conn = null;
      String url = "jdbc:mysql://localhost:3306/";
      String dbName = "jdbctutorial";
      String driver = "com.mysql.jdbc.Driver";
      String userName = "root"; 
      String password = "root";

      try {
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          conn.close();
      } catch (Exception e) {
          e.printStackTrace();
      }
  }
}

When deploying for either of these scenarios it is important that you have the appropriate JDBC driver in your classpath.

There are 2 scenarios here:

1. You define the connection directly in the code using simple JDBC and Class.forName() - in that case you just need to make sure the jar containing the driver is in the classpath and it should work.

2. This is the preferred method - Define a Datasource on the server and call it in the code by using the JNDI API:

   InitialContext ic = new InitialContext();  
   DataSource ds = (DataSource)ic.lookup("jdbc/testDS");  
   conn = ds.getConnection();  
   

in contex.xml using -- tag we can connect to any db