shangobject.java

(Java+SQL)-大型企业JAVA的ERP系统

// 1. 统一查询，删除，修改，接口
// 2。统一中文处理

package src.com;

import java.sql.*;
import java.io.*;
import java.text.DateFormat;
import java.util.*;
import java.math.BigDecimal;

public class ShangObject
{
public Connection conn; // 连接
public ResultSet rs; // 分页查寻用的rs

public String primarykey1Name; // 主键1
public int primarykey1Type; // 0: 整数 ; 1: 字符串
public String primarykey2Name; // 主键2
public int primarykey2Type; // 0: 整数 ; 1: 字符串
public String viewName;  // 对应的表名
public String tableName; // 分页查询时对应的表名或视图名，

public String orderBy;      //分页查询时排序字段
public String whereClause; // 分页查询时的查询条件

public  int recordNum; 	// 查询结果的数目
public  int numInOnePager;  // 每一页显示的数目
public  int listPosition=0;      //记录当前显示位置，第一页为0,第n页为(n-1)*queryClient.getNumInOnePage()
public  int totalPage=1;    // 总页数
public  int curPage=0;     // 当前页
public  int state=0;
public boolean debug=true;

public MyGlobal theGlobal;

public void setGlobal(MyGlobal theGlobal){
	this.theGlobal = theGlobal;
  }



public int getNumInOnePage() {
	return numInOnePager;
}


public int getRecordNum() {
	return recordNum;
}

public int getTotalPage(){
	return totalPage;
}

public int getCurPage(){
	return curPage;
}	

public void setRecordNum(int aRecordNum){
	recordNum = aRecordNum;
}


public void setTotalPage(){
	totalPage=(int)((recordNum-1)/numInOnePager)+1;	        
	if (recordNum<=numInOnePager){
	      state=0;
	   }
	   else{ 
	      state=2;
	     }
}


/**
具体的查询处理(构造查询where子句)，需要重载, 返回whereClause
*/
    String getWhereClause(javax.servlet.ServletRequest request) {
    	return "";
    }

/** 
增加一个查询的条件, 构造whereClause, 返回构造的新的whereClause
*/
 public String addWhereClause(String whereClause,   // 输入的已有的whereClause
    			String colName,        // 数据库的列名
    			String operator,       // 条件操作符号 >, < , like 
    			String fix,            // 引号 或 空 "'" , ""
    			String colValue) {     // 值
    	if( (colValue == null) || ( colValue.equals("")) )  // 未输入
		return whereClause;
    	if(fix.equals("'")){   // 字符型
		try {
	    		colValue = new String(colValue.getBytes("8859_1"));
		} catch(java.io.UnsupportedEncodingException e) {
			System.out.println("Baseservlet addWhereClause() : 编码不支持!!");
		}
	}
    	if( whereClause.equals("") == false) whereClause = whereClause + " and ";
    	if(operator.equals("like"))
		whereClause = whereClause + "(" + colName + " " + operator + " " + fix + "%" + colValue + "%" + fix  + ")";
	else
		whereClause = whereClause + "(" + colName + " " + operator + " " + fix + colValue + fix  + ")";
    	return whereClause;
    }

	 
public ShangObject(){
	primarykey1Name = ""; 
	primarykey1Type = 0; // 整数
	primarykey2Name = ""; 
	primarykey2Type = 0; // 整数
	tableName = "";  
	viewName = "";
	connectDB();
}

/** !!! 请重载 */
public int loadAttr() {
	return 1;
}

/** !!! 请重载 */
public int update()
{
	return 1;
}

/** !!! 请重载 */
public int insert() 
{
	return 1;
}

/** !!! 请重载 */

public int delete() {
	return -1;
}

/** !!! 请重载 */
public int delete(String primarykey1){
	try
	{
		String sqlStmt;
		Statement stmt = conn.createStatement();
		sqlStmt = "delete from " + tableName + "where " + primarykey1Name 
				+ "= '" + primarykey1 + "'" ;
		return stmt.executeUpdate(sqlStmt);
	} catch(SQLException e)
	{
		e.printStackTrace();
		return -1;
	}
}



public int load(String primarykey1) {
	int rtCode;
	String whereClause;
	if (primarykey1Type == 1 ) // Type is String
		whereClause = "(" + primarykey1Name + " = '" + primarykey1 + "') ";
	else
		whereClause = "(" + primarykey1Name + " = " + primarykey1 + ") ";
	rtCode = query( whereClause , "");
	if ( rtCode < 0 ) return -1;
	if ( rtCode == 0 ) return 0; // 
	if ( next() == -1 ) return -1;
	return 1;  // 成功
}

public int load(String primarykey1,String primarykey2) {
	int rtCode;
	String whereClause;	
	if (primarykey1Type == 1 ) // Type is String
		whereClause = "(" + primarykey1Name + " = '" + primarykey1 + "') ";	
	else
		whereClause = "(" + primarykey1Name + " = " + primarykey1 + ") ";

	if (primarykey2Type == 1 ) // Type is String
		whereClause = whereClause+"and "+"(" + primarykey2Name + " = '" + primarykey2 + "') ";
	else
		whereClause =  whereClause+"and"+"(" + primarykey2Name + " = " + primarykey2 + ")";

	rtCode = query( whereClause , "");
	if ( rtCode < 0 ) return -1;
	if ( rtCode == 0 ) return 0; // 
	if ( next() == -1 ) return -1;
	return 1;  // 成功
}

public int getQueryCount(String whereClause) {
	try
	{	
		Statement countStmt = conn.createStatement();
   		String sqlStmt;
   		sqlStmt = "select count(*) QueryCount from " + viewName ;
		if ( (whereClause != null) && (whereClause.equals("") == false) )
			sqlStmt = sqlStmt + " where " + whereClause;
		print(sqlStmt);
		ResultSet lRs=countStmt.executeQuery(sqlStmt);
        	lRs.next() ;
	        int queryCount = lRs.getInt("QueryCount");
	    	lRs.close();
	    	return queryCount;
	}
	catch(SQLException e)
	{
		e.printStackTrace();
   		return -1;
   	}
}

// 返回 行数 请重载
public int query(String whereClause, String orderBy) {
	this.whereClause = whereClause;
	this.orderBy = orderBy;
   	try {   
   		int rowCount = getQueryCount(whereClause);
   		print("rowCount== "+rowCount);
   		if (rowCount == -1)
   			return -1;

   		String sqlStmt;
   		sqlStmt = "select * from " + viewName;
		if ( (whereClause != null) && (whereClause.equals("") == false) )
			sqlStmt = sqlStmt + " where " + whereClause;

   		if ( orderBy.equals("") == false)
			sqlStmt = sqlStmt + " order by " + orderBy;
   		Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
   		rs = stmt.executeQuery(sqlStmt) ;
	        print(whereClause);
   		return rowCount;
   	} catch(SQLException e) {
   		System.out.println(e.getMessage());
   		e.printStackTrace();
   		return(-1);
   	} catch(Exception e) {
   		System.out.println("Exception ="+ e.getMessage());
   		e.printStackTrace();
   		return(-1);
   	}
}

public int query(String whereClause, String orderBy, String computeBy, String computeFomula) {
	this.whereClause = whereClause;
	this.orderBy = orderBy;
   	try {   
   		int rowCount = getQueryCount(whereClause);
   		print("rowCount== "+rowCount);
   		if (rowCount == -1)
   			return -1;

   		String sqlStmt;
   		sqlStmt = "select * from " + viewName;
		if ( (whereClause != null) && (whereClause.equals("") == false) )
			sqlStmt = sqlStmt + " where " + whereClause;

   		if ( orderBy.equals("") == false)
			sqlStmt = sqlStmt + " order by " + orderBy;

   		if ( computeBy.equals("") == false)
			sqlStmt = sqlStmt + " compute " + computeFomula + " by " + computeBy;

   		Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
   		rs = stmt.executeQuery(sqlStmt) ;
	        print(whereClause);
   		return rowCount;
   	} catch(SQLException e) {
   		System.out.println(e.getMessage());
   		e.printStackTrace();
   		return(-1);
   	} catch(Exception e) {
   		System.out.println("Exception ="+ e.getMessage());
   		e.printStackTrace();
   		return(-1);
   	}
}


public void connectDB() { // 连接数据库
  try {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        conn = DriverManager.getConnection("jdbc:odbc:wuyang", "sa", "");
	print("connect ok");
  }catch(Exception e) {
  	System.out.println("dbconnection failed.");
  }
}


/** 
query后load前一个对象的属性
返回是否到最前一个
1 : 否到最前一个
0 : 到最前一个
-1 : 数据库错
*/
public int previous(){
	return previous(true);
}

public int previous(boolean loadAttribute) {
	try {
		if ( rs.previous() ) {
			if (loadAttribute)
				loadAttr();
			return 1;
		}
		return 0;
	}catch (SQLException e)	{
		e.printStackTrace();
		return -1;
	}
}

/** 
query后load前一个对象的属性
返回是否到最前一个
*/
public int first() {
	return first(true);
}

public int first(boolean loadAttribute) {
	try{
		if ( rs.first() ) {
			if (loadAttribute)
				loadAttr();
			return 1;
		}
		return 0;
	}catch (SQLException e)	{
		e.printStackTrace();
		return -1;
	}
}

/**
query后load后一个对象的属性
返回是否到最后一个
*/
public int next() {
	return next(true);
}

public int next(boolean loadAttribute) {
	try{
		if ( rs.next() ) {
			if (loadAttribute)
				loadAttr();
			return 1;
		}
		return 0;
	}catch (SQLException e)	{
		e.printStackTrace();
		return -1;
	}
}

/**
query后load最后一个对象的属性
返回是否到最后一个
*/
public int last() {
	return last(true);
}

public int last(boolean loadAttribute) {
	try{
		if ( rs.last() ) {
			if (loadAttribute)
				loadAttr();
			return 1;
		}
		return 0;
	}catch (SQLException e)	{
		e.printStackTrace();
		return -1;
	}
}

/**
用于分页显示, 处理如下操作：
next , previous
*/
public int listPage(javax.servlet.ServletRequest request) {
   
   String browseMode = (String) request.getParameter("browseMode");
   String position = (String) request.getParameter("position");
   	listPosition = (new Integer(position)).intValue();
   String curentPage = (String) request.getParameter("curPage");
   	curPage = (new Integer(curentPage)).intValue();	
   	
   	if(browseMode.equals("first")){
   		curPage = 1;
   		listPosition = 0;
   		}
   	if(browseMode.equals("next")){         					 /*下翻页*/
	 		   curPage=curPage+1;
			   listPosition=listPosition+numInOnePager;
			   if( (listPosition+numInOnePager)>=recordNum ){
			       state=-1;
			   }else{ 
			       state=1;
			   }
	 	} 
	 	
	 if(browseMode.equals("previous")) {                               /*上翻页*/
	 		curPage=curPage-1;
			listPosition=listPosition-numInOnePager;
			   if (listPosition==0){
			       state=2;
			  } else {
			   	state=1;
			   }
	 	} 

 	first(false);
 	previous(false);	
									/*重新定位*/
   	for (int index = 0; index < listPosition; index++) {
   		next(false);
   	}

	return 1;
}


/** 
中文处理专用, 代替 PreparedStatement::getString(String str)
*/

public String getRsString(ResultSet rs, String name)
{
//InputStream is;
//int len;
	String str;
	try {
		if (name == null) return "";
		str = rs.getString(name); 		
		if(str != null)	  
			str = new String(str.getBytes(),"8859_1" );	
		else
			str = "";			
		return str;
	} catch (Exception e ) {
		e.printStackTrace();
		return "";
	}
}

public String getChnString(String name)
	{
	String str;
	try 	{
		if (name == null) return "";
		name = new String(name.getBytes(),"8859_1" );	
		return name;
		} 
	catch (Exception e ) 
		{
		e.printStackTrace();
		return "";
		}
	}

/** 
中文处理专用, 代替 PreparedStatement::setString(int parameterIndex, String str)
*/
public void setStmtString(PreparedStatement stmt, int parameterIndex, String str) throws SQLException
{
	byte[] b;

	if (str == null)
		stmt.setString(parameterIndex, null);
	else
	{
		b = str.getBytes();
		stmt.setAsciiStream(parameterIndex, (new ByteArrayInputStream(b)) , b.length); // ok
	}
}

/** 
中文字串处理专用, 在javabean中的 public void setXXXX(String aXXXX) 的中文处理可用以下形式：（例）
   {
      checkDocId = setChnString(aCheckDocId);
   }

*/
	public String setChnString(String name) 	//中文输入处理
		{
		try 	{
			String chnstr;                                                             
			if(name== null)
				{ 
				return "";
				}
			else	{
				chnstr = new String(name.getBytes("8859_1"));
				}
			return chnstr;               
			} 
		catch (UnsupportedEncodingException e)
			{
			}
		return "";
		}

/**
用于将从页面读进来的年、月、日转化为一个Date对象，并且在输入为空字符串时转化为1900-0-0
*/
public java.sql.Timestamp stringToDate(String _year,String _month,String _day) {
	
	java.sql.Timestamp adate;
	Integer transferDate1;
	Integer transferDate2;
	Integer transferDate3;
	int year,month,day;
	Calendar  calendar1 = null;//日期

       	calendar1 = Calendar.getInstance();
       	if( (_year == null) || (_month == null) || (_day == null) )return null;
	if( !_year.equals("") || !_month.equals("") ) 
	{
		transferDate1 = new Integer(_year);
		year = transferDate1.intValue();
		transferDate2 = new Integer(_month);
		month = transferDate2.intValue();
		transferDate3 = new Integer(_day);
		day = transferDate3.intValue();
		adate = new java.sql.Timestamp((year-1900),month-1,day,0,0,0,0);
		//calendar1.set(year, month, day );
		//adate = calendar1.getTime();
	} else
	{
		adate = null;
	}
	//返回一个日期
	return adate;
    }

// 格式化返回Date
public String getStringDate(java.util.Date dt , int datePart) {

        String strDate;
        Calendar  calendar1 = null;//日期
        
        if(dt == null) return "";
       	calendar1 = Calendar.getInstance();
        calendar1.setTime(dt);
        switch(datePart){
            case 0: // 返回YYYY-MM-DD格式
            	strDate = "" + calendar1.get(Calendar.YEAR) + "-" + 
            		(calendar1.get(Calendar.MONTH) + 1) + "-" + 
            		calendar1.get(Calendar.DAY_OF_MONTH);
            	break;
       	    case 1: // 返回年
       	    	strDate = calendar1.get(Calendar.YEAR) + ""; 
       	    	break;  	
	    case 2: // 返回月
	    	strDate = calendar1.get(Calendar.MONTH) + 1 + "";
	    	break;
	    case 3: // 返回日
	    	strDate = calendar1.get(Calendar.DAY_OF_MONTH) + "";
	    	break;
            default:
            	strDate = "";
         }
	//返回一个日期String
	return strDate;
}

public java.sql.Timestamp getCurDate() {
    Calendar  calendar1 = null;
    calendar1 = Calendar.getInstance();
    java.util.Date trialTime = new java.util.Date();
    calendar1.setTime(trialTime);
    return (new Timestamp( calendar1.get(Calendar.YEAR) - 1900
    			, calendar1.get(Calendar.MONTH)
    			, calendar1.get(Calendar.DAY_OF_MONTH),0,0,0,0 ));
}
 

public void print(String s){
	if(debug){
		System.out.println(s);
	}
		
}


public void setDebug(boolean state){
	debug = state;
}	

public String doubleFormat(double number1,int per)
   {
	BigDecimal num1=new BigDecimal(number1);
	num1 = num1.setScale(per,5);
	return num1.toString();
   }



}