|
Creating a Derby database and running SQL statements
Now, you will use the Derby ij
tool to load the Derby
database engine. You will use the Derby 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.
- Run the Derby 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.6
|
Windows
|
java -jar %DERBY_HOME%\lib\derbyrun.jar ij
ij version 10.6
|
- 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 Derby 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 Derby 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 Derby URL
attribute that is used to create a database. Derby 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:
|
