Apache Derby – Data Types

Apache Derby – Data Types ”; Previous Next Data Type is an attribute that specifies the type of data of any object. Each column, variable and expression has a related data type. You can use these data types while creating your tables. You can choose a data type for a table column based on your requirement. Derby Server offers several categories of data types for your use as listed below − Integer Numeric Data Types Following is the list of integer numeric data types − DATA TYPE SIZE FROM TO SMALLINT 2 bytes -32768 32767 INTEGER 4 bytes -2,147,483,648 2,147,483,647 BIGINT 8 bytes -9223372036854775808 9223372036854775808 Approximate Numeric Data Types Following is the list of approximate numeric data types − DATA TYPE SIZE FROM TO REAL 4 bytes -3.40E + 38 3.40E + 38 DOUBLE PRECISION 8 bytes -1.79E + 308 1.79E + 308 FLOAT -1.79E + 308 1.79E + 308 Exact Numeric Data Types Following is the list of exact numeric data types − DATA TYPE FROM TO DECIMAL -10^38 +1 10^38 -1 NUMERIC -10^38 +1 10^38 -1 Print Page Previous Next Advertisements ”;

Apache Derby – Order By Clause

Apache Derby – Order By Clause ”; Previous Next The ORDER BY clause is used to arrange the contents of the result set in the order it uses the keywords, ASC representing ascending order and DESC representing descending order. If you do not mention either of these, the contents will be arranged in ascending order by default. Syntax Following is the syntax of the ORDER BY clause − SELECT * FROM table_name ORDER BY column_name ASC|DESC. Example Suppose we have a table named Employees in the database with the following records − 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 The following query arranges the contents of the table in ascending order based on the name of the Employee. ij> SELECT * FROM Employees ORDER BY Name; This will generate the following output − ID |NAME |SALARY |LOCATION ————————————————————— 1 |Amit |30000 |Hyderabad 4 |Archana |15000 |Mumbai 2 |Kalyan |40000 |Vishakhapatnam 7 |Rahul |39000 |Lucknow 3 |Renuka |50000 |Delhi 6 |Suchatra|33000 |Pune 5 |Trupthi |45000 |Kochin 7 rows selected Similarly, following query arranges the contents of the table in descending order based on the salary of the Employee − ij> SELECT * FROM Employees ORDER BY Salary DESC; This will generate the following output − ID |NAME |SALARY |LOCATION ————————————————————— 3 |Renuka |50000 |Delhi 5 |Trupthi |45000 |Kochin 2 |Kalyan |40000 |Vishakhapatnam 7 |Rahul |39000 |Lucknow 6 |Suchatra |33000 |Pune 1 |Amit |30000 |Hyderabad 4 |Archana |15000 |Mumbai 7 rows selected Sorting Data using JDBC program This section teaches you how to sort the contents of a table in Derby using JDBC. You can arrange records in order using the ORDER BY clause and the keywords ASC (denoting ascending order), and DSC (denoting descending order). 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 sort the records of 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 returns data. Use either of these methods and execute the statement created previously. Example Following JDBC example demonstrates how to sort the 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.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class SortData { 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 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 Location, SUM(Salary) ” + “from Employees GROUP BY Location”; ResultSet rs = stmt.executeQuery(query); while(rs.next()) { System.out.println(“Salary: “+rs.getString(1)); System.out.println(“Location: “+rs.getString(2)); System.out.println(” “); } } } Output On executing the above program, you will get the following output − Salary: Chennai Location: 43000 Salary: Delhi Location: 50000 Salary: Hyderabad Location: 30000 Salary: Kochin Location: 45000 Salary: Lucknow Location: 39000 Salary: Mumbai Location: 15000 Salary: Pune Location: 33000 Print Page Previous Next Advertisements ”;

Apache Derby – Quick Guide

