Java tutorial
/************************************************************************* * * * 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 com.act.utils.TSVParser; import org.apache.commons.lang3.StringUtils; import org.apache.commons.lang3.tuple.Pair; import java.io.IOException; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; import java.util.List; import java.util.Map; public class ConstructEntry extends BaseDBModel<ConstructEntry> { public static final String TABLE_NAME = "constructs"; protected static final ConstructEntry INSTANCE = new ConstructEntry(); public static ConstructEntry getInstance() { return INSTANCE; } private enum DB_FIELD implements DBFieldEnumeration { ID(1, -1, "id"), CONSTRUCT_ID(2, 1, "construct_id"), TARGET(3, 2, "target"), HOST(4, 3, "host"), // proteins and ko_locus are ignored for now. ; private final int offset; private final int insertUpdateOffset; private final String fieldName; DB_FIELD(int offset, int insertUpdateOffset, String fieldName) { this.offset = offset; this.insertUpdateOffset = insertUpdateOffset; this.fieldName = fieldName; } @Override public int getOffset() { return offset; } @Override public int getInsertUpdateOffset() { return insertUpdateOffset; } @Override public String getFieldName() { return fieldName; } @Override public String toString() { return this.fieldName; } public static String[] names() { DB_FIELD[] values = DB_FIELD.values(); String[] names = new String[values.length]; for (int i = 0; i < values.length; i++) { names[i] = values[i].getFieldName(); } return names; } } protected static final List<String> ALL_FIELDS = Collections.unmodifiableList(Arrays.asList(DB_FIELD.names())); // id is auto-generated on insertion. protected static final List<String> INSERT_UPDATE_FIELDS = INSTANCE.makeInsertUpdateFields(); @Override public String getTableName() { return TABLE_NAME; } @Override public List<String> getAllFields() { return ALL_FIELDS; } @Override public List<String> getInsertUpdateFields() { return INSERT_UPDATE_FIELDS; } @Override protected List<ConstructEntry> fromResultSet(ResultSet resultSet) throws SQLException { List<ConstructEntry> results = new ArrayList<>(); while (resultSet.next()) { Integer id = resultSet.getInt(DB_FIELD.ID.getOffset()); String constructId = resultSet.getString(DB_FIELD.CONSTRUCT_ID.getOffset()); String target = resultSet.getString(DB_FIELD.TARGET.getOffset()); String host = resultSet.getString(DB_FIELD.HOST.getOffset()); results.add(new ConstructEntry(id, constructId, target, host)); } return results; } @Override protected ConstructEntry expectOneResult(ResultSet resultSet, String queryErrStr) throws SQLException { List<ConstructEntry> results = 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); } protected static final String GET_BY_ID_QUERY = INSTANCE.makeGetByIDQuery(); @Override protected String getGetByIDQuery() { return GET_BY_ID_QUERY; } // Select public static final String QUERY_GET_COMPOSITION_MAP_ENTRY_BY_COMPOSITION_ID = StringUtils.join(new String[] { "SELECT", StringUtils.join(ALL_FIELDS, ", "), "from", TABLE_NAME, "where construct_id = ?", }, " "); public ConstructEntry getCompositionMapEntryByCompositionId(DB db, String constructId) throws SQLException { try (PreparedStatement stmt = db.getConn() .prepareStatement(QUERY_GET_COMPOSITION_MAP_ENTRY_BY_COMPOSITION_ID)) { stmt.setString(1, constructId); try (ResultSet resultSet = stmt.executeQuery()) { return expectOneResult(resultSet, String.format("construct_id = %s", constructId)); } } } public static final String QUERY_GET_COMPOSITION_MAP_ENTRY_BY_TARGET = StringUtils.join( new String[] { "SELECT", StringUtils.join(ALL_FIELDS, ", "), "from", TABLE_NAME, "where target = ?", }, " "); public ConstructEntry getCompositionMapEntryByTarget(DB db, String target) throws SQLException { try (PreparedStatement stmt = db.getConn().prepareStatement(QUERY_GET_COMPOSITION_MAP_ENTRY_BY_TARGET)) { stmt.setString(1, target); try (ResultSet resultSet = stmt.executeQuery()) { return expectOneResult(resultSet, String.format("target = %s", target)); } } } // Insert/Update protected static final String INSERT_QUERY = INSTANCE.makeInsertQuery(); @Override public String getInsertQuery() { return INSERT_QUERY; } protected static final String UPDATE_QUERY = INSTANCE.makeUpdateQuery(); @Override public String getUpdateQuery() { return UPDATE_QUERY; } protected void bindInsertOrUpdateParameters(PreparedStatement stmt, String constructId, String target, String host) throws SQLException { stmt.setString(DB_FIELD.CONSTRUCT_ID.getInsertUpdateOffset(), constructId); stmt.setString(DB_FIELD.TARGET.getInsertUpdateOffset(), target); stmt.setString(DB_FIELD.HOST.getInsertUpdateOffset(), host); } @Override protected void bindInsertOrUpdateParameters(PreparedStatement stmt, ConstructEntry c) throws SQLException { bindInsertOrUpdateParameters(stmt, c.getCompositionId(), c.getTarget(), c.getHost()); } public static List<Pair<Integer, DB.OPERATION_PERFORMED>> insertOrUpdateCompositionMapEntriesFromTSV(DB db, TSVParser parser) throws SQLException, IOException { List<Map<String, String>> entries = parser.getResults(); List<Pair<Integer, DB.OPERATION_PERFORMED>> operationsPerformed = new ArrayList<>(entries.size()); for (Map<String, String> entry : entries) { String constructId = entry.get("id"); String target = entry.get("target"); String host = entry.get("host"); if (constructId == null || constructId.isEmpty() || target == null || target.isEmpty()) { System.err.format("WARNING: missing required field for construct '%s', skipping.\n", constructId); continue; } ConstructEntry construct = INSTANCE.getCompositionMapEntryByCompositionId(db, constructId); DB.OPERATION_PERFORMED op = null; if (construct == null) { construct = INSTANCE.insert(db, new ConstructEntry(null, constructId, target, host)); op = DB.OPERATION_PERFORMED.CREATE; } else { construct.setCompositionId(constructId); construct.setTarget(target); construct.setHost(host); INSTANCE.update(db, construct); op = DB.OPERATION_PERFORMED.UPDATE; } // Chem should only be null if we couldn't insert the row into the DB. if (construct == null) { operationsPerformed.add(Pair.of((Integer) null, DB.OPERATION_PERFORMED.ERROR)); } else { operationsPerformed.add(Pair.of(construct.getId(), op)); } } return operationsPerformed; } private Integer id; private String constructId; private String target; private String host; private ConstructEntry() { } public ConstructEntry(Integer id, String constructId, String target, String host) { this.id = id; this.constructId = constructId; this.target = target; this.host = host; } public Integer getId() { return id; } public String getCompositionId() { return constructId; } public void setCompositionId(String constructId) { this.constructId = constructId; } public String getTarget() { return target; } public void setTarget(String target) { this.target = target; } public String getHost() { return host; } public void setHost(String host) { this.host = host; } }