How to Update Record in CockroachDB using Java JDBC driver in Eclipse IDE

Written by orkb
July 05, 2019
  • CockroachDB
  • Eclipse IDE
  • Maven

Introduction

This tutorial will demonstrate how to go about updating records using Java JDBC driver in CockroachDB via Maven in Eclipse IDE. Examples of how to update records, create the Java project structure, how to connect the Java JDBC driver to the CockroachDB database, insert a single record in CockroachDB and how to insert a record via shell are provided. CockroachDB, the Java JDBC driver, Maven and Eclipse IDE must all be properly installed and configured in order to preform the functions in this tutorial. Instructions for installing and configuring each program are provided. Sample datasets are also provided.

Prerequisites

Eclipse IDE and Maven must be properly installed and configured before beginning.

Confirm that CockroachDB is properly installed and configured before beginning. * Consult the following link to learn how to install CockroachDB: How to Install CockroachDB on Mac OSX

Confirm that Java version <= 9 is properly installed.

The Dataset

The following dataset belongs to the table tblresto within the restodatabase.

Consult How to Perform Insert in CockroachDB for assistance with creating database and tables in CockroachDB.

id | name | phone | email | stars | category
+----+------------------+--------------+------------------------+-------+----------+
1 | pure coffee | 847-585-0171 | purebeauty@example.net | 5 | Coffee
2 | yumster delicacy | 225-456-0102 | yumsterD@example.com | 3 | Italian

How to Create the Java Project Structure

The project explorer should display a file tree with data that resembles the following after configuring MAVEN to an Eclipse project:

Maven downloaded the needed JAR Files as indicated in the pom.xml file

Create an additional java class in the src/main/java as follows: 1. DBConnection.java 2. Main.java 3. UserCrud.java

The maven-proj project structure should now resemble the following:

Added new java class in maven-proj directory

How to Connect the Java JDBC Driver to CockroachDB Database

In order to perform the insert command in CockroachDB the connection between the java jdbc driver and CockroachDB must be configured. Execute the following code in the DBConnection.java Class:

private static Connection con;
private static DBConnection dbInstance;

// Making the constructor private, prevents any other class
// from instantiating
private DBConnection() {}

// ClassicSingleton also known as the lazy instantiation to create the singleton.
// The getInstance() method must be called prior to the createion
// of the singleton instance.
public static DBConnection getInstance() {
if (dbInstance == null) {
dbInstance = new DBConnection();
}
return dbInstance;
}
public Connection getConnection(){
if (con == null) {


// Connect to the "restaurants" database.
Properties prp = new Properties();
prp.setProperty("user", "yeshua");
prp.setProperty("sslmode", "disable");
try {



this.con = DriverManager
.getConnection("jdbc:postgresql://127.0.0.1:26257/restaurants", prp);
}catch (Exception ex){
ex.printStackTrace();
}

}
return con;
}

The above connection utilizes singleton class that controls access to the resources, being the database in the above example.

Updating a Single Record in CockroachDB

The following section demonstrates how to update a single record in the CockroachDB database.

Update Record via Shell

Now that Eclipse IDE has been properly configured with Maven and the database is ready, the updates to the data on the existing records can now be executed.

CockroachDB uses the following sql format to update record via Shell:

UPDATE <TABLE name>
SET Column1 = Value1
WHERE condition;

Let’s follow the above format to perform an update within the CockroachDB data.

UPDATE tblrestaurants SET star s= 5 WHERE ID = 1;

The above SQL will update a column named “stars” from table “tblrestaurants” and change its value to 1.

The result should resemble something like the following:

  id |       name       |    phone     |         email          | stars | category  
+----+------------------+--------------+------------------------+-------+----------+
   1 | pure coffee      | 847-585-0171 | purebeauty@example.net |     1 | Coffee    
   2 | yumster delicacy | 225-456-0102 | yumsterD@example.com   |     3 | Italian

NOTE: Its the WHERE clause that specifies which data should be updated, without this it will update all the data within the stars column.

Updating Single Record using Java in Eclipse IDE.

Open the userCrud.java then create something like the following code:

// Instantiate database  connection
static Connection con = DBConnection.getInstance().getConnection();


// update tblrestaurants function
public static void updateResto() throws SQLException{
           
    try {
    // The question mark (?) is the placeholder for the value of the id
    String sqlUpdate = "UPDATE tblrestaurants SET stars = ? WHERE id = 1";
   
        PreparedStatement pstmt = con.prepareStatement(sqlUpdate);
       
        // The setter method is composed of key-value pair
        // the first value (1) is the pointer to the question mark's position,
        // should there be another question mark this will increment accordingly.
        // The next value is for the value of the id and this is a type integer thus, the .setInt setter was used.
       
        pstmt.setInt(1, 1);    
       

        // API method of the Statement executes the query
        ps.executeUpdate();
       
        // This handles the commit transaction of the application
        con.commit();

    } catch (Exception e) {
        System.out.println(e.getMessage());
        e.printStackTrace();
    }
}

