Creating a Derby database and running SQL statements
Now, you will use the ij
tool to load the
database engine. You will use the embedded
driver to create and connect to the firstdb database. You
will also use a few basic SQL statements to create and populate a table.
Connection URLembedded drivercreate=true
ijDatabasecreatingconnecting
toDerbylogfile, message and
errorSQLcreate tableinsertselect
Run the ij
tool. If you included the DERBY_HOME/bin directory
in your PATH environment variable, type: ij Otherwise,
you can use the java command to start the ij tool.
Operating System
Command
UNIX (Korn Shell)
java -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.2
Windows
java -jar %DERBY_HOME%\lib\derbyrun.jar ij
ij version 10.2
Create the database and open a connection to the database using
the embedded driver.CONNECT 'jdbc:derby:firstdb;create=true';
- Description of connection command:
-
- connect
- The ij command to establish a connection to a database.
The connection URL
is enclosed in single quotation marks. An ij command can
be in either uppercase or lowercase.
- jdbc:derby:
- The JDBC protocol specification for the driver.
- firstdb
- The name of the database. The name can be any string. Because no filepath
is specified, the database is created in the default working directory (DERBYTUTOR).
- ;create=true
- The URL
attribute that is used to create a database. does
not have an SQL create database command.
- ;
- The semicolon is the ij command terminator.
Create a table with two columns using standard SQL.CREATE TABLE FIRSTTABLE
(ID INT PRIMARY KEY,
NAME VARCHAR(12));
0 rows inserted/updated/deleted
Insert three records.INSERT INTO FIRSTTABLE VALUES
(10,'TEN'),(20,'TWENTY'),(30,'THIRTY');
3 rows inserted/updated/deleted
Perform a simple select of all records in the table.SELECT * FROM FIRSTTABLE;
ID |NAME
------------------------
10 |TEN
20 |TWENTY
30 |THIRTY
3 rows selected
Perform a qualified select of the record with column ID=20.
SELECT * FROM FIRSTTABLE
WHERE ID=20;
ID |NAME
------------------------
20 |TWENTY
1 row selected
Optional: Create and populate additional tables and other schema
objects.
Load the SQL script ToursDB_schema.sql.
run 'ToursDB_schema.sql';
ij> ...
CREATE TABLE AIRLINES
(
AIRLINE CHAR(2) NOT NULL ,
AIRLINE_FULL VARCHAR(24),
BASIC_RATE DOUBLE PRECISION,
...
0 rows inserted/updated/deleted
... Other output messages not shown ...
Populate the tables with data by running the script loadTables.sql.
run 'loadTables.sql';
ij> run 'loadCOUNTRIES.sql';
ij> insert into COUNTRIES values ( 'Afghanistan','AF','Asia');
1 row inserted/updated/deleted
ij> insert into COUNTRIES values ( 'Albania','AL','Europe');
1 row inserted/updated/deleted
... Other output messages not shown ...
Exit the ij tool.exit;
You should be returned to the DERBYTUTOR directory.
Browse the most important files created by this activity:
- The derby.log file. This file is a message and error
log that, under normal circumstances, contains a set of startup messages and
a shutdown message. ----------------------------------------------------------------
2007-05-10 19:55:20.837 GMT:
Booting Derby version The Apache Software Foundation
- Apache Derby - 10.2.2.0 - (485682):
instance c013800d-0109-7f82-e11f-000000119a68
on database directory C:\Derby_10\db-derby-10.2.2.0-bin\derbytutor\firstdb
Database Class Loader started - derby.database.classpath=''
2007-05-104 20:00:12.640 GMT:
Shutting down instance c013800d-0109-7f82-e11f-000000119a68
----------------------------------------------------------------
- The firstdb database directory. Within the directory
are the subdirectories seg0 (containing the data files) and log (containing
the transaction log files).