Category: apache Derby

Apache Derby – Where Clause ”; Previous Next The WHERE clause is used in the SELECT, DELETE or, UPDATE statements to specify the rows on which the operation needs to be carried out. Usually, this clause is followed by a condition or expression which returns a Boolean value, the Select, delete or, update operations are performed only on the rows which satisfy the given condition. ij> SELECT * from table_name WHERE condition; or, ij> DELETE from table_name WHERE condition; or, ij> UPDATE table_name SET column_name = value WHERE condition; The WHERE clause can use the comparison operators such as =,!=, <, >, <=, and >=, as well as the BETWEEN and LIKE operators. Example Let us assume we have a table named Employees in the database with 7 records as shown below − ID |NAME |SALARY |LOCATION —————————————————————————– 1 |Amit |30000 |Hyderabad 2 |Kalyan |40000 |Vishakhapatnam 3 |Renuka |50000 |Delhi 4 |Archana |15000 |Mumbai 5 |Trupthi |45000 |Kochin 6 |Suchatra|33000 |Pune 7 |Rahul |39000 |Lucknow The following SQL DELETE statement fetches the records of the employees whose salary is more than 35000 − ij> SELECT * FROM Employees WHERE Salary>35000; This will produce the following output − ID |NAME |SALARY |LOCATION ————————————————— 2 |Kalyan |40000 |Vishakhapatnam 3 |Renuka |50000 |Delhi 5 |Trupthi|45000 |Kochin 7 |Rahul |39000 |Lucknow 4 rows selected Similarly, you can also delete and update records using this clause. Following example updates the location of those whose salary is less than 30000. ij> UPDATE Employees SET Location = ”Vijayawada” WHERE Salary<35000; 3 rows inserted/updated/deleted If you verify the contents of the table, you can see the updated table as shown below − ij> SELECT * FROM Employees; ID |NAME |SALARY |LOCATION —————————————————————————— 1 |Amit |30000 |Vijayawada 2 |Kalyan |40000 |Vishakhapatnam 3 |Renuka |50000 |Delhi 4 |Archana |15000 |Vijayawada 5 |Trupthi |45000 |Kochin 6 |Suchatra|33000 |Vijayawada 7 |Rahul |39000 |Lucknow 7 rows selected Where clause JDBC example This section teaches you how to use WHERE clause and perform CURD operations on a table in Apache Derby database using JDBC application. If you want to request the Derby network server using network client, make sure that the server is up and running. The class name for the Network client driver is org.apache.derby.jdbc.ClientDriver and the URL is jdbc:derby://localhost:1527/DATABASE_NAME;create=true;user=USER_NAME; password=PASSWORD“. Follow the steps given below to use WHERE clause and perform CURD operations on a table in Apache Derby Step 1: Register the driver To communicate with the database, first of all, you need to register the driver. The forName() method of the class Class accepts a String value representing a class name loads it in to the memory, which automatically registers it. Register the driver using this method Step 2: Get the connection In general, the first step we do to communicate to the database is to connect with it. The Connection class represents the physical connection with a database server. You can create a connection object by invoking the getConnection() method of the DriverManager class. Create a connection using this method. Step 3: Create a statement object You need to create a Statement or PreparedStatement or, CallableStatement objects to send SQL statements to the database. You can create these using the methods createStatement(), prepareStatement() and, prepareCall() respectively. Create either of these objects using the appropriate method. Step 4: Execute the query After creating a statement, you need to execute it. The Statement class provides various methods to execute a query like the execute() method to execute a statement that returns more than one result set. The executeUpdate() method executes queries like INSERT, UPDATE, DELETE. The executeQuery() method results that returns data. Use either of these methods and execute the statement created previously. Example Following JDBC example demonstrates how to use WHERE clause and perform CURD operations on a table in Apache Derby using JDBC program. Here, we are connecting to a database named sampleDB (will create if it does not exist) using the embedded driver. import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; import java.sql.ResultSet; public class WhereClauseExample { public static void main(String args[]) throws Exception { //Registering the driver Class.forName(“org.apache.derby.jdbc.EmbeddedDriver”); //Getting the Connection object String URL = “jdbc:derby:sampleDB;create=true”; Connection conn = DriverManager.getConnection(URL); //Creating the Statement object Statement stmt = conn.createStatement(); //Creating a table and populating it String query = “CREATE TABLE Employees(“ + “Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, “ + “Name VARCHAR(255), Salary INT NOT NULL, “ + “Location VARCHAR(255), “ + “PRIMARY KEY (Id))”; String query = “INSERT INTO Employees(“ + “Name, Salary, Location) VALUES “ + “(”Amit”, 30000, ”Hyderabad”), “ + “(”Kalyan”, 40000, ”Vishakhapatnam”), “ + “(”Renuka”, 50000, ”Delhi”), “ + “(”Archana”, 15000, ”Mumbai”), “ + “(”Trupthi”, 45000, ”Kochin”), “ + “(”Suchatra”, 33000, ”Pune”), “ + “(”Rahul”, 39000, ”Lucknow”), “ + “(”Trupti”, 45000, ”Kochin”)”; //Executing the query String query = “SELECT * FROM Employees WHERE Salary>35000”; ResultSet rs = stmt.executeQuery(query); while(rs.next()) { System.out.println(“Id: “+rs.getString(“Id”)); System.out.println(“Name: “+rs.getString(“Name”)); System.out.println(“Salary: “+rs.getString(“Salary”)); System.out.println(“Location: “+rs.getString(“Location”)); System.out.println(” “); } } } Output On executing the above program, you will get the following output − Id: 2 Name: Kalyan Salary: 43000 Location: Chennai Id: 3 Name: Renuka Salary: 50000 Location: Delhi Id: 5 Name: Trupthi Salary: 45000 Location: Kochin Id: 7 Name: Rahul Salary: 39000 Location: Lucknow Print Page Previous Next Advertisements ”;

