📄 sqlhelper.java
字号:
logger.error( "executeQuery SQL Error:["+e.getErrorCode()+"]" );
e.printStackTrace();
throw e;
} catch( Exception e ){
logger.error( "executeQuery Error" );
e.printStackTrace();
throw e;
}
return resultSetPropertiesWraper( rs, bgnLine, retLine );
}
//========================================================================================
//查询数据库
public List executeQuery ( String sqlString ) throws SQLException, Exception{
logger.info( "SQL String is:["+sqlString+"]" );
ResultSet rs;
try{
rs = statement.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;
}
return resultSetWraper( rs );
}
//========================================================================================
//查询数据库
public List executeQueryProperties ( String sqlString ) throws SQLException, Exception{
logger.info( "SQL String is:["+sqlString+"]" );
ResultSet rs;
try{
rs = statement.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;
}
return resultSetPropertiesWraper( rs );
}
//========================================================================================
//查询数据库
public Object executeQueryVO ( String sqlString, String className ) throws SQLException, Exception{
logger.info( "SQL String is:["+sqlString+"]" );
ResultSet rs;
List voList=null;
Class clazz=null;
Object vo=null;
Class[] parameters=null;
String methodName="";
String eleName ="";
Method[] methods=null;
try{
logger.info( "New the VO Object ["+className+"]" );
clazz = Class.forName( className );
vo = clazz.newInstance();
methods = clazz.getMethods();
} catch( Exception e ){
e.printStackTrace();
logger.error( "class :["+className+"] not find error!" );
throw e;
}
try{
rs = statement.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 );
try{
//从返回结果集中取得查询结果
Properties properties;
Object object;
ListIterator testListIterator = null;
Object[] arguments = null;
if( voList != null ){
testListIterator = voList.listIterator();
while( testListIterator.hasNext() ){
properties = (Properties)testListIterator.next();
for( int i=0; i<methods.length; i++ ){
methodName = methods[i].getName();
parameters = methods[i].getParameterTypes();
if( methodName.length() > 4 ){
if( methodName.substring( 0, 4 ).equals( "set_" ) && parameters.length == 1 && parameters[0].getName().equals( "java.lang.String" ) ){
// System.out.println( "methodName :"+methodName );
eleName = methodName.substring( 4, methodName.length() );
// System.out.println( "eleName:"+eleName );
object = properties.get( eleName );
if( object != null ){
//baseInfoVO.setCaravanId( new String(object.toString()) );
arguments = new Object[] {new String( object.toString() )};
methods[i].invoke( vo, arguments );
// System.out.println( eleName+": ["+object.toString()+"]" );
}
}
}
}
Method addMethod = clazz.getMethod( "add", null );
addMethod.invoke( vo, null );
}
}
}catch( Exception e ){
logger.error( "解析Web VO错误！" );
e.printStackTrace();
throw e;
}
return vo;
}
//========================================================================================
//查询数据库，分页 by wjliu at 2005-06-07
public Object executeQueryVO ( String sqlString, String className, Properties pagePro ) throws SQLException, Exception{
if(pagePro.get("PageSize")==null || pagePro.get("CurrentPage")==null){
logger.error( "分页对象pagePro为空！！" );
throw new Exception() ;
}
logger.info( "SQL String is:["+sqlString+"]" );
ResultSet rs;
List voList=null;
Class clazz=null;
Object vo=null;
Class[] parameters=null;
String methodName="";
String eleName ="";
Method[] methods=null;
try{
logger.info( "New the VO Object ["+className+"]" );
clazz = Class.forName( className );
vo = clazz.newInstance();
methods = clazz.getMethods();
} catch( Exception e ){
e.printStackTrace();
logger.error( "class :["+className+"] not find error!" );
throw e;
}
try{
//设置可滚动结果集
clossStatement() ;
statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs = statement.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;
}
//计算记录总数
int rowsCount=0;
if(rs.last()){
rowsCount=rs.getRow();
}
rs.beforeFirst();
//pagePro中装有三个属性，"PageSize","CurrentPage","PageCount"
int CurrentPage = Integer.parseInt( (String)pagePro.get("CurrentPage") ) ;
int PageSize = Integer.parseInt( (String)pagePro.get("PageSize") ) ;
int PageCount = 0 ;
int bgnLine = 0 ;
//计算页总数
if(rowsCount%PageSize==0){
PageCount = rowsCount/PageSize ;
}else{
PageCount = rowsCount/PageSize + 1 ;
}
pagePro.put("PageCount",""+PageCount) ;
//计算起始行
bgnLine = (CurrentPage-1)*PageSize + 1 ;
voList = resultSetPropertiesWraper( rs, bgnLine, PageSize );
try{
//从返回结果集中取得查询结果
Properties properties;
Object object;
ListIterator testListIterator = null;
Object[] arguments = null;
if( voList != null ){
testListIterator = voList.listIterator();
while( testListIterator.hasNext() ){
properties = (Properties)testListIterator.next();
for( int i=0; i<methods.length; i++ ){
methodName = methods[i].getName();
parameters = methods[i].getParameterTypes();
if( methodName.length() > 4 ){
if( methodName.substring( 0, 4 ).equals( "set_" ) && parameters.length == 1 && parameters[0].getName().equals( "java.lang.String" ) ){
// System.out.println( "methodName :"+methodName );
eleName = methodName.substring( 4, methodName.length() );
// System.out.println( "eleName:"+eleName );
object = properties.get( eleName );
if( object != null ){
//baseInfoVO.setCaravanId( new String(object.toString()) );
arguments = new Object[] {new String( object.toString() )};
methods[i].invoke( vo, arguments );
// System.out.println( eleName+": ["+object.toString()+"]" );
}
}
}
}
Method addMethod = clazz.getMethod( "add", null );
addMethod.invoke( vo, null );
}
}
}catch( Exception e ){
logger.error( "解析Web VO错误！" );
e.printStackTrace();
throw e;
}
return vo;
}
//========================================================================================
//查询数据库
public String executeQueryString ( String sqlString ) throws SQLException, Exception{
logger.info( "SQL String is:["+sqlString+"]" );
List voList=null;
ResultSet rs;
String resultString = "";
try{
rs = statement.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 );
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 String executeQueryXMLString ( String sqlString ) throws SQLException, Exception{
logger.info( "SQL String is:["+sqlString+"]" );
List voList=null;
ResultSet resultSet;
String resultString = "";
resultString = "<InfoList>";
try{
resultSet = statement.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;
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -