how to connect core java with sql server

[Mustafa k]

I’m doing a bsc-it from Mumbai university and I’m in last year of my course. I’m making a project using core java as a front-end and sql server as a back-end. I know the core java very well but I’ve never made a project in that. I want to use sql as a backend, but I don’t know the connectivity of jdbc with sql. I want a code for connectivity . please tell me the procedure of connectivity, Please help me.

[!const]

If you are using core java as a front-end, then you need to use applet or awt for creating a form. For creating a connection you need to create a ODBC data source. Then in the Connection Information window select "MS SQL Server", ODBC method of access, the name of the DSN in Database name and user/password in the last two fields. Leave empty all other fields. If you want all step by step procedure then search on internet on this topic, there are many sites which will guide you better.

[Gunilla]

For creating connection you need to set classpath variable. The Microsoft SQL Server 2000 driver for JDBC .jar files must be listed in your CLASSPATH variable. The CLASSPATH variable is the search string that Java Virtual Machine (JVM) uses to locate the JDBC drivers on your computer. If the drivers are not listed in your CLASSPATH variable, you receive the following error message when you try to load the driver:
java.lang.ClassNotFoundException: com/microsoft/jdbc/sqlserver/SQLServerDriver
Set your system CLASSPATH variable to include the following entries:
• \Your installation path\Lib\Msbase.jar
• \Your installation path\Lib\Msutil.jar
• \Your installation path\Lib\Mssqlserver.jar
• This is an example of a configured CLASSPATH variable:
• CLASSPATH=.;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msbase.jar;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msutil.jar;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver.jar

After setting classpath variable you can create a connection easily.

[CodGuru]

First you have to use a code which make connection to the database.You must pass your database connection information in the form of a connection URL. This is a template URL for Microsoft SQL Server 2000 Driver for JDBC. Substitute the values for your database:
jdbc:microsoft:sqlserver://servername:1433
The following sample code demonstrates how to specify a connection URL:
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433", "userName", "password");
For a complete list of connection URL parameters, see the Microsoft SQL Server 2000 Driver for JDBC HTML Help, or see the Online Guide. See the "Connection String Properties"

[void]

You need to create ODBC datasource to connect a database, Then in the Connection Information window select "MS SQL Server", ODBC method of access, the name of the DSN in Database name and user/password in the last two fields. After connecting it check the connection, you can test your connection. The following sample code tries to connect to the database and displays the database name, the version, and the available catalogs. Replace the server properties with the values for your server:
import java.*;
public class Connect{
private java.sql.Connection con = null;
private final String url = "jdbc:microsoft:sqlserver://";
private final String serverName= "localhost";
private final String portNumber = "1433";
private final String databaseName= "pubs";
private final String userName = "user";
private final String password = "password";
// 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.jdbc.sqlserver.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();
}
}


If this code is successful, the output is similar to the following:

Connection Successful!
Driver Information
Driver Name: SQLServer
Driver Version: 2.2.0022

Database Information
Database Name: Microsoft SQL Server
Database Version: Microsoft SQL Server 2000 - 8.00.384 (Intel X86)
May 23 2001 00:02:52
Copyright (c) 1988-2000 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: )

Avalilable Catalogs
catalog: master
catalog: msdb
catalog: pubs
catalog: tempdb

[mesterio]

Following are the example of making connection to Microsoft sql server using jtds driver
import java.sql.*;

public class testConnection
{
public static void main(String[] args)
{
DB db = new DB();
db.dbConnect(
"jdbc:jtds:sqlserver://localhost:1433/tempdb","sa","");
}
}

class DB
{
public DB() {}

public voidn dbConnect(String db_connect_string,
String db_userid, String db_password)
{
try
{
Class.forName("net.sourceforge.jtds.jdbc.Driver");
Connection conn = DriverManager.getConnection(
db_connect_string, db_userid, db_password);
System.out.println("connected");

}
catch (Exception e)
{
e.printStackTrace();
}
}
};

The above code will create a connection between java and sql server.