kylesqlutils.java

创建中间企业对象层

/**
 * @author duke
 * @version 1.2
 * @date 2004-8-10 14:37
 */

package com.kyle.eobject;

//import java.io.*;

public final class KyleSqlUtils
{
	public KyleSqlUtils()
	{
	}
	
	//构造基本查询SQL结构
	public static KyleQuerySQL getQuerySql(String table,String field,String where,String orderby)
	{
		KyleQuerySQL sql = new KyleQuerySQL();
		sql.table=table;
		sql.field=field;
		sql.where=where;
		sql.orderby=orderby;
		return sql;	
	}
	
	//构造无排序查询SQL结构
	public static KyleQuerySQL getQuerySql(String table,String field,String where)
	{
		return getQuerySql(table,field,where,"");
	}
	
	//构造最简单查询SQL结构
	public static KyleQuerySQL getQuerySql(String table,String field)
	{
		return getQuerySql(table,field,"","");
	}
	
	//构造统计数目SQL结构
	public static KyleQuerySQL getQueryCountSql(String table,String field,String asname,String where)
	{
		String pfield=field;
		if (pfield.trim().length()==0) pfield="*";
		String countField = "count(" + pfield + ")";
		if (asname!=null && asname.trim().length()>0)
			countField += " AS " + asname ;
		return getQuerySql(table,countField,where,"");	
	}
	
	//构造分页查询SQL结构
	public static KyleQuerySQL getPageSql(String table,
										  String field,
										  String where,
										  String orderby,
										  int pageNo,
										  int pageSize,
										  String key,
										  int keyValue)
	{
		String ptable=table;
		String pfield=field;
		String pwhere=where;
		String porderby=orderby;
		if (pfield.trim().length()==0) pfield="*";

		if (pageNo==1)
		{
	        pfield = "TOP " + pageSize + " " + pfield ;  
		}
		else
		{
	        if (where.length()>0)
	        {
	        	/*
	        	s = "select top " + pageSize + " " + fields + " from " + tableName ;
	        	s+= " where " + key + " NOT IN (select top " + pageSize*(pageNo-1) ;
	        	s+= " " + key + " from " + tableName + " where " + where +")";
	        	s+= " and " + where ;
	        	*/
	        	pfield = "TOP " + pageSize + " " + pfield ;
	        	pwhere =  key + " NOT IN (SELECT TOP " + pageSize*(pageNo-1) ;
	        	pwhere += " " + key + " FROM " + table + " WHERE " + where +")";
	        	pwhere += " AND " + where ;
	     	}
	     	else
	     	{
	        	/*
	        	s = "select top " + pageSize + " " + fields + " from " + tableName ;
	        	s+= " where " + key + " NOT IN (select top " + pageSize*(pageNo-1) ;
	        	s+= " " + key + " from " + tableName + ")";
	        	*/
	        	pfield = "TOP " + pageSize + " " + pfield ;
	        	pwhere =  key + " NOT IN (SELECT TOP " + pageSize*(pageNo-1) ;
	        	pwhere += " " + key + " FROM " + table + ")";
	     	}
		}
		return getQuerySql(ptable,pfield,pwhere,porderby);
	}
	
	//构造MS SQL Server以ID字段的分页查询SQL结构
	public static KyleQuerySQL getMSPageSql(String table,
										    String field,
										    String where,
										    String orderby,
										    int pageNo,
										    int pageSize)
	{
		return getPageSql(table,field,where,orderby,pageNo,pageSize,"ID",-1);
	}
	

	public static void main(String args[]) throws Exception
	{
		System.out.println("sss");	
		//KyleSqlUtils sql = new KyleSqlUtils();
		System.out.println(KyleSqlUtils.getMSPageSql("user","*","id>2","",2,10));	
	}
}