Tuesday, August 3, 2021

How to connect to Microsoft SQL Server database using Eclipse - Example Tutorial

Though I prefer SQL Server Management Studio to access and work with the Microsoft SQL Server database, Sometimes, it's better to connect the 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 databases you have heard e.g. Oracle, MySQL, PostgreSQL, DB2, etc. 

All the steps are pretty much the same, so once you know how to connect the 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 as part of the 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 the 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 investing some time and money learning the Eclipse IDE itself, this will make you more productive in Eclipse IDE.

By the way, if you are new to Microsoft SQL Server and T-SQL then I also suggest you join a comprehensive course to learn SQL Server fundamentals and how to work with T-SQL. If you need a recommendation then I suggest you go through these Microsoft SQL Server online courses. It's a great list to start with T-SQL and SQL queries in SQL Server.





If you have downloaded Eclipse IDE and are 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 a more productive Java developer. It covers a more recent version of Eclipse IDE like Eclipse 4.2 and Eclipse 4.3, so it's one of the updated books to learn Eclipse in the market.

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 tools 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 these Java EE courses and books 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
5) Add JDBC JAR
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 connecting to the 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 the 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 com.microsoft.sqlserver.jdbc.SQLServerConnection.(SQLServerConnection.java:304)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1011)
at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.createConnection(JDBCConnection.java:328)
at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBase.java:105)
at org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:54)
at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.open(JDBCConnection.java:96)
at org.eclipse.datatools.enablement.msft.internal.sqlserver.connection.JDBCSQLServerConnectionFactory.createConnection(JDBCSQLServerConnectionFactory.java:27)
at org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(ConnectionFactoryProvider.java:83)
at org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(ConnectionProfile.java:359)
at org.eclipse.datatools.connectivity.ui.PingJob.createTestConnection(PingJob.java:76)
at org.eclipse.datatools.connectivity.ui.PingJob.run(PingJob.java:59)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:53)


If you don't have a JDBC driver for Microsoft SQL Server, then you can download it 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
Derby
Flat File Data Source
Generic JDBC
HSQLDB
Informix
Ingres
MaxDB
MySQL
Oracle
PostgreSQL
SQL Server
SQLite
Sybase ASA
Sybase ASE
Web Services Data Source
XML Data Source


That's all about how to connect Eclipse to the 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 plans.

5 comments:

  1. Hi, what would be the suitable method if I'm using SQL Server 2014 and JDK 8 in Eclipse? Thank you!

    ReplyDelete
  2. Hello Anonymous, you can follow the instructions given here to connect SQL Server 2014 into Eclipse. Are you facing any issue?

    ReplyDelete
  3. Getting the following exception.

    com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "SQL Server did not return a response. The connection has been closed.".

    ReplyDelete
  4. Hi, what would be the suitable method if I'm using ORACLE SQL Server with JDK 7

    ReplyDelete
  5. Do you mean using Oracle SQL Developer tool or connecting to Oracle database from Java code? For later case, I have shared tutorial to connect Oracle database from Java, you can refer that one.

    ReplyDelete