การเชื่อมต่อ JSP sqlServer ด้วย JDBC บน netbeans (1/3) Part I: Create a connection

Introduction

This tutorial show you how to use NetBeans to connect SQL Server (2000 and 2005) by using Microsoft SQL Server JDBC Driver.

I’ll divide into 3 parts:

1. Part I : Create a connection
   This part which you’re reading shows about how to establish a connection between NetBeans and SQL Server. In this example, I use SQL Server 2000 SP4 and NetBeans IDE 5.5
2. Part II : Perform SQL Operations
   This part show how to perform some basic operations from NetBeans with SQL Server. For instance, send querys as SELECT, INSERT, UPDATE to the database.
3. Part III: Troubleshooting
   The last part is about problems and how to fix them.

Requirements

1. Microsoft SQL Server JDBC Driver
   To get latest version, visit Microsoft SQL Server 2005 JDBC Driver.
2. NetBeans with JRE (Java Runtime Environment) version 1.4 or later
   You can find at NetBeans.org.

Step-by-Step Guides

1. Installation
   • Install NetBeans.
   • Download Microsoft SQL Server 2005 JDBC Driver, name ‘sqljdbc_1.1.1501.101_enu.exe’. (The file name may differs depends on the version if you’ve downloaded from the Official Site.)
   • Double-click sqljdbc_1.1.1501.101_enu.exe to extract it.
     
   • Then, you’ll see the folder that you’ve just extracted ( Microsoft SQL Server 2005 JDBC Driversqljdbc_1.1enu ), the file ‘sqljdbc.jar’ which is the library that will be added to the library in NetBeans later.
     
   • You can find more informations about JDBC Driver at Microsoft SQL Server 2005 JDBC Driversqljdbc_1.1enuhelpdefault.htm.
2. Add JDBC Driver to the project on NetBeans. (Add a library)
   I will show by create New Java Application Project called TestSQL and add ‘sqljdbc.jar’ that just get from previous step to the project’s library.
   1. Create New Project called TestSQL.
      
   2. In Projects window, right click the project name and select Properties.
      
   3. Project Properties window appears. The Categories on left side, select Libraries. And on right side in Compile tab, click Add JAR/Folder.
      
   4. New Window appears, browse to the file ‘sqljdbc.jar’ and click Open.
      
   5. You’ll see the .jar file was added to the project. Click OK to finish.
      
      Note: You should keep sqljdbc.jar in the directory that you won’t delete it (ex. not in temp folder). May be in the same directory that keep common library files. If you delete the file without delete a link from the project, the project will show error about missing library.
3. Connect to the database

Now it’s the coding time. I going to show how to connect to SQL Server. Assume that I have SQL Server 2000 running on local machine. Let continue from the project just created in previous step, in main.java.

1. I’m going to use Connection and DriverMapper Classes so I need to import libraries.
   import java.sql.*;
   
2. Now I will connect to my SQL Server on local machine, the Northwind database(a sample database in SQL Server 2000). In main method, add the following code.

   try {
           Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
           String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
               "databaseName=Northwind;user=sa;password=123456;";
           Connection con = DriverManager.getConnection(connectionUrl);
           } catch (SQLException e) {
               System.out.println("SQL Exception: "+ e.toString());
           } catch (ClassNotFoundException cE) {
               System.out.println("Class Not Found Exception: "+ cE.toString());
           }

   The code explanation:

   • Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”); means load the SQL Server driver.
   • localhost:1433 refers to connect to SQL Server at localhost port 1433 (default port).
   • databaseName=Northwind refers to the database name you want to connect to.
   • user and password refer Username and Password that used to connect to the SQL Server.

   

3. Compile and run the project. If no error occurs, it means that the connection has established successfully.

That’s it for part I. Now you know how to connect to SQL Server on NetBeans, the part II will coming soon.