The result from the above code should look something like the following:

  id |       name       |    phone     |           email           | stars | category  
+----+------------------+--------------+---------------------------+-------+----------+
   1 | pure coffee      | 847-585-0171 | purebeauty@example.net    |     1 | Coffee    
   2 | yumster delicacy | 225-456-0102 | yumsterD@example.com      |     3 | Italian

Update Multiple Record in CockroachDB

The following section will show ways on how to update a multiple record in CockroachDB database.

Updating Multiple Record via Shell

CockroachDB uses the following sql format to insert multiple record via Shell:

UPDATE <TABLE name>
SET Column1 = Value1;

To use the above format, use the following code:

UPDATE tblrestaurants SET stars = 5;

Updating Multiple Record using Java in Eclipse IDE.

Below java code will update multiple record into the CockroachDB database.

static Connection con = DBConnection.getInstance().getConnection();
   
   
// update tblrestaurants function
public static void updateResto() throws SQLException{

       
try {
   
    String sqlUpdate = "UPDATE tblrestaurants SET stars = ? ";
   
        PreparedStatement pstmt = con.prepareStatement(sqlUpdate);
       
        pstmt.setInt(1, 5);    
       
        con.commit();

    } catch (Exception e) {
        System.out.println(e.getMessage());
        e.printStackTrace();
    }

The result of the above code when verified via Shell using this command: “SELECT * FROM tblrestaurants;”

  id |          name           |    phone     |           email           | stars | category  
+----+-------------------------+--------------+---------------------------+-------+----------+
   1 | pure coffee             | 847-585-0171 | purebeauty@example.net    |     5 | Coffee    
   2 | yumster delicacy        | 225-456-0102 | yumsterD@example.com      |     5 | Italian

Execute the java code

To execute the codes we discussed in the previous section, use the Main.java class to make a function call to the update command.

import java.sql.SQLException;

public class Main {
   
    public static void main(String[] args) throws ClassNotFoundException, SQLException{
       
        UserCrud.updateResto();
    }
}

The Java Code

Below is the entirety of the code for this article.

The DBConnection.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class DBConnection {
   
    private static Connection con;
    private static DBConnection dbInstance;
   
    private DBConnection() {
    }
   
    public static DBConnection getInstance() {
        if (dbInstance == null) {
            dbInstance = new DBConnection();
        }
        return dbInstance;
    }
    public Connection getConnection(){
        if (con == null) {
       

        Properties prp = new Properties();
        prp.setProperty("user", "yeshua");
        prp.setProperty("sslmode", "disable");
       
        try {

        this.con = DriverManager
            .getConnection("jdbc:postgresql://127.0.0.1:26257/restaurants", prp);
        con.setAutoCommit(false);
        }catch (Exception ex){
            ex.printStackTrace();
        }
       
        }
        return con;
    }
}

The UserCrud.java

public class UserCrud {
    static Connection con = DBConnection.getInstance().getConnection();
   
   
// update tblrestaurants function
public static void updateResto() throws SQLException{

    // ====    Code for the update Multiple record =======
    // Comment the whole block of code if you wish to use the
    // Single update method
    try {
   
    String sqlUpdate = "UPDATE tblrestaurants SET stars = ? ";
   
        PreparedStatement pstmt = con.prepareStatement(sqlUpdate);
       
        pstmt.setInt(1, 5);    
       
        con.commit();

    } catch (Exception e) {
        System.out.println(e.getMessage());
        e.printStackTrace();
    }
    // ==== END FOR MULTIPLE UPDATE ===============


    // ====    Code for the update single record =======
    // Comment the whole block of code if you wish to use the
    // Multiple update method
    try{
    String sqlUpdate = "UPDATE tblrestaurants SET stars = ? WHERE id = 1 ";

    PreparedStatement pstmt = con.prepareStatement(sqlUpdate);
   
    pstmt.setInt(1, 5);    
   
    con.commit();

    } catch (Exception e) {
        System.out.println(e.getMessage());
        e.printStackTrace();
    }
    // ========= END for SINGLE UPDATE RECORD ===================

    }
   
}

The Main.java

import java.sql.SQLException;

public class Main {
   
    public static void main(String[] args) throws ClassNotFoundException, SQLException{
       
        UserCrud.updateResto();
    }
}

Conclusion

This tutorial demonstrated how to go about updating records using Java JDBC driver in CockroachDB via Maven in Eclipse IDE. The article provided instructions on how to create the Java project structure, how to connect the Java JDBC driver to CockroachDB database, insert a single record in CockroachDB and how to insert a record via shell. Remember that in order to perform the “insert” command in CockroachDB the connection between java JDBC driver and CockroachDB must be configured. Bear in mind that the examples shown in this tutorial for connecting the Java JDBC driver to the CockroachDB database used a singleton class to control access to the resources. Be sure to confirm that Java Version 9 is properly installed.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.