/*************************************************************************
* *
* This file is part of the 20n/act project. *
* 20n/act enables DNA prediction for synthetic biology/bioengineering. *
* Copyright (C) 2017 20n Labs, Inc. *
* *
* Please direct all queries to act@20n.com. *
* *
* This program is free software: you can redistribute it and/or modify *
* it under the terms of the GNU General Public License as published by *
* the Free Software Foundation, either version 3 of the License, or *
* (at your option) any later version. *
* *
* This program is distributed in the hope that it will be useful, *
* but WITHOUT ANY WARRANTY; without even the implied warranty of *
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
* GNU General Public License for more details. *
* *
* You should have received a copy of the GNU General Public License *
* along with this program. If not, see <http://www.gnu.org/licenses/>. *
* *
*************************************************************************/
package com.act.lcms.db.model;
import com.act.lcms.db.io.DB;
import org.apache.commons.lang3.StringUtils;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
public abstract class BaseDBModel<T extends BaseDBModel> {
// These should be static, but static and abstract are incompatible! :(
public abstract String getTableName();
public abstract List<String> getAllFields();
public abstract List<String> getInsertUpdateFields();
protected abstract List<T> fromResultSet(ResultSet resultSet) throws SQLException, IOException, ClassNotFoundException;
protected List<String> makeInsertUpdateFields() {
List<String> allFields = getAllFields();
return Collections.unmodifiableList(allFields.subList(1, allFields.size()));
}
protected String makeGetByIDQuery() {
return StringUtils.join(new String[]{
"SELECT", StringUtils.join(getAllFields(), ','),
"from", getTableName(),
"where id = ?",
}, " ");
}
protected abstract String getGetByIDQuery();
public T getById(DB db, Integer id) throws SQLException, IOException, ClassNotFoundException {
try (PreparedStatement stmt = db.getConn().prepareStatement(getGetByIDQuery())) {
stmt.setInt(1, id);
try (ResultSet resultSet = stmt.executeQuery()) {
return expectOneResult(resultSet, String.format("id = %d", id));
}
}
}
/**
* Important: use this only when building constants to avoid the risk of SQL injection attacks.
* @param field The field to select.
* @return A query that binds a value to the specified field in a `WHERE` clause .
*/
protected String makeGetQueryForSelectField(String field) {
return StringUtils.join(new String[] {
"SELECT", StringUtils.join(this.getAllFields(), ','),
"from", this.getTableName(),
String.format("where %s = ?", field),
}, " ");
}
public abstract String getInsertQuery();
protected String makeInsertQuery() {
List<String> parameters = new ArrayList<>(getInsertUpdateFields().size());
for (String field : getInsertUpdateFields()) {
parameters.add("?");
}
return StringUtils.join(new String[] {
"INSERT INTO", getTableName(), "(", StringUtils.join(getInsertUpdateFields(), ", "), ") VALUES (",
StringUtils.join(parameters, ", "),
")",
}, " ");
}
public abstract String getUpdateQuery();
protected String makeUpdateQuery() {
List<String> parameters = new ArrayList<>(getInsertUpdateFields().size());
for (String field : getInsertUpdateFields()) {
parameters.add(String.format("%s = ?", field));
}
return StringUtils.join(new String[] {
"UPDATE", getTableName(), "SET",
StringUtils.join(parameters, ", "),
"WHERE",
"id = ?",
}, " ");
}
protected abstract void bindInsertOrUpdateParameters(PreparedStatement stmt, T parameterSource)
throws SQLException, IOException;
protected T insert(DB db, T toInsert) throws SQLException, IOException {
return insert(db, toInsert, null);
}
protected T insert(DB db, T toInsert, String errMsg) throws SQLException, IOException {
Connection conn = db.getConn();
try (PreparedStatement stmt = conn.prepareStatement(getInsertQuery(), Statement.RETURN_GENERATED_KEYS)) {
bindInsertOrUpdateParameters(stmt, toInsert);
stmt.executeUpdate();
try (ResultSet resultSet = stmt.getGeneratedKeys()) {
if (resultSet.next()) {
// Get auto-generated id.
int id = resultSet.getInt(1);
toInsert.setId(id);
return toInsert;
} else {
// TODO: log error here.
if (errMsg != null) {
System.err.format("ERROR: %s\n", errMsg);
} else {
System.err.format("ERROR: could not retrieve autogenerated key for inserted row\n");
}
return null;
}
}
}
}
public boolean update(DB db, T toUpdate) throws SQLException, IOException {
Connection conn = db.getConn();
try (PreparedStatement stmt = conn.prepareStatement(getUpdateQuery())) {
bindInsertOrUpdateParameters(stmt, toUpdate);
stmt.setInt(getInsertUpdateFields().size() + 1, toUpdate.getId());
return stmt.executeUpdate() > 0;
}
}
protected T expectOneResult(ResultSet resultSet, String queryErrStr) throws SQLException, IOException, ClassNotFoundException {
List<T> results = this.fromResultSet(resultSet);
if (results.size() > 1) {
throw new SQLException("Found multiple results where one or zero expected: %s", queryErrStr);
}
if (results.size() == 0) {
return null;
}
return results.get(0);
}
/* Use protected rather than private, as this class represents common attributes of all plate wells. Extending
* classes can/should have access to its fields, as its existence is simply an organizational convenience. */
protected Integer id;
public Integer getId() {
return id;
}
protected void setId(Integer id) {
this.id = id;
}
}