rapture.repo.jdbc.JDBCSqlStore Maven / Gradle / Ivy
/**
* Copyright (C) 2011-2015 Incapture Technologies LLC
*
* This is an autogenerated license statement. When copyright notices appear below
* this one that copyright supercedes this statement.
*
* Unless required by applicable law or agreed to in writing, software is distributed
* on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
* or implied.
*
* Unless explicit permission obtained in writing this software cannot be distributed.
*/
package rapture.repo.jdbc;
import java.net.HttpURLConnection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import rapture.common.exception.RaptureException;
import rapture.common.exception.RaptureExceptionFactory;
import rapture.common.exception.RaptureExceptionFormatter;
import rapture.common.impl.jackson.JacksonUtil;
import rapture.common.impl.jackson.JsonContent;
import rapture.common.sql.FieldMapping;
import rapture.common.sql.JoinMapping;
import rapture.common.sql.SQLField;
import rapture.repo.RepoVisitor;
import rapture.repo.SQLStore;
import rapture.repo.qrep.DistinctSpecification;
import rapture.repo.qrep.FieldValue;
import rapture.repo.qrep.WhereRestriction;
import rapture.util.ResourceLoader;
public class JDBCSqlStore implements SQLStore {
private static Logger log = Logger.getLogger(JDBCSqlStore.class);
private String url;
private String user;
private String password;
@SuppressWarnings("unused")
private String instanceName;
private Connection connection;
@Override
public void setInstanceName(String instanceName) {
this.instanceName = instanceName;
}
@Override
public void setConfig(Map config) {
log.info("Initializing JDBCSqlStore");
url = config.get("url");
user = config.get("user");
password = config.get("password");
log.info("Attempting to connect to the database through " + url);
// Create a connection to the database
try {
connection = DriverManager.getConnection(url, user, password);
if (connection == null) {
log.error("A null connection was returned");
}
} catch (SQLException e) {
RaptureException raptException = RaptureExceptionFactory.create(HttpURLConnection.HTTP_INTERNAL_ERROR, "Error connecting to the database");
log.error(RaptureExceptionFormatter.getExceptionMessage(raptException, e));
throw raptException;
}
log.info("Database connection made successfully");
if (config.containsKey("setup")) {
log.info("Will setup with default data");
TestDataLoader loader = new TestDataLoader(connection);
String content = ResourceLoader.getResourceAsString(this, "/world-lendinginterestrate.csv");
loader.loadStandardTable(content, "irate");
}
}
@Override
public void executeDistinct(DistinctSpecification distinct, List whereClauses, RepoVisitor visitor, boolean isFolder) {
StringBuilder sb = new StringBuilder();
sb.append("SELECT DISTINCT ");
sb.append(distinct.getField().getField());
sb.append(" FROM ");
sb.append(distinct.getField().getTable());
if (!whereClauses.isEmpty()) {
sb.append(" WHERE ");
boolean first = true;
for (WhereRestriction w : whereClauses) {
if (!first) {
sb.append(" AND ");
} else {
first = false;
}
sb.append(w.getField().getField());
sb.append("=");
sb.append(" '");
sb.append(w.getValue());
sb.append("'");
}
}
PreparedStatement ps = null;
try {
ps = connection.prepareStatement(sb.toString());
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String val = rs.getString(1);
if (!visitor.visit(val, null, isFolder)) {
break;
}
}
} catch (SQLException e) {
log.error("Failed to select document - " + e.getMessage());
} finally {
safeClose(ps);
}
}
@Override
public void executeDocument(List whereClauses, FieldMapping fields, RepoVisitor visitor) {
StringBuilder sb = getSelectString(whereClauses, fields);
PreparedStatement ps = null;
try {
ps = connection.prepareStatement(sb.toString());
ResultSet rs = ps.executeQuery();
while (rs.next()) {
JsonContent content = new JsonContent(getContent(rs, fields));
if (!visitor.visit("", content, false)) {
break;
}
}
} catch (SQLException e) {
log.error("Failed to select document - " + e.getMessage());
} finally {
safeClose(ps);
}
}
private StringBuilder getSelectString(List whereClauses, FieldMapping fields) {
StringBuilder sb = new StringBuilder();
sb.append("SELECT ");
boolean first = true;
for (SQLField f : fields.getFields()) {
if (!first) {
sb.append(",");
} else {
first = false;
}
sb.append(f.getRef().getField());
}
sb.append(" FROM ");
sb.append(fields.getFields().get(0).getRef().getTable());
addWhereClause(whereClauses, sb);
return sb;
}
private void addWhereClause(List whereClauses, StringBuilder sb) {
if (whereClauses != null && !whereClauses.isEmpty()) {
sb.append(" WHERE ");
boolean fst = true;
for (WhereRestriction rest : whereClauses) {
if (!fst) {
sb.append(" AND ");
} else {
fst = false;
}
sb.append(rest.getField().getField());
sb.append("='");
sb.append(rest.getValue());
sb.append("'");
}
}
}
private String getContent(ResultSet rs, FieldMapping fields) throws SQLException {
Map content = new LinkedHashMap();
for (int i = 0; i < fields.getFields().size(); i++) {
Object val = rs.getObject(i + 1);
SQLField f = fields.getFields().get(i);
addField(content, f.getJsonKey(), val);
}
String cont = JacksonUtil.jsonFromObject(content);
return cont;
}
@SuppressWarnings("unchecked")
private void addField(Map content, String jsonKey, Object val) {
String[] parts = jsonKey.split("\\.");
Map toAdd = content;
for (int i = 0; i < parts.length - 1; i++) {
if (toAdd.containsKey(parts[i])) {
toAdd = (Map) toAdd.get(parts[i]);
} else {
Map newMap = new LinkedHashMap();
toAdd.put(parts[i], newMap);
toAdd = newMap;
}
}
toAdd.put(parts[parts.length - 1], val);
}
@Override
public String executeDocument(List whereClauses, FieldMapping fields) {
StringBuilder selectString = getSelectString(whereClauses, fields);
PreparedStatement ps = null;
try {
ps = connection.prepareStatement(selectString.toString());
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String content = getContent(rs, fields);
if (content != null) {
return content;
}
}
} catch (SQLException e) {
log.error("Failed to select document - " + e.getMessage());
} finally {
safeClose(ps);
}
return null;
}
@Override
public void performUpdate(List fieldValues, List whereClauses, JoinMapping joins) {
// TODO: Assuming no joins at the moment
// We basically want to do an UPDATE tableName SET x=y,a=b WHERE
// whereRestrictions
StringBuilder updateString = new StringBuilder();
updateString.append("UPDATE ");
updateString.append(fieldValues.get(0).getField().getTable());
updateString.append(" SET ");
boolean first = true;
for (FieldValue fv : fieldValues) {
if (!first) {
updateString.append(",");
} else {
first = false;
}
updateString.append(fv.getField().getField());
updateString.append("=");
if (fv.getValue() instanceof String) {
updateString.append("'");
updateString.append(fv.getValue());
updateString.append("'");
} else {
updateString.append(fv.getValue());
}
}
addWhereClause(whereClauses, updateString);
PreparedStatement ps = null;
try {
log.info("Using sql " + updateString.toString());
ps = connection.prepareStatement(updateString.toString());
int altered = ps.executeUpdate();
log.info("Altered " + altered + " document(s)");
} catch (SQLException e) {
log.error("Failed to update document - " + e.getMessage());
} finally {
safeClose(ps);
}
}
private void safeClose(PreparedStatement ps) {
if (ps != null) {
try {
ps.close();
} catch (Exception e) {
log.error("Failed to close prepared statement " + e.getMessage());
}
}
}
@Override
public Boolean validate() {
// this works with MySql, SQLite, SQL Server from MSFT, postgress, Sybase and H2. May not work with older versions of Oracle
try {
PreparedStatement ps = connection.prepareStatement("SELECT 1");
ps.executeQuery();
} catch (Exception ex) {
return false;
}
return true;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy