The CREATE TABLE statement is used for creating a new table in Derby database.

Syntax

Following is the syntax of the 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)
);

Another way to create a table in Apache Derby is that you can specify the column names and data types using a query. The syntax for this is given below −

CREATE TABLE table_name AS SELECT * FROM desired_table WITH NO DATA;

Example

The following SQL statement creates a table named Student with four columns, where id is the primary key and it is auto generated.

ij> CREATE TABLE Student (
   Id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
   Age INT NOT NULL,
   First_Name VARCHAR(255),
   last_name VARCHAR(255),
   PRIMARY KEY (Id)
);
> > > > > > > 0 rows inserted/updated/deleted

The DESCRIBE command describes specified table by listing the columns and their details, if the table exists. You can use this command to verify if the table is created.

ij> DESCRIBE Student;
COLUMN_NAME |TYPE_NAME |DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ID |INTEGER |0 |10 |10 |AUTOINCRE&|NULL |NO
AGE |INTEGER |0 |10 |10 |NULL |NULL |NO
FIRST_NAME |VARCHAR |NULL|NULL|255 |NULL |510 |YES
LAST_NAME |VARCHAR |NULL|NULL|255 |NULL |510 |YES
4 rows selected

Create a Table using JDBC Program

This section teaches you how to create 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 create 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 create 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 CreateTable {
   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 = "CREATE TABLE Employees( "
         + "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
         + "Name VARCHAR(255), "
         + "Salary INT NOT NULL, "
         + "Location VARCHAR(255), "
         + "PRIMARY KEY (Id))";
         stmt.execute(query);
         System.out.println("Table created");
   }
}

Output

On executing the above program, you will get the following output

Table created

Apache Derby provides you tools such as sysinfo, ij and, dblook.

sysinfo tool

Using this tool, you can get information about Java and Derby environment.

Browse through the bin folder of Derby installation directory and execute the sysinfo command as shown below −

C:UsersMY_USER>cd %DERBY_HOME%/bin
C:Derbybin>sysinfo

On executing, it gives you system information about java and derby as given below −

------------------ Java Information ------------------
Java Version: 1.8.0_101
Java Vendor: Oracle Corporation
Java home: C:Program FilesJavajdk1.8.0_101jre
Java classpath: 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:Derbylibderby.jar;C:Derbylibderbyclient.jar;C:Derbylibderb
yLocale_cs.jar;C:DerbylibderbyLocale_de_DE.jar;C:DerbylibderbyLocale_es.j
ar;C:DerbylibderbyLocale_fr.jar;C:DerbylibderbyLocale_hu.jar;C:Derbylib
derbyLocale_it.jar;C:DerbylibderbyLocale_ja_JP.jar;C:DerbylibderbyLocale
_ko_KR.jar;C:DerbylibderbyLocale_pl.jar;C:DerbylibderbyLocale_pt_BR.jar;C
:DerbylibderbyLocale_ru.jar;C:DerbylibderbyLocale_zh_CN.jar;C:Derbylib
derbyLocale_zh_TW.jar;C:Derbylibderbynet.jar;C:Derbylibderbyoptionaltools
.jar;C:Derbylibderbyrun.jar;C:Derbylibderbytools.jar;;C:Derby/lib/derby.
jar;C:Derby/lib/derbynet.jar;C:Derby/lib/derbyclient.jar;C:Derby/lib/derbyto
ols.jar;C:Derby/lib/derbyoptionaltools.jar
OS name: Windows 10
OS architecture: amd64
OS version: 10.0
Java user name: Tutorialspoint
Java user home: C:UsersTutorialspoint
Java user dir: C:Derbybin
java.specification.name: Java Platform API Specification
java.specification.version: 1.8
java.runtime.version: 1.8.0_101-b13
--------- Derby Information --------
[C:Derbylibderby.jar] 10.14.2.0 - (1828579)
[C:Derbylibderbytools.jar] 10.14.2.0 - (1828579)
[C:Derbylibderbynet.jar] 10.14.2.0 - (1828579)
[C:Derbylibderbyclient.jar] 10.14.2.0 - (1828579)
[C:Derbylibderbyoptionaltools.jar] 10.14.2.0 - (1828579)
------------------------------------------------------
----------------- Locale Information -----------------
Current Locale : [English/United States [en_US]]
Found support for locale: [cs]
 version: 10.14.2.0 - (1828579)
Found support for locale: [de_DE]
 version: 10.14.2.0 - (1828579)
Found support for locale: [es]
 version: 10.14.2.0 - (1828579)
Found support for locale: [fr]
 version: 10.14.2.0 - (1828579)
Found support for locale: [hu]
 version: 10.14.2.0 - (1828579)
Found support for locale: [it]
 version: 10.14.2.0 - (1828579)
Found support for locale: [ja_JP]
 version: 10.14.2.0 - (1828579)
Found support for locale: [ko_KR]
 version: 10.14.2.0 - (1828579)
Found support for locale: [pl]
 version: 10.14.2.0 - (1828579)
Found support for locale: [pt_BR]
 version: 10.14.2.0 - (1828579)
