Apache Derby using JAVA

Exchanging information with the database is one of the major concerns for any single desktop or a web-based application. In this article, we will acquire more knowledge about managing Apache Derby using Java. If you are not much aware of Apache Derby, you can read Getting Started with Apache Derby. There you will get to know all the prerequisites and basic knowledge about controlling Apache Derby. Now, since we usually use databases for supporting our application, we need to know how we can communicate with this, using a back-end technology and the well-known back-end technology is JAVA. So, let’s start exploring Java utilities for the same.

Operations Involved:

1. Loading the Apache Derby’s JDBC Client Driver class for stacking up the utilities.
2. Retrieving the connection from Driver Manager, using the connection string, specifying the expected database.
3. Initializing the statement, from the desired connection, and executing the SQL statement(s).
4. Iterating over the ResultSet, if produced.
5. Closing the essentials like result set, statement(s), and most importantly connection.

And, by applying these steps, we will be controlling the database through our Java programs.

Performing CRUD Operations using JAVA:

In this section, we will be implementing the steps discussed above. And, if you are planning to use everything of Apache Derby on one machine, go with the embedded driver. Or, if the JDBC client connects to the Apache Derby server on a remote machine, go with the network client driver. Make sure to include the derby client jar file to the classpath every time you execute any of the below-elaborated codes. This jar file is for the network client driver.

Insert a Record:

For inserting the record we will just use the previously used SQL statement, and compile it with the above-mentioned procedure, to get the desired result.

Syntax:

insert into [table name] (column names) values (desired values);

Complete Java Implementation:

import java.sql.*;
class insert { 
public static void main(String args[]) { 
try { 
//Loading the Driver class
Class.forName("org.apache.derby.jdbc.ClientDriver");
//Establishing a connection with desired database
Connection connection = DriverManager.getConnection
("jdbc:derby://localhost:1527/database/places"); 
//Initializing statement and executing the an Insert SQL
Statement statement = connection.createStatement(); 
statement.executeUpdate
("insert into city (name, state_code) values('Lonavla' , 1)"); 
//Terminating statement and connection
statement.close(); 
connection.close(); 
System.out.println("Record inserted"); 
}
catch(SQLException sqlException) 
{ System.out.println(sqlException); } 
// Raised either if the connection is refused, or some other technical issue
catch(ClassNotFoundException cnfe) 
{ System.out.println(cnfe); } 
// Raised in case the Driver class is  not found
}
}

Now to compile the above as normally done, but while executing make sure to include the derby client jar to the classpath, like done below:

java -classpath [complete path to derby client jar file] insert

Don’t forget to start the network server, before executing. Now, once you are done, you will be seeing the message “Record Inserted”, make sure to cross-validate it through the Apache Derby Client prompt.

Since, while inserting we often need to get the id corresponding to the newly added record. Hence, you can alter the statement with a prepared statement. Just initialize PreparedStatement rather than Statement, and iterate over the resultSet to get the generated key, like elaborated below:

//Initializing the prepared statement with SQL and specifying argument for returning key
PreparedStatement preparedStatement = connection.prepareStatement
("insert into city (name,state_code) values(?, ?)" , Statement.RETURN_GENERATED_KEYS); 
//Setting up the prepared Statement
preparedStatement.setString(1, name); 
preparedStatement.setInt(2, stateCode); 
//Executing the prepared statement
preparedStatement.executeUpdate(); 
//Maintaining Result set for receiving the generated key
ResultSet resultSet = preparedStatement.getGeneratedKeys(); 
if(resultSet.next()) { 
int code = resultSet.getInt(1); //retrieving generated code
System.out.println("Assigned Code : "+code); 
} 
//Terminating Result set and prepared Statement
resultSet.close(); 
preparedStatement.close();

Compile and execute this, as done previously and now you will be getting the id corresponding to the newly added city. Now, similarly, we will understand to update, delete and fetch operations, using Java.

Update a Record:

For updating, we will fire a query, with a “where” clause, which will firstly search for the record that satisfies the constrain and, update that record with the new information. Below is the syntax and code for updating a record:

Syntax:

update [table name] set [column name]=[new value] where [condition];
//multiple column names can be specified consecutively, separated by ","

Complete Java Implementation:

import java.sql.*;
class update { 
public static void main(String args[]) { 
try { 
//Loading the Driver class
Class.forName("org.apache.derby.jdbc.ClientDriver"); 
Connection connection = DriverManager.getConnection
("jdbc:derby://localhost:1527/database/places");
//Initializing the prepared statement with an Update SQL
PreparedStatement preparedStatement = connection.prepareStatement
("update city set name = ? where name = ?"); 
//Setting up the prepared Statement
//preparing the statement
String oldName = "Lonavla";
String newName = "Nagpur";
preparedStatement.setString(1,newName); 
preparedStatement.setString(2,oldName); 
//Executing the prepared statement
preparedStatement.executeUpdate(); 
//Terminating prepared Statement and connection
preparedStatement.close();
connection.close(); 
System.out.println("Record updated"); 
}catch(SQLException sqlException) 
{ System.out.println(sqlException); } 
// Raised either if the connection is refused or some other technical issue
catch(ClassNotFoundException cnfe) 
{ System.out.println(cnfe); } 
// Raised in case the Driver class is  not found
}
}

