[For super beginners] Mirage SQL super introduction

Introduction

I recently learned how to execute SQL in a Java application without writing it directly in Java. It's like so-called out-of-the-box SQL, but in my little development experience, the environment had already been built when I started development, so there were some things I didn't understand well, so I took the time to build the environment. I tried it. There seem to be various Java SQL execution tools (OR mappers), and there was an article that carefully compared them, so I personally chose the easiest one and played with it. , I will introduce the construction method at that time.

This time, I will introduce the external SQL using a library called MirageSQL. There are many others, so please see here.

1. Environmental introduction

First, I will introduce the environment used in this article.

1.1. Development environment

The development environment is here.

category mono version Remarks
Integrated development environment Eclipse 64bit 4.4 Luna
Database Oracle Database 64bit 11g(11.2.0.1.0)

1.2. Table

I don't write complicated SQL, so I use a single table. Describe the definition and DDL.

Column name Data type Number of digits PK NULL?
UNO NUMBER 4 ×
UNAME VARCHAR2 10
AGE NUMBER 7,2
BDATE DATE
■DDL
CREATE TABLE SCOTT.USR
(
	UNO                         NUMBER(4) NOT NULL,
	UNAME                       VARCHAR2(10),
	AGE                         NUMBER(7,2),
	BDATE                       DATE,
	CONSTRAINT PK_USER PRIMARY KEY (UNO)
);

2. Create a project

Use Eclipse to create a sample project. There are two ways to build the environment, one is to use Maven and the other is to build it by yourself. Maven is convenient, but it has some disadvantages that I can't think of (at least I am ...), so I will build it myself this time.

File> New> Java project Click to display the Create Project Wizard.

p1.jpg

Enter the project name and proceed to the next. The project name can be anything you like, but in the sample it is "SampleMirageSQLBasic".

p2.jpg

Click Create New Source Folder.

p3.jpg

Enter resources for the folder name and you're done.

p4.jpg

Confirm that the "resources" folder has been added and complete.

p5.jpg

"Sample Mirage SQL Basic" has been added to the Package Explorer. This is the tree just after the project is created.

p6.jpg

Next, create a folder to place the dependent libraries. Right-click the project> New> Click the folder.

p7.jpg

Enter the folder name in the dialog to complete. The folder name is "lib".

p8.jpg

The "lib" folder has been created.

p9.jpg

Next, deploy the libraries needed to use MirageSQL. In Maven, 6 jars are added as dependencies, but if you use it in the minimum configuration, you do not need to add it. Add only those with a circle in the required columns in the following table as dependent libraries. At the same time, the library used for the application log and the JDBC driver used for DB connection are also added.

jar Mandatory Remarks
javassist-3.21.0-GA.jar
miragesql-2.0.0.jar
slf4j-api-1.7.25.jar
ognl-3.2.3.jar
log4j-api-2.9.1.jar Used for log output.
log4j-core-2.9.1.jar Used for log output.
ojdbc8.jar Used to connect to the DB.
junit-4.12.jar It will be added if it is Maven, but it is not required.
hamcrest-core-1.3.jar It will be added if it is Maven, but it is not required.

Place the above library in the lib folder and add it to your build path. Select the added library and right-click> Build Path> Add to Build Path.

p1.jpg

All the jars placed in the reference library have been added. At this point, the project creation is complete.

p2.jpg

This time I built it by myself, but for reference, I will describe the description contents of pom.xml when building with Maven.

■pom.xml
<dependency>
	<groupId>com.miragesql</groupId>
	<artifactId>miragesql</artifactId>
	<version>2.1.0</version>
</dependency>

3. Implementation

Finally, implement the sample program.

3.1. Application settings

Configure application settings such as MirageSQL and logger.

3.1.1. Connection destination DB settings

Set the DB to connect to from the application. Place the property that defines the connection destination in the "resources" folder added when creating the project, and set it. The connection to the DB uses the MirageSQL function, but due to the limitation of the function, it seems that it is necessary to put the definition information in the root of the folder registered in the class path. Also, it seems that the file name should be "jdbc.properties".

■jdbc.properties
# -----------------------------------------------------------------

Mirage SQL connection destination DB setting

MirageSQL automatically detects the connection destination, so

Make sure to use the file name "jdbc.properties" in the classpath

Place it at the root of the registered folder.

# -----------------------------------------------------------------

JDBC driver to use

jdbc.driver=oracle.jdbc.driver.OracleDriver

Connection string of connection destination DB

jdbc.url=jdbc:oracle:thin:@localhost:1521/ORCL

#Connected user jdbc.user=scott #Password jdbc.password=tiger

3.1.2. Log output settings

Set the output of the application log. If you want to output the log to a file, please restore the comment out and use it.

■log4j2.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration>
<Configuration status="off">

<!-Appender settings->

			</PatternLayout>
		</Console>

		<RollingFile name="FILE" fileName="./log/appLog.log" filePattern="appLog-%d{yyyy-MM-dd}-%i.log">
			<TimeBasedTriggeringPolicy />
<!--			<SizeBasedTriggeringPolicy size="100MB" /> -->
			<DefaultRolloverStrategy max="3"/>
		</RollingFile>
	</Appenders>

<!-Logger settings used for output->

3.2. Implementation of database access processing

Let's do a simple search, insert, and update process.

3.2.1 SQL implementation

Let's implement search SQL. SQL can be placed anywhere as long as it is a folder registered in the class path, but in the sample, a new folder called "sql" is added to "resources" and SQL is placed there. Each file name

will do.

■SelectUsr.sql
select
	*
from
	USR
/*-------------------------------------------

This SQL is called 2WaySQL. To put it simply, 2WaySQL is a SQL that can be executed by pasting the SQL as it is, or even if the parameters are variable in the application. In addition, MirageSQL can implement dynamic SQL that enables or disables unnecessary rows each time depending on the contents of the specified parameters. If you embed the control statement in SQL in comment format, MirageSQL will be nice to you.

Implement INSERT and UPDATE with the same 2-way SQL. Please arrange in the order of SelectUsr.sql.

■InsertUsr.sql

--INSERT new record into user table insert into USR ( UNO , UNAME , AGE , BDATE ) values ( /UNO/1000 -- UNO NUMBER(4) NOT NULL , 'test02' -- ENAME VARCHAR2(10) , 0 -- AGE NUMBER(7,2) , SYSDATE -- BDATE DATE )

■UpdateUsr.sql

--Update user table update USR set AGE = /age/0 where UNO = /uno/1000

3.2.2. Creating an entity

Implement a class called an entity that will be the container for the retrieved data. Since this is just a container for data, there is no need to do anything other than the field to put the acquired record and the process to acquire and set it. In the sample, the toString method is overridden to make it easier to output the acquisition result. Right-click the "src" folder> New> Add the entity class from the class.

p3.jpg

Enter the package, name and you're done. The package name and class name do not have to match the sample.

p01.jpg

Describes the implementation of the added class. This time we will search all columns, so we have prepared a field to set the values of all columns. The columns defined in the actual table and the fields of the entity class are made to correspond by using annotations, and the key columns are added and annotated. By defining key columns with annotations, Insert and Update can be performed without using SQL.

■ Implementation of entity class package jp.co.sample.tarosa.entity;

import com.miragesql.miragesql.annotation.Column;
import com.miragesql.miragesql.annotation.PrimaryKey;
import com.miragesql.miragesql.annotation.PrimaryKey.GenerationType;

/**

/** username */ @Column(name = "UNAME") private String uName;

/ ** Age * / @Column(name = "AGE") private Double age;

