{"id":4282,"date":"2024-07-26T16:00:21","date_gmt":"2024-07-26T09:00:21","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4282"},"modified":"2024-07-29T07:53:57","modified_gmt":"2024-07-29T00:53:57","slug":"java-sql-server-update","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/java-sql-server\/java-sql-server-update\/","title":{"rendered":"Java SQL Server: Updating Data"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to update data in a table in SQL Server from a Java program.<\/p>\n\n\n\n

This tutorial begins where the Querying Data from SQL Server using the LIKE Operator tutorial<\/a> left off.<\/p>\n\n\n\n

Updating data #<\/a><\/h2>\n\n\n\n

Step 1. Define a new method in the AuthorDB<\/code> class that updates the data of an Author<\/code> object:<\/p>\n\n\n

public<\/span> void<\/span> update<\/span>(Author author)<\/span> throws<\/span> DBException <\/span>{\n  if<\/span> (author == null<\/span>) {\n    return<\/span>;\n  }\n\n  var<\/span> sql = \"UPDATE Authors SET FirstName =?, LastName=?, BirthDate = ? WHERE AuthorID=?\"<\/span>;\n\n  try<\/span> (var<\/span> statement = connection.prepareStatement(sql)) {\n    \/\/ Bind values to parameters<\/span>\n    statement.setString(1<\/span>, author.getFirstName());\n    statement.setString(2<\/span>, author.getLastName());\n    statement.setDate(3<\/span>, Date.valueOf(author.getBirthDate()));\n    statement.setInt(4<\/span>, author.getAuthorId());\n\n    \/\/ Execute the update<\/span>\n    statement.executeUpdate();\n  } catch<\/span>(SQLException e) {\n    throw<\/span> new<\/span> DBException(e.getMessage());\n  }\n\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, define update()<\/code> method that accepts an Author<\/code> object and throws a DBException<\/code> if an error occurs during the update:<\/p>\n\n\n
public<\/span> void<\/span> update<\/span>(Author author)<\/span> throws<\/span> DBException <\/span>{<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, return immediately if the author object is null:<\/p>\n\n\n
if<\/span> (author == null<\/span>) {\n  return<\/span>;\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, construct an UPDATE<\/code> statement that updates author data based on author id:<\/p>\n\n\n
var<\/span> sql = \"UPDATE Authors SET FirstName =?, LastName=?, BirthDate = ? WHERE AuthorID=?\"<\/span>;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, create a prepared statement object:<\/p>\n\n\n
try<\/span> (var<\/span> statement = connection.prepareStatement(sql)) {<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, bind values to the statement:<\/p>\n\n\n
statement.setString(1<\/span>, author.getFirstName());\nstatement.setString(2<\/span>, author.getLastName());\nstatement.setDate(3<\/span>, Date.valueOf(author.getBirthDate()));\nstatement.setInt(4<\/span>, author.getAuthorId());<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, execute the UPDATE<\/code> statement by calling the executeUpdate()<\/code> method of the PreparedStatement<\/code> object:<\/p>\n\n\n
statement.executeUpdate();<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, throw a new DBException<\/code> if an error occurs during the update:<\/p>\n\n\n
} catch<\/span>(SQLException e) {\n  throw<\/span> new<\/span> DBException(e.getMessage());\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Modify the main()<\/code> method of the Main<\/code> class to find an author with id 1 and update his first name:<\/p>\n\n\n
import<\/span> java.sql.SQLException;\n\npublic<\/span> class<\/span> Main<\/span> <\/span>{\n    public<\/span> static<\/span> void<\/span> main<\/span>(String[] args)<\/span> <\/span>{\n        try<\/span> (var<\/span> connection = SQLServerConnection.connect()) {\n            var<\/span> authorDB = new<\/span> AuthorDB(connection);\n            \/\/ Find author with id 1<\/span>\n            var<\/span> author = authorDB.findById(1<\/span>);\n            \/\/ Change the first name<\/span>\n            author.setFirstName(\"Johnson\"<\/span>);\n            \/\/ Update the author<\/span>\n            authorDB.update(author);\n\n        } catch<\/span> (SQLException | DBException e) {\n            System.err.println(e.getMessage());\n        }\n    }\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, connect to the SQL Server:<\/p>\n\n\n
try<\/span> (var<\/span> connection = SQLServerConnection.connect()) {<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create an AuthorDB<\/code> object:<\/p>\n\n\n
var<\/span> authorDB = new<\/span> AuthorDB(connection);<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, find the author with id 1:<\/p>\n\n\n
var<\/span> author = authorDB.findById(1<\/span>);<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, change the first name to Johnson:<\/p>\n\n\n
author.setFirstName(\"Johnson\"<\/span>);<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, apply the change to the database by calling the update()<\/code> method of the AuthorDB<\/code> object:<\/p>\n\n\n
authorDB.update(author);<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, display an error if the author does not exist or the update fails:<\/p>\n\n\n
} catch<\/span>(SQLException | DBException e) {\n  System.err.println(e.getMessage());\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Launch the SQL Server Management Studio, connect to the SQL Server, and execute the following query to retrieve data of the author id 3:<\/p>\n\n\n
SELECT * FROM Authors\nWHERE AuthorId = 1<\/span>;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
AuthorID | FirstName | LastName | BirthDate\n---------+-----------+----------+-----------\n1<\/span>        | John      | Doe      | 1990<\/span>-12<\/span>-31<\/span>\n(1<\/span> row)<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Run the Java program to update the author’s first name:<\/p>\n\n\n\n
Step 5. Rerun the query to verify the update:<\/p>\n\n\n
SELECT * FROM Authors\nWHERE AuthorId = 1<\/span>;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
AuthorID | FirstName | LastName | BirthDate\n---------+-----------+----------+-----------\n1<\/span>        | Johnson   | Doe      | 1990<\/span>-12<\/span>-31<\/span>\n(1<\/span> row)<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
The output shows that the author’s first name was updated to Johnson.<\/p>\n\n\n\n
Download the project source code #<\/a><\/h2>\n\n\n\n
Download the project source code<\/a><\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Call the executeUpdate()<\/code> method of the PreparedStatement<\/code> object to execute an UPDATE<\/code> statement to update data in a table from a Java program.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t