Compile and execute the above code as done previously. Just make sure to include the derby client jar to classpath while executing. No classpath is required while compiling the code. After executing, connect through the ij prompt and, cross-validate if the record has been altered correctly.

Similarly, the “where” clause can be followed by numerous constraints for more complex query structures. But, all the queries can always be fragmented into small individual query, for better comprehending. Now, we will look forward to deleting a record, using Java.

Delete a Record:

For deleting a record, we will again fire a query, with a “where” clause, which will firstly search for the record that satisfies the constrain and, remove that record from the table. Below is the syntax and code for deleting a record:

Syntax:

delete from [table name] where [condition];

Complete Java Implementation:

import java.sql.*;
class delete{ 
public static void main(String args[]) { 
try { 
//Loading the Driver class
Class.forName("org.apache.derby.jdbc.ClientDriver"); 
Connection connection = DriverManager.getConnection
("jdbc:derby://localhost:1527/database/places");
//Initializing the prepared statement with SQL
PreparedStatement preparedStatement = connection.prepareStatement
("delete from city where name = ?"); 
//Setting up the prepared Statement
preparedStatement.setString(1,"Nagpur"); 
//Executing the prepared statement
preparedStatement.executeUpdate(); 
//Terminating prepared statement and connection
preparedStatement.close();
connection.close(); 
System.out.println("Record deleted");
 
}catch(SQLException sqlException) 
{ System.out.println(sqlException); } 
// Raised either if the connection is refused or some other technical issue
catch(ClassNotFoundException cnfe) 
{ System.out.println(cnfe); } 
// Raised in case the Driver class is  not found
}
}

Compile and execute the above code as done previously. Again, make sure to include the derby client jar to classpath while executing. If derby client jar is not included while executing, a Class not found exception is raised. After executing, connect through the ij prompt and, cross-validate that the desired record has been removed accurately.

Now, we are left with the last and only CRUD operation, that is, retrieving data from the table, about which we will be studying in the coming section.

Retrieving Records:

There can be a number of variations to this operation using clauses like where, order by, group by, and many more using subqueries. Below is the syntax and code for retrieving records from a table:

Syntax:

select [column name] from [table name] where [condition];
//where clause is optional. If not given, all the records will be retrieved and if given, only records following the constrain will be retrieved
//if all columns are required, use "*" in place of column name

Complete Java implementation:

import java.sql.*;
class select {
public static void main(String args[]) {
try{
//Loading the driver class
Class.forName("org.apache.derby.jdbc.ClientDriver"); 
Connection connection = DriverManager.getConnection
("jdbc:derby://localhost:1527/database/places");
//Initializing the prepared statement with select SQL
PreparedStatement preparedStatement = connection.prepareStatement
("select * from city"); 
//Executing the prepared statement and retrieving results
ResultSet resultSet = preparedStatement.executeQuery();
int code;
String name;
while(resultSet.next())
{
code = resultSet.getInt("code");
name = resultSet.getString("name").trim();
System.out.printf("Code : %d, Country : %s\n", code, name);
}
//Terminating result set, prepared statement and connection
resultSet.close();
preparedStatement.close();
connection.close();
}catch(SQLException sqlException) 
{ System.out.println(sqlException); } 
// Raised either if the connection is refused or some other technical issue
catch(ClassNotFoundException cnfe) 
{ System.out.println(cnfe); } 
// Raised in case the Driver class is  not found
}
}

Compile and execute the above code as done previously, and your prompt should look like this:

The path specified while executing is the path to derby client jar on my machine

And, now we are finished with controlling Apache Derby using Java. But below is some additional interesting information regarding patterns of retrieving information. These operations are performed from Apache Derby Client prompt, but can effortlessly be amended for Java implementation.

Joins:

Following are some joins with their laconic descriptions:

Inner Join: This is a join between two tables with an explicit join clause.
Left Join: This is a join between two tables with an explicit join clause, conserving unmatched rows from the first table.
Right Join: This is a join between two tables with an explicit join clause, conserving unmatched rows from the second table.
Natural Join: This is an inner or outer join between two tables. It has no explicit join clause. Instead, one is created implicitly using the common columns from the two tables.

Some joins and respected outputs are illustrated below:

select * from state left join country on state.country_code = country.code;

Left Join applied on State and Country Tables

select city.name as "City", state.name as "State", country.name as "Country" from city inner join state on city.state_code = state.code inner join country on state.country_code = country.code order by city.name desc, state.name, country.name;

Conclusion:

In this article, we understood the process of controlling the Apache Derby using Java. This is considered crucial, as through this we can communicate with the database in any application, supported by Apache Derby and Java as back-end technology.

Do look out for other articles to get the knowledge about various topics and feel free to drop a comment for doubts or suggestions.

What are the top 10 useful functions of Underscore.js?

What are Decorators in Python?

Multithreading in Java

Understanding Apache Derby using Java

TCP/IP Socket Programming in Java

How to send HTTP Requests using React JSX?