GitHubプロジェクト:jkrasnay/sqlbuilderの使用
10455 ワード
image.png
技術選択:
技術選択:
レポート・クエリーでは、通常、動的に追加できる条件が必要です.
古いプロジェクトで使用されているのはTconditionのオブジェクト分割sqlです.
import java.util.HashMap;
public class TCondition {
private String sql;
private Object[] paraValues;
private String orderBy;
private String groupBy;
private String having;
/**
* key ,value left join on ( )
*/
private HashMap leftJoinMap;
/**
* count count , count(1)
*/
private String mainId;
public TCondition() {
}
public TCondition(String sql, Object... paraValues) {
this.setSql(sql);
this.setParaValues(paraValues);
}
public static TCondition of() {
return new TCondition();
}
public static TCondition of(String sql, Object... paraValues) {
return new TCondition(sql, paraValues);
}
public String getSql() {
return sql;
}
public TCondition setSql(String sql) {
this.sql = sql;
return this;
}
public Object[] getParaValues() {
return paraValues;
}
public TCondition setParaValues(Object[] paraValues) {
this.paraValues = paraValues;
return this;
}
public String getOrderBy() {
return orderBy;
}
public TCondition setOrderBy(String orderBy) {
this.orderBy = orderBy;
return this;
}
public String getGroupBy() {
return groupBy;
}
public TCondition setGroupBy(String groupBy) {
this.groupBy = groupBy;
return this;
}
public String getHaving() {
return having;
}
public TCondition setHaving(String having) {
this.having = having;
return this;
}
public String getMainId() {
return mainId;
}
public TCondition setMainId(String mainId) {
this.mainId = mainId;
return this;
}
@Override
public String toString() {
StringBuffer result = new StringBuffer(this.getSql() + ":");
for (int i = 0; i < paraValues.length; i++) {
result.append(paraValues[i]).append(",");
}
result.append(" || ");
return result.toString();
}
public HashMap getLeftJoinMap() {
return leftJoinMap;
}
public TCondition setLeftJoinMap(HashMap leftJoinMap) {
this.leftJoinMap = leftJoinMap;
return this;
}
しかし、古いコードのデータベース・アクセス・レイヤと緊密に結合されているため、他のプロジェクトで多重化できないため、GitHubでは類似のパッケージSQLクエリーのオブジェクトが見つかりました.
パッケージの内容:package ca.krasnay.sqlbuilder;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
/**
* Tool for programmatically constructing SQL select statements. This class aims
* to simplify the task of juggling commas and SQL keywords when building SQL
* statements from scratch, but doesn't attempt to do much beyond that. Here are
* some relatively complex examples:
*
*
* String sql = new SelectBuilder()
* .column("e.id")
* .column("e.name as empname")
* .column("d.name as deptname")
* .column("e.salary")
* .from(("Employee e")
* .join("Department d on e.dept_id = d.id")
* .where("e.salary > 100000")
* .orderBy("e.salary desc")
* .toString();
*
*
*
* String sql = new SelectBuilder()
* .column("d.id")
* .column("d.name")
* .column("sum(e.salary) as total")
* .from("Department d")
* .join("Employee e on e.dept_id = d.id")
* .groupBy("d.id")
* .groupBy("d.name")
* .having("total > 1000000").toString();
*
*
* Note that the methods can be called in any order. This is handy when a base
* class wants to create a simple query but allow subclasses to augment it.
*
* It's similar to the Squiggle SQL library
* (http://code.google.com/p/squiggle-sql/), but makes fewer assumptions about
* the internal structure of the SQL statement, which I think makes for simpler,
* cleaner code. For example, in Squiggle you would write...
*
*
* select.addCriteria(new MatchCriteria(orders, "status", MatchCriteria.EQUALS, "processed"));
*
*
* With SelectBuilder, we assume you know how to write SQL expressions, so
* instead you would write...
*
*
* select.where("status = 'processed'");
*
*
* To include parameters, it's highly recommended to use the
* {@link ParameterizedPreparedStatementCreatorTest}, like this:
*
*
* String sql = new SelectBuilder("Employee e")
* .where("name like :name")
* .toString();
*
* PreparedStatement ps = new ParameterizedPreparedStatementCreator(sql)
* .setParameter("name", "Bob%")
* .createPreparedStatement(conn);
*
*
*
* @author John Krasnay
*/
public class SelectBuilder extends AbstractSqlBuilder implements Cloneable, Serializable {
private static final long serialVersionUID = 1;
private boolean distinct;
private List columns = new ArrayList();
private List tables = new ArrayList();
private List joins = new ArrayList();
private List leftJoins = new ArrayList();
private List wheres = new ArrayList();
private List groupBys = new ArrayList();
private List havings = new ArrayList();
private List unions = new ArrayList();
private List orderBys = new ArrayList();
private boolean forUpdate;
private boolean noWait;
public SelectBuilder() {
}
public SelectBuilder(String table) {
tables.add(table);
}
/**
* Copy constructor. Used by {@link #clone()}.
*
* @param other
* SelectBuilder being cloned.
*/
protected SelectBuilder(SelectBuilder other) {
this.distinct = other.distinct;
this.forUpdate = other.forUpdate;
this.noWait = other.noWait;
for (Object column : other.columns) {
if (column instanceof SubSelectBuilder) {
this.columns.add(((SubSelectBuilder) column).clone());
} else {
this.columns.add(column);
}
}
this.tables.addAll(other.tables);
this.joins.addAll(other.joins);
this.leftJoins.addAll(other.leftJoins);
this.wheres.addAll(other.wheres);
this.groupBys.addAll(other.groupBys);
this.havings.addAll(other.havings);
for (SelectBuilder sb : other.unions) {
this.unions.add(sb.clone());
}
this.orderBys.addAll(other.orderBys);
}
/**
* Alias for {@link #where(String)}.
*/
public SelectBuilder and(String expr) {
return where(expr);
}
public SelectBuilder column(String name) {
columns.add(name);
return this;
}
public SelectBuilder column(SubSelectBuilder subSelect) {
columns.add(subSelect);
return this;
}
public SelectBuilder column(String name, boolean groupBy) {
columns.add(name);
if (groupBy) {
groupBys.add(name);
}
return this;
}
@Override
public SelectBuilder clone() {
return new SelectBuilder(this);
}
public SelectBuilder distinct() {
this.distinct = true;
return this;
}
public SelectBuilder forUpdate() {
forUpdate = true;
return this;
}
public SelectBuilder from(String table) {
tables.add(table);
return this;
}
public List getUnions() {
return unions;
}
public SelectBuilder groupBy(String expr) {
groupBys.add(expr);
return this;
}
public SelectBuilder having(String expr) {
havings.add(expr);
return this;
}
public SelectBuilder join(String join) {
joins.add(join);
return this;
}
public SelectBuilder leftJoin(String join) {
leftJoins.add(join);
return this;
}
public SelectBuilder noWait() {
if (!forUpdate) {
throw new RuntimeException("noWait without forUpdate cannot be called");
}
noWait = true;
return this;
}
public SelectBuilder orderBy(String name) {
orderBys.add(name);
return this;
}
/**
* Adds an ORDER BY item with a direction indicator.
*
* @param name
* Name of the column by which to sort.
* @param ascending
* If true, specifies the direction "asc", otherwise, specifies
* the direction "desc".
*/
public SelectBuilder orderBy(String name, boolean ascending) {
if (ascending) {
orderBys.add(name + "asc");
} else {
orderBys.add(name + "desc");
}
return this;
}
@Override
public String toString() {
StringBuilder sql = new StringBuilder("select ");
if (distinct) {
sql.append("distinct ");
}
if (columns.size() == 0) {
sql.append("*");
} else {
appendList(sql, columns, "", ", ");
}
appendList(sql, tables, "from ", ", ");
appendList(sql, joins, "join ", "join ");
appendList(sql, leftJoins, "left join ", "left join ");
appendList(sql, wheres, "where ", "and ");
appendList(sql, groupBys, "group by ", ", ");
appendList(sql, havings, "having ", "and ");
appendList(sql, unions, "union ", "union ");
appendList(sql, orderBys, "order by ", ", ");
if (forUpdate) {
sql.append("for update");
if (noWait) {
sql.append("nowait");
}
}
return sql.toString();
}
/**
* Adds a "union"select builder. The generated SQL will union this query
* with the result of the main query. The provided builder must have the
* same columns as the parent select builder and must not use "order by"or
* "for update".
*/
public SelectBuilder union(SelectBuilder unionBuilder) {
unions.add(unionBuilder);
return this;
}
public SelectBuilder where(String expr) {
wheres.add(expr);
return this;
}
}
このコンポーネントの については、レポート で します^^;
githubアドレス:
https://github.com/jkrasnay/sqlbuilder
ブログ:
http://john.krasnay.ca/2010/02/15/building-sql-in-java.html
import java.util.HashMap;
public class TCondition {
private String sql;
private Object[] paraValues;
private String orderBy;
private String groupBy;
private String having;
/**
* key ,value left join on ( )
*/
private HashMap leftJoinMap;
/**
* count count , count(1)
*/
private String mainId;
public TCondition() {
}
public TCondition(String sql, Object... paraValues) {
this.setSql(sql);
this.setParaValues(paraValues);
}
public static TCondition of() {
return new TCondition();
}
public static TCondition of(String sql, Object... paraValues) {
return new TCondition(sql, paraValues);
}
public String getSql() {
return sql;
}
public TCondition setSql(String sql) {
this.sql = sql;
return this;
}
public Object[] getParaValues() {
return paraValues;
}
public TCondition setParaValues(Object[] paraValues) {
this.paraValues = paraValues;
return this;
}
public String getOrderBy() {
return orderBy;
}
public TCondition setOrderBy(String orderBy) {
this.orderBy = orderBy;
return this;
}
public String getGroupBy() {
return groupBy;
}
public TCondition setGroupBy(String groupBy) {
this.groupBy = groupBy;
return this;
}
public String getHaving() {
return having;
}
public TCondition setHaving(String having) {
this.having = having;
return this;
}
public String getMainId() {
return mainId;
}
public TCondition setMainId(String mainId) {
this.mainId = mainId;
return this;
}
@Override
public String toString() {
StringBuffer result = new StringBuffer(this.getSql() + ":");
for (int i = 0; i < paraValues.length; i++) {
result.append(paraValues[i]).append(",");
}
result.append(" || ");
return result.toString();
}
public HashMap getLeftJoinMap() {
return leftJoinMap;
}
public TCondition setLeftJoinMap(HashMap leftJoinMap) {
this.leftJoinMap = leftJoinMap;
return this;
}
package ca.krasnay.sqlbuilder;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
/**
* Tool for programmatically constructing SQL select statements. This class aims
* to simplify the task of juggling commas and SQL keywords when building SQL
* statements from scratch, but doesn't attempt to do much beyond that. Here are
* some relatively complex examples:
*
*
* String sql = new SelectBuilder()
* .column("e.id")
* .column("e.name as empname")
* .column("d.name as deptname")
* .column("e.salary")
* .from(("Employee e")
* .join("Department d on e.dept_id = d.id")
* .where("e.salary > 100000")
* .orderBy("e.salary desc")
* .toString();
*
*
*
* String sql = new SelectBuilder()
* .column("d.id")
* .column("d.name")
* .column("sum(e.salary) as total")
* .from("Department d")
* .join("Employee e on e.dept_id = d.id")
* .groupBy("d.id")
* .groupBy("d.name")
* .having("total > 1000000").toString();
*
*
* Note that the methods can be called in any order. This is handy when a base
* class wants to create a simple query but allow subclasses to augment it.
*
* It's similar to the Squiggle SQL library
* (http://code.google.com/p/squiggle-sql/), but makes fewer assumptions about
* the internal structure of the SQL statement, which I think makes for simpler,
* cleaner code. For example, in Squiggle you would write...
*
*
* select.addCriteria(new MatchCriteria(orders, "status", MatchCriteria.EQUALS, "processed"));
*
*
* With SelectBuilder, we assume you know how to write SQL expressions, so
* instead you would write...
*
*
* select.where("status = 'processed'");
*
*
* To include parameters, it's highly recommended to use the
* {@link ParameterizedPreparedStatementCreatorTest}, like this:
*
*
* String sql = new SelectBuilder("Employee e")
* .where("name like :name")
* .toString();
*
* PreparedStatement ps = new ParameterizedPreparedStatementCreator(sql)
* .setParameter("name", "Bob%")
* .createPreparedStatement(conn);
*
*
*
* @author John Krasnay
*/
public class SelectBuilder extends AbstractSqlBuilder implements Cloneable, Serializable {
private static final long serialVersionUID = 1;
private boolean distinct;
private List columns = new ArrayList();
private List tables = new ArrayList();
private List joins = new ArrayList();
private List leftJoins = new ArrayList();
private List wheres = new ArrayList();
private List groupBys = new ArrayList();
private List havings = new ArrayList();
private List unions = new ArrayList();
private List orderBys = new ArrayList();
private boolean forUpdate;
private boolean noWait;
public SelectBuilder() {
}
public SelectBuilder(String table) {
tables.add(table);
}
/**
* Copy constructor. Used by {@link #clone()}.
*
* @param other
* SelectBuilder being cloned.
*/
protected SelectBuilder(SelectBuilder other) {
this.distinct = other.distinct;
this.forUpdate = other.forUpdate;
this.noWait = other.noWait;
for (Object column : other.columns) {
if (column instanceof SubSelectBuilder) {
this.columns.add(((SubSelectBuilder) column).clone());
} else {
this.columns.add(column);
}
}
this.tables.addAll(other.tables);
this.joins.addAll(other.joins);
this.leftJoins.addAll(other.leftJoins);
this.wheres.addAll(other.wheres);
this.groupBys.addAll(other.groupBys);
this.havings.addAll(other.havings);
for (SelectBuilder sb : other.unions) {
this.unions.add(sb.clone());
}
this.orderBys.addAll(other.orderBys);
}
/**
* Alias for {@link #where(String)}.
*/
public SelectBuilder and(String expr) {
return where(expr);
}
public SelectBuilder column(String name) {
columns.add(name);
return this;
}
public SelectBuilder column(SubSelectBuilder subSelect) {
columns.add(subSelect);
return this;
}
public SelectBuilder column(String name, boolean groupBy) {
columns.add(name);
if (groupBy) {
groupBys.add(name);
}
return this;
}
@Override
public SelectBuilder clone() {
return new SelectBuilder(this);
}
public SelectBuilder distinct() {
this.distinct = true;
return this;
}
public SelectBuilder forUpdate() {
forUpdate = true;
return this;
}
public SelectBuilder from(String table) {
tables.add(table);
return this;
}
public List getUnions() {
return unions;
}
public SelectBuilder groupBy(String expr) {
groupBys.add(expr);
return this;
}
public SelectBuilder having(String expr) {
havings.add(expr);
return this;
}
public SelectBuilder join(String join) {
joins.add(join);
return this;
}
public SelectBuilder leftJoin(String join) {
leftJoins.add(join);
return this;
}
public SelectBuilder noWait() {
if (!forUpdate) {
throw new RuntimeException("noWait without forUpdate cannot be called");
}
noWait = true;
return this;
}
public SelectBuilder orderBy(String name) {
orderBys.add(name);
return this;
}
/**
* Adds an ORDER BY item with a direction indicator.
*
* @param name
* Name of the column by which to sort.
* @param ascending
* If true, specifies the direction "asc", otherwise, specifies
* the direction "desc".
*/
public SelectBuilder orderBy(String name, boolean ascending) {
if (ascending) {
orderBys.add(name + "asc");
} else {
orderBys.add(name + "desc");
}
return this;
}
@Override
public String toString() {
StringBuilder sql = new StringBuilder("select ");
if (distinct) {
sql.append("distinct ");
}
if (columns.size() == 0) {
sql.append("*");
} else {
appendList(sql, columns, "", ", ");
}
appendList(sql, tables, "from ", ", ");
appendList(sql, joins, "join ", "join ");
appendList(sql, leftJoins, "left join ", "left join ");
appendList(sql, wheres, "where ", "and ");
appendList(sql, groupBys, "group by ", ", ");
appendList(sql, havings, "having ", "and ");
appendList(sql, unions, "union ", "union ");
appendList(sql, orderBys, "order by ", ", ");
if (forUpdate) {
sql.append("for update");
if (noWait) {
sql.append("nowait");
}
}
return sql.toString();
}
/**
* Adds a "union"select builder. The generated SQL will union this query
* with the result of the main query. The provided builder must have the
* same columns as the parent select builder and must not use "order by"or
* "for update".
*/
public SelectBuilder union(SelectBuilder unionBuilder) {
unions.add(unionBuilder);
return this;
}
public SelectBuilder where(String expr) {
wheres.add(expr);
return this;
}
}
このコンポーネントの については、レポート で します^^;
githubアドレス:
https://github.com/jkrasnay/sqlbuilder
ブログ:
http://john.krasnay.ca/2010/02/15/building-sql-in-java.html