Recently I was happened to setup a MsSql server and connect it to WSO2 G-Reg data-sources. Since it was my first experience with MsSQL server and as it really seemed like a simple walk-through could’ve been very useful for a noob just like I was, it just struck me to come up with write-up. This is nothing more than a step to step guide through setting up a MsSQL server and executing some simple SQL queries from a Java client.
Installing and setting up MsSQL server
First download and install MsSQL server.
When you are installing, make sure to install management tools for SQL server using the feature installation. The management tool will provide you the GUIs that needs to configure MsSQL server.
Once the installation wizard is completed, go to All apps > Microsoft SQL Server 2014* > SQL Server 2014* Management Studio.
*Based on the installation
Then select File > Connect Object Explorer
Select Desktop authentication as the authentication option and click connect. The Desktop authentication option should have prompted at the installation and if you have proceeded with the defaults at installation process it should be available at this stage. Then you will be prompted with the available objects at the root level.
To add a new user, click on security > new > login. Then provide a new login name and a new password. Make sure to select SQL server authentication for the new user since it’ll provide ability to login from, remote programs. Make sure you uncheck “Enforce password policy” and “Enforce password expiration”.
Then open the properties for the new user we just created and assign dbcreator privilege. (logins > root > properties > server roles)
Then select the current server (DESKTOP-A6480CQSQLEXPRESS) and select connect. Select SQL server authentication and provide the username and the password for the user we just created.
Right click on the Databases element under the new connection and click on new database.
Provide the database name as you wish and click OK. Then create a new table on the database as employees and add 2 columns as “name” and “age”. You can create tables simply as right click on the database and create new table. Don’t forget Ctrl+S once you change the schema. (As typical to any windows program) 😉
Example Java Client
Now lets try to write a simple Java class to connect and insert some rows to table, employees.
Create a new Java program on your favourite IDE (mine is IDEA) add the following code in the main class.
Add the correct* MsSQL JDBC driver to the project path to act as the bridge between the program and the MsSQL server. Checkout MsSQL JDBC driver download page here.
*As I encountered, MsSQL Server 2014 and Java 1.7 goes with sqljdbc41.jar
Run the Java client. If everything has gone well, it should output something like following.
PS : In case, it throws a connection refused exception change the MsSQL server port using MsSQL server Configuration Manager. JDBC driver search for the default port 1433 and it needs to be explicitly set in tcp/ip settings. Right click on “Protocols for SQLEXPRESS” and select Properties. Then make sure TCP/IP is enabled and Listen all is true. Also add 1433 as the port under “IP Addresses” tab.