Apache Derby – Quick Guide ”; Previous Next Apache Derby – Introduction 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. Oracle released the equivalent of Apache Derby with the name JavaDB. Features of Apache Derby Following are the notable features of Derby database − Platform independent − Derby uses on-disc database format where the databases in it are stored in a file in the disc within the directory with the same name as the database. No modifying data − Because of this, you can move derby databases to other machines without modifying the data. Transactional support − Derby provides complete support for transactions ensuring data integrity. Including databases − You can include pre-build/existing databases into your current derby applications. Less space − Derby database has a small footprint, i.e., it occupies less space and it is easy to use and deploy it. Embed with Java Application − Derby provides an embedded database engine which can be embedded in to Java applications and it will be run in the same JVM as the application. Simply loading the driver starts the database and it stops with the applications. Limitations of Apache Derby Following are the limitations of Apache Derby − Derby does not support indexes for datatypes such as BLOB and LONGVARCHAR. If Derby does not have enough disc space, it will shut down immediately. Data storage While storing data, Apache Derby follows a concept known as conglomerate. In this, data of a table will be stored in a separate file. In the same way, each index of a table is also stored in a separate file. Thus, there will be a separate file for every table or index in the database. Apache Derby Library/Components Apache Derby distribution provides various components. In the lib folder of the apache distribution you have downloaded, you can observe jar files representing various components. Jar file Component Description derby.jar Database Engine and JDBC driver The Database engine of Apache Derby is an embedded relational database engine which supports JDBC and SQL API’s. This also acts as embedded Driver, using which you can communicate to Derby using Java applications. derbynet.jar derbyrun.jar Network server The Network Sever of Apache Derby provides the client server functionality, where the clients can connect to the Derby server through a network. derbyclient.jar Network client JDBC driver derbytools.jar Command line tools This jar file holds tools such as sysinfo, ij, and dblook. derbyoptionaltools.jar Optional command line utilities (tools) This jar file provides optional tools: databaseMetaData optional tool, foreignViews optional tool, luceneSupport optional tool, rawDBReader optional tool, simpleJson optional tool, etc derbyLocale_XX.jar Jar files to localize messages In addition to the above mentioned jar files, you can see several derbyLocale_XX.jar (es, fr, hu, it, ja, etc.). Using these, you can localize the messages of Apache Derby. Apache Derby – Deployment Modes You can deploy apache derby in two modes, namely embedded mode and server mode. Embedded mode You can run derby in embedded mode using Java application (using embedded driver). If you deploy Derby in embedded mode, the database engine will run in the same JVM as the Java application. It starts and stops with the application. You can access the database only with this application. Server mode In the server mode, derby will be run in the JVM of an application server where you can send a request to the server to access it. Unlike in embedded mode, multiple applications (java) can send a request to the server and access the database. Apache Derby – Environment Setup 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

Apache Derby – GROUP BY Clause

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

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

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 – Home

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

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

Apache Derby – Syntax

Apache Derby – Syntax ”; Previous Next This chapter gives you the syntax of all the Apache Derby SQL statements. All the statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;). The SQL statements of Apache Derby are case in sensitives including table names. CREATE Statement CREATE TABLE table_name ( column_name1 column_data_type1 constraint (optional), column_name2 column_data_type2 constraint (optional), column_name3 column_data_type3 constraint (optional) ); DROP TABLE DROP TABLE table_name; INSERT Statement INSERT INTO table_name VALUES (column_name1, column_name2, …); SELECT Statement SELECT column_name, column_name, … FROM table_name; UPDATE Statement UPDATE table_name SET column_name = value, column_name = value, … WHERE conditions; DELETE Statement DELETE FROM table_name WHERE condition; DESCRIBE Statement Describe table_name SQL TRUNCATE TABLE Statement TRUNCATE TABLE table_name; ALTER Statement – Adding column ALTER TABLE table_name ADD COLUMN column_name column_type; ALTER Statement – Adding constraint ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint (column_name); ALTER Statement – Dropping column ALTER TABLE table_name DROP COLUMN column_name; ALTER Statement – Dropping constraint ALTER TABLE table_name DROP CONSTRAINT constraint_name; WHERE Clause SELECT * from table_name WHERE condition; or, DELETE from table_name WHERE condition; or, UPDATE table_name SET column_name = value WHERE condition; GROUP BY Clause SELECT column1, column2, . . . table_name GROUP BY column1, column2, . . .; ORDER BY Clause SELECT * FROM table_name ORDER BY column_name ASC|DESC. Having Clause SELECT column1, column2 . . . from table_name GROUP BY column having condition; Creating Index CTREATE INDEX index_name on table_name (column_name); Creating an UNIQUE index CREATE UNIQUE INDEX index_name on table_name (column_name); Creating a COMPOSITE index CREATE INDEX index_name on table_name (column_name1, column_name2); Displaying the Indexes SHOW INDEXES FROM table_name; Dropping Indexes DROP INDEX index_name; Print Page Previous Next Advertisements ”;

Apache Derby – Drop Table

Apache Derby – Drop Table ”; Previous Next The DROP TABLE statement is used to remove an existing table including all its triggers, constraints, permissions. Syntax Following is the Syntax of the DROP TABLE statement. ij> DROP TABLE table_name; Example Suppose you have a table named Student in the database. The following SQL statement deletes a table named Student. ij> DROP TABLE Student; 0 rows inserted/updated/deleted Since we have removed the table if we try to describe it, we will get an error as follows ij> DESCRIBE Student; IJ ERROR: No table exists with the name STUDENT Drop Table using JDBC program This section teaches you how to drop 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;passw ord=PASSWORD“ Follow the steps given below to drop 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 execute 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 drop 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; public class DropTable { 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(); //Executing the query String query = “DROP TABLE Employees”; stmt.execute(query); System.out.println(“Table dropped”); } } Output On executing the above program, you will get the following output − Table dropped Print Page Previous Next Advertisements ”;