How to connect to Microsoft SQL Server database using Eclipse

Though I prefer SQL Server Management Studio to access and work with Microsoft SQL Server database, Sometimes, it's better to connect MSSQL database directly from Eclipse. This will save a lot of time which is wasted on switching between two applications, Eclipse and SSMS. It will also keep your PC fast enough because less application means less overhead. Eclipse IDE allows you to connect to almost all the database you have heard e.g. Oracle, MySQL, PostgreSQL, DB2 etc. All the steps are pretty much same, so once you know how to connect SQL Server database from Eclipse, you can connect Oracle or MySQL by yourself. Since Eclipse connects to the database using JDBC, you need to deploy JDBC drivers in your classpath. This is done part of New Database Connection Profile, one of the steps, which we will see later. In this article, I'll show you step by step guide to connect to Microsoft SQL Server database using Eclipse.

For a Java developer good knowledge of Eclipse IDE, or whatever IDE they use e.g. Netbeans or IntelliJ, is a must. It's always worth to invest some time and money learning the Eclipse IDE itself, this will make you more productive in Eclipse IDE.

If you have downloaded Eclipse IDE and not sure how to start, read the Eclipse IDE (vogella) by Lars Vogel. This book assumes no prior knowledge and can be used by a new Java developer to learn Eclipse. It's an essential read if you want to become more productive Java developer. It covers more recent version of Eclipse IDE e.g. Eclipse 4.2 and Eclipse 4.3, so it's one of the updated books to learn Eclipse in the market.

Best book to learn Eclipse IDE

Just remember, you need Eclipse IDE for Java EE developers to access the database. It contains tools for database development e.g. database explorer. The Eclipse IDE for Java Developers doesn't contain those tool by default. FYI, I am using Eclipse Java EE IDE for Web Developers, Version: Kepler Service Release 2.

Alternatively, if you are primarily a Java EE developer then you can also take a look at Ram Kulkarni's Java EE Development with Eclipse book, which will help you to write code, debug, test, and troubleshoot Java EE 7 applications right from the Eclipse IDE.  One of the best Eclipse books for Java EE developers.

Best Eclipse book for Java EE Developers

Steps to connect Eclipse to Microsoft SQL Server database

1) Open Eclipse IDE and Select Database Perspective (Windows >> Open Perspective >> Other >> Database Development).
2) Create Connection Profile
3) Select the Database you want to connect
4) Put connection detail
6) Test Connection
7) Use SQL ScrapBook to write and execute SQL Query
8) View Execution Plan

Let's see step by step guide to connect to Microsoft SQL Server database from Eclipse itself.

1) Open Eclipse IDE and Select Database Perspective (Windows >> Open Perspective >> Other >> Database Development).

Step 1 - How to open Database Development Perspective in Eclipse

2) Create Connection Profile and chose the database you want to connect. I have chosen Microsoft SQL Server as shown below:

Step 2 - How to create Database connection profile in Eclipse

3) Specify the JDBC Driver details

Specify a Driver Template and Definition Name as per your chosen database in the previous screen. Here, we have selected Microsoft SQL Server 2008 JDBC Driver

Now go to the second tab, JAR List and specify the path of JDBC driver from your local computer. By default sqljdbc.jar is added there but since the path is not specified you will see something like "Unable to locate JAR/zip in file system as specified by the driver definition: sqljdbc.jar", as shown below:

To solve this problem, you just need to click on Add JAR/Zip option and specify the Path where you have downloaded the JDBC driver for Microsoft SQL Server 2008 Edition i.e. sqljdbc.jar or sqljdbc4.jar. Use sqljdbc4.jar if you are running on JDK 7 or using JDBC 4.0, otherwise, you will get following error:

java.lang.UnsupportedOperationException: Java Runtime Environment (JRE) version 1.7 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.
at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.createConnection(
at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(
at org.eclipse.datatools.enablement.msft.internal.sqlserver.connection.JDBCSQLServerConnectionFactory.createConnection(
at org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(
at org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(
at org.eclipse.datatools.connectivity.ui.PingJob.createTestConnection(

If you don't have JDBC driver for Microsoft SQL Server, then you can download from here.

4) Specify database connection detail e.g. host, port, username, password and database etc

You are done. Now you are connected to the SQL Server database from Eclipse itself. Just open an SQL file and choose the connection and write SQL queries to execute.

List of Database Supported by Eclipse

As I told you in the first paragraph that Eclipse supports several databases, here is a full list of the database which you can connect directly from Eclipse.

DB2 for Linux, UNIX, and Windows
DB2 for i5/OS
DB2 for z/OS
Flat File Data Source
Generic JDBC
SQL Server
Sybase ASA
Sybase ASE
Web Services Data Source
XML Data Source

That's all about how to connect Eclipse to Microsoft SQL Server database using JDBC. Yes, you read it right, Eclipse internally uses JDBC to connect to these various databases. That's why it needs the JDBC JAR files in the classpath. You can further  use the SQL ScrapBook to write and execute SQL Query and view Execution Plan. It provides both text and image based execution plan.

No comments :

Post a Comment