package sorescu.db;
import groovy.util.Eval;
import oracle.jdbc.driver.OracleConnection;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import org.apache.commons.lang3.tuple.Pair;
import org.springframework.jdbc.core.JdbcTemplate;
import sun.misc.IOUtils;
import java.sql.*;
import java.sql.Array;
import java.time.LocalDate;
import java.util.*;
import java.util.Date;
import java.util.stream.Collectors;
public class OracleJdbcConnector {
public final OracleConnection connection;
public OracleJdbcConnector(String url, String user, String pass) {
connection = (OracleConnection)TryGet(()->DriverManager.getConnection(url, user, pass));
}
@Deprecated
public OracleJdbcConnector(JdbcTemplate jdbcTemplate) {
try {
this.connection = (OracleConnection) jdbcTemplate.getDataSource().getConnection();
} catch (Throwable t) {
throw new RuntimeException(t);
}
}
private PreparedStatement prepareStatement(String query) throws SQLException {
return connection.prepareStatement(query);
}
private String getTypeFor(Object o) {
Set<String> types = new HashSet<>();
int length = java.lang.reflect.Array.getLength(o);
for (int i = 0; i < length; i++) {
Object oo = java.lang.reflect.Array.get(o, i);
if (oo instanceof Number) types.add("NUMARRAY");
else if (oo instanceof String) types.add("STRARRAY");
else if (oo == null) {} else throw new RuntimeException("Type not treated: " + oo.getClass());
}
if (types.size() > 1)
throw new RuntimeException("Too many types: " + types);
return (String) types.toArray()[0];
}
public Optional<Map<String, Object>> selectUnique(String query, Object... args) {
OracleJdbcRowset result = select(query, args);
if (result.size() == 0) return Optional.empty();
if (result.size() > 1)
throw new RuntimeException("Too many results (" + result.size() + ") in Unique for `" + query + "`");
return Optional.of(result.get(0));
}
public Map<String, Object> selectUniqueCached(String query, Object... args) {
OracleJdbcRowset result = selectCached(query, args);
if (result.size() == 0)
throw new RuntimeException("No result in SelectUnique for `" + query + "`");
if (result.size() == 1) return result.get(0);
throw new RuntimeException("Too many results (" + result.size() + ") in SelectUnique for `" + query + "`");
}
public OracleJdbcRowset select(String query, Object... args) {
try {
System.out.println(connection.getUserName() + "@" + connection.getURL());
System.out.println("SELECT: " + query.trim());
try (PreparedStatement statement = prepareStatement(query)) {
for (int i = 0; i < args.length; i++) {
if (args[i] != null) {
if (args[i] instanceof Date) {
args[i] = new java.sql.Timestamp(((Date) args[i]).getTime());
}
if (args[i].getClass().isArray()) {
String type = getTypeFor(args[i]);
Array array = connection.createARRAY(type, args[i]);
args[i] = array;
}
}
System.out.println("queryArgs[" + i + "]: " + args[i] + " - " + ((args[i] == null) ? "NULL" : (args[i].getClass())));
if (args[i] instanceof Array)
statement.setArray(i + 1, (Array) args[i]);
else
statement.setObject(i + 1, args[i]);
}
statement.execute();
ResultSet resultSet = statement.getResultSet();
int columnsCount = resultSet.getMetaData().getColumnCount();
List<String> headers = new ArrayList<>();
for (int columnIndex = 0; columnIndex < columnsCount; columnIndex++)
headers.add(resultSet.getMetaData().getColumnLabel(columnIndex + 1));
List<Map<String, Object>> result = new LinkedList<>();
while (resultSet.next()) {
Map<String, Object> row = new HashMap<>();
for (int columnIndex = 0; columnIndex < columnsCount; columnIndex++) {
Object data = resultSet.getObject(columnIndex + 1);
if (data instanceof oracle.sql.BLOB)
data = IOUtils.readFully(((BLOB) data).getStream(), -1, true);
if (data instanceof oracle.sql.CLOB)
data = ((CLOB) data).getSubString(1, (int) ((CLOB) data).length());
row.put(headers.get(columnIndex), data);
}
result.add(row);
}
OracleJdbcRowset rs = new OracleJdbcRowset(result, headers, this, query, args);
return rs;
}
} catch (Throwable t) {
throw new RuntimeException(t);
}
}
public boolean execute(String query, Object... args) {
try {
System.out.println("EXECUTE: " + query.trim());
try (PreparedStatement statement = prepareStatement(query)) {
for (int i = 0; i < args.length; i++) {
if (args[i] != null) {
if (args[i] instanceof LocalDate) {
args[i] = java.sql.Timestamp.valueOf(((LocalDate) args[i]).atStartOfDay());
} else if (args[i] instanceof Date) {
args[i] = new java.sql.Timestamp(((Date) args[i]).getTime());
} else if (args[i].getClass().isArray()) {
if (args[i].getClass().getComponentType().getTypeName().equals("byte")) {
args[i] = connection.createBlob(((byte[]) args[i]));
} else {
String type = getTypeFor(args[i]);
Array array = connection.createARRAY(type, args[i]);
args[i] = array;
}
}
}
System.out.println("queryArgs[" + i + "] = " + args[i] + " - " + ((args[i] == null) ? "null" : (args[i].getClass())));
if (args[i] instanceof Array)
statement.setArray(i + 1, (Array) args[i]);
else
statement.setObject(i + 1, args[i]);
}
return statement.execute();
}
} catch (SQLIntegrityConstraintViolationException icve) {
throw new RuntimeException(icve);
} catch (Throwable t) {
throw new RuntimeException(t);
}
}
private Map<Pair<String, List<Object>>, OracleJdbcRowset> cache = new HashMap<>();
public OracleJdbcRowset selectCached(String query, Object... arguments) {
Pair<String, List<Object>> key = Pair.of(query, Arrays.asList(arguments));
return cache.computeIfAbsent(key, k -> select(query, arguments));
}
public Optional<Map<String, Object>> selectFirst(String query, Object... args) {
return selectUnique("WITH wrapper_SelectFirst as (" + query + ") select * from wrapper_SelectFirst where rownum<2", args);
}
public OracleJdbcRowset getAt(String tableName, Map<String, Object> fields) {
List<String> keys = new ArrayList<>(fields.keySet());
String sql = "SELECT * FROM " + tableName + " WHERE " + keys.stream().map(it -> it + "=?").collect(Collectors.joining(" AND "));
List<Object> arguments = keys.stream().map(fields::get).collect(Collectors.toList());
return select(sql, arguments.toArray());
}
public void deleteAt(String tableName, Map<String, Object> fields) {
List<String> keys = new ArrayList<>(fields.keySet());
String sql = "DELETE FROM " + tableName + " WHERE " + keys.stream().map(it -> it + "=?").collect(Collectors.joining(" AND "));
List<Object> arguments = keys.stream().map(fields::get).collect(Collectors.toList());
execute(sql, arguments.toArray());
}
public OracleJdbcRowset getAt(String tableName, String key, Object value) {
String sql = "SELECT * FROM " + tableName + " WHERE " + key + "=?";
return select(sql, value);
}
public boolean add(String tableName, Map<String, Object> fields) {
List<String> keys = fields.entrySet().stream().filter(it -> it.getValue() != null).map(Map.Entry::getKey).collect(Collectors.toList());
String sql = "INSERT into " + tableName + " (" + keys.stream().collect(Collectors.joining(",")) + ") values (" +
keys.stream().map(key -> (fields.get(key) instanceof OracleJdbcExpression) ? ("(" + ((OracleJdbcExpression) fields.get(key)).code + ")") : "?").collect(Collectors.joining(",")) + ")";
List<Object> arguments = keys.stream().filter(key -> !(fields.get(key) instanceof OracleJdbcExpression)).map(fields::get).collect(Collectors.toList());
return execute(sql, arguments.toArray());
}
}