/** birthday */ @Column(name = "BDATE") private String bDate;

	/**

// String representation assembly StringBuilder sb = new StringBuilder(); sb.append(this.uNo); sb.append(", "); sb.append(this.uName); sb.append(", "); sb.append(this.age); sb.append(", "); sb.append(this.bDate);

		return new String(sb);
	}
}

3.2.3 Implementation of database access processing

Implement database access processing. Add the class in the same way as the entity. The package and class names are as follows.

Package name: jp.co.sample.tarosa.dao Class name: UsrDao

We will implement DB search, insert, and update processing in this class.

■UsrDao.java
package jp.co.sample.tarosa.dao;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jp.co.sample.tarosa.entity.Usr;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import com.miragesql.miragesql.ClasspathSqlResource;
import com.miragesql.miragesql.SqlManager;
import com.miragesql.miragesql.SqlResource;
import com.miragesql.miragesql.session.Session;
import com.miragesql.miragesql.session.SessionFactory;

/**

// Generate session object. // Get an instance using MirageSQL class method // ------------------------------------------------------------- Session session = SessionFactory.getSession(); SqlManager sqlManager = session.getSqlManager();

		// -------------------------------------------------------------

// Start session // There is also a way to work with Spring // ------------------------------------------------------------- session.begin(); try { // ------------------------------------------------------------- // Start SQL record search // Generate SQL resources using Mirage SQL features // ------------------------------------------------------------- SqlResource selecttUsrSql = new ClasspathSqlResource("sql/SelectUsr.sql");

			// -------------------------------------------------------------

// Generate search condition parameters // Specify the employee number here // Specify the parameter as a string, even if it is a number // ------------------------------------------------------------- Map<String, Object> params = new HashMap<>(); params.put("uno", "9000");

			// -------------------------------------------------------------

// Generate SQL as a resource and pass parameters as Map. // ------------------------------------------------------------- Usr usr = sqlManager.getSingleResult(Usr.class, selecttUsrSql, params);

			// --------------------------------------------

// Output search results // -------------------------------------------- System.out.println(usr); System.out.println("--------------------------------------------");

			// -------------------------------------------------------------

// Next, set the user name as a parameter // ------------------------------------------------------------- params = new HashMap<>(); params.put("uname", "%TARO");

			// -------------------------------------------------------------

// Get multiple results because the user name is like search // ------------------------------------------------------------- List result = sqlManager.getResultList(Usr.class, selecttUsrSql, params);

			// -------------------------------------------------------------

// Output all search results // ------------------------------------------------------------- result.forEach(li -> System.out.println(li)); System.out.println("--------------------------------------------");

			// -------------------------------------------------------------

// Search all without parameters // ------------------------------------------------------------- result = sqlManager.getResultList(Usr.class, selecttUsrSql);

			// -------------------------------------------------------------

// Output all search results // ------------------------------------------------------------- result.forEach(li -> System.out.println(li));

			// --------------------------------------------

// commit session // -------------------------------------------- session.commit(); } catch(Exception e) { logger.error ("DB error", e);

// Rollback in case of error session.rollback(); } finally { // Be sure to release the session session.release(); }

logger.info ("User table search process finished"); }

	/**

// Start session session.begin(); try { // ------------------------------------------------------------ // Generate entity for record to INSERT // INSERT using Mirage SQL methods instead of SQL. // ------------------------------------------------------------ Usr usr = new Usr(new Long(1000), "test01", new Double(0), new SimpleDateFormat("yyyy-MM-dd").format(new Date())); SqlManager sqlManager = session.getSqlManager(); int result = sqlManager.insertEntity(usr); System.out.println ("Number of inserted in entity Number of inserted:" + result);

			// ------------------------------------------------------------

// INSERT using SQL // MirageSQL does not have the ability to issue INSERT SQL // INSERT using the update method. // ------------------------------------------------------------ Map<String, Object> params = new HashMap<>(); // Parameters are set in the same way as search params.put("uno", "1001"); SqlResource insertUsrSql = new ClasspathSqlResource("sql/insertUsr.sql"); result = session.getSqlManager().executeUpdate(insertUsrSql, params); System.out.println ("Number of inserted items by SQL Number of inserted items:" + result);

			session.commit();
		} catch(Exception e) {
			e.printStackTrace();
			session.rollback();
		} finally {
			session.release();
		}

logger.info ("User table insertion process finished"); }

	/**

// Start session session.begin();

		try {
			// ------------------------------------------------------------

// Generate entity for record to UPDATE // INSERT using Mirage SQL methods instead of SQL. // ------------------------------------------------------------ Usr usr = new Usr(new Long(1000), "test01", new Double(99), new SimpleDateFormat("yyyy-MM-dd").format(new Date())); SqlManager sqlManager = session.getSqlManager(); int result = sqlManager.updateEntity(usr); System.out.println ("Update count:" + result);

			// ------------------------------------------------------------

// UPDATE using SQL // MirageSQL does not have the ability to issue INSERT SQL // INSERT using the search method. // ------------------------------------------------------------ Map<String, Object> params = new HashMap<>(); // Parameters are set in the same way as search params.put("uno", "1001"); params.put("age", "300"); SqlResource updateUsrSql = new ClasspathSqlResource("sql/updateUsr.sql"); result = session.getSqlManager().executeUpdate(updateUsrSql, params); System.out.println ("Update count:" + result);

			session.commit();
		} catch(Exception e) {
			e.printStackTrace();
			session.rollback();
		} finally {
			session.release();
		}

logger.info ("Employee table update process finished"); } }

3.2.4. Implementation of main processing

Finally, the main process that performs the process implemented so far is implemented and completed. Follow the same procedure as before to add a class.

Package name: jp.co.sample.tarosa.main Class name: SampleMirageSQLBasic

■SampleMirageSQLBasic.java
import jp.co.sample.tarosa.dao.UsrDao;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

/**

// Data access object generation UsrDao dao = new UsrDao();

		// -------------------------------------------------------------

// Search processing call // ------------------------------------------------------------- dao.selectUsr();

		// -------------------------------------------------------------

// Insertion call // ------------------------------------------------------------- dao.insertUsr();

		// -------------------------------------------------------------

// Update processing call // ------------------------------------------------------------- dao.updateUsr();

logger.info ("Main processing finished"); } }

3.2.5. Implementation completed

At this point, the implementation of the sample program is complete. Finally, I will list the tree of the completed project. Make sure your project has the following tree.

p04.jpg

■ Tree (text) Project root ├─src │ └─jp │ └─co │ └─sample │ └─tarosa │ ├─dao │ │ UsrDao.java │ │ │ ├─entity │ │ Usr.java │ │ │ └─main │ SampleMirageSQLBasic.java │ ├─resources │ │ jdbc.properties │ │ log4j2.xml │ │ │ └─sql │ InsertUsr.sql │ SelectUsr.sql │ UpdateUsr.sql │ ├─lib │ javassist-3.21.0-GA.jar │ log4j-api-2.9.1.jar │ log4j-core-2.9.1.jar │ miragesql-2.0.0.jar │ ognl-3.2.3.jar │ ojdbc8.jar │ slf4j-api-1.7.25.jar │ └─log * Added for log output

4. Run

Right-click the project> Execute> Execute the sample in the Java application.

p1.jpg

■ Console output [2018-09-10 21: 03: 04.829], INFO, main, jp.co.sample.tarosa.main.SampleMirageSQLBasic, Main Start processing [2018-09-10 21: 03: 04.833], INFO, main, jp.co.sample.tarosa.dao.UsrDao, user table search process started SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder". SLF4J: Defaulting to no-operation (NOP) logger implementation SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details. 9000, ATARO, 20.0, 1990-01-01 00:00:00.0 -------------------------------------------- 9003, DTARO, 30.0, 1980-08-01 00:00:00.0 9000, ATARO, 20.0, 1990-01-01 00:00:00.0 -------------------------------------------- 9003, DTARO, 30.0, 1980-08-01 00:00:00.0 9000, ATARO, 20.0, 1990-01-01 00:00:00.0 9001, BJIRO, 21.0, 1989-05-25 00:00:00.0 9002, CSABURO, 19.0, 1991-12-31 00:00:00.0 [2018-09-10 21: 03: 07.043], INFO, main, jp.co.sample.tarosa.dao.UsrDao, user table search process finished [2018-09-10 21: 03: 07.043], INFO, main, jp.co.sample.tarosa.dao.UsrDao, user table insertion process started Number of inserted items in entity Number of inserted items: 1 Number of items inserted by SQL Number of items inserted: 1 [2018-09-10 21: 03: 07.168], INFO, main, jp.co.sample.tarosa.dao.UsrDao, User table insertion process finished [2018-09-10 21: 03: 07.168], INFO, main, jp.co.sample.tarosa.dao.UsrDao, user table update process started Number of updates: 1 Number of updates: 1 [2018-09-10 21: 03: 07.250], INFO, main, jp.co.sample.tarosa.dao.UsrDao, Employee table update process completed [2018-09-10 21: 03: 07.250], INFO, main, jp.co.sample.tarosa.main.SampleMirageSQLBasic, Main Processing completed

■ Table before update p02.jpg

■ Updated table p03.jpg

At the end

This is the end of the basics of implementing out-of-the-box SQL using Mirage SQL. This time, it's just the basic usage, so I implemented everything by myself, but for transaction control, there is also a way to cooperate with spring. If you use spring, spring will do it for you without you having to implement session start etc. by yourself. This requires knowledge of spring separately, so I will omit it in this article. If I have some free time, I will write an article about how to implement it in cooperation with spring, so please wait for a while about how to cooperate with spring.

EX. Reference article

Here are some reference articles that helped me study Mirage SQL and even write this article. I am grateful to the author and the administrator for writing the reference article.

-Mirage SQL ~ Data Access Library for Java using 2Way SQL -Mirage SQL Official Wiki

Recommended Posts

[For super beginners] Mirage SQL super introduction
[For super beginners] DBUnit super introduction
[For super beginners] Ant super introduction
[For super beginners] Maven super introduction
[For super beginners] Struts2 Super Primer --2018 Edition
Let's learn SQL SELECT statement for beginners
[For super super beginners] What is object orientation?
Ractor super introduction
[For super beginners] How to use autofocus: true
[Introduction to Java] Basics of java arithmetic (for beginners)
Let's use Java New FileIO! (Introduction, for beginners)
Introduction to Java for beginners Basic knowledge of Java language ①
Groovy super easy introduction
How to use GitHub for super beginners (team development)
Scraping for beginners (Ruby)
Java debug execution [for Java beginners]
[Java] Basic statement for beginners
(For beginners) [Rails] Install Devise
More usable Enumerable for beginners
Java for beginners, data hiding
Java application for beginners: stream