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.
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
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
derby.connection.requireAuthentication to false.
I can create a DB without creating derby.properties separately.
Later, when I wanted to set a user or woke up, I thought it would be more convenient to create it in advance, so I wrote it.::Create a directory to save the DB
C:\Tools\derby\bin>md %DERBY_HOME%\dat
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
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 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.
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