Работа с базой данных без боли и страданий

Уже очень давно я писал заметку про работу с базой данных с использованием Groovy. В этот раз хочу более глубоко раскрыть тему.

sql+basis+data

Итак, допустим, вам нужно работать с базой данных: выполнять запросы, вызывать хранимые процедуры, возможно, записывать какие-то данные. Стандартом работы с базами данных в java является JDBC. Но вот код, написанный с использованием jdbc, выглядит не очень:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCExample1 {
  public static void main(String[] args) {
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    try{
      Class.forName("org.gjt.mm.mysql.Driver");
      con = DriverManager.getConnection("jdbc:mysql://localhost:3306/words",
           "words", "words");
      stmt = con.createStatement();
      rs = stmt.executeQuery("select * from word");
      while (rs.next()) {
        System.out.println("word id: " + rs.getLong(1) +
            " spelling: " + rs.getString(2) +
            " part of speech: " + rs.getString(3));
      }
    }catch(SQLException e){
      e.printStackTrace();
    }catch(ClassNotFoundException e){
      e.printStackTrace();
    }finally{
      try{rs.close();}catch(Exception e){}
      try{stmt.close();}catch(Exception e){}
      try{con.close();}catch(Exception e){}
   }
  }
}

Писать такой код очень не хотелось бы, тем более в тестовом фреймворке, который должен быть легковесным. Что же можно с этим сделать? Нужно использовать библиотеки, которые значительно облегчат жизнь.

1. JDBI

Имплементация этой библиотеки схожа с Groovy SQL. C использованием JBDI библиотеки можно будет писать такой вот код:

DataSource ds = JdbcConnectionPool.create("jdbc:h2:mem:test",
                                          "username",
                                          "password");
DBI dbi = new DBI(ds);
Handle h = dbi.open();
h.execute("create table something (id int primary key, name varchar(100))");
h.execute("insert into something (id, name) values (?, ?)", 1, "Brian");
String name = h.createQuery("select name from something where id = :id")
                    .bind("id", 1)
                    .map(StringMapper.FIRST)
                    .first();

assertThat(name, equalTo("Brian"));
h.close();

Очень не кисло и читабельно, более того - можно запросы мапить на объекты, что позволит еще больше упростить жизнь.

2. JOOQ

Библиотека, о которой я читал давно, но вот только сейчас руки дошли поработать с ней. Скажу сразу, я с ней просто игрался. Впечатление двоякое: во-первых, почему-то есть бесплатная и платная версия, во-вторых, она умеет делать кучу всего, чего только душе угодно, и из-за этого повышается сложность работы с ней. Пример кода:

select(KeyColumnUsage.CONSTRAINT_NAME, KeyColumnUsage.TABLE_NAME, KeyColumnUsage.COLUMN_NAME)
                .from(KEY_COLUMN_USAGE).join(TABLE_CONSTRAINTS)
                .on(KeyColumnUsage.TABLE_SCHEMA.equal(TableConstraints.TABLE_SCHEMA))
                .and(KeyColumnUsage.TABLE_NAME.equal(TableConstraints.TABLE_NAME))
                .and(KeyColumnUsage.CONSTRAINT_NAME.equal(TableConstraints.CONSTRAINT_NAME))
                .where(TableConstraints.CONSTRAINT_TYPE.equal(constraintType))
                .and(KeyColumnUsage.TABLE_SCHEMA.equal(getSchemaName()))
                .orderBy(KeyColumnUsage.TABLE_NAME.ascending(), KeyColumnUsage.ORDINAL_POSITION.ascending()).fetch()

Лично мое мнение: я бы не использовал ее на проекте, лучше уж что-то попроще, чем такие куски не совсем читабельного и плохо отлаживаемого кода.

3. Querydsl

Еще одна библиотека из разряда навороченных. Умеет делать кучу всего полезного. Код, который можно писать, выглядит вот так:

List<Person> persons = queryFactory.selectFrom(person)
  .where(
    person.firstName.eq("John"),
    person.lastName.eq("Doe"))
  .fetch();

Мое мнение: слишком уж навороченная. К тому же, документация достаточно запутанная. Не люблю, когда для чего-то простого нужно перечитать тонну документации.

