panda.dao.sql.expert.SqlExpert Maven / Gradle / Ivy
Show all versions of panda-core Show documentation
package panda.dao.sql.expert;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import panda.cast.Castors;
import panda.dao.DB;
import panda.dao.DaoClient;
import panda.dao.DaoNamings;
import panda.dao.DatabaseMeta;
import panda.dao.entity.Entity;
import panda.dao.entity.EntityFKey;
import panda.dao.entity.EntityField;
import panda.dao.entity.EntityIndex;
import panda.dao.query.Filter;
import panda.dao.query.Filter.ComboFilter;
import panda.dao.query.Join;
import panda.dao.query.Operator;
import panda.dao.query.Query;
import panda.dao.sql.Sql;
import panda.dao.sql.Sqls;
import panda.lang.Iterators;
import panda.lang.Order;
import panda.lang.Strings;
import panda.lang.Texts;
/**
* SQL expert for general JDBC
*
* !! thread-safe !!
*/
public abstract class SqlExpert {
protected DatabaseMeta databaseMeta;
protected Map options;
protected Castors castors;
protected DaoClient client;
/**
* @return the databaseMeta
*/
public DatabaseMeta getDatabaseMeta() {
return databaseMeta;
}
/**
* @param name database name
* @param version database version
*/
public void setDatabaseMeta(String name, String version) {
databaseMeta = new DatabaseMeta(getDatabaseType(), name, version);
}
/**
* @return the database type
*/
public abstract DB getDatabaseType();
/**
* @return the options
*/
public Map getOptions() {
return options;
}
/**
* @param options the options to set
*/
public void setOptions(Map options) {
this.options = options;
}
/**
* @return the castors
*/
public Castors getCastors() {
return castors;
}
/**
* @param castors the castors to set
*/
public void setCastors(Castors castors) {
this.castors = castors;
}
/**
* @return the client
*/
public DaoClient getClient() {
return client;
}
/**
* @param client the client to set
*/
public void setClient(DaoClient client) {
this.client = client;
}
protected String getEntityOptionString(Entity entity, String name) {
Object v = getEntityOption(entity, name, null);
return (v == null ? null : v.toString());
}
protected String getEntityOptionString(Entity entity, String name, String defv) {
Object v = getEntityOption(entity, name, null);
return (v == null ? defv : v.toString());
}
protected Object getEntityOption(Entity entity, String name) {
return getEntityOption(entity, name, null);
}
protected Object getEntityOption(Entity entity, String name, Object defv) {
Object v = entity.getOption(name);
if (v == null && options != null) {
v = options.get(name);
}
return v == null ? defv : v;
}
public boolean isSupportAutoIncrement() {
return true;
}
public boolean isSupportPaginate() {
return true;
}
public boolean isSupportScroll() {
return true;
}
public boolean isSupportDropIfExists() {
return false;
}
public String meta(String tableName) {
return "SELECT * FROM " + escapeTable(tableName) + " where 1!=1";
}
public String exists(String tableName) {
return "SELECT COUNT(1) FROM " + escapeTable(tableName) + " where 1!=1";
}
public List drop(Entity entity) {
List sqls = new ArrayList(1);
sqls.add(dropTable(client.getTableName(entity)));
return sqls;
}
public String dropTable(String tableName) {
return "DROP TABLE " + escapeTable(tableName);
}
public String identityInsertOn(Entity entity) {
return null;
}
public String identityInsertOff(Entity entity) {
return null;
}
protected String getSequence(Entity entity) {
return client.getTableName(entity) + '_' + entity.getIdentity().getColumn() + "_SEQ";
}
public String prepIdentity(Entity entity) {
return null;
}
public String postIdentity(Entity entity) {
return null;
}
public abstract List create(Entity entity);
protected void asTableAlias(Sql sql, String alias) {
if (Strings.isNotEmpty(alias)) {
sql.append(' ').append(escapeTable(alias));
}
}
protected void asTableColumn(Sql sql, String talias, String col, String cname) {
sql.append(' ')
.append(escapeColumn(talias, col))
.append(" AS ")
.append(escapeColumn(DaoNamings.javaName2ColumnLabel(cname)))
.append(',');
}
public Sql count(Query query) {
return count(query, "c_");
}
protected Sql count(Query query, String alias) {
Sql sql = new Sql();
sql.append("SELECT COUNT(*) FROM ").append(escapeTable(client.getTableName(query)));
asTableAlias(sql, alias);
join(sql, query, alias);
where(sql, query, alias);
return sql;
}
public Sql select(Query query) {
return select(query, "s_");
}
protected Sql select(Query query, String alias) {
Sql sql = new Sql();
select(sql, query, alias);
join(sql, query, alias);
where(sql, query, alias);
group(sql, query, alias);
order(sql, query, alias);
_limit(sql, query, alias);
return sql;
}
public Sql delete(Query query) {
Sql sql = new Sql();
sql.append("DELETE FROM ").append(escapeTable(client.getTableName(query)));
where(sql, query, null);
return sql;
}
protected Object getFieldValue(EntityField ef, Object data) {
return ef.getValue(data);
}
public Sql insert(Entity entity, Object data, boolean autoId) {
if (!isSupportAutoIncrement()) {
autoId = false;
}
Sql sql = new Sql();
sql.append("INSERT INTO ").append(escapeTable(client.getTableName(entity)));
sql.append('(');
for (EntityField ef : entity.getFields()) {
if (ef.isReadonly() || (ef.isAutoIncrement() && autoId)) {
continue;
}
sql.append(' ').append(escapeColumn(ef.getColumn())).append(',');
}
sql.setCharAt(sql.length() - 1, ')');
sql.append(" VALUES (");
for (EntityField ef : entity.getFields()) {
if (ef.isReadonly() || (ef.isAutoIncrement() && autoId)) {
continue;
}
sql.append("?,");
Object v = getFieldValue(ef, data);
if (v == null && Strings.isNotEmpty(ef.getDefaultValue())) {
v = Texts.translate(ef.getDefaultValue(), data);
v = castors.cast(v, ef.getType());
}
sql.addParam(v);
}
sql.setCharAt(sql.length() - 1, ')');
return sql;
}
public Sql update(Object data, Query query) {
Sql sql = new Sql();
sql.append("UPDATE ").append(escapeTable(client.getTableName(query)));
sql.append(" SET");
Entity entity = query.getEntity();
boolean set = false;
for (EntityField ef : entity.getFields()) {
if (ef.isReadonly() || query.shouldExclude(ef.getName())) {
continue;
}
String col = ef.getColumn();
if (Strings.isEmpty(col)) {
throw new IllegalArgumentException("Can not update no-mapping field - " + ef.getName());
}
sql.append(' ');
String val = query.getColumn(ef.getName());
if (Strings.isEmpty(val)) {
if (data == null) {
throw new IllegalArgumentException("Failed to get field(" + ef.getName() + ") value from null object");
}
sql.append(escapeColumn(col)).append("=?,");
sql.addParam(getFieldValue(ef, data));
}
else {
sql.append(escapeColumn(col)).append('=').append(val).append(',');
}
set = true;
}
if (!set) {
throw new IllegalArgumentException("Nothing to UPDATE!");
}
sql.setLength(sql.length() - 1);
where(sql, query, null);
return sql;
}
protected EntityField getColumnField(Entity entity, String field, String name) {
EntityField ef = entity.getField(field);
if (ef == null || Strings.isEmpty(ef.getColumn())) {
throw new IllegalArgumentException("invalid " + name + " field '" + field + "' of entity " + entity.getType());
}
return ef;
}
protected void select(Sql sql, Query query, String alias) {
sql.append("SELECT");
if (query.isDistinct()) {
sql.append(" DISTINCT");
}
Entity entity = query.getEntity();
if (entity != null) {
boolean sel = false;
for (EntityField ef : entity.getFields()) {
if (query.shouldExclude(ef.getName())) {
continue;
}
String col = query.getColumn(ef.getName());
if (Strings.isEmpty(col)) {
col = ef.getColumn();
// skip join column which has not joined
if (Strings.isEmpty(col)) {
continue;
}
}
asTableColumn(sql, alias, col, ef.getName());
sel = true;
}
if (!sel) {
throw new IllegalArgumentException("Nothing to SELECT!");
}
sql.setCharAt(sql.length() - 1, ' ');
sql.append("FROM ").append(escapeTable(client.getViewName(entity)));
asTableAlias(sql, alias);
}
else {
boolean sel = false;
if (query.hasColumns()) {
for (Entry en : query.getColumns().entrySet()) {
String col = en.getValue();
if (col == null) {
continue;
}
if (col.length() == 0) {
col = en.getKey();
}
asTableColumn(sql, alias, col, en.getKey());
sel = true;
}
if (sel) {
sql.setCharAt(sql.length() - 1, ' ');
}
}
if (!sel) {
if (Strings.isNotEmpty(alias)) {
sql.append(' ').append(escapeTable(alias)).append('.');
}
sql.append("* ");
}
sql.append("FROM ").append(escapeTable(client.getTableName(query))).append(' ').append(escapeTable(alias));
}
}
protected void join(Sql sql, Query query, String talias) {
if (!query.hasJoins()) {
return;
}
Entity entity = query.getEntity();
for (Entry en : query.getJoins().entrySet()) {
String jalias = en.getKey();
Join join = en.getValue();
sql.append(' ').append(join.getType()).append(" JOIN ");
// join table
Query jq = join.getQuery();
if (jq.hasFilters()) {
sql.append('(');
sql.append(select(jq));
sql.append(')');
}
else {
sql.append(escapeTable(client.getTableName(jq)));
}
asTableAlias(sql, jalias);
// join condition
Entity je = jq.getEntity();
sql.append(" ON (");
for (String s : join.getConditions()) {
int l = 1;
int d = s.indexOf('=');
if (d < 0) {
l = 2;
d = s.indexOf("<>");
}
if (d < 0) {
throw new IllegalArgumentException("Invalid join condition: " + s);
}
// main table alias.column
String lc = Strings.trim(s.substring(0, d));
if (entity != null) {
EntityField ef = entity.getField(lc);
if (ef != null) {
lc = ef.getColumn();
}
}
sql.append(escapeColumn(talias, lc));
// operator
sql.append(s.substring(d, d + l));
// join table alias.column
String rc = Strings.trim(s.substring(d + l));
if (je != null) {
EntityField ef = je.getField(rc);
if (ef != null) {
rc = ef.getColumn();
}
}
sql.append(escapeColumn(jalias, rc));
sql.append(" AND ");
}
// remove last " AND ";
sql.setLength(sql.length() - 5);
sql.append(')');
}
}
private void whereComboFilter(Sql sql, Entity entity, String alias, ComboFilter cf) {
Iterator it = cf.getFilters().iterator();
while (it.hasNext()) {
Filter exp = it.next();
if (exp instanceof Filter.ValueFilter) {
Filter.ValueFilter evc = (Filter.ValueFilter)exp;
EntityField ef = getColumnField(entity, evc.getField(), "where");
whereValueFilter(sql, alias, ef.getColumn(), evc);
}
else if (exp instanceof Filter.ReferFilter) {
Filter.ReferFilter efc = (Filter.ReferFilter)exp;
EntityField ef = getColumnField(entity, efc.getField(), "where");
EntityField ef2 = getColumnField(entity, efc.getRefer(), "compare");
sql.append(escapeColumn(alias, ef.getColumn()))
.append(efc.getOperator()).append(escapeColumn(alias, ef2.getColumn()));
}
else if (exp instanceof Filter.SimpleFilter) {
Filter.SimpleFilter es = (Filter.SimpleFilter)exp;
EntityField ef = getColumnField(entity, es.getField(), "simple");
sql.append(escapeColumn(alias, ef.getColumn())).append(' ').append(es.getOperator());
}
else if (exp instanceof Filter.ComboFilter) {
// AND/OR
sql.append('(');
whereComboFilter(sql, entity, alias, (ComboFilter)exp);
sql.append(')');
}
if (it.hasNext()) {
sql.append(' ').append(cf.getLogical()).append(' ');
}
}
}
private void whereComboFilter(Sql sql, String alias, ComboFilter cf) {
Iterator it = cf.getFilters().iterator();
while (it.hasNext()) {
Filter f = it.next();
if (f instanceof Filter.ValueFilter) {
Filter.ValueFilter vf = (Filter.ValueFilter)f;
whereValueFilter(sql, alias, vf.getField(), vf);
}
else if (f instanceof Filter.ReferFilter || f instanceof Filter.SimpleFilter) {
sql.append(f.toString());
}
else if (f instanceof Filter.ComboFilter) {
// AND/OR
sql.append('(');
whereComboFilter(sql, alias, (ComboFilter)f);
sql.append(')');
}
if (it.hasNext()) {
sql.append(' ').append(cf.getLogical()).append(' ');
}
}
}
protected void where(Sql sql, Query query, String alias) {
if (!query.hasFilters()) {
return;
}
sql.append(" WHERE ");
Entity entity = query.getEntity();
if (entity != null) {
whereComboFilter(sql, entity, alias, query.getFilters());
}
else {
whereComboFilter(sql, alias, query.getFilters());
}
}
protected void whereValueFilter(Sql sql, String table, String column, Filter.ValueFilter vf) {
sql.append(escapeColumn(table, column)).append(' ');
Operator op = vf.getOperator();
if (op == Operator.BETWEEN || op == Operator.NOT_BETWEEN) {
sql.append(op).append(" ? AND ?");
sql.addParam(vf.getValue(0));
sql.addParam(vf.getValue(1));
}
else if (op == Operator.IN || op == Operator.NOT_IN) {
sql.append(op).append('(');
for (Object v : Iterators.asIterable(vf.getValue())) {
sql.append('?').append(',');
sql.addParam(v);
}
sql.setCharAt(sql.length() - 1, ')');
}
else if (op == Operator.CONTAINS) {
sql.append("LIKE ? ESCAPE '~'");
sql.addParam(Sqls.stringLike(vf.getValue().toString(), '~'));
}
else if (op == Operator.NOT_CONTAINS) {
sql.append("NOT LIKE ? ESCAPE '~'");
sql.addParam(Sqls.stringLike(vf.getValue().toString(), '~'));
}
else if (op == Operator.STARTS_WITH) {
sql.append("LIKE ? ESCAPE '~'");
sql.addParam(Sqls.startsLike(vf.getValue().toString(), '~'));
}
else if (op == Operator.NOT_STARTS_WITH) {
sql.append("NOT LIKE ? ESCAPE '~'");
sql.addParam(Sqls.startsLike(vf.getValue().toString(), '~'));
}
else if (op == Operator.ENDS_WITH) {
sql.append("LIKE ? ESCAPE '~'");
sql.addParam(Sqls.endsLike(vf.getValue().toString(), '~'));
}
else if (op == Operator.NOT_ENDS_WITH) {
sql.append("NOT LIKE ? ESCAPE '~'");
sql.addParam(Sqls.endsLike(vf.getValue().toString(), '~'));
}
else if (op == Operator.LIKE || op == Operator.NOT_LIKE) {
if (vf.getValue() instanceof Object[]) {
Object[] vs = (Object[])vf.getValue();
sql.append(op).append(" ? ESCAPE '").append(vs[1]).append('\'');
sql.addParam(vs[0]);
}
else {
sql.append(op).append(' ').append('?');
sql.addParam(vf.getValue());
}
}
else {
sql.append(op).append(' ').append('?');
sql.addParam(vf.getValue());
}
}
protected void order(Sql sql, Query query, String alias) {
order(sql.getSqlBuilder(), query, alias);
}
protected void order(StringBuilder sql, Query query, String alias) {
if (!query.hasOrders()) {
return;
}
sql.append(" ORDER BY");
Entity entity = query.getEntity();
if (entity != null) {
for (Entry en : query.getOrders().entrySet()) {
EntityField ef = entity.getField(en.getKey());
if (ef == null) {
// alias or something else
sql.append(' ').append(escapeColumn(en.getKey()));
}
else if (Strings.isEmpty(ef.getColumn())) {
// join column
sql.append(' ').append(escapeColumn(DaoNamings.javaName2ColumnLabel(ef.getName())));
}
else {
// normal column
sql.append(' ').append(escapeColumn(alias, ef.getColumn()));
}
sql.append(' ').append(en.getValue()).append(',');
}
}
else {
for (Entry en : query.getOrders().entrySet()) {
sql.append(' ').append(escapeColumn(en.getKey())).append(' ').append(en.getValue()).append(',');
}
}
sql.setCharAt(sql.length() - 1, ' ');
}
protected void group(Sql sql, Query query, String alias) {
if (!query.hasGroups()) {
return;
}
sql.append(" GROUP BY");
Entity entity = query.getEntity();
if (entity != null) {
for (String k : query.getGroups()) {
EntityField ef = entity.getField(k);
if (ef == null) {
// alias or something else
sql.append(' ').append(escapeColumn(k));
}
else if (Strings.isEmpty(ef.getColumn())) {
// join column
sql.append(' ').append(escapeColumn(DaoNamings.javaName2ColumnLabel(ef.getName())));
}
else {
// normal column
sql.append(' ').append(escapeColumn(alias, ef.getColumn()));
}
sql.append(',');
}
}
else {
for (String k : query.getGroups()) {
sql.append(' ').append(escapeColumn(k));
}
}
sql.setCharAt(sql.length() - 1, ' ');
}
//-----------------------------------------------------------------------
private void _limit(Sql sql, Query query, String alias) {
if (query != null && query.needsPaginate() && isSupportPaginate()) {
limit(sql, query, alias);
}
}
protected abstract void limit(Sql sql, Query query, String alias);
//-----------------------------------------------------------------------
protected String evalFieldType(String type, int size, int scale) {
if (size == 0 && scale == 0) {
return type;
}
if (scale == 0) {
return type + '(' + size + ')';
}
return type + '(' + size + ',' + scale + ')';
}
protected String evalFieldType(EntityField ef) {
String type = ef.getNativeType();
if (Strings.isEmpty(type)) {
type = ef.getJdbcType();
}
return evalFieldType(type, ef.getSize(), ef.getScale());
}
protected void addPrimaryKeys(StringBuilder sb, Entity entity) {
List pks = entity.getPrimaryKeys();
if (!pks.isEmpty()) {
sb.append('\n');
sb.append("PRIMARY KEY (");
for (EntityField pk : pks) {
sb.append(escapeColumn(pk.getColumn())).append(',');
}
sb.setCharAt(sb.length() - 1, ')');
sb.append("\n ");
}
}
protected void addPrimaryKeysConstraint(StringBuilder sql, Entity entity) {
List pks = entity.getPrimaryKeys();
if (!pks.isEmpty()) {
sql.append('\n');
sql.append("CONSTRAINT ").append(client.getTableName(entity)).append("_PK PRIMARY KEY (");
for (EntityField pk : pks) {
sql.append(pk.getColumn()).append(',');
}
sql.setCharAt(sql.length() - 1, ')');
sql.append("\n ");
}
}
protected String alterPrimaryKeys(Entity entity) {
List pks = entity.getPrimaryKeys();
if (pks.isEmpty()) {
return Strings.EMPTY;
}
StringBuilder sb = new StringBuilder();
sb.append("ALTER TABLE ")
.append(escapeTable(client.getTableName(entity)))
.append(" ADD CONSTRAINT ")
.append(client.getTableName(entity))
.append("_PK PRIMARY KEY (");
for (EntityField pk : pks) {
sb.append(escapeColumn(pk.getColumn())).append(',');
}
sb.setCharAt(sb.length() - 1, ')');
return sb.toString();
}
protected void addComments(List sqls, Entity entity) {
if (Strings.isNotEmpty(entity.getComment())) {
String sql = "COMMENT ON TABLE "
+ escapeTable(client.getTableName(entity))
+ " IS '"
+ Sqls.escapeString(entity.getComment())
+ '\'';
sqls.add(sql);
}
for (EntityField ef : entity.getFields()) {
if (Strings.isNotEmpty(ef.getComment())) {
String sql = "COMMENT ON COLUMN "
+ escapeColumn(client.getTableName(entity), ef.getColumn())
+ " IS '"
+ Sqls.escapeString(ef.getComment())
+ '\'';
sqls.add(sql);
}
}
}
protected void addIndexes(List sqls, Entity entity) {
Collection indexs = entity.getIndexes();
if (indexs == null || indexs.isEmpty()) {
return;
}
StringBuilder sb = new StringBuilder();
for (EntityIndex index : indexs) {
if (!index.isReal()) {
continue;
}
sb.setLength(0);
sb.append("Create");
if (index.isUnique()) {
sb.append(" UNIQUE");
}
sb.append(" Index ");
sb.append(client.getTableName(entity))
.append("_")
.append(index.isUnique() ? "UX" : "IX")
.append('_')
.append(index.getName());
sb.append(" ON ").append(escapeTable(client.getTableName(entity))).append(" (");
for (EntityField ef : index.getFields()) {
sb.append(escapeColumn(ef.getColumn())).append(',');
}
sb.setCharAt(sb.length() - 1, ')');
sqls.add(sb.toString());
}
}
protected void addForeignKeysConstraint(StringBuilder sql, Entity entity) {
Collection fks = entity.getForeignKeys();
if (fks == null || fks.isEmpty()) {
return;
}
for (EntityFKey fk: fks) {
sql.append('\n');
sql.append("CONSTRAINT ")
.append(client.getTableName(entity))
.append("_FK_")
.append(fk.getName())
.append(" FOREIGN KEY (");
for (EntityField ef : fk.getFields()) {
sql.append(escapeColumn(ef.getColumn())).append(',');
}
sql.setCharAt(sql.length() - 1, ')');
Entity ref = fk.getReference();
sql.append(" REFERENCES ").append(escapeTable(client.getTableName(ref))).append(" (");
for (EntityField ef : ref.getPrimaryKeys()) {
sql.append(escapeColumn(ef.getColumn())).append(',');
}
sql.setCharAt(sql.length() - 1, ')');
if (Strings.isNotEmpty(fk.getOnUpdate())) {
sql.append(" ON UPDATE ").append(fk.getOnUpdate());
}
if (Strings.isNotEmpty(fk.getOnDelete())) {
sql.append(" ON DELETE ").append(fk.getOnDelete());
}
sql.append(',');
}
}
protected void addForeignKeys(List sqls, Entity entity) {
Collection fks = entity.getForeignKeys();
if (fks == null || fks.isEmpty()) {
return;
}
for (EntityFKey fk: fks) {
StringBuilder sql = new StringBuilder();
sql.append("ALTER TABLE ")
.append(escapeTable(client.getTableName(entity)))
.append(" ADD CONSTRAINT ")
.append(client.getTableName(entity))
.append("_FK_")
.append(fk.getName())
.append(" FOREIGN KEY (");
for (EntityField ef : fk.getFields()) {
sql.append(escapeColumn(ef.getColumn())).append(',');
}
sql.setCharAt(sql.length() - 1, ')');
Entity ref = fk.getReference();
sql.append(" REFERENCES ").append(escapeTable(client.getTableName(ref))).append(" (");
for (EntityField ef : ref.getPrimaryKeys()) {
sql.append(escapeColumn(ef.getColumn())).append(',');
}
sql.setCharAt(sql.length() - 1, ')');
if (Strings.isNotEmpty(fk.getOnUpdate())) {
sql.append(" ON UPDATE ").append(fk.getOnUpdate());
}
if (Strings.isNotEmpty(fk.getOnDelete())) {
sql.append(" ON DELETE ").append(fk.getOnDelete());
}
sqls.add(sql.toString());
}
}
public String escape(String s) {
return s;
}
public String escapeColumn(String table, String column) {
if (Strings.startsWithChar(column, '(') && Strings.endsWithChar(column, ')')) {
return column;
}
int dot = Strings.indexOf(column, '.');
if (dot > 0) {
table = column.substring(0, dot);
column = column.substring(dot + 1);
}
if (Strings.isEmpty(table)) {
return escapeColumn(column);
}
return escapeTable(table) + '.' + escapeColumn(column);
}
public String escapeTable(String table) {
return escape(table);
}
public String escapeColumn(String column) {
return escape(column);
}
}