Apache Derby – GROUP BY Clause ”; Previous Next The GROUP BY clause is used with SELECT statements. It is used to form subsets in case of identical data. Usually, this clause is followed by ORDER BY clause and placed after the WHERE clause. Syntax Following is the syntax of GROUP BY clause − ij>SELECT column1, column2, . . . table_name GROUP BY column1, column2, . . .; Example Suppose we have a table named Employees in the database with the following records − ID |NAME |SALARY |LOCATION —————————————————————— 1 |Amit |30000 |Hyderabad 2 |Rahul |39000 |Lucknow 3 |Renuka |50000 |Hyderabad 4 |Archana |15000 |Vishakhapatnam 5 |Kalyan |40000 |Hyderabad 6 |Trupthi |45000 |Vishakhapatnam 7 |Raghav |12000 |Lucknow 8 |Suchatra|33000 |Vishakhapatnam 9 |Rizwan |20000 |Lucknow The following SELECT statement with GROUP BY clause groups the table based on location. It displays the total amount of salary given to employees at a location. ij> SELECT Location, SUM(Salary) from Employees GROUP BY Location; This will generate the following output − LOCATION |2 ——————————————————- Hyderabad |120000 Lucknow |71000 Vishakhapatnam |93000 3 rows selected In the same way, following query finds the average amount spent on the employees as salary in a location. ij> SELECT Location, AVG(Salary) from Employees GROUP BY Location; This will generate the following output − LOCATION |2 —————————————————– Hyderabad |40000 Lucknow |23666 Vishakhapatnam |31000 3 rows selected Group By clause JDBC example This section teaches you how to use Group By clause and perform CURD operations on a table in Apache Derby database using JDBC application. If you want to request the Derby network server using network client, make sure that the server is up and running. The class name for the Network client driver is org.apache.derby.jdbc.ClientDriver and the URL is jdbc:derby://localhost:1527/DATABASE_NAME;create=true;user=USER_NAME;password=PASSWORD“ Follow the steps given below to use Group By clause and perform CURD operations on a table in Apache Derby Step 1: Register the driver To communicate with the database, first of all, you need to register the driver. The forName() method of the class Class accepts a String value representing a class name loads it in to the memory, which automatically registers it. Register the driver using this method. Step 2: Get the connection In general, the first step we do to communicate to the database is to connect with it. The Connection class represents the physical connection with a database server. You can create a connection object by invoking the getConnection() method of the DriverManager class. Create a connection using this method. Step 3: Create a statement object You need to create a Statement or PreparedStatement or, CallableStatement objects to send SQL statements to the database. You can create these using the methods createStatement(), prepareStatement() and, prepareCall() respectively. Create either of these objects using the appropriate method. Step 4: Execute the query After creating a statement, you need to execute it. The Statement class provides various methods to execute a query like the execute() method to execute a statement that returns more than one result set. The executeUpdate() method is used to execute queries like INSERT, UPDATE, DELETE. The executeQuery() method returns data. Use either of these methods and execute the statement created previously. Example Following JDBC example demonstrates how to use Group By clause and perform CURD operations on a table in Apache Derby using JDBC program. Here, we are connecting to a database named sampleDB (will create if it does not exist) using the embedded driver. import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; import java.sql.ResultSet; public class GroupByClauseExample { public static void main(String args[]) throws Exception { //Registering the driver Class.forName(“org.apache.derby.jdbc.EmbeddedDriver”); //Getting the Connection object String URL = “jdbc:derby:sampleDB;create=true”; Connection conn = DriverManager.getConnection(URL); //Creating the Statement object Statement stmt = conn.createStatement(); //Creating a table and populating it stmt.execute(“CREATE TABLE EmployeesData( “ + “Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, “ + “Name VARCHAR(255), “ + “Salary INT NOT NULL, “ + “Location VARCHAR(255), “ + “PRIMARY KEY (Id))”); stmt.execute(“INSERT INTO EmployeesData(Name, Salary, Location) “ + “VALUES (”Amit”, 30000, ”Hyderabad”), “ + “(”Rahul”, 39000, ”Lucknow”), “ + “(”Renuka”, 50000, ”Hyderabad”), “ + “(”Archana”, 15000, ”Vishakhapatnam”), “ + “(”Kalyan”, 40000, ”Hyderabad”), “ + “(”Trupthi”, 45000, ”Vishakhapatnam”), “ + “(”Raghav”, 12000, ”Lucknow”), “ + “(”Suchatra”, 33000, ”Vishakhapatnam”), “ + “(”Rizwan”, 20000, ”Lucknow”)”); //Executing the query String query = “SELECT Location, SUM(Salary) from EmployeesData GROUP BY Location”; ResultSet rs = stmt.executeQuery(query); while(rs.next()) { System.out.println(“Location: “+rs.getString(1)); System.out.println(“Sum of salary: “+rs.getString(2)); System.out.println(” “); } } } Output On executing the above program, you will get the following output − Location: Hyderabad Sum of salary: 120000 Location: Lucknow Sum of salary: 71000 Location: Vishakhapatnam Sum of salary: 93000 Print Page Previous Next Advertisements ”;

