mysqlquery.java

简易java框架开源论坛系统,简 易java框架开源论坛系统

package com.easyjf.dbo.sql;

import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.apache.log4j.Logger;

import com.easyjf.dbo.DBObject;
import com.easyjf.dbo.DBTable;
/**
 * MySql数据为的Sql查询语句处理程序
 * @author 蔡世友
 *
 */
public class MySqlQuery implements ISqlQuery {
	 private final static Logger logger = Logger.getLogger(MySqlQuery.class);
	   public String getRowCountSql(String sql) {
		   StringBuffer s=new StringBuffer();
		   if(sql==null)return "";
		   String s1=sql.toLowerCase();	   
		   if(s1.indexOf("order by")>0)
		   {		   
			   s1=s1.substring(0,s1.toLowerCase().indexOf("order by"));
		   }
		   s.append("select count(*)");
		   s.append(s1.substring(s1.indexOf(" from ")));   	   
		   return s.toString();
		}

	public String getTopSql(String sql,int number) {
		 StringBuffer s=new StringBuffer(sql);
		   if(sql==null)return "";
		   if(sql.toLowerCase().indexOf(" limit ")<0)
		   {
			   s.append(" limit "+number);
		   }	   
		   
		   return s.toString();
	}

		public String getInsertSql(DBObject obj) {
		StringBuffer s=new StringBuffer();
		if(obj.getValue()==null)return "";
		s.append("insert into "+obj.getTable().getName());	
		Iterator it =obj.getValue().keySet().iterator();
		int paraNum=0;
		if(it!=null)
		{
			s.append("(");
			while(it.hasNext())
			{   paraNum++;
				String field=(String)it.next();
				s.append(field);
				if(it.hasNext())s.append(",");
			}
			s.append(")");
		}
		if(paraNum>0){
			s.append(" values(");
		for(int i=0;i<paraNum;i++)
		{
			s.append("?");
			if(i<paraNum-1)s.append(",");
		}
			s.append(")");
		}	
			return s.toString();
		}

	public String getUpdateSql(DBObject obj) {
			StringBuffer s=new StringBuffer();
			if(obj.getValue()==null)return "";
			String id=obj.getTable().getId();
			if(obj.getIdValue()==null)return "";
			s.append("update "+obj.getTable().getName());	
			Iterator it =obj.getValue().keySet().iterator();		
			if(it!=null)
			{	
				s.append(" set ");
				while(it.hasNext())
				{ 
					String field=(String)it.next();
					if(!field.equals(id)){
					s.append(field+"=?");
					s.append(",");	
					}
				}		
				s.deleteCharAt(s.length()-1);
				s.append(" where "+id+"=?");
			}		
				return s.toString();	
		}

	public String getDelSql(DBObject obj) {
		StringBuffer s=new StringBuffer();
		if(obj.getValue()==null)return "";
		String id=obj.getTable().getId();
		if(obj.getIdValue()==null)return "";
		s.append("delete from "+obj.getTable().getName());
		s.append(" where "+id+"=?");	
		return s.toString();
		}

	public String getQuerySql(DBObject obj) {		
		StringBuffer s=new StringBuffer();
		if(obj.getTable()==null)return "";
		String id=obj.getTable().getId();	
		if(obj.getValue()!=null){
		Iterator it =obj.getValue().keySet().iterator();		
		if(it!=null)
		{	
			s.append("select ");
			while(it.hasNext())
			{ 
				String field=(String)it.next();				
				s.append(field);
				if(it.hasNext())s.append(",");				
			}			
			s.append(" from "+obj.getTable().getName());
		}	
		}
		else
		{
		s.append("select * from "+obj.getTable().getName());
		}
		s.append(" where "+id+"=?");	
		return s.toString();	
		
		}
	public String getQuerySql(DBObject obj,String scope) {		
		StringBuffer s=new StringBuffer();
		//System.out.println("test");
		if(obj.getTable()==null)return "";		
		//String id=obj.getTable().getId();	
		if(obj.getValue()!=null){
		Iterator it =obj.getValue().keySet().iterator();		
		if(it!=null)
		{	
			s.append("select ");
			while(it.hasNext())
			{ 
				String field=(String)it.next();				
				s.append(field);
				if(it.hasNext())s.append(",");				
			}			
			s.append(" from "+obj.getTable().getName());
		}	
		}
		else
		{
		s.append("select * from "+obj.getTable().getName());
		}
		s.append(" where "+scope);	
		return s.toString();	
		}
		/**
		 * @param args
		 */
	public static void main(String[] args) {
			DBObject obj=new DBObject();
			obj.setTable(new DBTable("test","cid"));
			Map value=new HashMap();
			obj.setIdValue("dfsdf");
			value.put("cid","cid");
			value.put("tt","tt");
			value.put("content","dksfksdlf");
			value.put("field2","field2");
			obj.setValue(value);
			ISqlQuery sqlQuery=new MySqlQuery();
			System.out.println(sqlQuery.getInsertSql(obj));
			System.out.println(sqlQuery.getUpdateSql(obj));
			System.out.println(sqlQuery.getDelSql(obj));
			System.out.println(sqlQuery.getQuerySql(obj));
			System.out.println(sqlQuery.getRowCountSql("Select cid,tt from test where dkdkdkdkdk order by cid desc"));
			System.out.println(sqlQuery.getTopSql("select * from test where dkdkdkdkdk",10));
			System.out.println(sqlQuery.getTopSql("select  * from test where dkdkdkdkdk limit 10",10));
		}

}