Platinum Partner
sql,netbeans

NetBeans IDE 6.9: Java with Microsoft SQL 2005

As far as developers can choose (or have it chosen for them) their software, they've had various scenarios to work with. Mostly companies use C# with MSSQL or Java with Oracle. Today I will show a slightly mixed scenario where the developer uses Java as the programming language and Microsoft SQL 2005 as their database engine. I will provide the most popular ways of using MSSQL 2005, such as executing a query from Java code, using Stored Procedures, working with output parameters and so on. We will start from scratch!

Requirements

I expect you are familiar with SQL and the basics of Java. If you don't know what JDBC is, you can look at:

http://en.wikipedia.org/wiki/Java_Database_Connectivity

 1)Downloading JDBC Driver.

For this article we will use the freshest version of JDBC provided by Microsoft: Microsoft SQL Server JDBC Driver 3.0 (sqljdbc_3.0.1301.101_enu.exe ). You can download it from:

http://www.microsoft.com/downloads/details.aspx?FamilyID=%20a737000d-68d0-4531-b65d-da0f2a735707&displaylang=en

 2)Registering Driver and establishing connection.

After you download and extract all files from sqljdbc_3.0.1301.101_enu.exe you can start your NetBeans. Now select: Services tab and right click over Drivers  and select New Driver. In a new window click Add... and find your directory  \YourPath\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\ now select file sqljdbc4.jar and click Open . You should see automatically selected driver class: com.microsoft.sqlserver.jdbc.SQLServerDriver. Now click OK. You should see a window like this below:

Registering new driver

 

Establishing connection:

2.1) Via SQL Authentication Mode

If you want to connect with this mode you should now click Databases and select New connection. From the dropdown list with Driver Name select Microsoft SQL Server 2005. While you try to connect to your local server type as Host Name -> localhost ,Database -> yourDB, Username ->yourSqlUser, Password -> UserPassword and click OK. In Advanced tab select schema dbo (or if dbo is not visible select db_owner). 

 

 

2.2) Via Windows Authentication Mode

Here we need to do extra job. When you look in Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\auth there is dll file. We need to include this file into your Windows System Variable PATH. Click on your menu start and right click over my computer next select properties -> Advanced Settings -> System Variables -> select PATH variable and select edit. In end of your PATH paste: fullPath\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\auth\x86\ now click OK  and save the other windows. If you finished this job go again to the Services tab and select New Connection . Again select Microsoft SQL Server 2005 but now fill these fields with your values:

Host -> localhost, Database : yourDb, Additional Properties -> integratedSecurity=true;. If you are familiar with SQL Managment Studio or Visual Studio you will find similarity with connectionString. Your URL should look like:jdbc:sqlserver://localhost;databaseName=mgr_db_1;integratedSecurity=true.  In the Advanced tab select dbo schema.

 

3)Quering Database from Query Window

3.1)Selecting

Let now do "Hello world" query to test our connection and see some result:

 Quering asp.net membership provider table

 

 3.2)Executing stored procedures

Here I put a full example while declaring variables and showing the  GblLanguageId output parameter after executing SP. You can call each stored procedure similarly like this below.

DECLARE @LanguageName nvarchar(50),@LanguageDescription nvarchar(100),
@LanguageFlag nvarchar(256),@GblLanguageId int
SET @LanguageName = N'Polish'
SET @LanguageDescription = N'Choose Polish - wybierz język polski'
SET @LanguageFlag = 'http://www.eleonorandre.pl/intro/polish_flag.gif'
EXEC gblLanguage_InsertLanguage @LanguageName,@LanguageDescription,@LanguageFlag,@GblLanguageId OUTPUT

SELECT @GblLanguageId

 

 3.3) Creating Stored Procedure

 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[gblUserContact_InsertUserContact]
@UserId uniqueidentifier,
@Email varchar(150),
@Phone varchar(50),
@URL varchar(100),
@Description nvarchar(256),
@Address nvarchar(100),
@GblContactId int out
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ContactId int
-- executing one stored procedure from another one and retreive output parameter
EXEC gblContact_InsertContact @Email,@Phone,@URL,@Description,@Address,@ContactId OUTPUT

INSERT INTO gblUserContact VALUES(@UserId,@ContactId)
SET @GblContactId = SCOPE_IDENTITY()
END
GO

 

 

 4)Quering Database from Java code

