package be.ugent.rml.access; import org.apache.commons.csv.CSVFormat; import org.apache.commons.csv.CSVPrinter; import java.io.ByteArrayInputStream; import java.io.IOException; import java.io.InputStream; import java.io.StringWriter; import java.sql.*; import java.util.HashMap; import java.util.Map; import static be.ugent.rml.Utils.getHashOfString; /** * This class represents the access to a relational database. */ public class RDBAccess implements Access { private String dsn; private DatabaseType databaseType; private String username; private String password; private String query; private String contentType; private Map<String, String> datatypes = new HashMap<>(); private String oracleJarPath; /** * This constructor takes as arguments the dsn, database, username, password, query, and content type. * @param dsn the data source name. * @param databaseType the database type. * @param username the username of the user that executes the query. * @param password the password of the above user. * @param query the SQL query to use. * @param contentType the content type of the results. */ public RDBAccess(String dsn, DatabaseType databaseType, String username, String password, String query, String contentType) { this.dsn = dsn; this.databaseType = databaseType; this.username = username; this.password = password; this.query = query; this.contentType = contentType; } /** * This method returns an InputStream of the results of the SQL query. * @return an InputStream with the results. * @throws IOException */ @Override public InputStream getInputStream() throws IOException, SQLException, ClassNotFoundException { // JDBC objects Connection connection = null; Statement statement = null; String jdbcDriver = databaseType.getDriver(); String jdbcDSN = "jdbc:" + databaseType.getJDBCPrefix() + "//" + dsn; InputStream inputStream = null; try { // Register JDBC driver Class.forName(jdbcDriver); // Open connection String connectionString = jdbcDSN; boolean alreadySomeQueryParametersPresent = false; if (username != null && !username.equals("") && password != null && !password.equals("")) { if (databaseType == DatabaseType.ORACLE) { connectionString = connectionString.replace(":@", ":" + username + "/" + password + "@"); } else if (!connectionString.contains("user=")) { connectionString += "?user=" + username + "&password=" + password; alreadySomeQueryParametersPresent = true; } } if (databaseType == DatabaseType.MYSQL) { if (alreadySomeQueryParametersPresent) { connectionString += "&"; } else { connectionString += "?"; } connectionString += "serverTimezone=UTC&useSSL=false"; } if (databaseType == DatabaseType.SQL_SERVER) { connectionString = connectionString.replaceAll("\\?|&", ";"); if (!connectionString.endsWith(";")) { connectionString += ";"; } } connection = DriverManager.getConnection(connectionString); // Execute query statement = connection.createStatement(); ResultSet rs = statement.executeQuery(query); // Turn the Results Set into a CSV stream. inputStream = getCSVInputStream(rs); // Clean-up environment rs.close(); statement.close(); connection.close(); } catch (Exception sqlE) { throw sqlE; } finally { // finally block used to close resources try { if (statement != null) { statement.close(); } } catch (SQLException se2) { }// nothing we can do try { if (connection != null) { connection.close(); } } catch (SQLException se) { se.printStackTrace(); } } return inputStream; } /** * This method returns the datatypes used for the columns in the accessed database. * @return a map of column names and their datatypes. */ @Override public Map<String, String> getDataTypes() { return datatypes; } /** * This method creates an CSV-formatted InputStream from a Result Set. * @param rs the Result Set that is used. * @return a CSV-formatted InputStream. * @throws SQLException */ private InputStream getCSVInputStream(ResultSet rs) throws SQLException { // Get number of requested columns ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); boolean filledInDataTypes = false; StringWriter writer = new StringWriter(); try { CSVPrinter printer = new CSVPrinter(writer, CSVFormat.DEFAULT.withHeader(getCSVHeader(rsmd, columnCount))); printer.printRecords(); // Extract data from result set while (rs.next()) { String[] csvRow = new String[columnCount]; // Iterate over column names for (int i = 1; i <= columnCount; i++) { String columnName = rsmd.getColumnName(i); if (!filledInDataTypes) { String dataType = getColumnDataType(rsmd.getColumnTypeName(i)); if (dataType != null) { datatypes.put(columnName, dataType); } } // Add value to CSV row. csvRow[i - 1] = rs.getString(columnName); } // Add CSV row to CSVPrinter. // non-varargs call printer.printRecord((Object[]) csvRow); filledInDataTypes = true; } } catch (IOException e) { e.printStackTrace(); } // Get InputStream from StringWriter. return new ByteArrayInputStream(writer.toString().getBytes()); } /** * This method returns the corresponding datatype for a SQL datatype. * @param type the SQL datatype. * @return the url of the corresponding datatype. */ private String getColumnDataType(String type) { switch (type.toUpperCase()) { case "BYTEA": case "BINARY": case "BINARY VARYING": case "BINARY LARGE OBJECT": case "VARBINARY": return "http://www.w3.org/2001/XMLSchema#hexBinary"; case "NUMERIC": case "DECIMAL": return "http://www.w3.org/2001/XMLSchema#decimal"; case "SMALLINT": case "INT": case "INT4": case "INT8": case "INTEGER": case "BIGINT": return "http://www.w3.org/2001/XMLSchema#integer"; case "FLOAT": case "FLOAT4": case "FLOAT8": case "REAL": case "DOUBLE": case "DOUBLE PRECISION": return "http://www.w3.org/2001/XMLSchema#double"; case "BIT": case "BOOL": case "BOOLEAN": return "http://www.w3.org/2001/XMLSchema#boolean"; case "DATE": return "http://www.w3.org/2001/XMLSchema#date"; case "TIME": return "http://www.w3.org/2001/XMLSchema#time"; case "TIMESTAMP": case "DATETIME": return "http://www.w3.org/2001/XMLSchema#dateTime"; } return null; } /** * This method returns the header of the CSV. * @param rsmd metdata of the Result Set * @param columnCount the number of columns. * @return a String array with the headers. * @throws SQLException */ private String[] getCSVHeader(final ResultSetMetaData rsmd, final int columnCount) throws SQLException { String[] headers = new String[columnCount]; for (int i = 1; i <= columnCount; i++) { headers[i - 1] = rsmd.getColumnName(i); } return headers; } @Override public boolean equals(Object o) { if (o instanceof RDBAccess) { RDBAccess access = (RDBAccess) o; return dsn.equals(access.getDSN()) && databaseType.equals(access.getDatabaseType()) && username.equals(access.getUsername()) && password.equals(access.getPassword()) && query.equals(access.getQuery()) && contentType.equals(access.getContentType()); } else { return false; } } @Override public int hashCode() { return getHashOfString(getDSN() + getDatabaseType() + getUsername() + getPassword() + getQuery() + getContentType()); } /** * This method returns the DNS. * @return the DNS. */ public String getDSN() { return dsn; } /** * This method returns the database type. * @return the database type. */ public DatabaseType getDatabaseType() { return databaseType; } /** * This method returns the username. * @return the username. */ public String getUsername() { return username; } /** * This method returns the password. * @return the password. */ public String getPassword() { return password; } /** * This method returns the SQL query. * @return the SQL query. */ public String getQuery() { return query; } /** * This method returns the content type. * @return the content type. */ public String getContentType() { return contentType; } }