Apache Derby Using Java

Reading Time: 4 minutes

Introduction:

Connecting  with database is one of the major concern for any desktop or  web based application. In this, we will acquire more knowledge about managing Apache Derby using Java. There you will get to know all the prerequisites and basic knowledge about controlling Apache Derby.

Operations Involved:

  1. Loading the Apache Derby’s JDBC Client Driver class.
  2. Retrieving the connection from Driver Manager, using the connection string, specifying the expected database which we want to create.
  3. Initializing the statement, from the desired connection, and executing the SQL statement.
  4. Iterating over the Result Set, if produced.
  5. Closing the essentials like result set, statement 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 are  planning to use everything of Apache Derby on one machine, go with the embedded driver. Or, if the JDBC client connects to 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. This jar file is for 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('Ujjain' , 18)"); //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 issuecatch(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 network server, before executing. Now, once you are done, you will be seeing message “Record Inserted”, make sure to cross validate it through 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 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.

 

 

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 = "Ujjain";
String newName = "Indore";
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 issuecatch(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.

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 issuecatch(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, Class not found exception is raised.

 

 

 

Retrieving Records:

There can be number of variations to this operation using clause like whereorder bygroup by and many more using sub queries. 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 issuecatch(ClassNotFoundException cnfe)
{ System.out.println(cnfe); }
// Raised in case the Driver class is  not found}
}

Conclusion:

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