In this section I will provide and explain code which connects to a Database and does things like CRUD paradigmat. For this purpose we will create a NetBeans project because many users have problems with Java classpath. The NetBeans IDE helps manage the classpath at runtime or build an application without any. knowledge about details of classpath or class loader.

4.1) Creating and configuring new application.

Open your IDE and select  File-> New Project -> Java Application. Name your application as you wish and click Finish. Now we need to add the JDBC library to your application. Right click over Libraries and select Add JAR/Folder. Find jdbcsql4.jar and click Open.  Your application should look like:

 Libraries needed to connect with MSSQL 2005

 4.2)Working with database in Java

So far so good, but now I will show you and explain a little JDBC architecture.  JDBC is an API (Advanced Programming Interface) which contains a huge set of Java Interfaces or Abstract Classes to unify work with any database.  When a developer wants to connect with a particular database he or she needs to get the JDBC Driver, which is an implementation of JDBC API. In ideal conditions, a developer only has to change the JDBC driver from Oracle to MSSQL (or any other database engine) and load the proper class.  They don't need to rewrite any code in the database layer, but in the real world there are a few diffrences in implementation of the JDBC API and develpers need to look around the JDBC Driver to rewrite the old DB layer.  That's theory.  Let practice.

As I mentioned first we need to download and load the proper driver name class. We can do it by calling Reflection API:

 

//Assuming SQLServerDriver is in our classpath
//otherwise ClassNotFoundException will be thrown.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

 Now we simply create a connection to database by using Connection class.

//Assuming dll file is in system PATH 
String connectionUrl = "jdbc:sqlserver://localhost:" +
"databaseName=AdventureWorks;integratedSecurity=true;";
Connection con = DriverManager.getConnection(connectionUrl);

 

 

String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
"databaseName=AdventureWorks;integratedSecurity=true;";
Connection con = null;//SqlConnection
Statement stmt = null;//SqlCommand
ResultSet rs = null;// IDataReader

try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(connectionUrl);
// Create and execute an SQL statement that returns some data.
String SQL = "SELECT TOP 10 * FROM Person.Contact";
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
// Iterate through the data in the result set and display it.
while (rs.next()) {
//Retrive 4 and 6 column value:
System.out.println(rs.getString(4) + " " + rs.getString(6));
}
}
catch (Exception ex){ex.printStackTrace();}
finally {
if (rs != null) try { rs.close(); } catch(Exception ex) {ex.printStackTrace();}
if (stmt != null) try { stmt.close(); } catch(Exception ex) {ex.printStackTrace();}
if (con != null) try { con.close(); } catch(Exception ex) {ex.printStackTrace();}
}

The code shown above will work with any jdbc driver because it only contains basic Interfaces from the JDBC API. We used AdventureWorks DB with Integrated Security mode.

 

