在一直使用的HQL查询语句,较多仅是对参数的拼凑。在java面向对象化开发语言,能否将其对象化,以此减少反复性的工作。由于HQL查询语句结构上比较清晰,所以实现上复杂度不高。select .. from table where ... groupby row...having...order by... 逐一将查询关键字封装成类,这是小码的开篇博文,技术上有所局限。不足之处,请各位多多指点,不胜感激。
/**
* 参数抽象类
* @author kaimin 2014/07/01
*
*/
public abstract class Parameter {
protected String tableName;//表名
protected String rowName;//行名
public Parameter(String tableName,String rowName){
this.tableName=tableName;
this.rowName=rowName;
}
public abstract String convertSql();
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getRowName() {
return rowName;
}
public void setRowName(String rowName) {
this.rowName = rowName;
}
}
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
/**
* 查询排序参数基础类
* @author kaimin 2014/07/01
*
*/
public class OrderParameter extends Parameter{
private int sortord=1;//排序方式 1-升序 2-降序
/**
* 默认使用升序排序
* @param rowName 列名
*/
public OrderParameter(String tableName,String rowName) {
super(tableName,rowName);
// TODO Auto-generated constructor stub
}
/**
* @param rowName 列名
* @param sortord 排序方式 1-升序 2-降序
*/
public OrderParameter(String tableName,String rowName,int sortord) {
super(tableName,rowName);
this.sortord=sortord;
// TODO Auto-generated constructor stub
}
@Override
public String convertSql() {
// TODO Auto-generated method stub
StringBuilder sb=new StringBuilder(" ");
sb.append("order by ");
sb.append(tableName);
sb.append(".");
sb.append(rowName);
sb.append(" ");
if(sortord==1)
sb.append("asc");
else
sb.append("desc");
sb.append(" ");
return sb.toString();
}
public static String convertSql(List<Parameter> parameters) {
// TODO Auto-generated method stub
if(parameters!=null&&!parameters.isEmpty()){
StringBuilder sb=new StringBuilder(" ");
sb.append("order by ");
int index=0;
Parameter p;
Iterator<Parameter> i=parameters.iterator();
while(i.hasNext()){
p=i.next();
if(p instanceof OrderParameter){
if(p!=null){
if(index>0)
sb.append(",");
sb.append(p.getTableName());
sb.append(".");
sb.append(p.getRowName());
if(((OrderParameter) p).getSortord()==1)
sb.append(" asc");
else
sb.append(" desc");
index++;
}
}
}
if(index>0){
sb.append(" ");
return sb.toString();
}
}
return null;
}
public int getSortord() {
return sortord;
}
public void setSortord(int sortord) {
this.sortord = sortord;
}
public static void main(String[]args){
Parameter p =new OrderParameter("goods","name");
System.out.println("--"+p.convertSql()+"--");
Parameter p1=new OrderParameter("goods","name",2);
Parameter p2=new OrderParameter("goods","name");
Parameter p3=new OrderParameter("goods","name");
Parameter p4=new OrderParameter("goods","name",2);
List<Parameter> list=new ArrayList<Parameter>();
list.add(p);
list.add(p1);
list.add(p2);
list.add(p3);
list.add(p4);
System.out.println("--"+OrderParameter.convertSql(list)+"--");
//System.out.println("--"+p.convertSql()+"--");
}
}
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
/**
* 分组查询参数基础类
* @author kaimin 2014/07/01
*
*/
public class GroupParameter extends Parameter{
public GroupParameter(String tableName,String rowName) {
super(tableName,rowName);
// TODO Auto-generated constructor stub
}
@Override
public String convertSql() {
// TODO Auto-generated method stub
StringBuilder sb=new StringBuilder(" group by ");
sb.append(tableName);
sb.append(".");
sb.append(rowName);
sb.append(" ");
return sb.toString();
}
public static String convertSql(List<Parameter> parameters) {
// TODO Auto-generated method stub
if(parameters!=null&&!parameters.isEmpty()){
StringBuilder sb=new StringBuilder(" group by ");
int index=0;
Parameter p;
Iterator<Parameter> i=parameters.iterator();
while(i.hasNext()){
p=i.next();
if(p instanceof GroupParameter){
if(p!=null){
if(index>0)
sb.append(",");
sb.append(p.getTableName());
sb.append(".");
sb.append(p.getRowName());
index++;
}
}
}
if(index>0){
sb.append(" ");
return sb.toString();
}
}
return null;
}
public static void main(String[]args){
Parameter p =new GroupParameter("goods","name");
Parameter p1=new GroupParameter("goods","name");
List<Parameter> list=new ArrayList<Parameter>();
list.add(p);
list.add(p1);
System.out.println("--"+GroupParameter.convertSql(list)+"--");
}
}
import java.util.ArrayList;
import java.util.List;
/**
* having查询参数
* @author kaimin 2014/07/01
*
*/
public class HavingParameter extends ConditionParameter{
/**
* 默认构造函数,使用表达式(=)匹配
* @param rowName 列名
* @param value 值
*/
public HavingParameter(String tableName,String rowName, String value) {
super(tableName,rowName, value);
// TODO Auto-generated constructor stub
}
/**
* @param rowName 列名
* @param value 值
* @param condition 条件 1--(and) 2--(or)
* @param fuzzyMatch 匹配方式 1--(=) 2--(>) 3--(>=) 4--(<) 5--(<=) 6--(%value) 7--(value%) 8--(%value%)
*/
public HavingParameter(String tableName,String rowName, String value, int condition,
int matchMode) {
super(tableName,rowName, value, condition, matchMode);
// TODO Auto-generated constructor stub
}
@Override
public String convertSql() {
String sql=super.convertSql();
if(sql!=null)
return " having "+sql;
return null;
}
public static String convertSql(List<Parameter> parameters) {
String sql=ConditionParameter.convertSql(parameters);
if(sql!=null)
return " having "+sql;
return null;
}
public static void main(String[]args){
Parameter p;
List<Parameter> list=new ArrayList<Parameter>();
for(int i=1;i<9;i++){
p=new HavingParameter("goods","name","供应",2,i);
list.add(p);
System.out.println("--"+p.convertSql()+"--");
}
System.out.println("--"+HavingParameter.convertSql(list)+"--");
}
}
import java.util.ArrayList;
import java.util.List;
/**
* where语句参数基础类
* @author kaimin 2014/07/01
*
*/
public class WhereParameter extends ConditionParameter{
/**
* 默认构造函数,使用表达式(=)匹配
* @param rowName 列名
* @param value 值
*/
public WhereParameter(String tableName,String rowName, String value) {
super(tableName,rowName, value);
// TODO Auto-generated constructor stub
}
/**
* @param rowName 列名
* @param value 值
* @param condition 条件 1--(and) 2--(or)
* @param fuzzyMatch 匹配方式 1--(=) 2--(>) 3--(>=) 4--(<) 5--(<=) 6--(%value) 7--(value%) 8--(%value%)
*/
public WhereParameter(String tableName,String rowName, String value, int condition,
int matchMode) {
super(tableName,rowName, value, condition, matchMode);
// TODO Auto-generated constructor stub
}
@Override
public String convertSql() {
String sql=super.convertSql();
if(sql!=null)
return " where "+sql;
return null;
}
public static String convertSql(List<Parameter> parameters) {
String sql=ConditionParameter.convertSql(parameters);
if(sql!=null)
return " where "+sql;
return null;
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Parameter p;
List<Parameter> list=new ArrayList<Parameter>();
for(int i=1;i<9;i++){
p=new WhereParameter("goods","name","供应",2,i);
list.add(p);
System.out.println("--"+p.convertSql()+"--");
}
System.out.println("--"+WhereParameter.convertSql(list)+"--");
}
}
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
/**
* 查询返回参数基础类
* @author kaimin 2014/07/02
*
*/
public class ReturnParameter extends Parameter{
/**
*
* @param tableName 表名
* @param rowName 行名 null--*(表示返回所有行)
*/
public ReturnParameter(String tableName, String rowName) {
super(tableName, rowName);
// TODO Auto-generated constructor stub
}
@Override
public String convertSql() {
// TODO Auto-generated method stub
StringBuilder sb=new StringBuilder(" select ");
sb.append(tableName);
sb.append(".");
if(StringUitl.isEmpty(rowName))
sb.append("*");
else
sb.append(rowName);
sb.append(" ");
return sb.toString();
}
public static String convertSql(List<Parameter> parameters) {
// TODO Auto-generated method stub
if(parameters!=null&&!parameters.isEmpty()){
StringBuilder sb=new StringBuilder(" ");
sb.append(" select ");
int index=0;
Parameter p;
Iterator<Parameter> i=parameters.iterator();
while(i.hasNext()){
p=i.next();
if(p instanceof ReturnParameter){
if(p!=null){
if(index>0)
sb.append(",");
sb.append(p.getTableName());
sb.append(".");
if(StringUitl.isEmpty(p.getRowName()))
sb.append("*");
else
sb.append(p.getRowName());
sb.append(" ");
index++;
}
}
}
if(index>0){
sb.append(" ");
return sb.toString();
}
}
return null;
}
public static void main(String[]args){
Parameter p=new ReturnParameter("goods","name");
Parameter p1=new ReturnParameter("goods","id");
Parameter p2=new ReturnParameter("goods","image");
List<Parameter> list=new ArrayList<Parameter>();
list.add(p);
list.add(p1);
list.add(p2);
System.out.println("--"+ReturnParameter.convertSql(list)+"--");
}
}
import java.util.ArrayList;
import java.util.List;
import java.util.ListIterator;
public class JoinParameter extends Parameter{
private String joinTableName;//连接表名
private String joinRowName;//连接表的依据行
private int style=2;//1--cross join 2--inner 3--left 4--right 5--full 6--self
public JoinParameter(String tableName,String rowName,String joinTableName,String joinRowName) {
super(tableName,rowName);
this.joinTableName=joinTableName;
this.joinRowName=joinRowName;
// TODO Auto-generated constructor stub
}
public JoinParameter(String tableName,String rowName,String joinTableName,String joinRowName,int style) {
super(tableName,rowName);
this.joinTableName=joinTableName;
this.joinRowName=joinRowName;
this.style=style;
// TODO Auto-generated constructor stub
}
@Override
public String convertSql() {
// TODO Auto-generated method stub
if(StringUitl.isEmpty(tableName)||StringUitl.isEmpty(rowName)
||StringUitl.isEmpty(joinRowName))
return null;
StringBuilder sb=new StringBuilder();
switch(style){
case 1 :sb.append(" cross join ");break;
case 3 :sb.append(" left join ");break;
case 4 :sb.append(" right join ");break;
case 5 :sb.append(" full join ");break;
case 6 :sb.append(" self join ");break;
default :sb.append(" inner join ");break;
}
sb.append(tableName);
sb.append(" on ");
sb.append(tableName);
sb.append(".");
sb.append(rowName);
sb.append("=");
sb.append(joinTableName);
sb.append(".");
sb.append(joinRowName);
sb.append(" ");
return sb.toString();
}
public static String convertSql(List<Parameter> parameters) {
if(parameters==null||parameters.isEmpty())
return null;
ListIterator<Parameter> i=parameters.listIterator();
StringBuilder sb=new StringBuilder();
Parameter p;
String tableName;
String rowName;
String joinTableName;
String joinRow;
int style=2;
int index=0;
while(i.hasNext()){
p=i.next();
if(p==null)
continue;
if(p instanceof JoinParameter){
tableName=((JoinParameter)p).getTableName();
joinTableName=((JoinParameter)p).getJoinTableName();
joinRow=((JoinParameter)p).getJoinRowName();
style=((JoinParameter)p).getStyle();
rowName=p.getRowName();
if(StringUitl.isEmpty(tableName)||StringUitl.isEmpty(rowName)
||StringUitl.isEmpty(joinTableName)||StringUitl.isEmpty(joinRow))
continue;
switch(style){
case 1 :sb.append(" cross join ");break;
case 3 :sb.append(" left join ");break;
case 4 :sb.append(" right join ");break;
case 5 :sb.append(" full join ");break;
case 6 :sb.append(" self join ");break;
default :sb.append(" inner join ");break;
}
sb.append(tableName);
sb.append(" on ");
sb.append(tableName);
sb.append(".");
sb.append(rowName);
sb.append("=");
sb.append(joinTableName);
sb.append(".");
sb.append(joinRow);
index=1;
}
}
if(index==1){
sb.append(" ");
return sb.toString();
}
return null;
}
public int getStyle() {
return style;
}
public void setStyle(int style) {
this.style = style;
}
public String getJoinTableName() {
return joinTableName;
}
public void setJoinTableName(String joinTableName) {
this.joinTableName = joinTableName;
}
public String getJoinRowName() {
return joinRowName;
}
public void setJoinRowName(String joinRowName) {
this.joinRowName = joinRowName;
}
public static void main(String[]args){
Parameter p=new JoinParameter("goods","username","user","name");
Parameter p1=new JoinParameter("catog","username","user","name");
List<Parameter> list=new ArrayList<Parameter>();
list.add(p);
list.add(p1);
System.out.println("--"+p.convertSql()+"--");
System.out.println("--"+JoinParameter.convertSql(list)+"--");
}
}
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
public class ConditionParameter extends Parameter{
private String value;
private int condition=1;//1--(and) 2--(or)
private int matchMode=1;//1--(=) 2--(>) 3--(>=) 4--(<) 5--(<=) 6--(%value) 7--(value%) 8--(%value%)
/**
* 默认构造函数,使用表达式(=)匹配
* @param rowName 列名
* @param value 值
*/
public ConditionParameter(String tableName,String rowName, String value) {
super(tableName,rowName);
this.value = value;
}
/**
* @param rowName 列名
* @param value 值
* @param condition 条件 1--(and) 2--(or)
* @param fuzzyMatch 匹配方式 1--(=) 2--(>) 3--(>=) 4--(<) 5--(<=) 6--(%value) 7--(value%) 8--(%value%)
*/
public ConditionParameter(String tableName,String rowName,String value, int condition, int matchMode) {
super(tableName,rowName);
this.value = value;
this.condition = condition;
this.matchMode = matchMode;
}
@Override
public String convertSql() {
// TODO Auto-generated method stub
if(value==null||value.isEmpty()
||matchMode<1||matchMode>8)
return null;
StringBuilder sb=new StringBuilder(" ");
sb.append(tableName);
sb.append(".");
sb.append(rowName);
switch(matchMode){
case 1: sb.append("='");sb.append(value);sb.append("'");break;
case 2: sb.append(">'");sb.append(value);sb.append("'");break;
case 3: sb.append(">='");sb.append(value);sb.append("'");break;
case 4: sb.append("<'");sb.append(value);sb.append("'");break;
case 5: sb.append("<='");sb.append(value);sb.append("'");break;
case 6: sb.append(" like '");sb.append("%");
sb.append(value);sb.append("'");break;
case 7: sb.append(" like '");sb.append(value);
sb.append("%'");break;
case 8: sb.append(" like '");sb.append("%");
sb.append(value);sb.append("%'");break;
}
sb.append(" ");
return sb.toString();
}
/**
* 将参数集转换为sql语句段
* 默认第一个条件为and
* @param parameters 需要转换的参数
* @return
*/
public static String convertSql(List<Parameter> parameters) {
// TODO Auto-generated method stub
if(parameters!=null&&!parameters.isEmpty()){
StringBuilder sb=new StringBuilder(" ");
int index=0;
Parameter p;
Iterator<Parameter> i=parameters.iterator();
String value=null;
int condition=1;
int matchMode=1;
while(i.hasNext()){
p=i.next();
if(p instanceof ConditionParameter){
if(p!=null){
value=((ConditionParameter)p).getValue();
matchMode=((ConditionParameter)p).getMatchMode();
condition=((ConditionParameter)p).getCondition();
if(value==null||value.isEmpty()
||matchMode<1||matchMode>8)
continue;
if(index>0){
if(condition==2)
sb.append(" or ");
else
sb.append(" and ");
}
sb.append(p.getTableName());
sb.append(".");
sb.append(p.getRowName());
switch(matchMode){
case 1: sb.append("='");sb.append(value);sb.append("'");break;
case 2: sb.append(">'");sb.append(value);sb.append("'");break;
case 3: sb.append(">='");sb.append(value);sb.append("'");break;
case 4: sb.append("<'");sb.append(value);sb.append("'");break;
case 5: sb.append("<='");sb.append(value);sb.append("'");break;
case 6: sb.append(" like '");sb.append("%");
sb.append(value);sb.append("'");break;
case 7: sb.append(" like '");sb.append(value);
sb.append("%'");break;
case 8: sb.append(" like '");sb.append("%");
sb.append(value);sb.append("%'");break;
}
index++;
}
}
}
if(index>0){
sb.append(" ");
return sb.toString();
}
}
return null;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
public int getMatchMode() {
return matchMode;
}
public void setMatchMode(int matchMode) {
this.matchMode = matchMode;
}
public int getCondition() {
return condition;
}
public void setCondition(int condition) {
this.condition = condition;
}
public static void main(String[] args) {
// TODO Auto-generated method stub
Parameter p;
List<Parameter> list=new ArrayList<Parameter>();
for(int i=1;i<9;i++){
p=new ConditionParameter("goods","name","供应",2,i);
list.add(p);
System.out.println("--"+p.convertSql()+"--");
}
System.out.println("--"+ConditionParameter.convertSql(list)+"--");
}
}
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
/**
* 查询参数类
* @author kaimin 2014/07/01
*
*/
public class QueryParameters {
private String tableName;
private List<Parameter> parameters;
public QueryParameters(String tableName){
this.tableName=tableName;
parameters=new ArrayList<Parameter>();
}
public QueryParameters(String tableName,List<Parameter> parameters){
this.tableName=tableName;
if(parameters!=null)
this.parameters=parameters;
else
parameters=new ArrayList<Parameter>();
}
//将查询参数转换为sql语句
public String convertSql(){
List<Parameter> returns=new ArrayList<Parameter>();
List<Parameter> where=new ArrayList<Parameter>();
List<Parameter> join=new ArrayList<Parameter>();
List<Parameter> having=new ArrayList<Parameter>();
List<Parameter> group=new ArrayList<Parameter>();
List<Parameter> order=new ArrayList<Parameter>();
if(parameters.isEmpty())
return null;
Iterator<Parameter> i=parameters.iterator();
Parameter p;
while(i.hasNext()){
p=i.next();
if(p!=null){
if(p instanceof ReturnParameter)
returns.add(p);
else if(p instanceof WhereParameter)
where.add(p);
else if(p instanceof HavingParameter)
having.add(p);
else if(p instanceof GroupParameter)
group.add(p);
else if(p instanceof OrderParameter)
order.add(p);
else if(p instanceof JoinParameter)
join.add(p);
}
}
String sqlReturn=ReturnParameter.convertSql(returns);
String sqlJoin=JoinParameter.convertSql(join);
String sqlWhere=WhereParameter.convertSql(where);
String sqlhaving=HavingParameter.convertSql(having);
String sqlgroup=GroupParameter.convertSql(group);
String sqlorder=OrderParameter.convertSql(order);
StringBuilder sb=new StringBuilder();
if(sqlReturn!=null)
sb.append(sqlReturn);
sb.append(" from ");
sb.append(tableName);
if(sqlJoin!=null)
sb.append(sqlJoin);
if(sqlWhere!=null)
sb.append(sqlWhere);
if(sqlgroup!=null)
sb.append(sqlgroup);
if(sqlhaving!=null)
sb.append(sqlhaving);
if(sqlorder!=null)
sb.append(sqlorder);
if(sb.length()==0)
return null;
return sb.toString();
}
public boolean add(Parameter parameter){
return parameters.add(parameter);
}
public boolean remove(Parameter parameter){
return parameters.remove(parameter);
}
public List<Parameter> getParameters() {
return parameters;
}
public void setParameters(List<Parameter> parameters) {
this.parameters = parameters;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
QueryParameters qp=new QueryParameters("Goods");
qp.add(new WhereParameter("goods","name","供应",2,1));
qp.add(new WhereParameter("goods","name","供应",2,2));
qp.add(new WhereParameter("goods","name","供应",2,6));
qp.add(new GroupParameter("goods","name"));
qp.add(new GroupParameter("goods","id"));
qp.add(new OrderParameter("goods","name",1));
qp.add(new OrderParameter("goods","id",2));
qp.add(new HavingParameter("goods","name","供应",2,6));
qp.add(new JoinParameter("goods","username","goods","name"));
System.out.println("--"+qp.convertSql()+"--");
}
}
/**
* String工具类
* @author kaimin 2014/07/01
*
*/
public class StringUitl {
public static boolean isEmpty(String str){
if(str==null||str.isEmpty())
return true;
return false;
}
public static void main(String[]args){
System.out.println("--"+StringUitl.isEmpty("")+"--");
}
}