Работа с базой данных без боли и страданий
Уже очень давно я писал заметку про работу с базой данных с использованием Groovy. В этот раз хочу более глубоко раскрыть тему.
Итак, допустим, вам нужно работать с базой данных: выполнять запросы, вызывать хранимые процедуры, возможно, записывать какие-то данные. Стандартом работы с базами данных в 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
}На этот раз все.