In code above we can see there is a SQL statement declared in java code. It's not good solution to put SQL code inside Java classes because database engine need to compile and check correctness of SQL Query. In addtition sql engine is not able to create sql execution plan which can make our database operation faster. Despite all performance stuff your Java code would be really hard to maintain and difficult to read. Insted I prefer calling stored procedures from Java code.

 

 4.3)Working with Stored Procedures in Java Code

 Now we will create a test table with the most common data types and column with null:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable](
[TestId] [int] IDENTITY(1,1) NOT NULL,
[TestNvarchar] [nvarchar](256) NOT NULL,
[TestBit] [bit] NOT NULL,
[TestDecimal] [decimal](16, 6) NOT NULL,
[TestDateTime] [datetime] NOT NULL,
[TestNullValue] [nvarchar](50) NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[TestId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

 

 And create simple stored procedures:

--INSERT
USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TestTable_Insert]
@TestNvarchar nvarchar(256),@TestBit bit,
@TestDecimal decimal(16, 6),@TestDateTime datetime,
@TestNullValue nvarchar(50),@TestId int out
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO AdventureWorks.dbo.TestTable VALUES(@TestNvarchar,@TestBit,@TestDecimal,
@TestDateTime,@TestNullValue)
SET @TestId = SCOPE_IDENTITY()
END
GO
--UPDATE
CREATE PROCEDURE [dbo].[TestTable_Update]
@TestId int,@TestNvarchar nvarchar(256),
@TestBit bit,@TestDecimal decimal(16, 6),
@TestDateTime datetime,@TestNullValue nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
UPDATE AdventureWorks.dbo.TestTable SET TestNvarchar = @TestNvarchar,
TestBit = @TestBit,TestDecimal = @TestDecimal,TestDateTime = @TestDateTime,
TestNullValue = @TestNullValue WHERE TestId = @TestId
END
GO
--DELETE
CREATE PROCEDURE [dbo].[TestTable_Delete]
@TestId int
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM AdventureWorks.dbo.TestTable WHERE TestId = @TestId
END
GO
--GET
CREATE PROCEDURE [dbo].[TestTable_GetById]
@TestId int
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM AdventureWorks.dbo.TestTable WHERE TestId = @TestId
END
GO

 Let's do java coding for all stored procedures above:

String connectionUrl = "jdbc:sqlserver://localhost;databaseName=AdventureWorks;userName=test;password=test";
try {
// Establish the connection.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(connectionUrl);
//each "?" is something like parameter place holder
CallableStatement cs = con.prepareCall("{call TestTable_Insert (?,?,?,?,?,?)}");
//SQLServerCallableStatement SQLCallStmt = (SQLServerCallableStatement) cs;
cs.setNString("TestNvarchar", "NVARCHAR Parameter example, źółżą");
cs.setBoolean("TestBit", true);
cs.setBigDecimal("TestDecimal", BigDecimal.valueOf(22.123456));
cs.setTimestamp("TestDateTime",Timestamp.valueOf("2010-07-01 13:00:01"));
cs.setNString("TestNullValue", "not null");
//For having null in table:
//cs.setNString("TestNullValue", null); or:
//cs.setNull("TestNullValue", java.sql.Types.NULL);
//Getting SCOPE_IDENTITY() from Stored Procedure
cs.registerOutParameter("TestId", java.sql.Types.INTEGER);
cs.execute();
System.out.println("Last inserted Test Id: "+cs.getInt("TestId"));
}
catch (Exception ex){System.out.println(ex.toString());
ex.printStackTrace();}

 In code above we used CallableStatement interface (from JDBC API) which mean this code is independent of any driver we use. Java automatically detect & load driver by calling Class.forName("driverClass") and then switch CallableStatement by it's own impelemtation SQLServerCallableStatement from Microsoft JDBC Driver. So if you are developing try to use as far as possible JDBC Iterfaces. If you use SQLServerCallableStatement directly as it is shown in commented out line: SQLServerCallableStatement SQLCallStmt you won't be able to quickly replace driver without extra coding.

 So that the other 3 stored procedures are similar and look like this:

 

//Update
CallableStatement cs = con.prepareCall("{call TestTable_Update (?,?,?,?,?,?)}");
cs.setInt("TestId", 1);
cs.setNString("TestNvarchar", "NVARCHAR Update");
cs.setBoolean("TestBit", false);
cs.setBigDecimal("TestDecimal",BigDecimal.valueOf(12.123456));
cs.setTimestamp("TestDateTime",Timestamp.valueOf("2010-07-01 13:00:01"));
cs.setNString("TestNullValue", "After update");
cs.execute();
//Delete
CallableStatement cs = con.prepareCall("{call TestTable_Delete (?)}");
cs.setInt("TestId", 1);
cs.execute();
//Get by Id
CallableStatement cs = con.prepareCall("{call TestTable_GetById (?)}");
cs.setInt("TestId", 2);
ResultSet result = cs.executeQuery();
while (result.next()){
System.out.println("--------- \n"+"TestId: "+ result.getInt("TestId")+"\n"+"TestNvarchar: "+
result.getNString("TestNvarchar")+"\n"+"TestBit: "+ result.getBoolean("TestBit")
+"TestDecimal: "+result.getBigDecimal("TestDecimal")+"\n"
+"TestDateTime: "+result.getTimestamp("TestDateTime")+"\n"
+"TestNullValue: " + result.getNString("TestNullValue"));
}

 

5) Important resources

This article only shows the most common usage of use Microsoft SQL 2005 with the Java language. If you want more complete information you propably should check these links:

http://www.jdbc-tutorial.com/

http://www.java2s.com/Code/JavaAPI/java.sql/Catalogjava.sql.htm

http://msdn.microsoft.com/en-us/library/ms378749.aspx

Many examples (including XML DataType)are in help directory inside \Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\help\samples\

 

 

 

{{ tag }}, {{tag}},

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}
{{ parent.authors[0].realName || parent.author}}

{{ parent.authors[0].tagline || parent.tagline }}

{{ parent.views }} ViewsClicks
Tweet

{{parent.nComments}}