Apache Derby – Retrieve Data ”; Previous Next The SELECT statement is used to retrieve data from a table. This returns the data in the form of a table known as result set. Syntax Following is the syntax of the SELECT statement − ij> SELECT column_name, column_name, … FROM table_name; Or, Ij>SELECT * from table_name Example Let us suppose we have a table named Employees in the database as shown below − ij> CREATE TABLE Employees ( Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, Name VARCHAR(255), Salary INT NOT NULL, Location VARCHAR(255), PRIMARY KEY (Id) ); > > > > > > > 0 rows inserted/updated/deleted And, inserted four records in it as shown below − ij> INSERT INTO Employees (Name, Salary, Location) VALUES (”Amit”, 30000, ”Hyderabad”), (”Kalyan”, 40000, ”Vishakhapatnam”), (”Renuka”, 50000, ”Delhi”), (”Archana”, 15000, ”Mumbai”); > > > > 4 rows inserted/updated/deleted The following SQL statement retrieves the name, age and salary details of all the employees in the table: ij> SELECT Id, Name, Salary FROM Employees; The output of this query is − ID|NAME |SALARY ———————————————————————— 1 |Amit |30000 2 |Kalyan |40000 3 |Renuka |50000 4 |Archana|15000 4 rows selected If you want to get all the records of this table at once, use * instead of the names of the columns. ij> select * from Employees; This will produce the following result − ID |NAME |SALARY |LOCATION —————————————————————— 1 |Amit |30000 |Hyderabad 2 |Kalyan |40000 |Vishakhapatnam 3 |Renuka |50000 |Delhi 4 |Archana |15000 |Mumbai 4 rows selected Retrieve Data using JDBC program This section teaches you how to Retrieve data from a table in Apache Derby database using JDBC application. If you want to request the Derby network server using network client, make sure that the server is up and running. The class name for the Network client driver is org.apache.derby.jdbc.ClientDriver and the URL is jdbc:derby://localhost:1527DATABASE_NAME;create=true;user=USER_NAME;passw ord=PASSWORD“ Follow the steps given below to Retrieve data from a table in Apache Derby − Step 1: Register the driver To communicate with the database, first of all, you need to register the driver. The forName() method of the class Class accepts a String value representing a class name loads it in to the memory, which automatically registers it. Register the driver using this method. Step 2: Get the connection In general, the first step we do to communicate to the database is to connect with it. The Connection class represents the physical connection with a database server. You can create a connection object by invoking the getConnection() method of the DriverManager class. Create a connection using this method. Step 3: Create a statement object You need to create a Statement or PreparedStatement or, CallableStatement objects to send SQL statements to the database. You can create these using the methods createStatement(), prepareStatement() and, prepareCall() respectively. Create either of these objects using the appropriate method. Step 4: Execute the query After creating a statement, you need to execute it. The Statement class provides various methods to execute a query like the execute() method to execute a statement that returns more than one result set. The executeUpdate() method executes queries like INSERT, UPDATE, DELETE. The executeQuery() method to results that returns data etc. Use either of these methods and execute the statement created previously. Example Following JDBC example demonstrates how to Retrieve data from a table in Apache Derby using JDBC program. Here, we are connecting to a database named sampleDB (will create if it does not exist) using the embedded driver. The executeQuery() method returns a ResultSet object which holds the result of the statement. Initially the result set pointer will be at the first record, you can print the contents of the ResultSet object using its next() and getXXX() methods. import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class RetrieveData { public static void main(String args[]) throws SQLException, ClassNotFoundException { //Registering the driver Class.forName(“org.apache.derby.jdbc.EmbeddedDriver”); //Getting the Connection object String URL = “jdbc:derby:sampleDB;create=true”; Connection conn = DriverManager.getConnection(URL); //Creating the Statement object 4Statement stmt = conn.createStatement(); //Creating a table and populating it String query = “CREATE TABLE Employees(“ + “Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, “ + “Name VARCHAR(255), Salary INT NOT NULL, “ + “Location VARCHAR(255), “ + “PRIMARY KEY (Id))”; String query = “INSERT INTO Employees(“ + “Name, Salary, Location) VALUES “ + “(”Amit”, 30000, ”Hyderabad”), “ + “(”Kalyan”, 40000, ”Vishakhapatnam”), “ + “(”Renuka”, 50000, ”Delhi”), “ + “(”Archana”, 15000, ”Mumbai”), “ + “(”Trupthi”, 45000, ”Kochin”), “ + “(”Suchatra”, 33000, ”Pune”), “ + “(”Rahul”, 39000, ”Lucknow”), “ + “(”Trupti”, 45000, ”Kochin”)”; //Executing the query String query = “SELECT Id, Name, Salary FROM Employees”; ResultSet rs = stmt.executeQuery(query); while(rs.next()) { System.out.println(“Id: “+rs.getString(“Id”)); System.out.println(“Name: “+rs.getString(“Name”)); System.out.println(“Salary: “+rs.getString(“Salary”)); System.out.println(” “); } } } Output On executing the above program, you will get the following output. Id: 1 Name: Amit Salary: 30000 Id: 2 Name: Kalyan Salary: 43000 Id: 3 Name: Renuka Salary: 50000 Id: 4 Name: Archana Salary: 15000 Id: 5 Name: Trupthi Salary: 45000 Id: 6 Name: Suchatra Salary: 33000 Id: 7 Name: Rahul Salary: 39000 Print Page Previous Next Advertisements ”;

