How to Insert Record in CockroachDB using Java JDBC driver in Eclipse ID
Introduction
This tutorial will demonstrate how to connect Java JDBC pg driver in CockroachDB and how to go about inserting record using java JDBC driver in CockroachDB via Maven in Eclipse IDE. Examples of how to insert a single record in CockroachDB and how to insert a record via shell are provided. CockroachDB, the Java JDBC driver, Eclipse IDE and Maven must all be properly installed and configured in order to preform the functions in this tutorial. The Java Database Connectivity, or JDBC, requires specific drivers for each database. Instructions for installing and configuring each component are provided. Sample datasets are also provided.
Prerequisites
Eclipse IDE and Maven must be properly installed and configured before beginning.
- If Eclipse IDE is not currently installed, consult Eclipse.org for more information.
- Consult the following resource for further information in learning 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 for further information on how to install CockroachDB: How to Install CockroachDB on Mac OSX
Confirm that Java version <= 9 is properly installed.
The Dataset
The below dataset belongs to the table tblresto
within the restodatabase
. Consult the following link for assistance in creating database and tables in CockroachDB: How to Perform Insert in CockroachDB for further information.
1 2 3 | id | name | phone | email | stars | category +----+------------------+--------------+------------------------+-------+----------+ 1 | pure coffee | 847-585-0171 | purebeauty@example.net | 4 | Coffee |
The Java Project Structure
After configuring MAVEN to an Eclipse project, the project explorer should display something that resembles the following:
Before beginning to interact with the CockroachDB database, create additional java class in the src/main/java
as follows:
- DBConnection.java
- Main.java
- UserCrud.java
After the additional java class has been created, the maven-proj
project structure should resemble the following:
How to Connect the Java JDBC Driver to the CockroachDB Database
To perform an “insert” command in CockroachDB, the connection between java jdbc driver and CockroachDB must be configure. 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 employs singleton class that controls access to the resources, being the database in the above example.
How to Insert a Single Record in CockroachDB
This section will demonstrate inserting a single record in CockroachDB database and inserting record using java JDBC driver in CockroachDB via Maven in Eclipse IDE.
How to insert a record via shell
Now that Eclipse IDE is properly configured with maven and the database is ready, we can now add data on the existing records.
CockroachDB uses the following sql format to insert record via Shell:
1 | INSERT INTO <table> (<Columns>) VALUES (<values>); |
Using the above format we can replace the “Columns” with the actual table column and the “values” with the values we want to store in that particular table.
To do this use the following command:
1 | INSERT INTO tblrestaurants (id,name,phone,email,star,categories) VALUES (2,'yumster delicacy','225-456-0102','yumsterD@example.com',3,'Coffee'); |
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 | 4 | Coffee 2 | yumster delicacy | 225-456-0102 | yumsterD@example.com | 3 | Italian |
Inserting Single Record using Java in Eclipse IDE.
In the previous section, the article shows how to insert a single record into CockroachDB via shell now in this section the article will show how to do this process using Java via Eclipse IDE.
In the userCrud.java
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 | // Instantiate database connection static Connection con = DBConnection.getInstance().getConnection(); // insert user function public static void insertResto() throws SQLException{ try { String sql = "INSERT INTO tblrestaurants (id,name,phone,email,stars,category)" + " values (?,?,?,?,?,?)"; PreparedStatement ps = con.prepareStatement(sql); ps.setInt(1, 3); ps.setString(2, "raizel cafeteria"); ps.setString(3, "857-555-0182"); ps.setString(4, "raizelbalooga@example.com"); ps.setInt(5, 3); ps.setString(6, "Dessert"); // 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 above code uses the prepareStatement
which enables us to send SQL command to the database and receive data from the database.
The result from the above code should look something like the following:
1 2 3 4 5 | id | name | phone | email | stars | category +----+------------------+--------------+---------------------------+-------+----------+ 1 | pure coffee | 847-585-0171 | purebeauty@example.net | 4 | Coffee 2 | yumster delicacy | 225-456-0102 | yumsterD@example.com | 3 | Italian 3 | raizel cafeteria | 857-555-0182 | raizelbalooga@example.com | 3 | Dessert |
Insert Multiple Record in CockroachDB
The following section will show ways on how to insert a multiple record in CockroachDB database.
Inserting Multiple Record via Shell
CockroachDB uses the following sql format to insert multiple record via Shell:
1 | INSERT INTO <table> (<Columns>) VALUES (<Set_of_values>),(<Another_set_of_values>); |
To use the above format, use the following code:
1 | INSERT INTO tblrestaurants (id,name,phone,email,star,categories) VALUES (4,'Hungry Pirate Resto Bar','908-555-045','PirateBar@example.com',0,(ARRAY['Pasta, 'Italian', 'Buffet', 'Cafeteria'])), (5,'El Nido Resto','123-456-0165','ElNido@example.com',2,(ARRAY['Steak', 'Seafood'])); |
Inserting Multiple Record using Java in Eclipse IDE.
Below java code will insert 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 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | static Connection con = DBConnection.getInstance().getConnection(); // insert restaurants details function public static void insertResto() throws SQLException{ try { String sql = "INSERT INTO tblrestaurants (id,name,phone,email,stars,category)" + " values (?,?,?,?,?,?)"; PreparedStatement ps = con.prepareStatement(sql); ps.setInt(1, 4); ps.setString(2, "Hungry Pirate Resto Bar"); ps.setString(3, "908-555-045 "); ps.setString(4, "PirateBar@example.com "); ps.setInt(5, 0); ps.setString(6, "Pasta"); // sends the first batch of SQL commands ps.addBatch(); ps.setInt(1, 5); ps.setString(2, "El Nido Resto "); ps.setString(3, "908-555-045 "); ps.setString(4, "ElNido@example.com"); ps.setInt(5, 2); ps.setString(6, "Steak"); // sends the 2nd batch of SQL commands ps.addBatch(); // executes the batches of SQL commands ps.executeBatch(); 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 5 6 7 | id | name | phone | email | stars | category +----+-------------------------+--------------+---------------------------+-------+----------+ 1 | pure coffee | 847-585-0171 | purebeauty@example.net | 4 | Coffee 2 | yumster delicacy | 225-456-0102 | yumsterD@example.com | 3 | Italian 3 | raizel cafeteria | 857-555-0182 | raizelbalooga@example.com | 3 | Dessert 4 | Hungry Pirate Resto Bar | 908-555-045 | PirateBar@example.com | 0 | Pasta 5 | El Nido Resto | 123-456-0165 | ElNido@example.com | 2 | Steak |
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 insert 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.insertResto(); } } |
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 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | public class UserCrud { static Connection con = DBConnection.getInstance().getConnection(); public static void insertResto() throws SQLException{ // Code for inserting multiple record // Comment the whole block of code if you wish to use the // single insert method try { String sql = "INSERT INTO tblrestaurants (id,name,phone,email,stars,category)" + " values (?,?,?,?,?,?)"; PreparedStatement ps = con.prepareStatement(sql); ps.setInt(1, 4); ps.setString(2, "Hungry Pirate Resto Bar"); ps.setString(3, "908-555-045 "); ps.setString(4, "PirateBar@example.com "); ps.setInt(5, 0); ps.setString(6, "Pasta"); ps.addBatch(); ps.setInt(1, 5); ps.setString(2, "El Nido Resto "); ps.setString(3, "908-555-045 "); ps.setString(4, "ElNido@example.com"); ps.setInt(5, 2); ps.setString(6, "Steak"); ps.addBatch(); ps.executeBatch(); con.commit(); } catch (Exception e) { System.out.println(e.getMessage()); e.printStackTrace(); } // ==== END FOR MULTIPLE INSERT =============== // ==== Code for the insert single record ======= // Comment the whole block of code if you wish to use the // Multiple insert method try { String sql = "INSERT INTO tblrestaurants (id,name,phone,email,stars,category)" + " values (?,?,?,?,?,?)"; PreparedStatement ps = con.prepareStatement(sql); ps.setInt(1, 3); ps.setString(2, "raizel cafeteria"); ps.setString(3, "857-555-0182"); ps.setString(4, "raizelbalooga@example.com"); ps.setInt(5, 3); ps.setString(6, "Dessert"); ps.executeUpdate(); con.commit(); } catch (Exception e) { System.out.println(e.getMessage()); e.printStackTrace(); } // ========= END for SINGLE INSERT 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.insertResto(); } } |
Conclusion
This tutorial demonstrated how to go about inserting record using java JDBC driver in CockroachDB via Maven in Eclipse IDE. The article provided instructions on how to insert a single record in CockroachDB and how to insert a record via shell. Remember that Maven must be configured to the specific Eclipse project. Also, be sure to confirm that Java Version 9 is properly installed. Bear in mind that the example shown in this tutorial for connecting the Java JDBC driver to the CockroachDB database used a singleton class to control access to the resources.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started