sqlhelper.java

一些基本jsp

							
							object = new Integer( tmpInt );
							
						} else if( jdbcTypeName.equals( "java.lang.Object" ) ){
							
							object = resultSet.getObject( i+1 );
							
						} else if( jdbcTypeName.equals( "java.lang.Float" ) ){
							
							float tmpFloat = resultSet.getFloat( i+1 );
							
							if( resultSet.wasNull() ){
								tmpFloat = 0;
							}
							
							object = new Float( tmpFloat );
							
						} else if( jdbcTypeName.equals( "java.lang.Short" ) ){
							
							short tmpShort = resultSet.getShort( i+1 );
							
							if( resultSet.wasNull() ){
								tmpShort = 0;
							}
							
							object = new Short( tmpShort );
							
						} else if( jdbcTypeName.equals( "java.sql.Time" ) ){
							
							object = resultSet.getTime( i+1 );
							
						} else if( jdbcTypeName.equals( "java.sql.Timestamp" ) ){
							
							object = resultSet.getTimestamp( i+1 );
							
						}
					
						//objArray[i] = object;
						if( object == null ){
							object = "null";
						}
						
						resultString = resultString+"<"+columnName+">"+object.toString().trim()+"</"+columnName+">";
					}
						
				}catch( SQLException e ){
					logger.error( "结果集解析失败:["+e.getErrorCode()+"]" );
					e.printStackTrace();
					throw e;
				} catch( Exception e ){
					logger.error( "结果集解析失败" );
					e.printStackTrace();
					throw e;
				}
				
				//resultList.add( objArray );
			}	
			
			resultString = resultString+"</record>";
			record_index++;
		} 
		
		logger.info( "结果集解析正确结束" );
		
		/*
		voList = resultSetPropertiesWraper( rs );
		
		try{
			//从返回结果集中取得查询结果
			Properties properties;
			Object object;
			ListIterator testListIterator = null;
			String columnName = "";
			int record_index = 0;
			
			if( voList != null ){
			
				testListIterator = voList.listIterator();
				while( testListIterator.hasNext() ){
					
					resultString = resultString+"<record record_id=\""+record_index+"\">";
					properties = (Properties)testListIterator.next();
					
					for( Enumeration enumeration =
			     		 properties.propertyNames();
			     		 enumeration.hasMoreElements(); ){
			    
			    		columnName = (String)enumeration.nextElement();
			    		object = properties.get( columnName );
						if( object == null ){
							object = "";
						}
						resultString = resultString+"<"+columnName+">"+object.toString().trim()+"</"+columnName+">";
					}
					
					resultString = resultString+"</record>";
					record_index++;
				}
			}
		}catch( Exception e ){
			logger.error( "组装结果集XML字符串错误！" );
			e.printStackTrace();
			throw e;
		}
		*/
		
		resultString = resultString + "</InfoList>";
		
		return resultString;
	}

