I am new to Tomcat and Java SE 6 and need to know what I need to do to install, configure and use the Microsoft JDBC driver.
I have download the JDBC driver and extracted it to a directory on a windows 2008 system. I am going to install a couple of Java applications into tomcat 6 that need to talk to a SQL database.
Do I need to copy files from the JDBC directory to the tomcat directories or the java application directories installed in tomcat?
Do I need to configure any anything anything else?
As the links that Gagandeep provided will say, in order for your JVM to make a database connection, the JDBC driver jar(s) have to be on the classpath, and you'll need to provide your Java program with an appropriate connection string and credentials.
As far as what you need to do specifically with Tomcat, that depends quite a bit on your applications and how they retrieve a connection to the datab
Now there are a few options as far creating and providing this connection. With Tomcat and Java EE applications, your Java application could define a in its web.xml. Tomcat would be responsible for creating/maintaining the connection (pool) and providing it to the application as needed. In this case the driver would have to be on Tomcat's classpath, you'd need some configuration in your application's corresponding context.xml and possibly even the server.xml. Here's some Tomcat docs on this method topic
Alternatively, your application could be responsible itself for setting up and tearing down the database connections, in which case the driver jars probably belong in the WAR / part of the WAR's classpath. Then all that is needed is a method for getting the required connection information into the application (environment entries, property files, any number of other methods)...
Try Getting Started with JDBC Drivers
One more Link Getting Started with Microsoft JDBC Drivers
I had done some research now, regarding your asked question. What I found out is this. Once you had downloaded your drivers, simply add them to the classpath of your Operating System. Now how to put them to your Tomcat part, I will suggest you do the MVC Model Approach, where the request will come to your servlet and then your class which will make the connection with your Database will be just one normal class, and your Servlet class will call this class which makes Connections with your Database and will send the needed fields back to the Servlet class. I had tested the drivers with Microsoft SQL Server 2008 and the following code works with that :
import java.*;
public class Connect
{
private java.sql.Connection con = null;
private final String url = "jdbc:sqlserver://";
private final String serverName= "localhost";
private final String portNumber = "1433";
private final String databaseName= "yourDatabaseName";
private final String userName = "yourUserName";
private final String password = "yourPassword";
// Informs the driver to use server a side-cursor,
// which permits more than one active statement
// on a connection.
private final String selectMethod = "cursor";
// Constructor
public Connect(){}
private String getConnectionUrl()
{
return url+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod+";";
}
private java.sql.Connection getConnection()
{
try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
if(con!=null) System.out.println("Connection Successful!");
}
catch(Exception e)
{
e.printStackTrace();
System.out.println("Error Trace in getConnection() : " + e.getMessage());
}
return con;
}
/*
Display the driver properties, database details
*/
public void displayDbProperties()
{
java.sql.DatabaseMetaData dm = null;
java.sql.ResultSet rs = null;
try
{
con= this.getConnection();
if(con!=null)
{
dm = con.getMetaData();
System.out.println("Driver Information");
System.out.println("\tDriver Name: "+ dm.getDriverName());
System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
System.out.println("\nDatabase Information ");
System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
System.out.println("Avalilable Catalogs ");
rs = dm.getCatalogs();
while(rs.next())
{
System.out.println("\tcatalog: "+ rs.getString(1));
}
rs.close();
rs = null;
closeConnection();
}else System.out.println("Error: No active Connection");
}
catch(Exception e)
{
e.printStackTrace();
}
dm=null;
}
private void closeConnection()
{
try
{
if(con!=null)
con.close();
con=null;
}
catch(Exception e)
{
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception
{
Connect myDbTest = new Connect();
myDbTest.displayDbProperties();
}
}
This code has some slight changes then the one provided in the Link given by me. Do add the sqljdbc.jar to your classpath and provide your DatabaseName, UserName and Password and try to run this code. If it's a success, then use one MVC (Model View Controller) Pattern for your Web Application, in which the request comes to the servlet, servlet passes it to the normal Java class, making the connection to the database and accessing database, and then retrieve what you need back into the Servlet class.
