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.
First, I will introduce the environment used in this article.
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) |
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)
);
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.
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.
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>
Finally, implement the sample program.
Configure application settings such as MirageSQL and logger.
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
# -----------------------------------------------------------------
# -----------------------------------------------------------------
jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521/ORCL
#Connected user jdbc.user=scott #Password jdbc.password=tiger
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->
Let's do a simple search, insert, and update process.
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
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;
/**
/** username */ @Column(name = "UNAME") private String uName;
/ ** Age * / @Column(name = "AGE") private Double age;
/** birthday */ @Column(name = "BDATE") private String bDate;
/**
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; }
/**
Get the user number.
@return uNo user number */ public Long getuNo() { return uNo; }
/**
Set the user number.
@param uNo User number */ public void setuNo(Long uNo) { this.uNo = uNo; }
/**
Get the user name.
@return uName username */ public String getuName() { return uName; }
/**
Set the user name.
@param uName Username */ public void setuName(String uName) { this.uName = uName; }
/**
Get age.
@return age age */ public Double getAge() { return age; }
/**
Set the age.
@param age age */ public void setAge(Double age) { this.age = age; }
/**
Get a birthday.
@return bDate birthday */ public String getbDate() { return bDate; }
/**
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);
}
}
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;
/**
A class that performs operations on the user table.
@author tarosa0001 */ public class UsrDao { / ** Logger * / private Logger logger = LogManager.getLogger(UsrDao.class);
/**
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
// -------------------------------------------------------------
// 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"); }
/**
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"); }
/**
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"); } }
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;
/**
@author tarosa0001 */ public class SampleMirageSQLBasic { / ** Logger * / private static Logger logger = LogManager.getLogger(SampleMirageSQLBasic.class);
/**
// 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"); } }
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
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
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.
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