Category: apache Derby

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 ”;

Apache Derby – Environment Setup ”; Previous Next Following chapter explains how to download and install Apache Derby. Downloading Apache Derby Visit the home page of Apache Derby home page https://db.apache.org/derby/. Click the Download tab. Select and click on the link of the latest version of Apache Derby. On clicking the selected link, you will be redirected to the Distributions page of apache derby. If you observe here, derby provides distributions namely, db-derby-bin, db-derbylib.zip, db-derby-lib-debug.zip, and db-derby-src.zip. Download the db-derby-bin folder. Copy its contents to a separate folder where you wanted to install Apache Derby. (for example, say C:Derby) Now, to work with Derby, Make sure that you already have set the JAVA_HOME variable by passing the location of bin folder of Java Installation folder, and include the JAVA_HOME/bin in the PATH variable. Create a new environment variable, DERBY_HOME with value C:Derby. The bin folder of db-derby-bin distributions (we changed it as C:Derbybin) contains all the required jar files. As discussed, Apache Derby can be installed/deployed in two ways as follows − Embedded mode − In this, you need to access the database using Embedded Derby JDBC driver. You can start and stop derby through Java application. Both Database engine and your application will run on the same JVM. Network Server mode − In this mode, you can access Derby in a typical clientserver fashion, where Derby is embedded in the server system. Then, the client machines running in different JVM’s (that of the Server) will send requests to the server, and the server responds to those requests. The client can be another JVM in the same system machine of the server or a Java application from a remote system. Installing Derby in Embedded Mode To install Apache Derby in embedded mode, include the jar file derby.jar in your CLASSPATH. Or, you can set the classpath for required jar files by running the setEmbeddedCP command. Browse through the bin directory of Apache Derby and run this file as shown below − C:UsersMYUSER>cd %DERBY_HOME%/bin C:Derbybin>setEmbeddedCP.bat C:Derbybin>SET DERBY_HOME=C:Derby C:Derbybin>set CLASSPATH=C:Derbylibderby.jar;C:Derbylibderbytools.jar;C:Derby/lib/derby optionaltools.jar;C:UsersTutorialspointGoogle DriveOfficeDerbyderby_zipNew folderdb-derby-10.12.1.1- binlib;C:EXAMPLES_Taskjars*;C:EXAMPLESjarsmysql-connector-java-5.1.40- bin.jar;C:UsersTutorialspointGoogle DriveOffice37.Junit Updatejars;C:Program FilesApache Software FoundationTomcat 8.5lib*;C:Derbylib*; After setting up Apache Derby, to access it, run Java programs using the embedded driver. Verification You can verify the setup using the ij tool as shown below − C:Derbybin>ij ij version 10.14 ij> connect ”jdbc:derby:SampleDB;create=true”; ij> Installing Derby in Network Server Mode To install Apache Derby in network server mode, you need to include derbynet.jar and derbytools.jar files to the CLASSPATH. Or, you can set the class path for required jar files by running the setNetworkServerCP command. Browse through the bin directory of Apache Derby and run this file as shown below − C:UsersMYUSER>cd %DERBY_HOME%/bin C:Derbybin>setNetworkServerCP.bat C:Derbybin>SET DERBY_INSTALL=C:Derby C:Derbybin>set CLASSPATH=C:Derbylibderbynet.jar;C:Derbylibderbytools.jar;C:Derby/lib/de rbyoptionaltools.jar;C:UsersTutorialspointGoogle DriveOfficeDerbyderby_zipNew folderdb-derby-10.12.1.1- binlib;C:EXAMPLES_Taskjars*;C:EXAMPLESjarsmysql-connector-java-5.1.40- bin.jar;C:UsersTutorialspointGoogle DriveOffice37.Junit Updatejars;C:Program FilesApache Software FoundationTomcat 8.5lib*;C:Derbylib*; Starting Derby in Server Mode You can start Network Server by running the command startNetworkServer. Browse through the bin directory of Apache Derby and run this command as shown below − C:Derbybin>startNetworkServer Fri Jan 04 11:20:30 IST 2019 : Security manager installed using the Basic server security policy. Fri Jan 04 11:20:30 IST 2019 : Apache Derby Network Server – 10.14.2.0 – (1828579) started and ready to accept connections on port 1527 Or, you can start the server using derbyrun.jar as shown below − C:UsersMYUSER>cd %DERBY_HOME%/lib C:Derbylib>java -jar derbyrun.jar server start Fri Jan 04 11:27:20 IST 2019: Security manager installed using the Basic server security policy. Fri Jan 04 11:27:21 IST 2019: Apache Derby Network Server – 10.14.2.0 – (1828579) started and ready to accept connections on port 1527 Network Client In client, add the jar files derbyclient.jar and derbytools.jar to the CLASSPATH. Or, run the setNetworkClientCP command as shown below − C:UsersMYUSER>cd %DERBY_HOME%/bin C:Derbybin>setNetworkClientCP C:Derbybin>SET DERBY_HOME=C:Derby C:Derbybin>set CLASSPATH=C:Derbylibderbyclient.jar;C:Derbylibderbytools.jar;C:Derby/lib /derbyoptionaltools.jar;C:Derbylibderby.jar;C:Derbylibderbytools.jar;C:D erby/lib/derbyoptionaltools.jar;C:UsersTutorialspointGoogle DriveOfficeDerbyderby_zipNew folderdb-derby-10.12.1.1- binlib;C:EXAMPLES_Taskjars*;C:EXAMPLESjarsmysql-connector-java-5.1.40- bin.jar;C:UsersTutorialspointGoogle DriveOffice37.Junit Updatejars;C:Program FilesApache Software FoundationTomcat 8.5lib*;C:Derbylib*; Then from this client, you can send requests to the server. Verification You can verify the setup using the ij tool as shown below − C:Derbybin>ij ij version 10.14 ij> connect ”jdbc:derby://localhost:1527/SampleDB;create=true”; ij> Apache Derby Eclipse Environment While working with Eclipse, you need to set the build path for all the required jar files. Step 1: Create a project and set build path Open eclipse and create a sample project. Right click on the project and select the option Build Path -> Configure Build Path as shown below − In the Java Build Path frame in the Libraries tab, click on Add External JARs. And select the required jar files in the lib folder of the Derby installation folder and click on Apply and Close. Print Page Previous Next Advertisements ”;