//========================================================================================
	//查询数据库
	public String executeQueryXMLString ( String sqlString, int bgnLine, int retLine ) throws SQLException, Exception{

		logger.info( "SQL String is:["+sqlString+"]" );
		List voList=null;
		ResultSet resultSet;
		String resultString = "";
		resultString = "<InfoList>";
		
		try{
			Statement fetchStatement = connection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
			resultSet = fetchStatement.executeQuery( sqlString );
			
		} catch( SQLException e ){
			logger.error( "executeQuery SQL Error:["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		} catch( Exception e ){
			logger.error( "executeQuery Error" );
			e.printStackTrace();
			throw e;
		}
		
		//在结果集解析过程中生成xml串，提高查询效率
		ResultSetMetaData resMetaData = resultSet.getMetaData();
		int columnCount = resMetaData.getColumnCount();
		int record_index = 0;
		
		String javaTypeName = "";
		String jdbcTypeName = "";
		String columnName = "";
		Object object = null;
			
		while( resultSet.next() ){
			
			resultString = resultString+"<record record_id=\""+record_index+"\">";
			{
				//ArrayList objArray = new ArrayList();
				//Object[] objArray = new Object[columnCount];
			
				try{	
					javaTypeName = "";
					jdbcTypeName = "";
					columnName = "";
					//注意有byte[]的未处理
					for( int i=0; i<columnCount; i++ ){
						//Object object = clazz[i].newInstance();
						object = null; 
						
						jdbcTypeName = resMetaData.getColumnClassName(i+1);
						columnName = resMetaData.getColumnName( i+1 );
						
						if( jdbcTypeName.equals( "java.sql.Array" ) ){
							
							object = resultSet.getArray( i+1 );
							
						} else if( jdbcTypeName.equals( "java.lang.Long" ) ){
							
							long tmpLong = resultSet.getLong( i+1 );
							
							if( resultSet.wasNull() ){
								tmpLong = 0;
							}
							
							object = new Long( tmpLong );
							
						} else if( jdbcTypeName.equals( "java.lang.Boolean" ) ){
							
							boolean tmpBoolean = resultSet.getBoolean( i+1 );
							
							if( resultSet.wasNull() ){
								tmpBoolean = false;
							}
							
							object = new Boolean( tmpBoolean );
							
						} else if( jdbcTypeName.equals( "java.sql.Blob" ) ){
							
							object = resultSet.getBlob( i+1 );
							
						}  else if( jdbcTypeName.equals( "java.lang.String" ) ){
							
							object = resultSet.getString( i+1 );
							
						} else if( jdbcTypeName.equals( "java.sql.Clob" ) ){
							
							object = resultSet.getClob( i+1 );
							
						} else if( jdbcTypeName.equals( "java.net.URL" ) ){
							
							object = resultSet.getURL( i+1 );
							
						} else if( jdbcTypeName.equals( "java.sql.Date" ) ){
							
							object = resultSet.getDate( i+1 );
							
						} else if( jdbcTypeName.equals( "java.math.BigDecimal" ) ){
							
							object = resultSet.getBigDecimal( i+1 );
							
						} else if( jdbcTypeName.equals( "java.lang.Double" ) ){
							
							double tmpDouble = resultSet.getDouble( i+1 );
							
							if( resultSet.wasNull() ){
								tmpDouble = 0;
							}
							
							object = new Double( tmpDouble );
						
						} else if( jdbcTypeName.equals( "java.lang.Integer" ) ){
							
							int tmpInt = resultSet.getInt( i+1 );
							
							if( resultSet.wasNull() ){
								tmpInt = 0;
							}
							
							object = new Integer( tmpInt );
							
						} else if( jdbcTypeName.equals( "java.lang.Object" ) ){
							
							object = resultSet.getObject( i+1 );
							
						} else if( jdbcTypeName.equals( "java.lang.Float" ) ){
							
							float tmpFloat = resultSet.getFloat( i+1 );
							
							if( resultSet.wasNull() ){
								tmpFloat = 0;
							}
							
							object = new Float( tmpFloat );
							
						} else if( jdbcTypeName.equals( "java.lang.Short" ) ){
							
							short tmpShort = resultSet.getShort( i+1 );
							
							if( resultSet.wasNull() ){
								tmpShort = 0;
							}
							
							object = new Short( tmpShort );
							
						} else if( jdbcTypeName.equals( "java.sql.Time" ) ){
							
							object = resultSet.getTime( i+1 );
							
						} else if( jdbcTypeName.equals( "java.sql.Timestamp" ) ){
							
							object = resultSet.getTimestamp( i+1 );
							
						}
					
						//objArray[i] = object;
						if( object == null ){
							object = "null";
						}
						
						resultString = resultString+"<"+columnName+">"+object.toString().trim()+"</"+columnName+">";
					}
						
				}catch( SQLException e ){
					logger.error( "结果集解析失败:["+e.getErrorCode()+"]" );
					e.printStackTrace();
					throw e;
				} catch( Exception e ){
					logger.error( "结果集解析失败" );
					e.printStackTrace();
					throw e;
				}
				
				//resultList.add( objArray );
			}	
			
			resultString = resultString+"</record>";
			record_index++;
		} 
		
		logger.info( "结果集解析正确结束" );
		
		/*
		voList = resultSetPropertiesWraper( rs, bgnLine, retLine );
		
		try{
			//从返回结果集中取得查询结果
			Properties properties;
			Object object;
			ListIterator testListIterator = null;
			String columnName = "";
			int record_index = 0;
			
			if( voList != null ){
			
				testListIterator = voList.listIterator();
				while( testListIterator.hasNext() ){
					
					resultString = resultString+"<record record_id=\""+record_index+"\">";
					properties = (Properties)testListIterator.next();
					
					for( Enumeration enumeration =
			     		 properties.propertyNames();
			     		 enumeration.hasMoreElements(); ){
			    
			    		columnName = (String)enumeration.nextElement();
			    		object = properties.get( columnName );
						if( object == null ){
							object = "";
						}
						resultString = resultString+"<"+columnName+">"+object.toString().trim()+"</"+columnName+">";
					}
					
					resultString = resultString+"</record>";
					record_index++;
				}
			}
		}catch( Exception e ){
			logger.error( "组装结果集XML字符串错误！" );
			e.printStackTrace();
			throw e;
		}
		*/
		
		resultString = resultString + "</InfoList>";
		
		return resultString;
	}

//========================================================================================
	//查询数据库
	public String executeQueryString ( String sqlString, int bgnLine, int retLine ) throws SQLException, Exception{

		logger.info( "SQL String is:["+sqlString+"]" );
		List voList=null;
		ResultSet rs;
		String resultString = "";
		
		try{
			Statement fetchStatement = connection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
			rs = fetchStatement.executeQuery( sqlString );
			
		} catch( SQLException e ){
			logger.error( "executeQuery SQL Error:["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		} catch( Exception e ){
			logger.error( "executeQuery Error" );
			e.printStackTrace();
			throw e;
		}
		
		voList = resultSetPropertiesWraper( rs, bgnLine, retLine );
		
		try{
			//从返回结果集中取得查询结果
			Properties properties;
			Object object;
			ListIterator testListIterator = null;
			String columnName = "";
			
			if( voList != null ){
			
				testListIterator = voList.listIterator();
				while( testListIterator.hasNext() ){
					properties = (Properties)testListIterator.next();
					
					for( Enumeration enumeration =
			     		 properties.propertyNames();
			     		 enumeration.hasMoreElements(); ){
			    
			    		columnName = (String)enumeration.nextElement();
			    		object = properties.get( columnName );
						if( object == null ){
							object = "";
						}
						resultString = resultString + columnName + "|=|"+object.toString().trim();
						
						if( enumeration.hasMoreElements() ){
							resultString = resultString + "|+|";
						}
					}
					
					if( testListIterator.hasNext() ){
						resultString = resultString + "|^|";
					}
				}
			}
		}catch( Exception e ){
			logger.error( "组装结果集字符串错误！" );
			e.printStackTrace();
			throw e;
		}
		
		return resultString;
	}


//==========================================================================================	
	//修改数据库
	public int executeUpdate ( String sqlString ) throws SQLException, Exception{
		
		logger.info( "SQL String is:["+sqlString+"]" );
		
		int updateNum = 0;
		
		try{
			updateNum = statement.executeUpdate( sqlString );
		} catch ( SQLException e ){
			logger.error( "executeUpdate SQL Error:["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		}
		
		logger.info( "数据修改成功" );
		
		return updateNum;
	}
	
//========================================================================================
	//目前封装器不支持bit类型
	//ResultSet 包装器
	public List resultSetWraper( ResultSet resultSet, Class clazz ) throws SQLException, Exception {
		
		LinkedList resultList = new LinkedList();
		Field[] clazzFields = clazz.getDeclaredFields();
		int clazzFieldCount = clazzFields.length;
		
		ResultSetMetaData resMetaData = resultSet.getMetaData();
		int columnCount = resMetaData.getColumnCount();
		
		if( clazzFieldCount != columnCount ){
			logger.error( this.getClass().getName()+"executeQuery( String sqlString , Class clazz )"
							+"类字段与返回数据字段数量不匹配" );