How to Delete a Record in CockroachDB using the Java JDBC Driver in Eclipse IDE
Introduction
If you’re working with CockroachDB, it’s important to know how to perform basic operations, such as deleting records, in Java. Fortunately, it’s easy to connect CockroachDB to Java using the JDBC driver. In this article, we’ll provide instructions for deleting a record using the Java JDBC driver in CockroachDB via Maven in the Eclipse IDE.
Prerequisites
Before we turn our attention to the code examples, let’s review the key prerequisites for this task:
First, you must ensure that the Eclipse IDE and Maven are both installed and properly configured. If you need assistance installing the Eclipse IDE, please consult Eclipse.org for more details. For more information on configuring Maven in Eclipse IDE, please consult Connecting Java JDBC driver to CockroachDB using Maven in Eclipse IDE
You’ll also need to ensure that CockroachDB is properly installed and configured. To learn how to install CockroachDB, please consult How to Install CockroachDB on Mac OSX
Finally, you’ll need to ensure that Java version 9 or below has been properly installed.
The Dataset
If you plan to follow along with the code examples in this tutorial, you’ll find it helpful to use the same data. The following dataset represents the contents of the table tblresto, which is found within the restodatabase. For more information on creating a database and tables in CockroachDB, you can consult — How to Perform Insert 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 |
The Java project structure
Now that we’ve reviewed the prerequisites and created our sample dataset, we can start looking at Java. After configuring Maven to an Eclipse project, you’ll see something like the following in your project explorer:
Let’s create a few additional Java classes in src/main/java with the following names:
- DBConnection.java
- Main.java
- UserCrud.java
After doing so, you’re maven-proj project structure should look like this:
Connect Java JDBC Driver to CockroachDB Database
In order to perform database operations such as inserting and deleting records from Java, we first need to configure the connection between the Java JDBC driver and CockroachDB.
In the DBConnection.java class, use 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 33 34 35 36 | 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; } |
We used the classic “Singleton” design pattern to create our connection, which allows it to control access to resources such as database in this case. Once we set up our connection instance, we then connected to CockroachDB.
Deleting a Single Record in CockroachDB
The following section will show two different methods for deleting a single record from a CockroachDB database.
Delete Record via Shell
Now that the Eclipse IDE has been properly configured with Maven and our database is ready, we can try deleting an existing record.
You can use the following SQL format to delete a record via Shell:
1 2 | DELETE FROM tblresto WHERE id = 1 |
This will delete a single record from the tblresto table with the specified id.
Delete Record via Eclipse IDE
You can also delete a record using the Java JDBC driver in CockroachDB via Maven in Eclipse IDE:
Conclusion
Deleting a record is a common task when you’re using a database to store and manage data. With the Java JDBC driver, it’s easy to connect Java to CockroachDB and perform tasks such as deleting records from your Java code. Using the instructions and examples provided in this article, you’ll have no trouble deleting a record in CockroachDB using the Java JDBC driver and the Eclipse IDE.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started