From 1d381026a5d256a59fe41da42c7f4e86bb60f02c Mon Sep 17 00:00:00 2001 From: James Moger Date: Wed, 10 Aug 2011 09:01:04 -0400 Subject: [PATCH] Dialect refinements. --- src/com/iciql/Db.java | 70 +++++++++------- src/com/iciql/SQLDialect.java | 33 +++++--- src/com/iciql/SQLStatement.java | 4 +- src/com/iciql/TableDefinition.java | 38 ++------- src/com/iciql/TableInspector.java | 11 +-- src/com/iciql/dialect/DefaultSQLDialect.java | 30 +++---- src/com/iciql/dialect/H2Dialect.java | 33 ++++++++ src/com/iciql/dialect/MySQLDialect.java | 85 ++++++++++++++++++++ 8 files changed, 207 insertions(+), 97 deletions(-) create mode 100644 src/com/iciql/dialect/MySQLDialect.java diff --git a/src/com/iciql/Db.java b/src/com/iciql/Db.java index cae384d..d6f70d1 100644 --- a/src/com/iciql/Db.java +++ b/src/com/iciql/Db.java @@ -18,6 +18,7 @@ package com.iciql; import java.sql.Connection; +import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; @@ -69,38 +70,51 @@ public class Db { static { TOKENS = Collections.synchronizedMap(new WeakIdentityHashMap()); DIALECTS = Collections.synchronizedMap(new HashMap>()); - DIALECTS.put("org.h2", H2Dialect.class); + // can register by... + // 1. Connection class name + // 2. DatabaseMetaData.getDatabaseProductName() + DIALECTS.put("h2", H2Dialect.class); } private Db(Connection conn) { this.conn = conn; - dialect = getDialect(conn.getClass().getCanonicalName()); - dialect.configureDialect(conn); - } - - public static void registerDialect(Connection conn, Class dialectClass) { - registerDialect(conn.getClass().getCanonicalName(), dialectClass); - } - - public static void registerDialect(String connClass, Class dialectClass) { - DIALECTS.put(connClass, dialectClass); + String databaseName = null; + DatabaseMetaData data = null; + try { + data = conn.getMetaData(); + databaseName = data.getDatabaseProductName(); + } catch (SQLException s) { + throw new IciqlException("Failed to retrieve database metadata!", s); + } + dialect = getDialect(databaseName, conn.getClass().getName()); + dialect.configureDialect(databaseName, data); } - SQLDialect getDialect(String clazz) { - // try dialect by connection class name - Class dialectClass = DIALECTS.get(clazz); - while (dialectClass == null) { - // try dialect by registered name - for (String registeredName : DIALECTS.keySet()) { - if (clazz.indexOf(registeredName) > -1) { - dialectClass = DIALECTS.get(registeredName); - break; - } - } - if (dialectClass == null) { - // did not find a match, use default - dialectClass = DefaultSQLDialect.class; - } + /** + * Register a new/custom dialect class. You can use this method to replace + * any existing dialect or to add a new one. + * + * @param token + * the fully qualified name of the connection class or the + * expected result of DatabaseMetaData.getDatabaseProductName() + * @param dialectClass + * the dialect class to register + */ + public static void registerDialect(String token, Class dialectClass) { + DIALECTS.put(token, dialectClass); + } + + SQLDialect getDialect(String databaseName, String className) { + Class dialectClass = null; + if (DIALECTS.containsKey(className)) { + // dialect registered by connection class name + dialectClass = DIALECTS.get(className); + } else if (DIALECTS.containsKey(databaseName)) { + // dialect registered by database name + dialectClass = DIALECTS.get(databaseName); + } else { + // did not find a match, use default + dialectClass = DefaultSQLDialect.class; } return instance(dialectClass); } @@ -268,8 +282,8 @@ public class Db { // table is using iciql version tracking. DbVersion v = new DbVersion(); String schema = StringUtils.isNullOrEmpty(model.schemaName) ? "" : model.schemaName; - DbVersion dbVersion = from(v).where(v.schemaName).like(schema).and(v.tableName).like(model.tableName) - .selectFirst(); + DbVersion dbVersion = from(v).where(v.schemaName).like(schema).and(v.tableName) + .like(model.tableName).selectFirst(); if (dbVersion == null) { // table has no version registration, but model specifies // version: insert DbVersion entry diff --git a/src/com/iciql/SQLDialect.java b/src/com/iciql/SQLDialect.java index 8291e53..3ca2c4c 100644 --- a/src/com/iciql/SQLDialect.java +++ b/src/com/iciql/SQLDialect.java @@ -17,7 +17,7 @@ package com.iciql; -import java.sql.Connection; +import java.sql.DatabaseMetaData; import com.iciql.TableDefinition.IndexDefinition; @@ -28,22 +28,23 @@ import com.iciql.TableDefinition.IndexDefinition; public interface SQLDialect { /** - * Configure the dialect from the database connection. + * Configure the dialect from the database metadata. * - * @param conn + * @param databaseName + * @param data */ - void configureDialect(Connection conn); + void configureDialect(String databaseName, DatabaseMetaData data); /** * Returns a properly formatted table name for the dialect. * - * @param schema + * @param schemaName * the schema name, or null for no schema - * @param table + * @param tableName * the properly formatted table name * @return the SQL snippet */ - String prepareTableName(String schema, String table); + String prepareTableName(String schemaName, String tableName); /** * Returns a properly formatted column name for the dialect. @@ -57,15 +58,27 @@ public interface SQLDialect { /** * Get the CREATE INDEX statement. * - * @param schema + * @param schemaName * the schema name - * @param table + * @param tableName * the table name * @param index * the index definition * @return the SQL statement */ - String prepareCreateIndex(String schema, String table, IndexDefinition index); + String prepareCreateIndex(String schemaName, String tableName, IndexDefinition index); + + /** + * Get a MERGE or REPLACE INTO statement. + * + * @param schemaName + * the schema name + * @param tableName + * the table name + * @param index + * the index definition + */ + void prepareMerge(SQLStatement stat, String schemaName, String tableName, TableDefinition def, Object obj); /** * Append "LIMIT limit" to the SQL statement. diff --git a/src/com/iciql/SQLStatement.java b/src/com/iciql/SQLStatement.java index 3a7c901..49c7627 100644 --- a/src/com/iciql/SQLStatement.java +++ b/src/com/iciql/SQLStatement.java @@ -38,7 +38,7 @@ public class SQLStatement { this.db = db; } - void setSQL(String sql) { + public void setSQL(String sql) { this.sql = sql; buff = new StringBuilder(sql); } @@ -64,7 +64,7 @@ public class SQLStatement { return sql; } - SQLStatement addParameter(Object o) { + public SQLStatement addParameter(Object o) { params.add(o); return this; } diff --git a/src/com/iciql/TableDefinition.java b/src/com/iciql/TableDefinition.java index eb018bc..5d934a5 100644 --- a/src/com/iciql/TableDefinition.java +++ b/src/com/iciql/TableDefinition.java @@ -67,12 +67,12 @@ public class TableDefinition { * The meta data of a field. */ - static class FieldDefinition { - String columnName; + public static class FieldDefinition { + public String columnName; Field field; String dataType; int maxLength; - boolean isPrimaryKey; + public boolean isPrimaryKey; boolean isAutoIncrement; boolean trimString; boolean nullable; @@ -121,12 +121,12 @@ public class TableDefinition { } } + public ArrayList fields = Utils.newArrayList(); String schemaName; String tableName; int tableVersion; private boolean createTableIfRequired = true; private Class clazz; - private ArrayList fields = Utils.newArrayList(); private IdentityHashMap fieldMap = Utils.newIdentityHashMap(); private List primaryKeyColumnNames; @@ -362,7 +362,7 @@ public class TableDefinition { * Optionally truncates strings to the maximum length and converts * java.lang.Enum types to Strings or Integers. */ - private Object getValue(Object obj, FieldDefinition field) { + public Object getValue(Object obj, FieldDefinition field) { Object value = field.getValue(obj); if (value == null) { return value; @@ -434,33 +434,7 @@ public class TableDefinition { + " - no update possible"); } SQLStatement stat = new SQLStatement(db); - StatementBuilder buff = new StatementBuilder("MERGE INTO "); - buff.append(db.getDialect().prepareTableName(schemaName, tableName)).append(" ("); - buff.resetCount(); - for (FieldDefinition field : fields) { - buff.appendExceptFirst(", "); - buff.append(db.getDialect().prepareColumnName(field.columnName)); - } - buff.append(") KEY("); - buff.resetCount(); - for (FieldDefinition field : fields) { - if (field.isPrimaryKey) { - buff.appendExceptFirst(", "); - buff.append(db.getDialect().prepareColumnName(field.columnName)); - } - } - buff.append(") "); - buff.resetCount(); - buff.append("VALUES ("); - for (FieldDefinition field : fields) { - buff.appendExceptFirst(", "); - buff.append('?'); - Object value = getValue(obj, field); - stat.addParameter(value); - } - buff.append(')'); - stat.setSQL(buff.toString()); - + db.getDialect().prepareMerge(stat, schemaName, tableName, this, obj); StatementLogger.merge(stat.getSQL()); stat.executeUpdate(); } diff --git a/src/com/iciql/TableInspector.java b/src/com/iciql/TableInspector.java index 51b2d69..a5b6dee 100644 --- a/src/com/iciql/TableInspector.java +++ b/src/com/iciql/TableInspector.java @@ -226,16 +226,7 @@ public class TableInspector { ap.addParameter("name", table); if (primaryKeys.size() > 1) { - StatementBuilder pk = new StatementBuilder(); - pk.append("{ "); - for (String key : primaryKeys) { - pk.appendExceptFirst(", "); - pk.append("\""); - pk.append(key); - pk.append("\""); - } - pk.append(" }"); - ap.addParameter("primaryKey", pk.toString()); + ap.addParameter("primaryKey", primaryKeys); } // finish @IQTable annotation diff --git a/src/com/iciql/dialect/DefaultSQLDialect.java b/src/com/iciql/dialect/DefaultSQLDialect.java index 517ae10..f3f222e 100644 --- a/src/com/iciql/dialect/DefaultSQLDialect.java +++ b/src/com/iciql/dialect/DefaultSQLDialect.java @@ -1,12 +1,12 @@ package com.iciql.dialect; -import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.SQLException; import com.iciql.IciqlException; import com.iciql.SQLDialect; import com.iciql.SQLStatement; +import com.iciql.TableDefinition; import com.iciql.TableDefinition.IndexDefinition; import com.iciql.util.StringUtils; @@ -16,25 +16,20 @@ import com.iciql.util.StringUtils; */ public class DefaultSQLDialect implements SQLDialect { float databaseVersion; - String productName; + String databaseName; String productVersion; @Override public String toString() { - return getClass().getName() + ": " + productName + " " + productVersion; + return getClass().getName() + ": " + databaseName + " " + productVersion; } @Override - public void configureDialect(Connection conn) { - loadIdentity(conn); - } - - protected void loadIdentity(Connection conn) { + public void configureDialect(String databaseName, DatabaseMetaData data) { + this.databaseName = databaseName; try { - DatabaseMetaData data = conn.getMetaData(); databaseVersion = Float.parseFloat(data.getDatabaseMajorVersion() + "." + data.getDatabaseMinorVersion()); - productName = data.getDatabaseProductName(); productVersion = data.getDatabaseProductVersion(); } catch (SQLException e) { throw new IciqlException(e); @@ -57,11 +52,11 @@ public class DefaultSQLDialect implements SQLDialect { } @Override - public String prepareTableName(String schema, String table) { - if (StringUtils.isNullOrEmpty(schema)) { - return table; + public String prepareTableName(String schemaName, String tableName) { + if (StringUtils.isNullOrEmpty(schemaName)) { + return tableName; } - return schema + "." + table; + return schemaName + "." + tableName; } @Override @@ -70,10 +65,15 @@ public class DefaultSQLDialect implements SQLDialect { } @Override - public String prepareCreateIndex(String schema, String table, IndexDefinition index) { + public String prepareCreateIndex(String schemaName, String tableName, IndexDefinition index) { throw new IciqlException("Dialect does not support index creation!"); } + @Override + public void prepareMerge(SQLStatement stat, String schemaName, String tableName, TableDefinition def, Object obj) { + throw new IciqlException("Dialect does not support merge statements!"); + } + @Override public void appendLimit(SQLStatement stat, long limit) { stat.appendSQL(" LIMIT " + limit); diff --git a/src/com/iciql/dialect/H2Dialect.java b/src/com/iciql/dialect/H2Dialect.java index e0a5035..61babd7 100644 --- a/src/com/iciql/dialect/H2Dialect.java +++ b/src/com/iciql/dialect/H2Dialect.java @@ -1,5 +1,8 @@ package com.iciql.dialect; +import com.iciql.SQLStatement; +import com.iciql.TableDefinition; +import com.iciql.TableDefinition.FieldDefinition; import com.iciql.TableDefinition.IndexDefinition; import com.iciql.util.StatementBuilder; @@ -47,4 +50,34 @@ public class H2Dialect extends DefaultSQLDialect { buff.append(")"); return buff.toString(); } + + @Override + public void prepareMerge(SQLStatement stat, String schemaName, String tableName, TableDefinition def, Object obj) { + StatementBuilder buff = new StatementBuilder("MERGE INTO "); + buff.append(prepareTableName(schemaName, tableName)).append(" ("); + buff.resetCount(); + for (FieldDefinition field : def.fields) { + buff.appendExceptFirst(", "); + buff.append(field.columnName); + } + buff.append(") KEY("); + buff.resetCount(); + for (FieldDefinition field : def.fields) { + if (field.isPrimaryKey) { + buff.appendExceptFirst(", "); + buff.append(field.columnName); + } + } + buff.append(") "); + buff.resetCount(); + buff.append("VALUES ("); + for (FieldDefinition field : def.fields) { + buff.appendExceptFirst(", "); + buff.append('?'); + Object value = def.getValue(obj, field); + stat.addParameter(value); + } + buff.append(')'); + stat.setSQL(buff.toString()); + } } \ No newline at end of file diff --git a/src/com/iciql/dialect/MySQLDialect.java b/src/com/iciql/dialect/MySQLDialect.java new file mode 100644 index 0000000..e1ccaf3 --- /dev/null +++ b/src/com/iciql/dialect/MySQLDialect.java @@ -0,0 +1,85 @@ +package com.iciql.dialect; + +import com.iciql.SQLStatement; +import com.iciql.TableDefinition; +import com.iciql.TableDefinition.FieldDefinition; +import com.iciql.TableDefinition.IndexDefinition; +import com.iciql.util.StatementBuilder; + +/** + * H2 database dialect. + */ +public class MySQLDialect extends DefaultSQLDialect { + + @Override + public boolean supportsMemoryTables() { + return false; + } + + @Override + public boolean supportsMerge() { + return true; + } + + @Override + public String prepareColumnName(String name) { + return "`" + name + "`"; + } + + @Override + public String prepareCreateIndex(String schema, String table, IndexDefinition index) { + StatementBuilder buff = new StatementBuilder(); + buff.append("CREATE "); + switch (index.type) { + case STANDARD: + break; + case UNIQUE: + buff.append("UNIQUE "); + break; + case UNIQUE_HASH: + buff.append("UNIQUE "); + break; + } + buff.append("INDEX "); + buff.append(index.indexName); + buff.append(" ON "); + buff.append(table); + buff.append("("); + for (String col : index.columnNames) { + buff.appendExceptFirst(", "); + buff.append(prepareColumnName(col)); + } + buff.append(") "); + + // USING + switch (index.type) { + case HASH: + buff.append("USING HASH"); + break; + case UNIQUE_HASH: + buff.append("USING HASH"); + break; + } + return buff.toString().trim(); + } + + @Override + public void prepareMerge(SQLStatement stat, String schemaName, String tableName, TableDefinition def, Object obj) { + StatementBuilder buff = new StatementBuilder("REPLACE INTO "); + buff.append(prepareTableName(schemaName, tableName)).append('('); + for (FieldDefinition field : def.fields) { + buff.appendExceptFirst(", "); + buff.append(prepareColumnName(field.columnName)); + } + buff.append(") VALUES("); + buff.resetCount(); + for (FieldDefinition field : def.fields) { + buff.appendExceptFirst(", "); + buff.append('?'); + Object value = def.getValue(obj, field); + stat.addParameter(value); + } + buff.append(')'); + stat.setSQL(buff.toString()); + } +} \ No newline at end of file -- 2.39.5