package javaxt.sql;
import javaxt.json.*;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.concurrent.ConcurrentHashMap;
import java.util.*;
//******************************************************************************
//** Model
//******************************************************************************
/**
* Base class for persistence models generated by the javaxt-orm library.
*
******************************************************************************/
public abstract class Model {
protected Long id;
private String tableName; //escaped table name
private final String modelName;
private final HashMap fieldMap;
private String[] keywords;
private static ConcurrentHashMap
sqlCache = new ConcurrentHashMap<>();
private static ConcurrentHashMap
insertStatements = new ConcurrentHashMap<>();
private static ConcurrentHashMap
connPool = new ConcurrentHashMap<>();
private static ConcurrentHashMap
reservedKeywords = new ConcurrentHashMap<>();
private static ConcurrentHashMap
fields = new ConcurrentHashMap<>();
private static ConcurrentHashMap
tables = new ConcurrentHashMap<>();
//**************************************************************************
//** Constructor
//**************************************************************************
/** Used to create a new instance of this class.
* @param tableName The name of the table in the database associated with
* this model.
* @param fieldMap Used to map fields to column names. The key is the
* declared field name in the model and the value is the column name in the
* database. Example: fieldMap.put("countryCode", country_code");
* You do not need to include the "id" field.
*/
protected Model(String tableName, Map fieldMap){
//Set modelName
Class c = this.getClass();
String className = c.getName();
modelName = c.getSimpleName();
//Set keywords
synchronized(reservedKeywords){
keywords = reservedKeywords.get(className);
}
//Set tableInfo
synchronized(tables){
String[] tableInfo = tables.get(className);
if (tableInfo==null){
tableInfo = getTableInfo(tableName);
tables.put(className, tableInfo);
}
this.tableName = tableInfo[0];
}
//Set fieldMap
this.fieldMap = new HashMap<>();
Iterator it = fieldMap.keySet().iterator();
while (it.hasNext()){
String key = it.next();
String val = fieldMap.get(key);
this.fieldMap.put(key, val);
}
}
//**************************************************************************
//** getID
//**************************************************************************
public Long getID(){
return id;
}
//**************************************************************************
//** setID
//**************************************************************************
public void setID(Long id){
this.id = id;
}
//**************************************************************************
//** init
//**************************************************************************
/** Used to initialize the model using a record in the database.
* @param id Primary key in the database table associated with this model.
*/
protected final void init(long id) throws SQLException {
//Generate a list of fields names
ArrayList fieldNames = new ArrayList<>();
for (java.lang.reflect.Field f : this.getClass().getDeclaredFields()){
String fieldName = f.getName();
String columnName = fieldMap.get(fieldName);
if (columnName!=null){
Class c = f.getType();
if (ArrayList.class.isAssignableFrom(c)){
//Do nothing, probably a model
continue;
}
String className = c.getSimpleName();
if (className.equals("Geometry")){
fieldNames.add("ST_AsText(" + columnName + ") as " + columnName);
}
else{
fieldNames.add(columnName);
}
}
}
//Compile query
StringBuilder sql = new StringBuilder("select ");
boolean addID = true;
for (int i=0; i0) sql.append(", ");
String fieldName = fieldNames.get(i);
if (fieldName.equalsIgnoreCase("id")) addID = false;
sql.append(escape(fieldName));
}
if (addID) sql.append(", id");
sql.append(" from ");
sql.append(tableName);
sql.append(" where id=");
//Execute query using a PreparedStatement stored in the sqlCache. Note
//that PreparedStatements are not thread-safe and we are only caching one
//PreparedStatement per model. As a result, we can only execute one
//query at a time. In the future, we could add an option to allow users
//to specify the number of PreparedStatement per model.
PreparedStatement stmt = null;
String query = sql.toString() + "?";
try{
synchronized(sqlCache){
//Get or create prepared statement
stmt = sqlCache.get(query);
if (stmt==null){
Connection conn = getConnection(this.getClass());
stmt = conn.getConnection().prepareStatement(query);
sqlCache.put(query, stmt);
sqlCache.notify();
}
//Execute prepared statement
stmt.setLong(1, id);
try (java.sql.ResultSet rs = stmt.executeQuery()){
if (rs.next()){
update(rs);
this.id = id;
}
else{
throw new IllegalArgumentException();
}
}
}
}
catch(IllegalArgumentException e){
throw new SQLException(modelName + " not found");
}
catch(Exception e){
if (stmt!=null && stmt.getConnection().isClosed()){
//Remove the statement from the cache
synchronized(sqlCache){
sqlCache.remove(query);
sqlCache.notifyAll();
}
//Try closing the statement
try{stmt.close();}catch(Exception ex){}
//Call this method again
init(id);
return;
}
//If we're still here, execute query without a prepared statement
try (Connection conn = getConnection(this.getClass())){
query = sql.toString() + id;
try (Recordset rs = conn.getRecordset(query)){
rs.open(query, conn);
if (rs.EOF) throw new SQLException(modelName + " not found");
update(rs);
this.id = id;
}
}
catch(SQLException ex){
throw ex;
}
}
}
//**************************************************************************
//** update
//**************************************************************************
/** Used to set/update fields using a record from the database.
*/
protected abstract void update(Object rs) throws SQLException;
//**************************************************************************
//** update
//**************************************************************************
/** Used to set/update fields using a JSON representation of this class.
*/
protected abstract void update(JSONObject json) throws SQLException;
//**************************************************************************
//** save
//**************************************************************************
/** Used to persist the model in the database.
*/
public void save() throws SQLException {
//Get class name
String className = this.getClass().getName();
//Get list of fields and thier associated values
LinkedHashMap fields = getFields();
Iterator it;
//Identify and remove fields that we do not want to update in the database
ArrayList arr = new ArrayList<>();
it = fields.keySet().iterator();
while (it.hasNext()){
java.lang.reflect.Field f = it.next();
String fieldName = f.getName();
Class fieldType = f.getType();
if (fieldType.equals(ArrayList.class)){
arr.add(f);
}
if (fieldName.equalsIgnoreCase("id")){
arr.add(f);
}
}
for (java.lang.reflect.Field f : arr){
fields.remove(f);
}
//Update values as needed
int numNulls = 0;
for (Map.Entry entry : fields.entrySet()) {
Object val = entry.getValue();
if (val!=null){
if (val instanceof Model){
Model model = (Model) val;
model.save();
entry.setValue(model.getID());
}
else if (val instanceof JSONObject){
JSONObject json = (JSONObject) val;
if (json.isEmpty()) entry.setValue(null);
}
}
if (entry.getValue()==null) numNulls++;
}
//Check if there's anything to save
if (numNulls==fields.size()) return;
if (id==null){ //insert new record using prepared statement (faster than recordset)
//Get database fields associated with the model
Field[] dbFields;
synchronized(this.fields){ dbFields = this.fields.get(className); }
if (dbFields==null) throw new SQLException(
"Failed to retrieve metadata for " + className + ". " +
"The model may not have been initialized. See Model.init()");
//Generate list of database fields for insert
ArrayList updates = new ArrayList<>();
it = fields.keySet().iterator();
while (it.hasNext()){
java.lang.reflect.Field f = it.next();
Class fieldType = f.getType();
String packageName = fieldType.getPackage()==null ? "" :
fieldType.getPackage().getName();
//Get value. Replace with function as needed
Object val = fields.get(f);
if (packageName.startsWith("javaxt.geospatial.geometry") ||
packageName.startsWith("com.vividsolutions.jts.geom") ||
packageName.startsWith("org.locationtech.jts.geom")){
int srid = 4326; //getSRID();
try{
java.lang.reflect.Method method = fieldType.getMethod("getSRID");
if (method!=null){
Object obj = method.invoke(val, null);
if (obj!=null){
srid = (Integer) obj;
if (srid==0) srid = 4326;
}
}
}
catch(Exception e){
}
val = new Function(null, new Object[]{
val == null ? null : val.toString(),
srid
});
}
else if (packageName.startsWith("javaxt.json") ||
packageName.startsWith("org.json")){
val = new Function(null, new Object[]{
val == null ? null : val.toString()
});
}
boolean foundField = false;
String columnName = fieldMap.get(f.getName());
for (Field field : dbFields){
if (field.getName().equalsIgnoreCase(columnName)){
field = field.clone();
field.setValue(new Value(val));
updates.add(field);
foundField = true;
break;
}
}
if (!foundField){
throw new SQLException("Model/Schema mismatch. Failed to find " + columnName + " in the database.");
}
}
//Create new record. Note that PreparedStatements are not thread-safe
//and we are only caching one PreparedStatement per model. As a
//result, we can only insert one record per model at a time. If
//needed, we could add an option to allow users to specify the number
//of PreparedStatement per model.
synchronized(insertStatements){
//Get or create prepared statement
PreparedStatement stmt = insertStatements.get(className);
if (stmt==null){
Connection conn = getConnection(this.getClass());
StringBuilder sql = new StringBuilder();
sql.append("INSERT INTO " + tableName + " (");
it = fields.keySet().iterator();
while (it.hasNext()){
java.lang.reflect.Field f = it.next();
String columnName = fieldMap.get(f.getName());
sql.append(escape(columnName));
if (it.hasNext()) sql.append(",");
}
sql.append(") VALUES (");
it = fields.keySet().iterator();
while (it.hasNext()){
java.lang.reflect.Field f = it.next();
Class fieldType = f.getType();
String packageName = fieldType.getPackage()==null ? "" :
fieldType.getPackage().getName();
String q = "?";
if (packageName.startsWith("javaxt.json") ||
packageName.startsWith("org.json")){
javaxt.sql.Driver driver = conn.getDatabase().getDriver();
if (driver.equals("PostgreSQL")){
q = "?::jsonb";
}
}
else if (packageName.startsWith("javaxt.geospatial.geometry") ||
packageName.startsWith("com.vividsolutions.jts.geom") ||
packageName.startsWith("org.locationtech.jts.geom")){
String columnName = fieldMap.get(f.getName());
String STGeomFromText = null;
for (Field field : dbFields){
if (field.getName().equals(columnName)){
STGeomFromText = Recordset.getSTGeomFromText(field, conn);
break;
}
}
q = STGeomFromText + "(?,?)";
}
sql.append(q);
if (it.hasNext()) sql.append(",");
}
sql.append(")");
stmt = conn.getConnection().prepareStatement(sql.toString(), new String[]{"id"});
insertStatements.put(className, stmt);
insertStatements.notify();
}
//Insert record
try{
Recordset.update(stmt, updates);
stmt.executeUpdate();
}
catch(SQLException e){
if (stmt!=null && stmt.getConnection().isClosed()){
synchronized(insertStatements){
insertStatements.remove(className);
insertStatements.notifyAll();
}
save();
}
else{
throw Exception("Failed to save " + className + ". " + e.getMessage(), e);
}
}
//Get id
java.sql.ResultSet generatedKeys = stmt.getGeneratedKeys();
if (generatedKeys.next()) {
id = new Value(generatedKeys.getString(1)).toLong();
}
}
}
else{ //update existing record
try (Connection conn = getConnection(this.getClass())){
javaxt.sql.Driver driver = conn.getDatabase().getDriver();
if (driver==null) driver = new Driver("","","");
try(Recordset rs = conn.getRecordset(
"select * from " + tableName + " where id=" + id, false)){
if (rs.EOF){
rs.addNew();
rs.setValue("id", id);
}
it = fields.keySet().iterator();
while (it.hasNext()){
java.lang.reflect.Field f = it.next();
String columnName = fieldMap.get(f.getName());
Object val = fields.get(f);
if (val instanceof JSONObject || val instanceof JSONArray){
if (driver.equals("PostgreSQL")){
rs.setValue(columnName, new javaxt.sql.Function(
"?::jsonb", new Object[]{
val.toString()
}
));
}
else{
rs.setValue(columnName, val.toString());
}
}
else{
rs.setValue(columnName, val);
}
}
rs.update();
}
}
catch(SQLException e){
throw Exception("Failed to update " + className + "#" + id + ". " + e.getMessage(), e);
}
}
}
//**************************************************************************
//** delete
//**************************************************************************
/** Used to delete the model from the database.
*/
public void delete() throws SQLException {
if (id==null) return;
try (Connection conn = getConnection(this.getClass())){
conn.execute("delete from " + tableName + " where id=" + id);
}
catch(SQLException e){
String className = this.getClass().getName();
throw Exception("Failed to delete " + className + "#" + id + ". " + e.getMessage(), e);
}
}
//**************************************************************************
//** getException
//**************************************************************************
/** Used to build a custom exception when saving or deleting records.
*/
private SQLException Exception(String err, SQLException e){
SQLException ex = new SQLException(err);
ArrayList stackTrace = new ArrayList<>();
boolean addElement = false;
StackTraceElement[] arr = ex.getStackTrace();
for (int i=2; i fields = getFields();
Iterator it = fields.keySet().iterator();
while (it.hasNext()){
java.lang.reflect.Field f = it.next();
String fieldName = f.getName();
Object val = fields.get(f);
if (val!=null){
//Check if the val is a Model or an array of Models. If so,
//convert the val to JSON
if (val instanceof ArrayList){
ArrayList list = (ArrayList) val;
if (!list.isEmpty()){
Class c = list.get(0).getClass();
if (javaxt.sql.Model.class.isAssignableFrom(c)){
JSONArray arr = new JSONArray();
for (Object obj : list){
arr.add(((Model) obj).toJson());
}
val = arr;
}
}
}
else if (val instanceof Model){
val = ((Model) val).toJson();
}
json.set(fieldName, val);
}
}
return json;
}
//**************************************************************************
//** equals
//**************************************************************************
public boolean equals(Object obj){
if (id!=null){
if (this.getClass().isAssignableFrom(obj.getClass())){
return obj.hashCode()==hashCode();
}
}
return false;
}
//**************************************************************************
//** hashCode
//**************************************************************************
public int hashCode(){
return id==null ? -1 : (int) (id ^ (id >>> 32));
}
//**************************************************************************
//** _get
//**************************************************************************
/** Used to find a model in the database using a given set of constraints.
* Example:
_get(Contact.class, "firstname=", "John", "lastname=", "Smith");
_get(Contact.class, 123);
*/
protected static Object _get(Class c, Object...args) throws SQLException {
if (args.length==1){
if ((args[0] instanceof Long) || (args[0] instanceof Integer)){
try{
long id;
if (args[0] instanceof Long) id = (Long) args[0];
else id = new Long((Integer) args[0]);
return c.getConstructor(long.class).newInstance(id);
}
catch(Exception e){
return null;
}
}
else{
return null;
}
}
//Build sql to find the model id
String sql = getSQL(c, args);
//Execute query
Long id = null;
try (Connection conn = getConnection(c)){
javaxt.sql.Record record = conn.getRecord(sql);
if (record!=null) id = record.get(0).toLong();
}
catch(SQLException e){
throw e;
}
//Return model
if (id!=null){
try{
return c.getConstructor(long.class).newInstance(id);
}
catch(Exception e){
throw new SQLException("Failed to instantiate model for " + id);
}
}
return null;
}
//**************************************************************************
//** _find
//**************************************************************************
/** Returns an array of models from the database using a given set of
* constraints.
*/
protected static Object[] _find(Class c, Object...args) throws SQLException {
//Build sql using args
String sql = getSQL(c, args);
//Execute query
ArrayList ids = new ArrayList<>();
try (Connection conn = getConnection(c)){
for (javaxt.sql.Record record : conn.getRecords(sql)){
ids.add(record.get(0).toLong());
}
}
//Return model
if (!ids.isEmpty()){
ArrayList arr = new ArrayList(ids.size());
for (long id : ids){
try{
arr.add(c.getConstructor(long.class).newInstance(id));
}
catch(Exception e){
throw new SQLException("Failed to instantiate model for " + id);
}
}
return arr.toArray();
}
return new Object[0];
}
//**************************************************************************
//** getSQL
//**************************************************************************
/** Returns a sql statement used to generate a list of model IDs
*/
private static String getSQL(Class c, Object...args){
//Get tableName
String tableName = null;
try{ tableName = ((Model) c.newInstance()).tableName; }
catch(Exception e){}
StringBuilder str = new StringBuilder("select ");
str.append(tableName);
str.append(".id from ");
str.append(tableName);
if (args.length>1){
str.append(" where ");
for (int i=0; i
for (Jar.Entry entry : jar.getEntries()){
String name = entry.getName();
if (name.endsWith(".class")){
name = name.substring(0, name.length()-6).replace("/", ".");
Class c = Class.forName(name);
if (javaxt.sql.Model.class.isAssignableFrom(c)){
javaxt.sql.Model.init(c, database.getConnectionPool());
}
}
}
*/
public static void init(Class c, ConnectionPool connectionPool) throws SQLException {
//Check if class is a model
if (!Model.class.isAssignableFrom(c)){
throw new IllegalArgumentException();
}
//Get class name
String className = c.getName();
//Get database connection
try (Connection conn = connectionPool.getConnection()){
//Associate model with the connection pool
synchronized(connPool){
connPool.put(className, connectionPool);
connPool.notifyAll();
}
//Get reserved keywords associated with the database
String[] keywords = Database.getReservedKeywords(conn);
synchronized(reservedKeywords){
reservedKeywords.put(className, keywords);
reservedKeywords.notifyAll();
}
//Generate list of fields
Model model = ((Model) c.newInstance());
String sql = "select * from " + model.tableName + " where id is null";
try (Recordset rs = conn.getRecordset(sql)){
synchronized(fields){
fields.put(className, rs.getFields());
fields.notifyAll();
}
}
}
catch(Exception e){
SQLException ex = new SQLException("Failed to initialize Model: " + className);
ex.setStackTrace(e.getStackTrace());
throw ex;
}
}
//**************************************************************************
//** init
//**************************************************************************
/** Used to initialize all the Models found in a jar file and associate them
* with a database connection pool. This allows queries and other database
* metadata to be cached.
*/
public static void init(javaxt.io.Jar jar, ConnectionPool connectionPool) throws SQLException {
for (Class c : jar.getClasses()){
if (javaxt.sql.Model.class.isAssignableFrom(c)){
init(c, connectionPool);
}
}
}
//**************************************************************************
//** getTableName
//**************************************************************************
/** Returns the name of the table backing a given Model
*/
public static String getTableName(Model model){
return model.tableName;
}
//**************************************************************************
//** setSchemaName
//**************************************************************************
/** Provides an option to override the default schema used by a model
*/
public static void setSchemaName(String schemaName, Class c){
if (!javaxt.sql.Model.class.isAssignableFrom(c)) return;
String className = c.getName();
try{
//Instantiate model to initialize table info
Model model = ((Model) c.newInstance());
//Update table info
synchronized(tables){
String[] tableInfo = tables.get(className);
String tableName = tableInfo[1];
if (schemaName!=null) tableName = schemaName + "." + tableName;
tableInfo = model.getTableInfo(tableName);
tables.put(className, tableInfo);
}
}
catch(Exception e){
Exception ex = new Exception("Failed to update schema for Model: " + className);
ex.setStackTrace(e.getStackTrace());
throw new RuntimeException(ex);
}
}
//**************************************************************************
//** getTableInfo
//**************************************************************************
/** Used to parse a given string and extract table info (e.g. schema name,
* table name, etc).
*/
private String[] getTableInfo(String tableName){
String schemaName;
String escapedTableName;
int idx = tableName.indexOf(".");
if (idx>-1){
schemaName = tableName.substring(0, idx);
tableName = tableName.substring(idx+1);
escapedTableName = escape(schemaName) + "." + tableName;
}
else{
schemaName = null;
escapedTableName = escape(tableName);
}
return new String[]{escapedTableName, tableName, schemaName};
}
//**************************************************************************
//** escape
//**************************************************************************
/** Used to wrap column and table names in quotes if the name is a reserved
* SQL keyword.
*/
protected String escape(String colName){
/*
//TODO: Check whether the colName contains a function. Otherwise, the
//following logic won't work...
colName = colName.trim();
if (colName.contains(" ") && !colName.startsWith("[")){
colName = "[" + colName + "]";
}
*/
if (keywords==null) return colName;
for (String keyWord : keywords){
if (colName.equalsIgnoreCase(keyWord)){
colName = "\"" + colName + "\"";
break;
}
}
return colName;
}
//**************************************************************************
//** getFields
//**************************************************************************
/** Returns a list of private fields in the class and any associated values.
*/
private LinkedHashMap getFields(){
LinkedHashMap fields = new LinkedHashMap<>();
for (java.lang.reflect.Field f : this.getClass().getDeclaredFields()){
String fieldName = f.getName();
if (fieldMap.containsKey(fieldName)){
Object val = null;
try{
f.setAccessible(true);
val = f.get(this);
}
catch(Exception e){}
fields.put(f, val);
}
}
return fields;
}
}