Creating a JDBC Application to query SQL SERVER

Posted by
To create JDBC application we need  a specific driver to effectively connect our application to databases. Four different type of drivers in JDBC as listed below:

  1. Type-1 driver (JDBC-ODBC Bridge Driver)
  2. Type-2 driver (Native-API Partly-Java Driver)
  3. Type-3 driver (JDBC-Net Pure-Java Driver)
  4. Type-4 driver (Native Protocol Pure-Java driver)
Here we will use Type-1 driver for creating the application.To solve the above problem, perform the following tasks:
  1. Create a Data Source Name (DSN).
  2. Code the application.
  3. Compile and execute the application.

1.Create a DSN:

               Follow these steps to make DSN for SQL SERVER:
  • You need to first open the Control Panel Window. Select the Start ->Settings -> Control Panel. It will open the Control Panel Window.
  • To access the options that enable you to configure the administrative settings, double click the Administrative Tools  icon.
  • To open the ODBC Data Souce Administrative dialog box, double click the Data sources (ODBC)  icon. The following figure shows the 
    ODBC Data Souce Administrative dialog box.

  • Select the System DSN tab.
  • To select a driver for creating a DSN,  from the list of drivers, click the Add button.
  • To connect to SQL Server 2005, select the SQL SERVER option from the Name list box in the Create New Data Source dialog box,as shown in the following figure:
  • To complete the task of selecting the driver for creating a DSN, click the Finish button.This will show a new dialog box Create a New Data Souce To SQL Server as following :
  • Type the name of the DSN whatever you want to give in Name text box like MyDSN.Give description in Desription text box.
  • In Server text box give the server name. It appears when you open SQL Server Management Studio Express in Connect to Server dialog box.
  • After filling all the required field click on Next button.It will give following dialog box:
  • Select the SQL Server authentication and type Login ID and password which you enter when open the SQL Server.Now Click on the Next button. After clickin next appers a dialog box as given below
  • Select default database from the drop down list box and click the Next button.In the next dialog box select tha language English and click on Finish button. The ODBC Microsoft SQL Server Setup dialog box appears with all the details about the DSN.
  • To test the created DSN for the connection with the database, click the Test Data Source button. The message regarding the connectivity test appear in the SQL Server ODBC Data Souce Test dialog box as shown in the following figure:
  • To return to the ODBC Microsoft SQL  Server Setup dialog box, click the OK  button.
  • To save the configuration settings for the new data source ,click the OK  button.
  • To verify the DSN is created, check the name of created DSN in the User Data Source list box of the ODBC Data Source Administrator dialog box and click the OK button.
1.Code the Application:
You need to write the code to perform the following tasks in the application:
  • Load the JDBC-ODBC bridge Driver.
  • Connect to the MyDSN DSN
  • Create and execute JDBC statements.
  • Display the result.
You can use the following code to create the application:
import java.sql.*;
public class SimpleJDBCApp
{
public static void main(String ar[])
{
try
{
//initialize and load the JDBC-ODBC Bridge driver
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
//Establish a connection with the Data Source
Connection con=DriverManager.getConnection(“jdbc:odbc:MyDSN”);
//Create a statment object to process the SELECT statement
Statement stmt=con.createStatement();
//Execute the SELECT SQL statement
ResultSet rs=stmt.executeQuery(“select name from employee where name like ‘j
%'”);
System.out.println(“Name”);
while(rs.next())
{
String name=rs.getString(“name”);
System.out.println(name);
}
con.close();
}
catch(Exception e)
{
System.out.println(“Error Occured”);
System.out.println(“Error:” + e);
}
}
}
3.Compile and Execute the Application:
The command to compile the SimpleJDBCApp application is:
           javac SimpleJDBCApp.java
The command to execute the SimpleJDBCApp application is:
          java SimpleJDBCApp
The following shows the output of the code that displays information from the employee table:

2 comments

Leave a Reply