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:

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 where, order by, group 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.