4. Sql2o

Достаточно симпатичная библиотека, которая позволит вещи делать быстро и просто. На странице Github сами же разработчики пишут, что Sql2o является самой производительной библиотекой. Лично не проверял, оставил этот момент на совести разработчиков. Код можно писать такой:

public class Task{
    private int id;
    private String category;
    private Date dueDate;

    // getters and setters here
}

Sql2o sql2o = new Sql2o(DB_URL, USER, PASS);

String sql =
    "SELECT id, category, duedate " +
    "FROM tasks " +
    "WHERE category = :category";

try(Connection con = sql2o.open()) {
    List<Task> tasks = con.createQuery(sql)
        .addParameter("category", "foo")
        .executeAndFetch(Task.class);
}

Личное мнение: все достаточно просто и удобно, хорошая документация с четкими и понятными примерами. Одно удручает - вот эти вот строки SQL с конкатенациями =(

5. Groovy SQL

Не могу не написать про Groovy еще раз. Ну вот нравится мне этот язык за гибкость и отсутствие boilerplate.

Только теперь я расскажу о том, как я написал свою обертку над стандартым DataSet и добавил метод удаления, которого у стандартного класса нету.

Итак, чего хотелось? Хотелось уметь удалять записи из базы в таком вот виде:

employees.delete {it.id > 5}

Но такой возможности не было, делать select можно, а удалять нет. Что ж, пришлось написать такой вот класс:

class DataTable {

    @Delegate
    DataSet dataSet

    DataTable(Sql sql, Class<?> type) {
        dataSet = sql.dataSet(type)
    }

    def delete(Closure where) {
        def visitor = getSqlWhereVisitor(where)
        def whereClause = visitor.getWhere()
        def params = visitor.getParameters()
        def sql = "DELETE FROM ${dataSet.@table} WHERE " + whereClause
        dataSet.@delegate.executeUpdate(sql, params)
    }

    protected SqlWhereVisitor getSqlWhereVisitor(Closure where) {
        def visitor = new SqlWhereVisitor();
        visit(where, visitor);
        return visitor;
    }

    private void visit(Closure closure, CodeVisitorSupport visitor) {
        if (closure != null) {
            ClassNode classNode = closure.getMetaClass().getClassNode();
            if (classNode == null) {
                throw new GroovyRuntimeException(
                        "DataSet unable to evaluate expression. AST not available for closure: " + closure.getMetaClass().getTheClass().getName() +
                                ". Is the source code on the classpath?");
            }
            List methods = classNode.getDeclaredMethods("doCall");
            if (!methods.isEmpty()) {
                MethodNode method = (MethodNode) methods.get(0);
                if (method != null) {
                    Statement statement = method.getCode();
                    if (statement != null) {
                        statement.visit(visitor);
                    }
                }
            }
        }
    }

    def add(Map... maps) {
        for (row in maps) {
            dataSet.add(row)
        }
    }
}

После этого появилась возможность делать такие вот приятные штучки:

@Canonical
class Employee {
    Integer id
    String name
    String lastName
    Integer age
    Integer department
}

dbSettings = [
        url: 'jdbc:hsqldb:hsql://localhost/cookingdb',
        driver: 'org.hsqldb.jdbcDriver',
        user: 'sa',
        password: ''
]

def db = Sql.newInstance(dbSettings)
def employees = new DataTable(db, Employee)

employees.add(
        [id: 5, name: "vova", lastName: "ivliv"],
        [id: 8, name: "viktor", lastName: "lovliv"],
        [id: 1, name: "adam", lastName: "bomobm", age: 23, department: "5"]
)

employees.delete { it.id == 5 || it.id == 6 }

employees.findAll { it.id > 0 }.rows()

Один горький недостаток этого подхода - нельзя параметризовать запросы. Такой вот код не сработает:

def userId = 0
employees.findAll { it.id > userId }.rows()

Не очень удобно конечно, но это легко объезжается на велосипеде =)

def query = employees.findAll { it.id == ":id" && it.name == ":name" }.sql

db.eachRow(query, [id: 3, name: "ivan"]) {
    println it
}

На этот раз все.

Частные консультации и обучение