package siena.jdbc;
import java.io.ByteArrayInputStream;
import java.io.StringReader;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import siena.ClassInfo;
import siena.DateTime;
import siena.Json;
import siena.Query;
import siena.QueryJoin;
import siena.QueryOrder;
import siena.SienaException;
import siena.SienaRestrictedApiException;
import siena.SimpleDate;
import siena.Text;
import siena.Time;
import siena.Util;
import siena.core.DecimalPrecision;
import siena.core.Polymorphic;
import siena.embed.Embedded;
import siena.jdbc.JdbcPersistenceManager.JdbcClassInfo;
public class JdbcDBUtils {
public static final String WHERE = " WHERE ";
public static final String AND = " AND ";
public static final String IS_NULL = " IS NULL";
public static final String IS_NOT_NULL = " IS NOT NULL";
public static void closeStatement(Statement st) {
if(st == null) return;
try {
st.close();
} catch (SQLException e) {
throw new SienaException(e);
}
}
public static void closeResultSet(ResultSet rs) {
if(rs == null) return;
try {
rs.close();
} catch (SQLException e) {
throw new SienaException(e);
}
}
public static void closeStatementAndConnection(JdbcPersistenceManager pm, Statement st) {
try {
if(st != null){
st.close();
}
} catch (SQLException e) {
throw new SienaException(e);
} finally {
try {
if(pm.getConnection().getAutoCommit()){
pm.closeConnection();
}
}
catch(SQLException ex){
// don't do anything with it
}
catch(SienaException ex){
// don't do anything with it
}
}
}
public static <T> StringBuilder buildSqlSelect(Query<T> query) {
Class<T> clazz = query.getQueriedClass();
JdbcClassInfo info = JdbcClassInfo.getClassInfo(clazz);
List<String> cols = new ArrayList<String>();
List<Field> joinFields = JdbcMappingUtils.getJoinFields(query, info);
if(joinFields==null){
JdbcClassInfo.calculateColumnsAliases(info.allFields, cols, info.tableName, "");
StringBuilder sql =
new StringBuilder("SELECT " + Util.join(cols, ", ") + " FROM " + info.tableName);
return sql;
}
// builds fields from primary class
JdbcClassInfo.calculateColumnsAliases(info.allFields, cols, info.tableName, "");
StringBuilder sql = new StringBuilder(" FROM " + info.tableName);
int i=0;
String alias;
for(Field field: joinFields){
JdbcClassInfo fieldInfo = JdbcClassInfo.getClassInfo(field.getType());
if (!ClassInfo.isModel(field.getType())){
throw new SienaException("Join not possible: Field "+field.getName()+" is not a relation field");
}
alias = fieldInfo.tableName + i++;
fieldInfo.joinFieldAliases.put(field.getName(), alias);
// DO NOT remove the field itself from columns because it allows to find NULL fields
// cols.remove( info.tableName+"."+field.getName());
// adds all field columns using Alias
JdbcClassInfo.calculateColumnsAliases(fieldInfo.allFields, cols, alias, "");
String[] columns = ClassInfo.getColumnNames(field, info.tableName);
if (columns.length > 1 || fieldInfo.keys.size() > 1){
throw new SienaException("Join not possible: join field "+field.getName()+" has multiple keys");
}
// LEFT INNER JOIN TO GET NULL FIELDS
sql.append(" LEFT JOIN " + fieldInfo.tableName + " AS " + alias
+ " ON " + columns[0]
+ " = " + alias + "." + fieldInfo.keys.get(0).getName());
}
sql.insert(0, "SELECT " + Util.join(cols, ", "));
return sql;
}
public static <T> void appendSqlOrder(Query<T> query, StringBuilder sql) {
Class<T> clazz = query.getQueriedClass();
JdbcClassInfo info = JdbcClassInfo.getClassInfo(clazz);
List<QueryOrder> orders = query.getOrders();
List<QueryJoin> joins = query.getJoins();
if(orders.isEmpty() && joins.isEmpty()) { return; }
sql.append(" ORDER BY ");
boolean first = true;
for (QueryOrder order : orders) {
if(!first) {
sql.append(", ");
}
first = false;
if(order.parentField==null){
String[] columns = ClassInfo.getColumnNames(order.field, info.tableName);
for (String column : columns) {
sql.append(column+ (order.ascending? "" : " DESC"));
}
}else {
try {
JdbcClassInfo parentCi = JdbcClassInfo.getClassInfo(order.parentField.getType());
Field subField = order.parentField.getType().getField(order.field.getName());
// get columns using join field alias
//String[] columns = ClassInfo.getColumnNames(subField, parentCi.tableName);
String[] columns =
ClassInfo.getColumnNames(
subField, parentCi.joinFieldAliases.get(order.parentField.getName()));
for (String column : columns) {
sql.append(column+ (order.ascending? "" : " DESC"));
}
}catch(NoSuchFieldException ex){
throw new SienaException("Order not possible: join sort field "+order.field.getName()+" is not a known field of "+order.parentField.getName(), ex);
}
}
}
}
public static <T> void appendSqlLimitOffset(Query<T> query, StringBuilder sql, List<Object> parameters) {
//QueryOptionPage pag = (QueryOptionPage)query.option(QueryOptionPage.ID);
//QueryOptionOffset offset = (QueryOptionOffset)query.option(QueryOptionOffset.ID);
QueryOptionJdbcContext jdbcCtx = (QueryOptionJdbcContext)query.option(QueryOptionJdbcContext.ID);
sql.append(" LIMIT ?");
parameters.add(jdbcCtx.realPageSize);
sql.append(" OFFSET ?");
parameters.add(jdbcCtx.realOffset);
/*if(pag.isActive()) {
sql.append(" LIMIT ?");
parameters.add(jdbcCtx.realPageSize);
sql.append(" OFFSET ?");
parameters.add(jdbcCtx.realOffset);
}
// offset without paging is non sense in JDBC
// so puts the MAX_VALUE as page size
else {
sql.append(" LIMIT ?");
parameters.add(Integer.MAX_VALUE);
sql.append(" OFFSET ?");
parameters.add(jdbcCtx.realOffset);
}*/
}
public static int toSqlType(Object obj, Field field, String DB) {
if(obj == null) return -1;
Class<?> type = field.getType();
if(type == Byte.class || type == Byte.TYPE) return Types.TINYINT;
else if(type == Short.class || type == Short.TYPE) return Types.SMALLINT;
else if(type == Integer.class || type == Integer.TYPE) return Types.INTEGER;
else if(type == Long.class || type == Long.TYPE) return Types.BIGINT;
else if(type == Float.class || type == Float.TYPE) return Types.FLOAT; // TODO verify
else if(type == Double.class || type == Double.TYPE) return Types.DOUBLE; // TODO verify
else if(type == String.class) {
if(field.getAnnotation(Text.class) != null) {
return Types.LONGVARCHAR;
} else {
return Types.VARCHAR;
}
}
else if(type == Boolean.class || type == Boolean.TYPE) return Types.BOOLEAN;
else if(type == Date.class) {
if(field.getAnnotation(DateTime.class) != null)
return Types.TIMESTAMP;
else if(field.getAnnotation(Time.class) != null)
return Types.TIME;
else if(field.getAnnotation(SimpleDate.class) != null)
return Types.DATE;
else
return Types.TIMESTAMP;
} else if(type == Json.class) {
return Types.LONGVARCHAR;
} else if(type == byte[].class){
return Types.BLOB;
} else if(Enum.class.isAssignableFrom(type)){
return Types.VARCHAR;
} else if(type == BigDecimal.class){
DecimalPrecision an = field.getAnnotation(DecimalPrecision.class);
if(an == null) {
return Types.DECIMAL;
}
else {
if(an.storageType() == DecimalPrecision.StorageType.NATIVE){
return Types.DECIMAL;
}else if(an.storageType() == DecimalPrecision.StorageType.STRING) {
return Types.VARCHAR;
}else if(an.storageType() == DecimalPrecision.StorageType.DOUBLE) {
return Types.DOUBLE;
}else {
return Types.DECIMAL;
}
}
}
else {
Embedded embedded = field.getAnnotation(Embedded.class);
if(embedded != null) {
if("h2".equals(DB)){
return Types.CLOB;
}
else {
return Types.LONGVARCHAR;
}
} else if(field.isAnnotationPresent(Polymorphic.class)){
return Types.BLOB;
}else {
throw new SienaRestrictedApiException(DB, "createColumn", "Unsupported type for field "
+type.getName()+"."+field.getName());
}
}
}
public static void setObject(PreparedStatement ps, int index, Object value, Field field, String DB) throws SQLException {
if(value == null) {
ps.setNull(index, JdbcDBUtils.toSqlType(value, field, DB));
return;
}
Class<?> type = field.getType();
if(type == Byte.class || type == Byte.TYPE) ps.setByte(index, (Byte)value);
else if(type == Short.class || type == Short.TYPE) ps.setShort(index, (Short)value);
else if(type == Integer.class || type == Integer.TYPE) ps.setInt(index, (Integer)value);
else if(type == Long.class || type == Long.TYPE) ps.setLong(index, (Long)value);
else if(type == Float.class || type == Float.TYPE) ps.setFloat(index, (Float)value);
else if(type == Double.class || type == Double.TYPE) ps.setDouble(index, (Double)value);
else if(type == String.class) {
ps.setString(index, (String)value);
}
else if(type == Boolean.class || type == Boolean.TYPE) ps.setBoolean(index, (Boolean)value);
else if(type == Date.class) {
if(field.getAnnotation(DateTime.class) != null){
java.sql.Timestamp ts = new java.sql.Timestamp(((Date)value).getTime());
ps.setTimestamp(index, ts);
}
else if(field.getAnnotation(Time.class) != null){
java.sql.Time ts = new java.sql.Time(((Date)value).getTime());
ps.setTime(index, ts);
}
else if(field.getAnnotation(SimpleDate.class) != null){
java.sql.Date d = new java.sql.Date(((Date)value).getTime());
ps.setDate(index, d);
}
else {
java.sql.Timestamp ts = new java.sql.Timestamp(((Date)value).getTime());
ps.setTimestamp(index, ts);
}
} else if(type == Json.class) {
ps.setString(index, (String)value);
} else if(type == byte[].class){
ByteArrayInputStream bis = new ByteArrayInputStream((byte[])value);
ps.setBlob(index, bis);
} else if(Enum.class.isAssignableFrom(type)){
ps.setString(index, (String)value);
} else if(type == BigDecimal.class){
DecimalPrecision an = field.getAnnotation(DecimalPrecision.class);
if(an == null) {
ps.setObject(index, value);
}
else {
if(an.storageType() == DecimalPrecision.StorageType.NATIVE){
ps.setBigDecimal(index, (BigDecimal)value);
}else if(an.storageType() == DecimalPrecision.StorageType.STRING) {
ps.setString(index, ((BigDecimal)value).toPlainString());
}else if(an.storageType() == DecimalPrecision.StorageType.DOUBLE) {
ps.setDouble(index, ((BigDecimal)value).doubleValue());
}else {
ps.setBigDecimal(index, (BigDecimal)value);
}
}
}
else {
Embedded embedded = field.getAnnotation(Embedded.class);
if(embedded != null) {
if("h2".equals(DB)){
StringReader reader = new StringReader((String)value);
ps.setClob(index, reader);
}
else {
ps.setString(index, (String)value);
}
} else if(field.isAnnotationPresent(Polymorphic.class)){
ByteArrayInputStream bis = new ByteArrayInputStream((byte[])value);
ps.setBlob(index, bis);
}else {
throw new SienaRestrictedApiException(DB, "createColumn", "Unsupported type for field "
+type.getName()+"."+field.getName());
}
}
}
}