How to Insert Record in CockroachDB using Java JDBC driver in Eclipse ID

Written by Data Pilot

July 05, 2019

CockroachDB
Eclipse
Maven

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

The Java Project Structure

After configuring MAVEN to an Eclipse project, the project explorer should display something that resembles the following:

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

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:

Added new java class in maven-proj directory

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

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

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

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