JAVA SQL SERVER 2005 ODBC / JDBC CONNECTION

 

This artice explains how to create a connection to a SQL Server 2005 database from a JAVA application.

Step 1:
Download the JDBC driver from the folloiwing Microsoft site:
http://www.microsoft.com/downloads/details.aspx?FamilyID=e22bc83b-32ff-4474-a44a-22b6ae2c4e17&displaylang=en

Microsoft installation instructions:

  1. Download sqljdbc_<version>_enu.exe to a temporary directory.
  2. Run sqljdbc_<version>_enu.exe.
  3. Enter an installation directory when prompted. We recommend that you unpack this zip file in %ProgramFiles% with the default directory: “Microsoft SQL Server 2005 JDBC Driver”.

 

Step 2:
Locate the file called sqljdbc4.jar within the installation directory for the JDBC driver. Currently the JAR file can be found in %Microsoft SQL Server 2005 JDBC Driver\sqljdbc_2.0\enu.
Copy this JAR file into the class path of your JAVA application.

If using Netbeans, copy sqljdbc4.jar to the project folder in windows explorer.
In Netbeans – right-click the project in the projects window and select properties.
Select the Libraries catagory.
On the Compile tab click Add JAR/Folder.
Select sqljdbc4.jar and click open.
Click OK.
sqljdbc4.jar is now in the class path of the current JAVA application.

 

Step 3: CREATE A CLASS IN YOUR CURRENT APPLICATION CALLED MyConnection
The myConnection class instantiates the connection the SQL Server using the JDBC.

Enter the code exactlyas it appears below omitting your information for the following:

USERNAME = your SQL account username
PASSWORD = your SQL account password
SERVERNAME = your SQL servername e.g. SQL-SERV-1. The IP address of the SQL server can be used instead.
DATABASENAME = the name of the database connecting to.

 

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

import java.sql.Connection;

import java.sql.DriverManager;

 

class MyConnection {

  public static Connection getConnection() throws Exception {

     

      Connection con = null;

      try{

      SQLServerDataSource ds = new SQLServerDataSource();

      ds.setUser(“USERNAME”);

      ds.setPassword(“PASSWORD”);

      ds.setServerName(“SERVERNAME”);

      // ds.setServerName(“xxx.xxx.x.xx”);

      ds.setPortNumber(1433);

      ds.setDatabaseName(“DATABASENAME”);

      con = ds.getConnection();

     

      }catch(Exception e){

          e.printStackTrace();

      }

     

      return con;

 

    }

} 

 

 

STEP 5: Quering the database using a class called JDBCTest

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

 

/**

 *

 * @author Rob.Bamforth

 */

public class JDBCTest {

 

    static Connection theConn;

   

    public JDBCTest(){

       

        try {       

                    theConn = MyConnection.getConnection();

                    ResultSet rs;

                    Statement stmt;

 

String sql =  “select top 100 title, forename, surname from DATA.DBO.CUSTOMERS”;

                    stmt = theConn.createStatement();               

                    rs = stmt.executeQuery(sql);

                    while (rs.next()) {                                                

                                               

                        System.out.println(

                            “Title: ” + rs.getString(1).trim() +

                            ” Forename: ” + rs.getString(2).trim() +

                            ” Surname: ” + rs.getString(3).trim()

                        );                                             

                       

                    }   

                } catch (SQLException ex) {

                    ex.printStackTrace();

                } catch (Exception ex) {

                    ex.printStackTrace();

                }

       

    }           

   

    public static void main(String args[]) {

        new JDBCTest();

    }

       

}

 

The above code selects the top 100 title, forename, surname from the database DATA.DBO.CUSTOMERS.

 

rs.getString(1).trim() returns eliment 1 in the select list.  

rs.getString(2).trim() returns eliment 2 in the select list.

rs.getString(3).trim() returns eliment 3 in the select list etc.

 

 

Advertisements

One thought on “JAVA SQL SERVER 2005 ODBC / JDBC CONNECTION

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s