Inserting Rows into SQL Server from a Java Program

By: Dallas Snider   |   Updated: 2017-03-06   |   Comments (1)   |   Related: > Application Development

Problem

I need to write a Java program to connect to a SQL Server database using Windows authentication and insert a row into a database table using the INSERT command. How can I accomplish this?

Solution

Java connects to SQL Server via the Microsoft JDBC Driver. In this tip we will download and install the JDBC driver, set the classpath to the driver, examine the code, and then execute the program from the Windows command prompt.

First, let's download and install the Microsoft JDBC 4 driver. Please remember the path and file name of the driver as it is needed in the next step. In this tip, the installation program was run with Administrator rights and the driver was installed in the directory C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server.

Next, we need to add the full path and file name of the Microsoft JDBC 4 driver to the CLASSPATH system environment variable. In this example, the path and file name is C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\sqljdbc4.jar.

• Open the Control Panel.
• Click on System.
• Click on Advanced System Settings.

Click on Environment Variables...

Look for a CLASSPATH variable in the System Variables box.

If a CLASSPATH variable exists, click on CLASSPATH and then Edit. In the Variable Value box, append a semicolon and the full path to the JDBC jar file to the existing CLASSPATH value. Make sure to include the file name. Click on OK to close the Edit System Variable window.

If CLASSPATH does not exist click on New.

In the Variable Value box enter the full path and file name to the JDBC jar file. Click on OK to close the New System Variable window.

Now that the CLASSPATH is pointing to the JDBC driver, click on OK to close the Environment Variable window.

Click on OK to close the System Properties window.

We will run the following T-SQL command in the Java program. We should test it first in SQL Server Management Studio.

Now we are ready to examine the Java code. There are comments throughout the code below. Some of the lines of code are long, so please feel free to copy and paste the code below into your favorite Java development environment.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
* Connect to SQL Server and execute an INSERT command.
*
*/  
public class InsertIntoSQLServer
{
  //The SQL Server JDBC Driver is in 
  //C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\auth\x64
  private static final String jdbcDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  
  //The JDBC connection URL which allows for Windows authentication is defined below.
  private static final String jdbcURL = "jdbc:sqlserver://localhost:1433;databasename=AdventureWorks2014;integratedSecurity=true;";
  //To make Windows authenticaion work we have to set the path to sqljdbc_auth.dll at the command line
  
  /**
  * main method.
  *
  * @param  args  command line arguments
  */  
  public static void main(String[] args)
  {
    System.out.println("Program started");
    try
    {
       Class.forName(jdbcDriver).newInstance();
       System.out.println("JDBC driver loaded");
    }
    catch (Exception err)
    {
       System.err.println("Error loading JDBC driver");
       err.printStackTrace(System.err);
       System.exit(0);
    }
    
    Connection databaseConnection= null;
    try
    {
      //Connect to the database
      databaseConnection = DriverManager.getConnection(jdbcURL);
      System.out.println("Connected to the database");
    }
    catch (SQLException err)
    {
       System.err.println("Error connecting to the database");
       err.printStackTrace(System.err);
       System.exit(0);
    }
    
    try
    {
      //declare the statement object
      Statement sqlStatement = databaseConnection.createStatement();
 
      //Build the command string
      String commandString="insert into [Sales].[CurrencyRate] values";
      commandString+="(getdate(), 'USD', 'EUR', 0.9420, 0.9420, getdate())";

      //print the command string to the screen
      System.out.println("\nCommand string:");
      System.out.println(commandString);
      
      //execute the command using the execute method
      sqlStatement.execute(commandString);
      
      System.out.println("Closing database connection");

      //close the database connection
      databaseConnection.close();
    }
    catch (SQLException err)
    {
       System.err.println("SQL Error");
       err.printStackTrace(System.err);
       System.exit(0);
    }
    System.out.println("Program finished");
  }
}

After compiling, it is time to execute and test our Java program. We have to include a path to the sqljdbc_auth.dll file which is required for Windows authentication. At the command line enter the following command.

C:\MSSQLTips\InsertRowFromJava>java -Djava.library.path="C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\auth\x64" InsertIntoSQLServer

The program output is shown below.

We can query the database again to see the newly inserted row, along with the test row we inserted earlier.

Next Steps

• Try to change this to use UPDATE and DELETE commands.
• Also, please check out other tips on MSSQLTips.com.

About the author

Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips