package com.taobao.yugong.common.db.sql;
import java.util.List;
import com.taobao.yugong.common.db.meta.ColumnMeta;
/**
* sql构造
*
* @author agapple 2013-9-10 下午6:10:10
* @since 1.0.0
*/
public class SqlTemplate {
private static final String DOT = ".";
/**
* 根据字段的列表顺序，拼写以 col1,col2,col3,....
*/
public String makeColumn(List<ColumnMeta> columns) {
StringBuilder str = new StringBuilder();
int size = columns.size();
for (int i = 0; i < size; i++) {
str.append(getColumnName(columns.get(i)));
if (i < (size - 1)) {
str.append(",");
}
}
return str.toString();
}
/**
* 根据字段的列表顺序，拼写以 ?,?,?,....
*/
public String makeInsert(List<ColumnMeta> columns) {
StringBuilder str = new StringBuilder();
int size = columns.size();
for (int i = 0; i < size; i++) {
str.append('?');
if (i < (size - 1)) {
str.append(",");
}
}
return str.toString();
}
/**
* 根据字段的列表顺序，拼写 column1=?,column2=?,column3=?,...
*/
public String makeUpdate(List<ColumnMeta> columns) {
StringBuilder str = new StringBuilder();
int size = columns.size();
for (int i = 0; i < size; i++) {
str.append(getColumnName(columns.get(i)));
str.append("=?");
if (i < (size - 1)) {
str.append(",");
}
}
return str.toString();
}
/**
* 根据字段列表，拼写column=? and column=? and ...字符串
*/
public String makeWhere(List<ColumnMeta> columns) {
StringBuilder sb = new StringBuilder("");
int size = columns.size();
for (int i = 0; i < size; i++) {
sb.append(getColumnName(columns.get(i)));
sb.append("=?");
if (i != (size - 1)) {
sb.append(" and ");
}
}
return sb.toString();
}
/**
* 根据字段列表，拼写column >= ? and column < ?
*/
public String makeRange(ColumnMeta column) {
return makeRange(column.getName());
}
/**
* 根据字段列表，拼写 column >= ? and column < ?
*/
public String makeRange(String columnName) {
StringBuilder sb = new StringBuilder("");
sb.append(getColumnName(columnName));
sb.append(" >= ? and ");
sb.append(getColumnName(columnName));
sb.append(" <= ?");
return sb.toString();
}
/**
* 根据字段名和参数个数，拼写 column in (?,?,...) 字符串
*/
public String makeIn(ColumnMeta column, int size) {
return makeIn(column.getName(), size);
}
/**
* 根据字段名和参数个数，拼写 column in (?,?,...) 字符串
*/
public String makeIn(String columnName, int size) {
StringBuilder sb = new StringBuilder("");
sb.append(getColumnName(columnName));
sb.append(" in (");
for (int i = 0; i < size; i++) {
sb.append("?");
if (i != (size - 1)) {
sb.append(",");
}
}
sb.append(")");
return sb.toString();
}
public String getSelectSql(String schemaName, String tableName, String[] pkNames, String[] colNames) {
StringBuilder sql = new StringBuilder();
sql.append("select ");
String[] allColumns = buildAllColumns(pkNames, colNames);
int size = allColumns.length;
for (int i = 0; i < size; i++) {
sql.append(getColumnName(allColumns[i])).append(splitCommea(size, i));
}
sql.append(" from ").append(makeFullName(schemaName, tableName)).append(" where ( ");
if (pkNames.length > 0) { // 可能没有主键
makeColumnEquals(sql, pkNames, "and");
} else {
makeColumnEquals(sql, colNames, "and");
}
sql.append(" ) ");
return sql.toString().intern();
}
public String getSelectInSql(String schemaName, String tableName, String[] pkNames, String[] columnNames, int inSize) {
StringBuilder sql = new StringBuilder("select ");
String[] allColumns = buildAllColumns(pkNames, columnNames);
int size = allColumns.length;
for (int i = 0; i < size; i++) {
sql.append(getColumnName(allColumns[i])).append(splitCommea(size, i));
}
sql.append(" from ").append(makeFullName(schemaName, tableName)).append(" where ( ");
size = pkNames.length;
if (size == 0) {
size = columnNames.length;
}
for (int i = 0; i < size; i++) {
if (pkNames.length > 0) {
sql.append(getColumnName(pkNames[i])).append(splitCommea(size, i));
} else {
sql.append(getColumnName(columnNames[i])).append(splitCommea(size, i));
}
}
sql.append(") in (");
size = pkNames.length;
if (size == 0) {
size = columnNames.length;
}
for (int i = 0; i < inSize; i++) {
sql.append('(');
for (int j = 0; j < size; j++) {
sql.append('?').append((j + 1 < size) ? " , " : "");
}
sql.append(')').append((i + 1 < inSize) ? " , " : "");
}
sql.append(")");
return sql.toString();
}
public String getInsertSql(String schemaName, String tableName, String[] pkNames, String[] columnNames) {
StringBuilder sql = new StringBuilder();
sql.append("insert into ").append(makeFullName(schemaName, tableName)).append("(");
String[] allColumns = buildAllColumns(pkNames, columnNames);
int size = allColumns.length;
for (int i = 0; i < size; i++) {
sql.append(getColumnName(allColumns[i])).append(splitCommea(size, i));
}
sql.append(") values (");
makeColumnQuestions(sql, allColumns);
sql.append(")");
return sql.toString().intern();
}
public String getUpdateSql(String schemaName, String tableName, String[] pkNames, String[] columnNames) {
StringBuilder sql = new StringBuilder();
sql.append("update ").append(makeFullName(schemaName, tableName)).append(" set ");
makeColumnEquals(sql, columnNames, ",");
sql.append(" where (");
makeColumnEquals(sql, pkNames, "and");
sql.append(")");
return sql.toString().intern();
}
public String getDeleteSql(String schemaName, String tableName, String[] pkNames) {
StringBuilder sql = new StringBuilder();
sql.append("delete from ").append(makeFullName(schemaName, tableName)).append(" where ");
makeColumnEquals(sql, pkNames, "and");
// intern优化，避免出现大量相同的字符串
return sql.toString().intern();
}
protected String makeFullName(String schemaName, String tableName) {
String full = schemaName + DOT + tableName;
return full.intern();
}
protected void makeColumnEquals(StringBuilder sql, String[] columns, String separator) {
int size = columns.length;
for (int i = 0; i < size; i++) {
sql.append(" ").append(getColumnName(columns[i])).append(" = ").append("? ");
if (i != size - 1) {
sql.append(separator);
}
}
}
protected void makeColumnQuestions(StringBuilder sql, String[] columns) {
int size = columns.length;
for (int i = 0; i < size; i++) {
sql.append("?").append(splitCommea(size, i));
}
}
protected String getColumnName(String columName) {
return columName;
}
protected String getColumnName(ColumnMeta column) {
return column.getName();
}
protected String splitCommea(int size, int i) {
return (i + 1 < size) ? " , " : "";
}
protected String[] buildAllColumns(String[] pkNames, String[] colNames) {
String[] allColumns = new String[pkNames.length + colNames.length];
System.arraycopy(colNames, 0, allColumns, 0, colNames.length);
System.arraycopy(pkNames, 0, allColumns, colNames.length, pkNames.length);
return allColumns;
}
}