table.java

重点不是信息管理系统

package com.yijia_ctgu.DB;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BasicDynaBean;

import com.yijia_ctgu.exception.AlreadyExistException;
import com.yijia_ctgu.exception.NotQueryException;
/**
 * 
 * @author yijia
 *
 */
public class Table {
	String tableName;
	String mainPropertyName;
	DBExcute dbExcute=new DBExcute();
	
    Table(String tableName,String mainPropertyName){
    	this.tableName=tableName;
    	this.mainPropertyName=mainPropertyName;
    }
    /**
     * 做编辑用
     * @param mainPropertyValue
     * @return Row
     * @throws SQLException
     * @throws NotQueryException
     */
    public Row getRow(String mainPropertyValue)throws SQLException, NotQueryException{
    	String sql="Select "+mainPropertyName+" from "+tableName+" where "+mainPropertyName+"='"+mainPropertyValue+"'";
    	try {
			dbExcute.queryString(sql);
			return new Row(tableName,mainPropertyName,mainPropertyValue);
		} catch (SQLException e) {
			throw e;
		} catch (NotQueryException e) {
			throw e;
		}
    }
    /**
     * 做查询用
     * @param mainPropertyValue
     * @return BasicDynaBean
     * @throws SQLException
     * @throws NotQueryException
     */
    public BasicDynaBean getRowData(String mainPropertyValue)throws SQLException, NotQueryException{
    	String sql="Select * from "+tableName+" where "+mainPropertyName+"='"+mainPropertyValue+"'";
    	try {
    		List list=dbExcute.queryList(sql);
    		if(list==null||list.size()==0){
    			throw new NotQueryException();
    		}
    		else return (BasicDynaBean)list.get(0);
		} catch (SQLException e) {
			throw e;
		} catch (NotQueryException e) {
			throw e;
		}
    }
    public Row insert(String mainPropertyValue)throws SQLException,AlreadyExistException{
    	Row row=null;
    	String sql="insert into "+tableName+"("+mainPropertyName+")values ('"+mainPropertyValue+"')";
    	try {
			if(getRow(mainPropertyValue)!=null) throw new AlreadyExistException();
		} catch (NotQueryException e) {
			dbExcute.update(sql);
			row=new Row(tableName,mainPropertyName,mainPropertyValue);
		}catch (SQLException ex) {
    		throw ex;
    	}
		return row;
    }
    public void insert(String mainPropertyValue,String[][] property)throws SQLException,AlreadyExistException{
    	String sql="insert into "+tableName+"(";
    	int i;
    	for(i=0;i<(property.length-1);i++){
    		sql=sql+property[i][0]+",";
    	}
    	sql=sql+property[i][0]+")values (";
    	int j;
    	for(j=0;j<(property.length-1);j++){
    		sql=sql+"'"+property[j][1]+"',";
    	}
    	sql=sql+"'"+property[j][1]+"')";
    	try {
			if(getRow(mainPropertyValue)!=null) throw new AlreadyExistException();
		} catch (NotQueryException e) {
			dbExcute.update(sql);
		}catch (SQLException ex) {
    		throw ex;
    	}
    }
    public void delete(String propertyName,String propertyValue)throws SQLException{
    	String sql="delete from "+tableName+" where "+propertyName+"='"+propertyValue+"'";
    	try {
    		dbExcute.update(sql);
    	} catch (SQLException ex) {
    		throw ex;
    	}
    } 
    
    public int getMax(String propertyName) throws NotQueryException, SQLException{
    	String sql="SELECT max("+propertyName+") from "+tableName;
    	int max;
    	try {
			String s=dbExcute.queryString(sql);
			max=Integer.parseInt(s);
		} catch (SQLException ex) {
			throw ex;
		} catch (NotQueryException ex) {
			throw ex;
		}
		return max;
    }
    public List list() throws SQLException,NotQueryException{
    	String sql="Select * from "+tableName;
    	try {
    		return dbExcute.queryList(sql);
		} catch (SQLException ex) {
			throw ex;
		}catch (NotQueryException ex) {
			throw ex;
		}
    }
    
    public List list(int start,int limit) throws SQLException,NotQueryException{
    	String sql="SELECT * from "+tableName+"  limit "+start+","+limit;
    	try {
    		return dbExcute.queryList(sql);
		} catch (SQLException ex) {
			throw ex;
		}catch (NotQueryException ex) {
			throw ex;
		}
    }
    public List search(String propertyName,String propertyValue )throws SQLException,NotQueryException{
    	String sql="Select * from "+tableName+" where "+propertyName+"='"+propertyValue+"'";
    	try {
    		return dbExcute.queryList(sql);
		} catch (SQLException ex) {
			throw ex;
		}catch (NotQueryException ex) {
			throw ex;
		}
    }public List search(String[][] property )throws SQLException,NotQueryException{
    	String sql="Select * from "+tableName+" where ";
    	int i;
    	for(i=0;i<(property.length-1);i++){
    		sql=sql+property[i][0]+"='"+property[i][1]+"' and ";
    	}
    	sql=sql+property[i][0]+"='"+property[i][1]+"'";
    	try {
    		return dbExcute.queryList(sql);
		} catch (SQLException ex) {
			throw ex;
		}catch (NotQueryException ex) {
			throw ex;
		}
    }
    public List search(String[][] property,int start,int limit)throws SQLException,NotQueryException{
    	String sql="Select * from "+tableName+" where ";
    	int i;
    	for(i=0;i<(property.length-1);i++){
    		sql=sql+property[i][0]+"='"+property[i][1]+"' and ";
    	}
    	sql=sql+property[i][0]+"='"+property[i][1]+"'";
    	sql=sql+"  limit "+start+","+limit;
    	try {
    		return dbExcute.queryList(sql);
		} catch (SQLException ex) {
			throw ex;
		}catch (NotQueryException ex) {
			throw ex;
		}
    }
    public int count() throws SQLException, NotQueryException{
    	String sql="select count(*) from "+tableName;
    	try{
    		String str=dbExcute.queryString(sql);
    		int count=Integer.parseInt(str);
    		return count;
    	}catch(SQLException ex){
    		throw ex;
    	}catch(NotQueryException ex){
    		throw ex;
    	}
    }
    public int count(String propertyName,String propertyValue) throws SQLException, NotQueryException{
    	String sql="select count(*) from "+tableName+" where "+propertyName+"='"+propertyValue+"'";
    	try{
    		String str=dbExcute.queryString(sql);
    		int count=Integer.parseInt(str);
    		return count;
    	}catch(SQLException ex){
    		throw ex;
    	}catch(NotQueryException ex){
    		throw ex;
    	}
    }
    public int count(String[][] property) throws SQLException, NotQueryException{
    	String sql="select count(*) from "+tableName+" where ";
    	int i;
    	for(i=0;i<(property.length-1);i++){
    		sql=sql+property[i][0]+"='"+property[i][1]+"' and ";
    	}
    	sql=sql+property[i][0]+"='"+property[i][1]+"'";
    	try{
    		String str=dbExcute.queryString(sql);
    		int count=Integer.parseInt(str);
    		return count;
    	}catch(SQLException ex){
    		throw ex;
    	}catch(NotQueryException ex){
    		throw ex;
    	}
    }
    public static void main(String []dsf){
     String[][] s={{"id","1"},{"username","1"}};
      try {
		System.out.println(DB.getTable("user").search(s, 0, 1));
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (NotQueryException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
}