Nablarch SQL Executor¶

Expected usage of this tool¶

To use this tool, you need to configure the DB and build it with Maven. Since the pre-built tool is distributable, only one member of the project needs to do this work.

This tool is intended to be used in the following ways.

• The member in charge of building the project’s environment builds and distributes the SQL Executor.
• The distributed files must be used by the designers.

Pre-built tools can be used if you have Java and a DB connection environment.

[1]

Selecting the DB connection method¶

This tool can take the following two methods for DB connection.

• All users connect to the project common DB
• Each user connects to a local DB

Figure of All users connect to the project common DB:

[1]

Figure of Each user connects to a local DB:

[1]

Limitations¶

This tool has the following limitations. Therefore, if you want to execute these SQLs, use the SQL execution environment of the database instead of this tool.

• SQL that starts with a WITH clause cannot be executed
• Cannot include , in the IN clause
• Cannot search for a DATETIME literal

Prerequisites¶

The prerequisites for building and distributing the tool are listed below.

• Firefox or Chrome installed.
• Nablarch development environment set up.
• If you use a RDBMS that does not have a JDBC driver in the Maven Central Repository, the JDBC driver must have been registered in the Project Local Repository or Local Repository. See File registration to the Maven repository for registration instructions.

Get the source code¶

Clone the repository published on the following site.

https://github.com/nablarch/sql-executor (external site)

DB configuration change¶

Change the settings according to the RDBMS being used.

db.url=jdbc:h2:./h2/db/SAMPLE
db.user=SAMPLE
db.password=SAMPLE

# jdbc:oracle:thin:@Host name: port number: database SID
db.url=jdbc:oracle:thin:@localhost:1521/xe
db.user=sample
db.password=sample

# jdbc:postgresql://Host name: Port number/database name
db.url=jdbc:postgresql://localhost:5432/postgres
db.user=sample
db.password=sample

# jdbc:db2://Host name: Port number/database name
db.url=jdbc:db2://localhost:50000/SAMPLE
db.user=sample
db.password=sample

# jdbc:sqlserver://Host name: Port number;instanceName= Instance name
db.url=jdbc:sqlserver://localhost:1433;instanceName=SQLEXPRESS
db.user=SAMPLE
db.password=SAMPLE

<dependencies>
  <!-- Middle is omitted -->

  <!--使用するRDBMSにあわせて、下記JDBCドライバの dependency を更新してください。 -->
  <dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.2.220</version>
    <scope>runtime</scope>
  </dependency>
</dependencies>

<dependencies>
  <!-- Middle is omitted -->

  <!--使用するRDBMSにあわせて、下記JDBCドライバの dependency を更新してください。 -->
  <dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <version>23.2.0.0</version>
    <scope>runtime</scope>
  </dependency>
</dependencies>

<dependencies>
  <!-- Middle is omitted -->

  <!--使用するRDBMSにあわせて、下記JDBCドライバの dependency を更新してください。 -->
  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.2</version>
    <scope>runtime</scope>
  </dependency>
</dependencies>

<dependencies>
  <!-- Middle is omitted -->

  <!--使用するRDBMSにあわせて、下記JDBCドライバの dependency を更新してください。 -->
  <dependency>
    <groupId>com.ibm.db2</groupId>
    <artifactId>jcc</artifactId>
    <version>11.5.9.0</version>
    <scope>runtime</scope>
  </dependency>
</dependencies>

<!-- データソース設定 -->
<!-- Data Source Configuration -->
<component name="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
  <!-- JDBC driver class name設定 -->
  <!-- Configure JDBC driver class name -->
  <!-- TODO: Database接続情報を変更する場合、ここを修正します -->
  <!--  If you want to change the Database connection information, modify this.-->
  <property name="driverClassName"
            value="org.h2.Driver" />
  <!-- Middle is omitted -->
</component>

<!-- Database接続用設定 -->
<!-- Configuration for Database Connection -->
<component name="connectionFactory"
    class="nablarch.core.db.connection.BasicDbConnectionFactoryForDataSource">
  <!-- Middle is omitted -->
  <property name="dialect">
    <!-- Dialect class name設定 -->
    <!-- Configure Dialect class name -->
    <!-- TODO: Databaseを変更する場合、ここを修正します。-->
    <!--  If you want to change the database, modify this.-->
    <component class="nablarch.core.db.dialect.H2Dialect"/>
  </property>
</component>

How to confirm launch¶

Execute the following command.

mvn compile exec:java

Then launch the browser and display http://localhost:7979/index.html.

How to create the distribution file¶

Execute the following command.

mvn package

Can use the tool without Git or Maven by distributing sql-executor-distribution.zip, which is created directly under target.

Prerequisites¶

The prerequisites for using the tool are listed below.

• The version of Java used in the project be installed.
• Be able to connect to the database specified in DB configuration change.
• Firefox or Chrome installed.

How to launch the distributed file¶

Unzip the distributed sql-executor-distribution.zip.

Run the file sql-executor.bat under sql-executor-distribution/sql-executor. Double-click the file or launch it from the command prompt.

sql-executor.bat

A case where you want to connect to a DB other than the one set up at the time of distribution¶

Edit the file sql-executor.bat. Configuration items are as follows.

As an example, the editing method when connecting to db.url=jdbc:h2:./h2/db/SAMPLE , db.user=SAMPLE, db.password=SAMPLE is as follows.

cd /d %~dp0

start java -Ddb.url=jdbc:h2:./h2/db/SAMPLE -Ddb.user=SAMPLE -Ddb.password=SAMPLE -jar sql-executor.jar （omitted）
cmd /c start http://localhost:7979/index.html

See FAQ , if nothing happens and the process ends abnormally.

Syntax of SQLExecutor¶

Methods of writing IN clause¶

Need to enclose the condition in [] to execute IN clause. Also, when multiple items are entered, they should be separated by ,.

Also, if the same variable name is specified in the $if special syntax and in the IN clause, the same value must be entered.

Examples are shown below.

If [] is not specified in the IN clause, the following errors are printed. java.lang.IllegalArgumentException: object type in field is invalid. valid object type is Collection or Array.

Warning

However, this tool does not allow you to use , as a search condition for IN clauses.

1970-12-11