Instructions for building Apache Derby on Windows

Not just the JDK, but Glassfish, WebSphere, Apache Hive, etc. Apache Derby has the impression that it is often bundled with Java-related tools.

Lightweight, embedded / server, Pure Java, so it can be used for all purposes. It is a database that is easy to use for learning.

Such a Derby construction procedure (from installation to data creation) is summarized, I wrote it from the idea that it will be possible to handle it as a DB that can be used more easily.

Install Derby

No installation is required to use the bundled Derby. If you do not have the bundled tools installed Or, if you want to install it separately, please download it from the following page.

http://db.apache.org/derby/derby_downloads.html

The latest version at the time of writing this article was 10.13.1.1. Click the link for that version to download the zip. db-derby-10.13.1.1-bin.zip

Unzip the above file. The save destination is arbitrary. In this manual, it has been moved to the following directory. C:\Tools\derby

Derby basic settings

Add the settings related to Derby to the system environment variables. DERBY_HOME=C:\Tools\derby PATH = (omitted);% DERBY_HOME% \ bin

Next, start the bin folder specified in PATH at the command prompt.

:: derby.Create properties and user/Set a password.
C:\Tools\derby\bin>type nul > derby.properties
C:\Tools\derby\bin>notepad derby.properties

Copy the following contents into Notepad. When you're done, save and close.

derby.properties


derby.authentication.provider=BUILTIN
derby.connection.requireAuthentication=true
derby.database.sqlAuthorization=true
derby.database.fullAccessUsers=admin
derby.database.readOnlyAccessUsers=guest
derby.user.admin=admin
derby.user.guest=guest
::Create a directory to save the DB
C:\Tools\derby\bin>md %DERBY_HOME%\dat

Creating a database

Now that you have the basic settings, it's time to create the database.

The Derby ij command is omitted here. Click here for details → Java DB memo You can also check it with the help command of ij, so if you are interested, there. However, the ij command absolutely has a; (semicolon) at the end of the sentence, so be careful from the beginning. Even if you forget the;, you can execute the command by pressing; on the next line and pressing Enter, so don't rush.

This time, write the command to create the database, table, and data in the sql file, Take the style of executing the sql file with the ij command.

database-create.sql


connect 'jdbc:derby:c:/Tools/derby/dat/test;user=admin;password=admin;create=true';
:: database-create.Move to the directory where sql is stored
C:\Tools\derby\bin>cd D:\workspace\script
::Creating a database.
D:\workspace\script>ij.bat database-create.sql

Creating a table

Next, create a table. In Derby, if TABLE exists, there is no statement to execute DROP, so it is necessary for a person to judge it. Of course, if you create such a function, it will be solved, but this time we will not deal with it.

users-create.sql


connect 'jdbc:derby:c:/Tools/derby/dat/test;user=admin;password=admin';

MAXIMUMDISPLAYWIDTH 15;

--DROP TABLE users if needed

show tables in admin;

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY
        GENERATED ALWAYS AS IDENTITY
        (START WITH 1, INCREMENT BY 1),
    login_id VARCHAR(20) NOT NULL,
    password VARCHAR(20) NOT NULL,
    user_name VARCHAR(20) NOT NULL,
    birthday CHAR(8),
    email VARCHAR(40) NOT NULL,
    address VARCHAR(200),
    credit_card_number CHAR(16),
    authority_type CHAR(1) NOT NULL,
    is_deleted CHAR(1) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by VARCHAR(20) NOT NULL
);

CREATE UNIQUE INDEX ui_users_01 ON users (
    login_id
);
CREATE UNIQUE INDEX ui_users_02 ON users (
    email
);
CREATE UNIQUE INDEX ui_users_03 ON users (
    credit_card_number
);

show tables in admin;
describe users;

commit;
::Create users table
D:\workspace\script>ij.bat users-create.sql

Insert record

Insert the record at the end.

users-insert.sql


connect 'jdbc:derby:c:/Tools/derby/dat/test;user=admin;password=admin';

MAXIMUMDISPLAYWIDTH 15;

select * from users;

INSERT INTO users VALUES 
(DEFAULT, 'sj', 'berkay', 'sercan', '20000318', '[email protected]',
'19 Mc Cabe Street', '1234567890123456', '1', '0', current_timestamp, current_timestamp, 'admin');
INSERT INTO users VALUES 
(DEFAULT, 'br', 'ugur', 'oljay', '19980322', '[email protected]',
'19 Mc Cabe Street 2145', '1234567890123450', '1', '0', current_timestamp, current_timestamp, 'admin');

select * from users;

commit;
::Insert data into the users table.
D:\workspace\script>ij.bat users-insert.sql

As you can see, Derby's SQL can be written in almost standard SQL. The difference in DML is that there is no LIMIT clause (the ROW_NUMBER function or the OFFSET / FETCH clause is used instead). Only "CASE conditional expression WHEN" is supported. ("CASE WHEN ~" cannot be used). Please refer to the manual for the function. I don't have COALESCE or REPLACE.

reference

Java DB memo [developerWorks --Development with Apache Derby](http://www.ibm.com/developerworks/jp/views/opensource/libraryview.jsp?search_by=Apache+Derby+%E3%82%92%E4%BD%BF % E7% 94% A8% E3% 81% 97% E3% 81% 9F% E9% 96% 8B% E7% 99% BA) Derby Reference Manual

Recommended Posts

Instructions for building Apache Derby on Windows
Build ffmpeg 4.3.1 on Ubuntu for Windows
How to use Apache Derby on Eclipse
Introducing Docker Desktop for Windows on WSL2
Building Java Web Applications on Windows Server 2016
Building a Ruby environment for classes on Mac
Operate Docker Desktop for Windows on Linux (WSL)
I tried using Docker Desktop for Windows on Windows 10 Home
Install Corretto 8 on Windows
Apache2 on Ubuntu20.04 LTS
M.S. docker on Windows
Difficulties in building a Ruby on Rails environment (Windows 10) (SQLite3)
Measures for permissions when building MySQL with Docker on WSL2