Found support for locale: [ru]
 version: 10.14.2.0 - (1828579)
Found support for locale: [zh_CN]
 version: 10.14.2.0 - (1828579)
Found support for locale: [zh_TW]
 version: 10.14.2.0 - (1828579)
------------------------------------------------------
------------------------------------------------------

ijtool

Using this tool, you can run scripts and queries of apache Derby.

Browse through the bin folder of Derby installation directory and execute the ij command as shown below −

C:UsersMY_USER>cd %DERBY_HOME%/bin
C:Derbybin>ij

This will give you ij shell where you can execute derby command and scripts, as shown below −

ij version 10.14
ij>

Using help command, you can get the list of commands supported by this shell.

C:Derbybin>cd %DERBY_HOME%/bin
C:Derbybin>ij
ij version 10.14
ij> help;
Supported commands include:
 PROTOCOL ''JDBC protocol'' [ AS ident ];
 -- sets a default or named protocol
 DRIVER ''class for driver -- loads the named class
 CONNECT ''url for database'' [ PROTOCOL namedProtocol ] [ AS connectionName ];
 -- connects to database URL
 -- and may assign identifier
 SET CONNECTION connectionName; -- switches to the specified connection
 SHOW CONNECTIONS; -- lists all connections
 AUTOCOMMIT [ ON | OFF ]; -- sets autocommit mode for the connection
 DISCONNECT [ CURRENT | connectionName | ALL ];
 -- drop current, named, or all connections;
-- the default is CURRENT
 SHOW SCHEMAS; -- lists all schemas in the current database
 SHOW [ TABLES | VIEWS | PROCEDURES | FUNCTIONS | SYNONYMS ] { IN schema };
 -- lists tables, views, procedures, functions or
synonyms
 SHOW INDEXES { IN schema | FROM table };
 -- lists indexes in a schema, or for a table
 SHOW ROLES; -- lists all defined roles in the database,
sorted
 SHOW ENABLED_ROLES; -- lists the enabled roles for the current
 -- connection (to see current role use
 -- VALUES CURRENT_ROLE), sorted
 SHOW SETTABLE_ROLES; -- lists the roles which can be set for the
 -- current connection, sorted
 DESCRIBE name; -- lists columns in the named table
 COMMIT; -- commits the current transaction
 ROLLBACK; -- rolls back the current transaction
 PREPARE name AS ''SQL-J text -- prepares the SQL-J text
 EXECUTE { name | ''SQL-J text'' } [ USING { name | ''SQL-J text'' } ] ;
 -- executes the statement with parameter
-- values from the USING result set row
 REMOVE name; -- removes the named previously prepared
statement
 RUN ''filename -- run commands from the named file
 ELAPSEDTIME [ ON | OFF ]; -- sets elapsed time mode for ij
 MAXIMUMDISPLAYWIDTH integerValue;
 -- sets the maximum display width for
-- each column to integerValue
 ASYNC name ''SQL-J text -- run the command in another thread
 WAIT FOR name; -- wait for result of ASYNC''d command
 HOLDFORCONNECTION; -- sets holdability for a connection to HOLD
 -- (i.e. ResultSet.HOLD_CURSORS_OVER_COMMIT)
 NOHOLDFORCONNECTION; -- sets holdability for a connection to NO HOLD
 -- (i.e. ResultSet.CLOSE_CURSORS_AT_COMMIT)
 GET [SCROLL INSENSITIVE] [WITH { HOLD | NOHOLD }] CURSOR name AS ''SQL-J
query
 -- gets a cursor (JDBC result set) on the query
-- the default is a forward-only cursor with
holdability
 NEXT name; -- gets the next row from the named cursor
 FIRST name; -- gets the first row from the named scroll
cursor
 LAST name; -- gets the last row from the named scroll
cursor
 PREVIOUS name; -- gets the previous row from the named scroll
cursor
 ABSOLUTE integer name; -- positions the named scroll cursor at the
absolute row number
 -- (A negative number denotes position from the
last row.)
 RELATIVE integer name; -- positions the named scroll cursor relative to
the current row
 -- (integer is number of rows)
 AFTER LAST name; -- positions the named scroll cursor after the
last row
 BEFORE FIRST name; -- positions the named scroll cursor before the
first row
 GETCURRENTROWNUMBER name; -- returns the row number for the current
position of the named scroll cursor
 -- (0 is returned when the cursor is not
positioned on a row.)
 CLOSE name; -- closes the named cursor
 LOCALIZEDDISPLAY [ ON | OFF ];
 -- controls locale sensitive data representation
 EXIT; -- exits ij
 HELP; -- shows this message
Any unrecognized commands are treated as potential SQL-J commands and executed
directly.

dblooktool

This tool is used to generate Data Definition Language.

Browse through the bin folder of Derby installation directory and execute the dblook command as shown below −

C:UsersMY_USER>cd %DERBY_HOME%/bin
C:Derbybin>dblook -d myURL

Where, myURL is the connection URL of the database for which you need to generate DDL.

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.