Connect SQL Server with Java (JDBC)
I recently started working on Java 1.8 and SQL Server 2012, after working up with both the technologies I thought to combine both and which requires JDBC (Java Database Connectivity).
The idea of working with JDBC is quite simple with MySQL but certain complications started to appear when we are dealing with SQL Server. These problems start from the configuration of IP Address and port which are not enabled by default.
- Note:- This might not be the best way to have connectivity but it works fine for small in-house projects or minor java development.
Steps Required for JDBC are:-
- Load the JDBC Driver (Register Driver)
- Get a connection with SQL Server
- Create a statement
- Execute the query (statement)
- Close the connection with SQL Server
Download Microsoft JDBC Driver for connectivity click here.
Before registering the driver we need to add external *.jar files which will allow us to load the driver in our program. To do that simply create a new project, inside the project create a package, and inside a package create a class that will allow us to get the connection.
Right-click on the project and select properties. A properties window will show up, after that click on Libraries which might be selected by default. Select Classpath and click on Add External JARs. Over there select the jar file according to your java version which was previously downloaded.
The java version can be checked by typing the following command in CMD.
java -version
Java Version “1.8.xx” signifies Java 8. At the time of writing this article file name, “mssql-jdbc-8.4.1.jre8.jar” is required to be added as an external file.
Now we have added the required driver which will be used in the Java code for the connectivity with the SQL Server.
Installing SQL Server via default configuration does not enable a couple of features that are required for the connectivity. These configurations are not part of JDBC but rather part of configuring SQL Server for the connectivity.
To configure SQL Server for connectivity open “SQL Server Configuration Manager”.
After opening SQL Server Configuration, need to select “SQL Server Network Configuration” and click on the “Protocols for SQLEXPRESS” window like below will be visible.
Enable all the Available Protocols, open properties of TCP/IP, and select tab IP Addresses.
At tab, “IP Addresses” enable IP address to yes and scroll down to select “IPAll” and set “TCP Dynamic Ports” to 0 and TCP Port to 1433 (or any other port you want to).
After completing the configuration, restarting the SQL Server is essential part soo changes can take place. Select “SQL Server Services” and right-click on SQL Server (SQLEXPRESS). After that click on Restart, it will take some time to restart.
After completing the configuration open “Microsoft SQL Server Management Studio” and select Windows Authentication. Click on Connect.
After opening SQL Server Management Studio, right-click on the connection in Object Explorer as shown in the image below and select properties.
After opening the Properties tab select “Security” from the “Select a page” window under security default selection of “Server authentication” mode is set as “Windows Authentication mode” change it to SQL Server and Windows Authentication mode.
After allowing Login selection we need to create a new user which will allow us to access SQL Server via username and password. Open Security in “Object Explorer” and right-click on “Logins”. After right-click select New Login…
After clicking on “New Login” a new window will be opened, in that window select SQL Server Authentication. Add “Login name” and “Password” as shown in the image below.
Select “User Mapping” to allow the user to have access to the required database.
Select “Server Roles” to allow the user to have complete access to the database and the user can edit, delete, and modify the database.
After Successfully Completing these steps we can write Java Code to connect with the SQL Server. Open class using which you need to connect with the database.
Note:- is you want to check on which port is SQL Server running it is exactly the same as last we set to it. But we can also run the following query to check the port number.
EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'
SQL Server is running at port number 1433, using this information we can start a connection with the SQL Server.
In the Java Class, we can write the following command to connect with SQL Server. The name of my database is lib in the following code.
This is not the best solution to configure servers but only for a personal testing project developing on localhost for personal development. A lot of security concerns have been ignored to make it as simple as possible.
