How to Update Record in CockroachDB using Java JDBC driver in Eclipse IDE
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.
- Consult the following link for assistance on how to install the Eclipse IDE: Eclipse.org
- Consult the following article for information how to configure MAVEN in Eclipse IDE: Connecting Java JDBC driver to CockroachDB using Maven in Eclipse IDE
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.
1 2 3 4 | 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:
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:
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | 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:
1 2 3 | UPDATE <TABLE name> SET Column1 = Value1 WHERE condition; |
Let’s follow the above format to perform an update within the CockroachDB data.
1 | 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:
1 2 3 4 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | // 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:
1 2 3 4 | 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:
1 2 | UPDATE <TABLE name> SET Column1 = Value1; |
To use the above format, use the following code:
1 | UPDATE tblrestaurants SET stars = 5; |
Updating Multiple Record using Java in Eclipse IDE.
Below java code will update multiple record into the CockroachDB database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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;”
1 2 3 4 | 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.
1 2 3 4 5 6 7 8 9 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | 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
1 2 3 4 5 6 7 8 9 | 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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started