Derby: Run SQL commands using the client

In my previous post, I explained simple application using derby in embedded mode. In this post, I am going to explain how to use Derby in Client-server model.

Below step-by-step procedure explain the setup.

Step 1: Start Derby Server.

We need to run 'derbyrun.jar' file to start the server.

On Windows

java -jar %DERBY_HOME%\lib\derbyrun.jar server start

On Linux systems

java -jar $DERBY_HOME/lib/derbyrun.jar server start

C:\>java -jar "%DERBY_HOME%"\lib\derbyrun.jar server start
Wed Jun 28 16:37:16 IST 2017 : Security manager installed using the Basic server security policy.
Wed Jun 28 16:37:16 IST 2017 Thread[main,5,main] java.io.FileNotFoundException: C:\derby.log (Access is denied)
Wed Jun 28 16:37:17 IST 2017 : Apache Derby Network Server - 10.13.1.1 - (1765088) started and ready to accept connections on port 1527
Wed Jun 28 16:37:17 IST 2017 : Apache Derby Network Server - 10.13.1.1 - (1765088) started and ready to accept connections on port 1527

As you see the console, you can observe server started listening on port 1527.

Step 2: Create a directory ‘derby_example2’.

Step 3: Open other command prompt and navigate to the directory ‘derby_example2’.

Run the command ij.

ij command is presented in 'DERBY_HOME/bin' directory. Make sure you added 'DERBY_HOME/bin' to your system path.

ij tool is the client application that is used to connect to Derby server.

Type below command to create database.

CONNECT 'jdbc:derby://localhost:1527/organization;create=true';

C:\Users\Krishna\Documents\Study\Apache Derby\examples\derby_example2>ij
ij version 10.13
ij> CONNECT 'jdbc:derby://localhost:1527/organization;create=true';
ij>

Below table summarizes different token of above command.

Token	Description
CONNECT	CONNECT command is used Used to establish a connection to a database.
jdbc:derby	The JDBC protocol specification for the Derby driver.
localhost:1527	Local host and port where the server is running. If the server running on other machine, then give the IP address of the machine and the port where the server is running
organization	The name of the database. Since no filepath is specified, the database is created in the default working directory 'derby_samples'
;create=true	Create attribute is used to create a database. Derby does not have an SQL ‘create database’ command.
;	The semicolon is the ij command terminator.

Step 4: Create a table employee.

Syntax

CREATE TABLE table_name(

  column1 data_type,

  column2 data_type,

         ...

         ...

  columnn data_type,

  PRIMARY KEY(columns)

);

Use below command to create table employee.

CREATE TABLE employee (id INT, name VARCHAR(20));

ij> CREATE TABLE employee (id INT, name VARCHAR(20));
0 rows inserted/updated/deleted

Step 4: Insert data into the table ‘employee’.

Use below commands to insert records to the table ‘employee’.

INSERT INTO employee VALUES (1, 'Hari Krishna');

INSERT INTO employee VALUES (2, 'Gopi Battu');

INSERT INTO employee VALUES (3, 'Chamu Majety');

ij> INSERT INTO employee VALUES (1, 'Hari Krishna');
1 row inserted/updated/deleted
ij> INSERT INTO employee VALUES (2, 'Gopi Battu');
1 row inserted/updated/deleted
ij> INSERT INTO employee VALUES (3, 'Chamu Majety');
1 row inserted/updated/deleted

Step 5: Display the records from the table ‘employee’.

ij> SELECT * FROM employee;
ID         |NAME
--------------------------------
1          |Hari Krishna
2          |Gopi Battu
3          |Chamu Majety

3 rows selected

Step 6: Exit the client by typing the command exit;

Step 7: Stop the derby server by using below command.

On Windows machines

java -jar %DERBY_HOME%\lib\derbyrun.jar server shutdown

On Linux machines
java -jar $DERBY_HOME/lib/derbyrun.jar server shutdown

C:\Users\Krishna\Documents\Study\Apache Derby\examples\derby_example2>java -jar "%DERBY_HOME%"\lib\derbyrun.jar server shutdown
Wed Jun 28 16:47:56 IST 2017 : Apache Derby Network Server - 10.13.1.1 - (1765088) shutdown

Previous                                                 Next                                                 Home