Inserting Rows into SQL Server from a Java Program


By:   |   Updated: 2017-03-06   |   Comments (1)   |   Related: More > 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.

Install the JDBC driver

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.
Advanced System Settings

Click on Environment Variables...

Environment Variables for System Properties

Look for a CLASSPATH variable in the System Variables box.

Edit Existing Classpath

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.

Edit Existing Classpath Value

If CLASSPATH does not exist click on New.

Classpath Is Not Defined

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.

New classpath entered

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

Classpath changes finished

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.

SSMS results

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.

Program execution

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

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


Last Updated: 2017-03-06


get scripts

next tip button



About the author
MSSQLTips author Dallas Snider 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.

View all my tips





Comments For This Article




Wednesday, August 29, 2018 - 3:14:00 AM - Tarak Back To Top (77331)

i want to extract data from .jsp web server(ALM) and dump it to sql db. any ideas.



download





Recommended Reading

Data Entry for SQL Server - building quick, efficient data input forms using InfoPath

Connecting a Java Program to SQL Server

How to Get Started with SQL Server and .NET

Querying SQL Server Tables from .NET

Working with SQL Server Stored Procedures and .NET








get free sql tips
agree to terms


Learn more about SQL Server tools