[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.

1.2. Table

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

■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.

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

Click Create New Source Folder.

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

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

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

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

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

The "lib" folder has been created.

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.

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.

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

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

• SelectUsr.sql
• InsertUsr.sql
• UpdateUsr.sql

will do.

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

• From BEGIN to the end,
• When all the processing in the box is gone
• Because it will be strange as SQL grammar
• This section automatically disappears. --------------------------------------------/ /BEGIN/ where /-------------------------------------------------
• Only when the parameter uno is not null
• A row-enabled control.
• In Mirage SQL, both dynamic SQL control and parameter definition
• Write in comment format. --------------------------------------------------*/ /IF uno != null/ UNO = /uno/0 /END/ /IF uname != null/ and UNAME like /uname/'' /END/ /END/

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.

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

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;

/**

• [User Table Entity]
  *
  

• Use annotations to match DB columns and fields.
  • @author tarosa0001 */ public class Usr { / ** User number * / @PrimaryKey(generationType = GenerationType.APPLICATION) @Column(name = "UNO") private Long uNo;

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

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

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

	/**

• [Constructor]
  *
  

• Use arguments to set fields for user entities.

• @param uNo User number

• @param uName Username

• @param age age

• @param bDate Birthday */ public Usr(Long uNo, String uName, Double age, String bDate) { this.uNo = uNo; this.uName = uName; this.age = age; this.bDate = bDate; }

   /**
  

• [User number acquisition process]
  

• Get the user number.
  

• @return uNo user number */ public Long getuNo() { return uNo; }

   /**
  

• [User number setting process]
  

• Set the user number.
  

• @param uNo User number */ public void setuNo(Long uNo) { this.uNo = uNo; }

   /**
  

• [User name acquisition process]
  

• Get the user name.
  

• @return uName username */ public String getuName() { return uName; }

   /**
  

• [User name setting process]
  

• Set the user name.
  

• @param uName Username */ public void setuName(String uName) { this.uName = uName; }

   /**
  

• [Age acquisition process]
  

• Get age.
  

• @return age age */ public Double getAge() { return age; }

   /**
  

• [Age setting process]
  

• Set the age.
  

• @param age age */ public void setAge(Double age) { this.age = age; }

   /**
  

• [Birthday acquisition process]
  

• Get a birthday.
  

• @return bDate birthday */ public String getbDate() { return bDate; }

   /**
  

• [Birthday setting process]
  

• Set your birthday.
  

• @param bDate Birthday */ public void setbDate(String bDate) { this.bDate = bDate; }

   /**
    * {@inheritDoc}
    */
   @Override
   public String toString() {
  

// 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;

/**

• [User table access class]
  *
  

• A class that performs operations on the user table.

  • @author tarosa0001 */ public class UsrDao { / ** Logger * / private Logger logger = LogManager.getLogger(UsrDao.class);

    /**

• [User table search process]
  *
  

• Search the user table. */ public void selectUsr() { logger.info ("Start user table search process");

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

// 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"); }

	/**

• [User table insertion process]
  *
  

• Insert a record into the user table. */ public void insertUsr() { logger.info ("Start user table insertion process");

   	Session session = SessionFactory.getSession();
  

// 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"); }

	/**

• [User table update process]
  *
  

• Update the record in the user table. */ public void updateUsr() { logger.info ("Start user table update process");

   	Session session = SessionFactory.getSession();
  

// 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;

/**

• [Main class of Mirage SQL sample]
  *
  

• Mirage A class with SQL sample Main processing.
  • @author tarosa0001 */ public class SampleMirageSQLBasic { / ** Logger * / private static Logger logger = LogManager.getLogger(SampleMirageSQLBasic.class);

    /**

• [Main processing]
  *
  

• Main processing of Mirage SQL sample.
  
• @param args command arguments */ public static void main(String[] args) { logger.info ("Main processing started");

// 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.

■ 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.

■ 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

■ Updated table

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