This time, I would like to explain the basic usage of Sql2o, which is a library for Java database access.
I knew Sql2o in the first place because I used Sql2o for DB access in the guidelines of Spark Framework.
If Spark Framework, which is a framework for simple microservices, is used, Sql2o is also simple, so I tried using it.
For more information on Spark Framework, please refer to [Easy Microservices with Spark Framework!".
A very simple Java library for database access, described on the Official Site (https://www.sql2o.org/) as follows:
Sql2o is a small Java library, that makes it easy to execute sql statements against your JDBC compliant database.
There is Rating: star: 642 on github. (As of April 1, 2018)
As stated on this page, it claims that the performance of SELECT is very high.
It contains comparison results with other major database access libraries (ʻApache DbUtils, MyBatis, Spring JdbcTemplate`, etc.). If you are interested, please take a look.
You can get the sql2o library from the maven repository. Add the rDBMS jdbc driver to be used to the dependency as well. In the sample, we will use PostgreSQL.
pom.xml
<dependency>
<groupId>org.sql2o</groupId>
<artifactId>sql2o</artifactId>
<version>1.5.4</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.4-1201-jdbc41</version>
</dependency>
Create a table to be used in the sample.
create_table.ddl
CREATE TABLE todo
(
todo_id character(36) NOT NULL,
todo_title character(100),
created_at timestamp without time zone NOT NULL,
finished character(1) NOT NULL,
CONSTRAINT todo_pk PRIMARY KEY (todo_id)
)
Defines a class that holds the data in the table. There is nothing special and it is implemented as a POJO.
It is easy to automatically generate the constructor and toString () method with the function of Eclipse.
Todo.java
package com.example.spark.demo;
import java.io.Serializable;
import java.util.Date;
public class Todo implements Serializable {
private static final long serialVersionUID = 1L;
private String todoId;
private String todoTitle;
private Date createdAt;
private boolean finished;
public Todo() {
}
public Todo(String todoId, String todoTitle, Date createdAt,
boolean finished) {
super();
this.todoId = todoId;
this.todoTitle = todoTitle;
this.createdAt = createdAt;
this.finished = finished;
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("Todo [todoId=");
builder.append(todoId);
builder.append(", todoTitle=");
builder.append(todoTitle);
builder.append(", createdAt=");
builder.append(createdAt);
builder.append(", finished=");
builder.append(finished);
builder.append("]");
return builder.toString();
}
// setter, getter omitted
}
First of all, let's take the simplest "get a single record" as an example, and here I would like to explain mainly the flow when using Sql2o.
Sql2oDemo.java
package com.example.spark.demo;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import org.sql2o.Connection;
import org.sql2o.Query;
import org.sql2o.Sql2o;
public class Sql2oDemo2 {
public static void main(String[] args) {
//★ Point 1
// 1. create sql2o instance
Sql2o sql2o = new Sql2o("jdbc:postgresql://127.0.0.1:5432/demodb", // url
"username", // user
"password"); // pass
//★ Point 2
// 2. set default column mappings (optional)
Map<String, String> colMaps = new HashMap<String, String>();
colMaps.put("TODO_ID", "todoId");
colMaps.put("TODO_TITLE", "todoTitle");
colMaps.put("CREATED_AT", "createdAt");
sql2o.setDefaultColumnMappings(colMaps);
//★ Point 3
// 3. get connection
try (Connection connection = sql2o.open()) {
//★ Point 4
// 4. create query
String queryText = "SELECT todo_id, todo_title, created_at, finished FROM todo"
+ " WHERE created_at = (SELECT max(created_at) FROM todo)";
Query query = connection.createQuery(queryText);
//★ Point 5
// 5. execute query
Todo todo = query.executeAndFetchFirst(Todo.class);
System.out.println(todo);
}
}
}
** ★ Point 1 **
Create an object of Sql2o with the new operator.
The arguments of the constructor are as follows.
After that, the Sql2o object generated here will be used.
**(Caution)
Some constructors take DataSource as an argument. It is recommended to use this when using Sql2o in the web application. ** **
** ★ Point 2 **
If the column name of the table to be accessed and the field name of the class match, Sql2o will do the mapping automatically (if the data type is convertible).
If the names are different, you need to match the names in the SQL ʻAS clause, but you can use setDefaultColumnMappings` to preconfigure this name translation rule.
** ★ Point 3 **
★ Use the ʻopen ()method from theSql2oobject created at point 1 to get a connection. As recommended in the guidelines, let's get a connection withtry-with-resourcesto close it automatically. This is the routine processing flow when usingSql2o`.
** ★ Point 4 **
Create an object of ʻorg.sql2o.Querythat defines the SQL to execute. The definition method is the same for both the reference system and the update system, and the SQL to be issued is defined asString. ★ Use the createQuery ()method from the connection obtained in point 3 to create aQuery` object.
To set parameters in SQL, refer to "[3.4. Set Parameters](# 34-Set Parameters)".
** ★ Point 5 **
SQL is executed by calling the method of the Query object generated in ★ point 4.
The method to be used differs depending on the SQL to be issued. The main methods are described below.
To get a single record, use the ʻexecuteAndFetchFirst method. Specify the data type (class) of the return value in the argument. ★ If the mapping is done correctly at point 2, you can easily get the acquired record as an object. Note that, as the name of ʻexecuteAndFetchFirst suggests, even if the SQL execution result is multiple records, no error will occur and the first record will be returned.
// 4. create query
String queryText = "SELECT todo_id, todo_title, created_at, finished FROM todo"
+ " WHERE created_at = (SELECT max(created_at) FROM todo)";
Query query = connection.createQuery(queryText);
// 5. execute query
Todo todo = query.executeAndFetchFirst(Todo.class);
System.out.println(todo);
To get multiple records, use the createQuery method.
Specify the data type (class) of the return value in the argument.
The return value will be java.util.List of the data type (class) specified by the argument.
If the search result is 0, it will be List with 0 elements.
// 4. create query
String queryText2 = "SELECT todo_id, todo_title, created_at, finished FROM todo";
Query query2 = connection.createQuery(queryText2);
// 5. execute query
List<Todo> todoList = query2.executeAndFetch(Todo.class);
System.out.println(todoList);
The method of embedding parameters when constructing a SQL string can cause SQL injection.
Sql2o provides a ʻaddParameter method in Queryas a way to set parameters. The method is simple, just write: parameter name in the place where you want to use the SQL parameter, and set the value you want to embed as a parameter with ʻaddParameter.
// 4. create query
String queryText3 = "SELECT todo_id, todo_title, created_at, finished FROM todo WHERE todo_id=:todoId";
Query query3 = connection.createQuery(queryText3)
.addParameter("todoId", "e8a57ac5-0e79-4444-be8a-3a281a5c0943");
// 5. execute query
Todo todo2 = query3.executeAndFetchFirst(Todo.class);
System.out.println(todo2);
To execute update SQL, use the ʻexecuteUpdatemethod. This method returns aSql2oconnection as a return value. I think that the number of changed records may be required for update SQL. In that case, you can get the number with thegetResult` method of the connection.
// 4. create query
String updateQueryText = "INSERT INTO todo(todo_id, todo_title, created_at, finished)"
+ " VALUES (:todoId, :todoTitle, :createAt, :finished)";
Query query4 = connection.createQuery(updateQueryText)
.addParameter("todoId", UUID.randomUUID().toString())
.addParameter("todoTitle", "world!")
.addParameter("createAt", new Date())
.addParameter("finished", "0");
// 5. execute query
int effectiveCount = query4.executeUpdate().getResult();
System.out.println(effectiveCount);
I haven't talked about transactions so far, but it's a point to be aware of in actual system development.
Sql2o does not provide declarative transaction functionality.
Since there is a method that explicitly controls the transaction, the implementer uses this to control the transaction.
beginTransaction method of Sql2o (get the connection that started the transaction)commit methodrollback method// management transaction
try (Connection connection = sql2o.beginTransaction()) {
// first sql
String updateQuery = "UPDATE todo SET todo_title=:todoTitle WHERE todo_id = :todoId";
Query query1 = connection.createQuery(updateQuery)
.addParameter("todoTitle", "googbye.")
.addParameter("todoId",
"e7801fe3-6e67-41ee-abb9-4f01841a3bf0");
int updateCount = query1.executeUpdate().getResult();
System.out.println(updateCount);
// If you want to try it, let's raise an exception here.
// sql2o.open() : first sql is effective(committed)
// sql2o.beginTransaction() : first sql is ineffective(uncommitted)
// second sql
String deleteQuery = "DELETE FROM todo WHERE todo_title = :todoTitle";
Query query2 = connection.createQuery(deleteQuery)
.addParameter("todoTitle", "world!");
int deleteCount = query2.executeUpdate().getResult();
System.out.println(deleteCount);
// commit transaction
connection.commit();
}
**(Caution)
If you start a transaction with the beginTransaction method in try-with-resources and neither commit nor rollback is performed, rollback will be executed automatically. ** **
This is also mentioned in the guidelines.
This time, I explained the basic usage of Sql2o, which is a library for Java database access. I think it was a very simple function and usage. It's a bit lacking in complex database access processing for the enterprise, but isn't it just right for creating simple microservices?
Recommended Posts