Apache Derby – Delete Data ”; Previous Next The DELETE statement is used to delete rows of a table. Just like the UPDATE statement, Apache Derby provides two types of Delete (syntax): searched delete and positioned delete. The searched delete statement deletes all the specified columns of a table. Syntax The syntax of the DELETE statement is as follows − ij> DELETE FROM table_name WHERE condition; Example Let us suppose we have a table named employee with 5 records as shown below − ID |NAME |SALARY |LOCATION —————————————————————————- 1 |Amit |30000 |Hyderabad 2 |Kalyan |40000 |Vishakhapatnam 3 |Renuka |50000 |Delhi 4 |Archana |15000 |Mumbai 5 |Trupti |45000 |Kochin 5 rows selected The following SQL DELETE statement deletes the record with name Trupti. ij> DELETE FROM Employees WHERE Name = ”Trupti”; 1 row inserted/updated/deleted If you get the contents of the Employees table, you can see only four records as shown below − ID |NAME |SALARY |LOCATION —————————————————————————- 1 |Amit |30000 |Hyderabad 2 |Kalyan |40000 |Vishakhapatnam 3 |Renuka |50000 |Delhi 4 |Archana |15000 |Mumbai 4 rows selected To delete all the records in the table, execute the same query without where clause. ij> DELETE FROM Employees; 4 rows inserted/updated/deleted Now, if you try to get the contents of the Employee table, you will get an empty table as given below − ij> select * from employees; ID |NAME |SALARY |LOCATION ——————————————————– 0 rows selected Delete Data using JDBC program This section explains how to delete the existing records of a table in Apache Derby database using JDBC application. If you want to request the Derby network server using network client, make sure that the server is up and running. The class name for the Network client driver is org.apache.derby.jdbc.ClientDriver and the URL is jdbc:derby://localhost:1527/DATABASE_NAME;create=true;user=USER_NAME;password=PASSWORD“. Follow the steps given below to delete the existing records of a table in Apache Derby: Step 1: Register the driver Firstly, you need to register the driver to communicate with the database. The forName() method of the class Class accepts a String value representing a class name loads it in to the memory, which automatically registers it. Register the driver using this method. Step 2: Get the connection In general, the first step we do to communicate to the database is to connect with it. The Connection class represents physical connection with a database server. You can create a connection object by invoking the getConnection() method of the DriverManager class. Create a connection using this method. Step 3: Create a statement object You need to create a Statement or PreparedStatement or, CallableStatement objects to send SQL statements to the database. You can create these using the methods createStatement(), prepareStatement() and, prepareCall() respectively. Create either of these objects using the appropriate method. Step 4: Execute the query After creating a statement, you need to execute it. The Statement class provides various methods to execute a query like the execute() method to execute a statement that returns more than one result set. The executeUpdate() method executes queries like INSERT, UPDATE, DELETE. The executeQuery() method results that returns data. Use either of these methods and execute the statement created previously. Example Following JDBC example demonstrates how to delete the existing records of a table in Apache Derby using JDBC program. Here, we are connecting to a database named sampleDB (will create if it does not exist) using the embedded driver. import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class DeleteData { public static void main(String args[]) throws Exception { //Registering the driver Class.forName(“org.apache.derby.jdbc.EmbeddedDriver”); //Getting the Connection object String URL = “jdbc:derby:sampleDB;create=true”; Connection conn = DriverManager.getConnection(URL); //Creating the Statement object Statement stmt = conn.createStatement(); //Creating a table and populating it String query = “CREATE TABLE Employees(“ + “Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, “ + “Name VARCHAR(255), Salary INT NOT NULL, “ + “Location VARCHAR(255), “ + “PRIMARY KEY (Id))”; String query = “INSERT INTO Employees(“ + “Name, Salary, Location) VALUES “ + “(”Amit”, 30000, ”Hyderabad”), “ + “(”Kalyan”, 40000, ”Vishakhapatnam”), “ + “(”Renuka”, 50000, ”Delhi”), “ + “(”Archana”, 15000, ”Mumbai”), “ + “(”Trupthi”, 45000, ”Kochin”), “ + “(”Suchatra”, 33000, ”Pune”), “ + “(”Rahul”, 39000, ”Lucknow”), “ + “(”Trupthi”, 45000, ”Kochin”)”; //Executing the query String query = “DELETE FROM Employees WHERE Name = ”Trupthi””; int num = stmt.executeUpdate(query); System.out.println(“Number of records deleted are: “+num); } } Output On executing the above program, you will get the following output − Number of records deleted are: 1 Print Page Previous Next Advertisements ”;

Apache Derby Tutorial PDF Version Quick Guide Resources Job Search Discussion Apache Derby is a Relational Database Management System which is fully based on (written/implemented in) Java programming language. It is an open source database developed by Apache Software Foundation. Audience This tutorial is prepared for beginners to help them understand the basic concepts related to Apache Derby. This tutorial will give you enough understanding on the various SQL queries of Apache along with JDBC examples. Prerequisites Before you start practicing with various types of examples given in this tutorial, I am assuming that you are already aware about what a database is, especially the RDBMS and what is a computer programming language. Print Page Previous Next Advertisements ”;