[JAVA] let's try it! Apache Derby

Introduction

Apache Derby is an RDBMS implemented in Java. It is convenient because it is easy to use. This time, I will make a note of the introduction method and simple usage. (It's easy to set up and execute, but I got a JAVA_HOME error when starting ij ...)

Environment: Mac OS 10.15.7

Download Derby

It used to be in the JDK, but not in recent JDKs. You have to download it yourself.

• Download Apache Derby (https://db.apache.org/derby/derby_downloads.html).
• After downloading, unzip to a suitable folder. (Here, decompress to / Users / XXXX / derby)

Configuration

Put it in the bin under the installed folder in the PATH. Add / Users / XXXX / derby / bin to your .zprofile.

~/.zprofile

export PATH=/Users/XXXX/derby/bin:$PATH

Start ij

ij is a tool for creating Derby's database and executing commands. Run ij on terminal. If successful, the following will be displayed.

ij version 10.15
ij> 

At this time, the JAVA_HOME setting is not correct. Correct ij to deal with it.

ij

case "`uname`" in
  CYGWIN*) cygwin=true ;;
  Darwin*) darwin=true
           if [ -z "$JAVA_HOME" ] ; then
             # JAVA_HOME=/System/Library/Frameworks/JavaVM.framework/Home
             JAVA_HOME=$(/usr/libexec/java_home)
           fi
           ;;
esac

Change the setting location of JAVA_HOME in the middle row to $ (/ usr / libexec / java_home). You can now start it.

Creating a database

Execute the following command while ij is executed on the terminal.

ij> connect 'jdbc:derby:/Users/XXXX/db/test.db;create=true';

Attempts to open a file (database) called test.db under / Users / XXXX / db /. If create = true is added, the file will be created automatically if it does not exist. This will create (connect) the database.

Creating a table

Now that we have a connection to the database, it's time to create the table.

createTable.sql

CREATE TABLE USER_INFO (
    USER_ID VARCHAR(10) PRIMARY KEY NOT NULL,
    USER_NAME VARCHAR(20) NOT NULL,
    ADDRESS VARCHAR(200),
    POST_CODE VARCHAR(7),
    AGE INTEGER,
    BIRTHDAY DATE
);

Copy this and paste it into ij, or run it with the run command.

ij> run 'createTable.sql'

The table is now created.

JDBC connection

I think that it is often accessed by JDBC connection from an application.

JdbcDriver=org.apache.derby.jdbc.EmbeddedDriver
JdbcUrl=jdbc:derby:/Users/XXXX/db/test.db;		

The jdbcDriver is located in /lib/derbytools.jar where you installed Derby, so add it to your classpath. It can also be> derby.jar. It depends on the version.

command

Here are some useful commands that you often use

Table list

ij> show tables;

Display of table structure

ij> describe user_info;  

user_info is the table name

Executing the script file

ij>run filename;  

The file name is the full path or the relative path from the folder that started ij