Java SQL Server: Inserting Data

Summary: in this tutorial, you will learn how to insert data into an SQL Server table using a Java program.

This tutorial begins where the connecting to the SQL Server from the Java program tutorial left off.

Inserting data into a table from Java

Here are the steps for inserting data into a table in SQL Server from a Java program using JDBC:

First, open a new database connection to the SQL Server.
Next, create a new PreparedStatement object that accepts an INSERT statement.
Then, set the parameters for the statement by calling the set* methods of the PreparedStatement object.
After that, call the executeUpdate() method of the PreparedStatement to run the INSERT statement to insert a new row into a table.
Finally, close the prepared statement and database connection.

If you use the try-with-resources statement, you don’t need to close the prepared statement and database connection manually.

Inserting author

Step 1. Create a new file Author.java that stores the Author class:

import java.time.LocalDate;

public class Author {
    private String firstName;
    private String lastName;
    private LocalDate birthDate;

    public Author(String firstName, String lastName, LocalDate birthDate) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.birthDate = birthDate;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public LocalDate getBirthDate() {
        return birthDate;
    }

    public void setBirthDate(LocalDate birthDate) {
        this.birthDate = birthDate;
    }

    @Override
    public String toString() {
        return "Author{" +
                "firstName='" + firstName + '\'' +
                ", lastName='" + lastName + '\'' +
                ", birthDate=" + birthDate +
                '}';
    }
}
Code language: JavaScript (javascript)

Step 1. Create a new Java file AuthorDB.java and define the AuthorDB class:

public class AuthorDB {
}Code language: Java (java)

Step 2. Add the insert() method to the AuthorDB class:

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;

public class AuthorDB {
    private final Connection connection;

    public AuthorDB(Connection connection) {
        this.connection = connection;
    }

    public void insert(Author author) {
        if (author == null) {
            return;
        }

        var sql = "INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES (?,?,?)";
        try (var statement = connection.prepareStatement(sql)) {
            // Bind values to parameters
            statement.setString(1, author.getFirstName());
            statement.setString(2, author.getLastName());
            statement.setDate(3, Date.valueOf(author.getBirthDate()));

            // Execute the query
            statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}Code language: Java (java)

How it works.

First, import the Date, SQLException, and Connection classes from the java.sql package:

import java.sql.Date;
import java.sql.SQLException;
import java.sql.Connection;Code language: Java (java)

Second, add a connection property to the AuthorDB class:

private final Connection connection;Code language: Java (java)

Third, define a constructor that accepts a Connection object:

public AuthorDB(Connection connection) {
   this.connection = connection;
}Code language: Java (java)

Fourth, define the method insert() that accepts an Author object and inserts a new row with the data from the author object into the Authors table:

 public void insert(Author author) throws DBExceptionCode language: Java (java)

Fifth, return immediately if the author object is null:

if (author == null) {
   return;
}Code language: Java (java)

Sixth, construct an INSERT statement that inserts a new row into the Authors table:

var sql = "INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES (?,?,?)";Code language: Java (java)

In the query, the question marks (?) are placeholders for parameters. They will be substituted with actual value at runtime. This approach ensures the user input is safely passed to the SQL query, which helps prevent SQL injection attacks.

Seventh, create a PreparedStatement object with the INSERT statement:

try (var pstmt = connection.prepareStatement(sql)) {Code language: Java (java)

Eighth, bind actual values to the placeholders in the INSERT statement:

statement.setString(1, author.getFirstName());
statement.setString(2, author.getLastName());
statement.setDate(3, Date.valueOf(author.getBirthDate()));Code language: Java (java)

Ninth, call the executeUpdate() method of the PreparedStatement object to execute the INSERT statement and return number of rows inserted:

statement.executeUpdate();Code language: Java (java)

Finally, throw a new DBException if an error occurred during the insertion:

throw new DBException(e.getMessage());Code language: JavaScript (javascript)

Step 3. Modify the main() method of the Main class to use the insert() method of the AuthorDB class:

import java.sql.SQLException;
import java.time.LocalDate;

public class Main {
    public static void main(String[] args) {

        // Create a new Author object
        var author = new Author("John","Doe",LocalDate.of(1990, 12, 31));

        // Connect to the SQL Server
        try (var connection = SQLServerConnection.connect()) {
            // Create a new AuthorDB object
            var authorDB = new AuthorDB(connection);

            // Insert the author into the database
            authorDB.insert(author);
        } catch (SQLException | DBException e) {
            System.err.println(e.getMessage());
        }
    }
}Code language: Java (java)

How it works.

First, create a new Author object for insertion:

var author = new Author("John","Doe",LocalDate.of(1990, 12, 31));Code language: Java (java)

Second, call the connect() method of the SQLServerConnection class to connect to the SQL Server:

try (var connection = SQLServerConnection.connect())Code language: Java (java)

Third, create a new AuthorDB object:

 var authorDB = new AuthorDB(connection);Code language: Java (java)

Fourth, insert a new Author into the database by calling the insert() method of the AuthorDB object:

authorDB.insert(author);Code language: CSS (css)

Finally, catch the SQLException and DBException and display the error:

} catch (SQLException | DBException e) {
     System.err.println(e.getMessage());
}Code language: JavaScript (javascript)

Step 4. Run the Java program.

Verify the insert

Step 1. Open SQL Server Management Studio (SSMS) and connect to the SQL Server.

Step 2. Execute the following query to retrieve data from the Authors table:

SELECT * FROM Authors;Code language: SQL (Structured Query Language) (sql)

Output:

AuthorID | FirstName | LastName | BirthDate
---------+-----------+----------+------------
1        | John      | Doe      | 1990-12-31
(1 row)Code language: plaintext (plaintext)

Download the project source code

Summary

Call the executeUpdate() method of the PreparedStatement object to execute an INSERT statement to insert a row into a table.

Was this tutorial helpful?