[Java] Mirage-Basic usage of SQL

I tried using Mirage-SQL as a framework for data access in a Java application, so I will introduce the basic usage.

• Maven is used as the project management tool.

Maven settings

pom.xml

  <dependency>
    <groupId>jp.sf.amateras.mirage</groupId>
    <artifactId>mirage</artifactId>
    <version>***</version>
  </dependency>

Also add a repository.

pom.xml

  <repository>
    <id>amateras</id>
    <name>amateras</name>
    <url>http://amateras.sourceforge.jp/mvn</url>
  </repository>

Connection settings to DB

Place the jdbc.properties file at the root of your classpath.

jdbc.properties

jdbc.driver=Driver class
jdbc.url=URL
jdbc.user=User name
jdbc.password=password

SQL file

Place the .sql file at the root of your classpath.

test.sql

SELECT *
FROM staff
 
/*BEGIN*/
WHERE
 
  /*IF username != null */
  staffname = /*staffname*/'testuser'
  /*END*/
 
  /*IF staffstatus != null */
  AND staffstatus = /*status*/1
  /*END*/
 
/*END*/

There are meaningful comments in between. This comment statement and the value after the comment are replaced with placeholders when the prepared statement is generated.

Since the bind variable is written as a comment of SQL and the value for testing is written immediately after that, this SQL can be executed as it is with the DB management tool or the command line. This is "2Way SQL".

Generally, when you operate a database from an application, you think that SQL is stored in a string type variable and executed, but Mirage-SQL can completely separate SQL from Java source code. Therefore, you can avoid the situation that "SQL cannot be confirmed until you try to execute it". Since SQL statements are not assembled dynamically, it is also a countermeasure against SQL injection.

It's a good idea to think of / * BEGIN * / ~ / * END * / as something that adjusts so that no syntax error occurs. (See github wiki for more information)

Entity class

Staff

import jp.sf.amateras.mirage.annotation.Column;
import jp.sf.amateras.mirage.annotation.Table;

@Table(name = "staff")//Can be omitted if the class name and table name are the same
public class Staff implements Serializable {
 
  @Column(name = "staffname")//Can be omitted if the column name and field name are the same
  private String staffname ;
 
  @Column(name = "staffstatus ")
  private int staffstatus ;

  //Below, the accessor method
  public void setStaffname (String staffname) {
    this.staffname= staffname;
  }

  public String getStaffname () {
    return staffname;
  }

  public void setStaffstatus (int staffstatus ) {
    this.staffstatus = staffstatus ;
  }

  public int getStaffstatus () {
    return staffstatus ;
  }

}

Data acquisition

Main

Session session = SessionFactory.getSession();
SqlManager sqlManager = session.getSqlManager();

session.begin();
try {
	Map<String, Object> params = new HashMap<>();
	params.put("status", 0);
	SqlResource selectUserSql = new ClasspathSqlResource("test.sql");
	List<Staff> results = sqlManager.getResultList(Staff.class, selectUserSql, params);

	for (Staff result : results ) {
		System.out.println(result.getStaffname());
	}
	session.commit();
} catch (Exception ex) {
	session.rollback();
} finally {
	session.release();
}

I used Map as the value to pass to the bind variable, but a class with the same name as the bind variable is fine.

• This article is based on github.