package x.sql2;
import java.lang.reflect.Field;
import java.sql.Array;
import java.sql.ResultSet;
import x.sql2.enums.SQLQueryTypes;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import javax.persistence.Column;
import javax.persistence.Id;
import x.sql2.enums.SQLFilterDelimiter;
import x.sql2.queries.SQLStatementInsert;
import x.sql2.queries.SQLStatementSelect;
import x.sql2.queries.SQLStatementUpdate;
public abstract class SQLTable<E extends SQLRow> {
public final SQLColumn COLUMN_ALL = new SQLColumn("*");
public final SQLColumn COLUMN_COUNT_ALL = new SQLColumn("count(*)");
protected final SQLColumnList COLUMNS_LIST = new SQLColumnList();
protected SQLConnectionsPool connections;
protected String TABLE_NAME;
protected SQLFilter DEFAULT_FILTER = new SQLFilter(SQLFilterDelimiter.AND);
protected SQLOrder DEFAULT_ORDER = new SQLOrder();
protected SQLConverter<E> CONVERTER = new SQLConverter<E>();
/**
* #SELECT# - SELECT * FROM @table@ <br>
* #INSERT# - INSERT INTO @table@ (@columns@) VALUES (@values@) <br>
* #UPDATE# - UPDATE @table@ SET @set@ <br>
* #DELETE# - DELETE FROM @table@ <br>
* <br>
* #INSERT ID# - #INSERT# "PATTERN_RETURN_ID"<br>
* #SELECT ID# - #SELECT# WHERE @id@=|@id@|<br>
* #UPDATE ID# - #UPDATE# WHERE @id@=|@id@|<br>
* #DELETE ID# - #DELETE# WHERE @id@=|@id@|<br>
* <br>
* #PAGING# - #SELECT# "PATTERN_PAGING_RULES" LIMIT ? OFFSET ?<br>
* #COUNT# - SELECT count(*) as count FROM @table@ "PATTERN_COUNT_RULES"<br>
* #SELECT D# - #SELECT#<br>
* <br>
* "PATTERN_PAGING_RULES" - ""<br>
* "PATTERN_COUNT_RULES" - ""<br>
* PATTERN_RETURN_ID - "RETURNING @id@"<br>
* <br>
* CONSTANTS: <br>
* PATTERN_PAGING_RULES = "#PAGING RULES#"<br>
* PATTERN_COUNT_RULES = "#COUNT RULES#"<br>
* PATTERN_RETURN_ID = "#RETURN ID#"<br>
*/
protected final HashMap<String, String> PATTERNS = new HashMap<String, String>();
// protected final String PATTERN_PAGING_RULES = "#PAGING RULES#";
protected final String PATTERN_COUNT_RULES = "#COUNT RULES#";
protected final String PATTERN_RETURN_ID = "#RETURN ID#";
protected final String PATTERN_DEFAULT_SELECT_TABLE = "#SELECT D TABLE#";
protected final String PATTERN_DEFAULT_SELECT_RULES = "#SELECT D RULES#";
protected final String PATTERN_FILTER = "#FILTER#";
protected final String CONSTANT_TABLE = "@table@";
protected final String CONSTANT_COLUMNS = "@columns@";
protected final String CONSTANT_VALUES = "@values@";
protected final String CONSTANT_SET = "@set@";
protected final String CONSTANT_ID = "@id@";
protected final String CONSTANT_WHERE = "@where@";
protected final String CONSTANT_ORDERBY = "@orderby@";
public SQLTable(String tableName, SQLConnectionsPool connections) {
this.connections = connections;
this.TABLE_NAME = tableName;
COLUMN_ALL.setInsertable(false);
COLUMN_ALL.setUpdatable(false);
COLUMN_ALL.setDefaultSelect(true);
PATTERNS.put("#SELECT#", "SELECT * FROM @table@ ");
PATTERNS.put("#INSERT#", "INSERT INTO @table@ (@columns@) VALUES (@values@) ");
PATTERNS.put("#UPDATE#", "UPDATE @table@ SET @set@ ");
PATTERNS.put("#DELETE#", "DELETE FROM @table@ ");
PATTERNS.put("#INSERT ID#", "#INSERT# " + PATTERN_RETURN_ID);
PATTERNS.put("#SELECT ID#", "#SELECT# WHERE @id@=" + SQLUtils.VALUE_DELIMITER + "@id@" + SQLUtils.VALUE_DELIMITER);
PATTERNS.put("#UPDATE ID#", "#UPDATE# WHERE @id@=" + SQLUtils.VALUE_DELIMITER + "@id@" + SQLUtils.VALUE_DELIMITER);
PATTERNS.put("#DELETE ID#", "#DELETE# WHERE @id@=" + SQLUtils.VALUE_DELIMITER + "@id@" + SQLUtils.VALUE_DELIMITER);
PATTERNS.put("#SELECT D#", "SELECT * FROM " + PATTERN_DEFAULT_SELECT_TABLE + " " + PATTERN_DEFAULT_SELECT_RULES);//DEFAULT SELECT
PATTERNS.put("#PAGING#", "#SELECT D# LIMIT ? OFFSET ?");
PATTERNS.put("#COUNT#", "SELECT count(*) as count FROM " + PATTERN_DEFAULT_SELECT_TABLE + " " + PATTERN_COUNT_RULES);
PATTERNS.put("#FILTER#", "#SELECT D# WHERE ^FILTER^");
//By Default
//PATTERNS.put(PATTERN_PAGING_RULES, "");
PATTERNS.put(PATTERN_COUNT_RULES, "@where@");
PATTERNS.put(PATTERN_RETURN_ID, connections.getDialect().getReturningIDStatement());
PATTERNS.put(PATTERN_DEFAULT_SELECT_TABLE, "@table@");
PATTERNS.put(PATTERN_DEFAULT_SELECT_RULES, "@where@ @orderby@");
//PATTERNS.put(PATTERN_FILTER, "#SELECT D# WHERE " + VARIABLE_FILTER);
}
public SQLConnectionsPool getConnections() {
return connections;
}
protected abstract SQLTable<E> getIntance();
public abstract E createRowInstance();
///////////////////////////////////////////////////
////////// PROTECTED METHODS /////////////////////
//////////////////////////////////////////////////
public List<E> executeSelect(String query, Object... params) throws SQLException {
if (!checkConnections()) {
return null;
}
SQLQuery q = connections.createQuery();
List<E> rows = createList();
try {
q.executeQuery(query, params);
while (q.next()) {
rows.add(createEFromAnnotations(q));
}
} finally {
q.close();
}
return rows;
}
public List<E> executeSelectF(String query, Object... params) throws SQLException {
return executeSelect(toQuery(query), params);
}
public int executeUpdate(String query, Object... params) throws SQLException {
if (!checkConnections()) {
return -1;
}
int i = -1;
SQLQuery q = connections.createQuery();
try {
i = q.executeUpdate(query, params);
} finally {
q.close();
}
return i;
}
public int executeUpdateF(String query, Object... params) throws SQLException {
return executeUpdate(toQuery(query), params);
}
public List<E> selectWhere(String where, Object... params) throws SQLException {
return executeSelectF("#SELECT# WHERE " + where, params);
}
public List<E> selectWhereOrder(String where, String order, Object... params) throws SQLException {
return executeSelectF("#SELECT# WHERE " + where + " ORDER BY " + order, params);
}
public int countWhere(String where, Object... params) {
if (!checkConnections()) {
return -1;
}
SQLQuery q = connections.createQuery();
try {
q.executeQuery(toQuery("SELECT count(*) as count FROM @table@ WHERE " + where), params);
if (q.next()) {
return q.getInt("count");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
q.close();
}
return -1;
}
public int updateWhere(String columns, String where, E row, Object... params) throws SQLException {
String[] fields = columns.split(",");
StringBuilder sb = new StringBuilder("UPDATE @table@ SET ");
for (int i = 0; i < fields.length; i++) {
if (i > 0) {
sb.append(",");
}
sb.append(fields[i].trim()).append("=").append(SQLUtils.VALUE_DELIMITER).append(fields[i].trim()).append(SQLUtils.VALUE_DELIMITER);
}
sb.append(" WHERE ").append(where);
//System.out.println(toQuery(sb.toString(), row));
return executeUpdate(toQuery(sb.toString(), row), params);
}
public void insert(String columns, E row, Object... params) {
throw new UnsupportedOperationException("Not implimented!");
}
public String getTableName() {
return TABLE_NAME;
}
protected E createEFromAnnotations(ResultSet rs) throws SQLException {
E row = createRowInstance();
ResultSetMetaData m = rs.getMetaData();
String colName;
SQLColumn col;
int type;
for (int i = 1; i <= m.getColumnCount(); i++) {
colName = m.getColumnName(i);
col = COLUMNS_LIST.getByColumnName(m.getColumnName(i));
type = m.getColumnType(i);
switch (type) {
case Types.NULL:
row.set(col, null);
break;
case Types.BINARY:
case Types.BLOB:
case Types.VARBINARY:
row.set(col, rs.getBytes(colName));
break;
case Types.ARRAY:
Array arr = rs.getArray(colName);
if (arr != null) {
row.set(col, arr.getArray());
} else {
row.set(col, null);
}
break;
case Types.DATE:
case Types.TIMESTAMP:
Date d = rs.getDate(colName);
if (d != null) {
row.set(col, new Date(d.getTime()));
} else {
row.set(col, d);
}
break;
default:
Object o = rs.getObject(colName);
if (o != null) {
row.set(col, rs.getObject(colName));
}
break;
}
// <editor-fold defaultstate="collapsed" desc="OLD">
// if (type == Types.NULL) {
// row.set(col, null);
// } else if (type == Types.BINARY) {
// row.set(col, rs.getBytes(colName));
// } else if (type == Types.ARRAY) {
// Array arr = rs.getArray(colName);
// if (arr != null) {
// row.set(col, arr.getArray());
// } else {
// row.set(col, null);
// }
// } else if (type == Types.DATE || type == Types.TIMESTAMP) {
// Date d = rs.getDate(colName);
// if (d != null) {
// row.set(col, new Date(d.getTime()));
// } else {
// row.set(col, d);
// }
// } else {
// Object o = rs.getObject(colName);
// if (o != null) {
// row.set(col, rs.getObject(colName));
// }
// }
// </editor-fold>
}
return row;
}
protected E createEFromAnnotations(SQLQuery q) throws SQLException {
E row = createRowInstance();
ResultSetMetaData metadata = q.getResultSetMetaData();
String colName;
SQLColumn col;
int type;
for (int i = 1; i <= metadata.getColumnCount(); i++) {
colName = metadata.getColumnName(i);
col = COLUMNS_LIST.getByColumnName(metadata.getColumnName(i));
type = metadata.getColumnType(i);
///DEBUGING
// System.out.println("----------");
// System.out.println("type="+type);
// System.out.println("col="+col.getColumnName());
// Object _temp_obj = q.getObject(colName);
// System.out.println("val="+_temp_obj);
// if (_temp_obj!=null){
// System.out.println("class="+_temp_obj.getClass());
// }
//
// System.out.println("----------");
switch (type) {
case Types.NULL:
row.set(col, null);
break;
case Types.BINARY:
case Types.BLOB:
case Types.VARBINARY:
row.set(col, q.getBytes(colName));
break;
case Types.ARRAY:
Array arr = q.getArray(colName);
if (arr != null) {
row.set(col, arr.getArray());
} else {
row.set(col, null);
}
break;
case Types.DATE:
case Types.TIMESTAMP:
Date d = q.getDatetime(colName);
if (d != null) {
row.set(col, new Date(d.getTime()));
} else {
row.set(col, d);
}
break;
case Types.NUMERIC:
Integer val = q.getInt(colName);
row.set(col, val);
break;
default:
Object o = q.getObject(colName);
if (o != null) {
row.set(col, q.getObject(colName));
}
break;
}
// <editor-fold defaultstate="collapsed" desc="OLD">
// if (type == Types.NULL) {
// row.set(col, null);
// } else if (type == Types.BINARY) {
// row.set(col, q.getBytes(colName));
// } else if (type == Types.ARRAY) {
// Array arr = q.getArray(colName);
// if (arr != null) {
// row.set(col, arr.getArray());
// } else {
// row.set(col, null);
// }
// } else if (type == Types.DATE || type == Types.TIMESTAMP) {
// Date d = q.getDatetime(colName);
// if (d != null) {
// row.set(col, new Date(d.getTime()));
// } else {
// row.set(col, d);
// }
// } else {
// Object o = q.getObject(colName);
// if (o != null) {
// row.set(col, q.getObject(colName));
// }
// }
// </editor-fold>
}
return row;
}
protected boolean checkConnections() {
return connections != null;
}
/**
* Transform @key@ to value<br>
* Must be start and ends with '@'
* @param constant
* @return
*/
protected String getConstantValue(String constant) {
if (constant.equals(CONSTANT_TABLE)) {
return TABLE_NAME;
} else if (constant.equals(CONSTANT_COLUMNS)) {
return COLUMNS_LIST.toInsertColumsString();
} else if (constant.equals(CONSTANT_VALUES)) {
return COLUMNS_LIST.toInsertValuesString();
} else if (constant.equals(CONSTANT_SET)) {
return COLUMNS_LIST.toUpdateString();
} else if (constant.equals(CONSTANT_ID)) {
return COLUMNS_LIST.getFirstPKColumName();
} else if (constant.equals(CONSTANT_WHERE)) {
if (DEFAULT_FILTER.size() > 0) {
return "WHERE " + DEFAULT_FILTER.join();
}
return "";
} else if (constant.equals(CONSTANT_ORDERBY)) {
if (DEFAULT_ORDER.size() > 0) {
return "ORDER BY " + DEFAULT_ORDER.join();
}
return "";
} else {
return null;
}
}
/**
*
* @param pattern must be start and ends with '#'
* @param value
*/
protected void registerPattern(String pattern, String value) {
PATTERNS.put(pattern, value);
}
///////////////////////////////////////////////////
////////////// PUBLIC METHODS /////////////////////
///////////////////////////////////////////////////
/**
* Executing #INSERT# pattern
* @param rows
* @throws SQLException
*/
public void executeDefaultInsert(E row) throws SQLException {
executeUpdateF(toQuery("#INSERT#", row));
}
/**
* Executing #UPDATE ID# pattern
* @param rows
* @throws SQLException
*/
public int executeDefaultUpdate(E row) throws SQLException {
return executeUpdate(toQuery("#UPDATE ID#", row));
}
/**
* Executing #SELECT D# pattern
* @return
* @throws SQLException
*/
public List<E> executeDefaultSelect() throws SQLException {
// System.out.println(toQuery(qu.toString()));
return executeSelect(toQuery("#SELECT D#"));
}
public List<E> executeDefaultSelect(SQLFilter filter) throws SQLException {
StringBuilder sb = new StringBuilder("#SELECT#");
if (filter != null && filter.size() > 0) {
sb.append(" WHERE ");
sb.append(filter.join());
}
//System.out.println(toQuery(sb.toString()));
return executeSelect(toQuery(sb.toString()));
}
/**
* Executing #PAGING# pattern
* @param page
* @param limit
* @return
*/
public List<E> executeDefaultSelectPaging(int page, int limit) {
int offset = (page - 1) * limit;
if (offset < 0) {
offset = 0;
}
try {
return executeSelect(toQuery("#PAGING#"), limit, offset);
} catch (SQLException ex) {
ex.printStackTrace();
return null;
}
}
/**
* Executing #COUNT# pattern
* @return
*/
public int executeDefaultSelectCount() {
if (!checkConnections()) {
return -1;
}
SQLQuery q = connections.createQuery();
try {
q.executeQuery(toQuery("#COUNT#", null));
if (q.next()) {
return q.getInt("count");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
q.close();
}
return -1;
}
/**
* Executing #SELECT ID# pattern
* @param row with setted ID
* @return
*/
public E executeDefaultSelectByPK(E row) {
try {
List<E> e = executeSelect(toQuery("#SELECT ID#", row));
if (e != null && e.size() > 0) {
return e.get(0);
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* Executing #INSERT ID# pattern
* @param row
* @return
*/
public E executeDefaultInsertReturningPK(E row) {
try {
List<E> e = executeSelect(toQuery("#INSERT ID#", row));
if (e != null && e.size() > 0) {
return e.get(0);
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* Executing #DELETE ID# pattern
* @param row
*/
public int executeDefaultDelete(E row) throws SQLException {
return executeUpdate(toQuery("#DELETE ID#", row));
}
// public List<E> executeDefaultFilter(E row, SQLFilter filter) throws SQLException {
// return executeSelect(toQuery(PATTERN_FILTER, row).replace(VARIABLE_FILTER, filter.join()));
//
// }
/**
* Generating query on standart templates
* @param type
* @param cond
* @return
*/
public String generateQuery(SQLQueryTypes type, SQLConditions cond) {
switch (type) {
case SELECT:
return new SQLStatementSelect(TABLE_NAME).generateQuery(cond);
case INSERT:
return new SQLStatementInsert(TABLE_NAME).generateQuery(cond);
case UPDATE:
return new SQLStatementUpdate(TABLE_NAME).generateQuery(cond);
case DELETE:
return "";
}
return "";
}
public String generateQuery(SQLQueryTypes type, SQLConditions cond, E row) {
String q = "";
switch (type) {
case SELECT:
q = new SQLStatementSelect(TABLE_NAME).generateQuery(cond);
break;
case INSERT:
q = new SQLStatementInsert(TABLE_NAME).generateQuery(cond);
break;
case UPDATE:
q = new SQLStatementUpdate(TABLE_NAME).generateQuery(cond);
break;
case DELETE:
return "";
}
return formatQuery(q, row);
}
public String formatQuery(String pattern, E row) {
return toQuery(pattern, row);
}
/**
* Formating query, replaicing :columnName: in real value in E
* @param queryTemplate query template
* @param row row instance
* @return formated query
*/
public String toQuery(String pattern, E row) {
Matcher m;
String mat;
//PATTERN TO SUB QUERY
ArrayList<String> pattern_was = new ArrayList<String>();
a:
for (;;) {
m = SQLUtils.PATTERNS_PATTERN.matcher(pattern);
boolean notfound = true;
b:
while (m.find()) {
notfound = false;
mat = m.group();
if (PATTERNS.containsKey(mat)) {
if (pattern_was.contains(mat)) {
break a;
}
pattern_was.add(mat);
pattern = pattern.replace(mat, PATTERNS.get(mat));
} else {
pattern = pattern.replace(mat, "");
}
}
if (notfound) {
break;
}
}
m = SQLUtils.CONSTANSTS_PATTERN.matcher(pattern);
while (m.find()) {
mat = m.group();
if (getConstantValue(mat) != null) {
pattern = pattern.replace(mat, getConstantValue(mat));
}
}
if (row == null) {
return pattern;
}
m = SQLUtils.VALUES_PATTERN.matcher(pattern);
while (m.find()) {
mat = m.group();
String colName = mat.replace(SQLUtils.VALUE_DELIMITER, "");
SQLColumn col = this.COLUMNS_LIST.getByColumnName(colName);
pattern = pattern.replace(mat, SQLUtils.formatColumnValue(row.get(col)));
}
return pattern;
}
/**
* Formating query, replaicing :columnName: in real value in E
* @param queryTemplate query template
* @return formated query
*/
public String toQuery(String pattern) {
return toQuery(pattern, null);
}
public List<E> createList() {
return new ArrayList<E>();
}
public E[] toArray(List<E> list) {
return (E[]) list.toArray(new SQLRow[0]);
}
protected void _fillColumnList(SQLTable child) {
Class c = child.getClass();
for (Field f : c.getDeclaredFields()) {
if (f.getType().equals(SQLColumn.class)) {
try {
SQLColumn col = (SQLColumn) f.get(child);
if (col.isIncludeInList()) {
COLUMNS_LIST.add(col);
}
if (f.getAnnotation(Id.class) != null) {
col.setPrimaryKey(true);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
public SQLColumnList getColumnsList() {
return COLUMNS_LIST;
}
public SQLColumn[] getColumnsListAsArray() {
return COLUMNS_LIST.toArray();
}
protected static SQLColumn createColumn(String colName, String fieldName) {
return new SQLColumn(colName, fieldName);
}
protected SQLColumn registerColumn(String colName, String fieldName) {
SQLColumn c = createColumn(colName, fieldName);
COLUMNS_LIST.add(c);
return c;
}
protected SQLColumn registerColumn(String colName, String fieldName, String hmKey) {
SQLColumn col = createColumn(colName, fieldName);
col.setHashMapKey(hmKey);
COLUMNS_LIST.add(col);
return col;
}
protected SQLColumn registerColumnPK(String colName, String fieldName) {
SQLColumn col = createColumn(colName, fieldName);
col.setPrimaryKey(true);
col.setInsertable(false);
col.setUpdatable(false);
COLUMNS_LIST.add(col);
return col;
}
protected SQLColumn registerColumnPK(String colName, String fieldName, String hmKey) {
SQLColumn col = createColumn(colName, fieldName);
col.setPrimaryKey(true);
col.setHashMapKey(hmKey);
COLUMNS_LIST.add(col);
return col;
}
protected void registerColumnsByAnnotations(Class sqlRowClass) {
for (Field f : sqlRowClass.getDeclaredFields()) {
Column anotColumn = f.getAnnotation(Column.class);
if (anotColumn != null) {
Id anotColumnPK = f.getAnnotation(Id.class);
SQLColumn col = null;
if (anotColumnPK != null) {
col = registerColumnPK(anotColumn.name(), f.getName());
} else {
col = registerColumn(anotColumn.name(), f.getName());
col.setInsertable(anotColumn.insertable());
col.setUpdatable(anotColumn.updatable());
}
}
}
}
protected Object serialize(SQLColumn col, Object rowValue) {
return rowValue;
}
protected Object deserialize(SQLColumn col, Object hmValue) {
return hmValue;
}
public Map<String, Object> toHashMap(E row, SQLColumn... columnsToSearch) {
HashMap<String, Object> hm = new HashMap<String, Object>();
for (SQLColumn col : columnsToSearch) {
if (col.getHashMapKey() != null) {
hm.put(col.getHashMapKey(), serialize(col, row.get(col)));
}
}
return hm;
}
public E fromHashMap(Map<String, Object> map, SQLColumn... columnsToSearch) {
E e = createRowInstance();
for (SQLColumn c : columnsToSearch) {
if (c.getHashMapKey() != null) {
e.set(c, deserialize(c, map.get(c.getHashMapKey())));
}
}
return e;
}
}