dyquery.java

java 数据库连接通用源码 java 数据库连接通用源码

package com.dyit.sql;
import java.util.*;
import java.sql.*;
import javax.sql.*;

/* 
* DYquery  	ver 1.02   beginner 0.9
* author :zhanghan             
* write-time :2001.4.3
* debug-time: 2002.3.14
* write for dyit-bsjs com
* function : get result set from delegated table
*
* debug:0.91 change name
*	0.92 change the bug of all element get the same thing : use clone()
*            seperate sql from select function
*	0.93 change function
*		add method getSpecialValue();getValues();getColumns();getColumnNum();getRowNum()
*               change iRow ,iColumn property
*	0.94 debug querySql()
*	0.95 change executesql()property add strErr
*	0.96 sql Date format to 'yyyy-mm-dd hh24:mi:ss' 
*	0.97 add method getSpecialValue(int)
*	0.98 change param myDataSource-> DYDataSource
*	0.99 add method getAllValues(); add multQuerySql(DYDataSource ds,String tablename,String[] rowname,String[] targetids,String[] colshow)
*	0.995 add Types doc && oracle.sql.ROWID type
*	0.996 update vRes in executeSql();
*	1.01 plus in a new executeSql(Connection con, String strSql)throws SQLException ;
*       1.02 change package
*/
/**提供数据查询的结果集, 将数据结果集与数据连接分开
*  <p>其中包含一个 集合属性 在集合属性中得到结果集的内容
*  
*/
public class DYquery{

private int iRow=0;//结果集的条数 含列名
private int iColumn=0;//每个结果的列数 ,从1开始
private String strErr = "";   //出错信息
/**结果集 结构：0 行 列名 ；1--(iRow-1)行 查询结果*/
public Vector vRes = new Vector();//结果集 结构：0 行 列名 ；1--(iRow-1)行 查询结果
 
 
/**  query and get all data from ResultSet to  vRes
   *	<p>按关键字 查询结果并将数据由resultset 中取出
   *    @param    ds          suport datasource,DYDataSource type
   *    @param    tablename   name of basic-data table, string type
   *    @param    rowname     name of the PrimaryKey column   , string type
   *	@param 	  targetid    value that want to find, string type
   *	@param    colshow     show column, string arrary type
   *    @return   int 	      0 success <p>1 err  
   *    @author   zhanghan
   *    @date     2001/4/3
   *	@version 0.95
   */
public int querySql(DYDataSource ds,String tablename,String rowname,String targetid,String[] colshow){
  
	//组织sql 语句
	String strSql = "select ";
	if(colshow!=null&&colshow.length>0){
		String ste = "";
		for(int i=0;i<colshow.length;i++)
			if(!colshow[i].trim().equals(""))ste = ste+colshow[i].trim()+", ";
		ste = ste.substring(0,ste.lastIndexOf(", "));
		strSql += ste; 
	}
	else strSql += "*";
	strSql = strSql+" from "+tablename;
	if(rowname!=null&&!rowname.equals(""))strSql+=" where "+rowname+"='"+targetid+"'";
	//System.out.println(strSql);
	return executeSql(ds,strSql);
}
/**  
   *	<p>按多个关键字 查询结果并将数据由resultset 中取出
   *    @param    ds          suport datasource,DYDataSource type
   *    @param    String tablename   name of basic-data table, string type
   *    @param    String []rowname     names of the PrimaryKey column   , string array
   *	@param 	  String []targetids    values that want to find, string arrary
   *	@param    colshow     show column, string arrary type
   *    @return   int 	      0 success <p>1 数据库错误,  -1 参数错
   *    @author   zhanghan
   *    @date     2001/7/25
   *	@version 0.99
   */
public int multQuerySql(DYDataSource ds,String tablename,String[] rowname,String[] targetids,String[] colshow){
  	
  	if(rowname.length!=targetids.length){strErr=" rowname's length is differe from its values length";return -1;}
	//组织sql 语句
	String strSql = "select ";
	if(colshow!=null&&colshow.length>0){
		String ste = "";
		for(int i=0;i<colshow.length;i++)
			if(!colshow[i].trim().equals(""))ste = ste+colshow[i].trim()+", ";
		ste = ste.substring(0,ste.lastIndexOf(", "));
		strSql += ste; 
	}
	else strSql += "*";
	strSql = strSql+" from "+tablename;
	if(rowname!=null&&rowname.length>0){
		//寻找数组中的开始项
		int item =0;
		while(rowname[item]==null||rowname[item].equals(""))item++;//直到找到不为空
		if(item<rowname.length){//数组中有值
			strSql+=" where ";
			for(int i=item;i<rowname.length;i++){
				if(rowname[i]!=null&&!rowname[i].equals(""))strSql+=rowname[i]+"='"+targetids[i]+"' and ";
			}
		strSql = strSql.substring(0,strSql.lastIndexOf("and "));
		}
	}
	//System.out.println(strSql);
	return executeSql(ds,strSql);
}
/****/
public void clear(){
	if(vRes!=null&&vRes.size()>0)vRes.clear();
	iRow =0;
	iColumn =0;
	strErr = "";
	}
/**  query and get all data from ResultSet to  vRes
	   *	<p>查询结果 resultset 中取出的执行函数  可以是任何sql语句
	   *    @param    ds         support datasource,DYDataSource type
	   *    @param    strsql     sql that want to run, string  type
	   *    @return   int 	      0 success<p> 1 err  
	   *    @author   zhanghan
	   *    @date     2001/4/3
	   */
public int executeSql(DYDataSource ds,String strSql){
	clear();// 清除原有结果集 update 2001-9-6
	
	// 执行函数  可以是任何sql语句
	//return int  0 success 1 err
	try{
	Connection con = ds.getConnection();
	executeSql(con,strSql);
	con.close();
	}
	catch(SQLException e)
	{	strErr = " fail when execute sql in DYquery.\n"+e.toString();
		return -1;
	}
	return 0;
}
/**扩展的 sql执行查询语句 使用已建立的连接查询 结束时没有关闭连接
*
*
*/
public void executeSql(Connection con, String strSql)throws SQLException{
	this.clear();
	Statement st = con.createStatement();
	ResultSet rs = st.executeQuery(strSql);
	ResultSetMetaData rsmd=rs.getMetaData();
	iColumn = rsmd.getColumnCount();
	//插入列名
	String s[] = new String[iColumn];
		for (int i=1; i<=iColumn; i++) {
	    		s[i-1] = rsmd.getColumnLabel(i) ;
		}
	vRes.add(s.clone());
	iRow++;
	//插入结果集

	while (rs.next()){
			// for every row
			for (int i=1;i<=iColumn;i++){
				//转换数据类型
				s[i-1]=convertData(rs,rsmd.getColumnType(i),i);
			}
			vRes.add(s.clone());
			iRow ++;
	}
	rs.close();
	




}

 /**   get sql error info
   	*	<p>取出错误信息
   	*    @return   String 	   <p>value of the exact row on column , string type 
   	*    @author   zhanghan
   	*    @date     2001/4/19
   	*/
public String getErrString(){
	
	 return strErr;
	}
	
/**   get special data from  vRes
   	*	<p>取出特定行特定列的数据
   	*    @param    columnName   name of column, string type
   	*    @param    iRows       row  you want in vRes   , int type
   	*    @return   String 	   value of the exact row on column , string type 
   	*    @author   zhanghan
   	*    @date     2001/4/10
   	*/
public String getSpecialValue(String columnName,int iRows){
	
   	boolean flag = true;
   	//check column if no this column return -1 else return Num of columnName
   	int iColumns = columnNameToNum(columnName);
   	if(iRows>=iRow||iColumns==-1)flag = false;
   	if(flag){
   		String [] strtemp = (String[])vRes.elementAt(iRows);
   		return strtemp[iColumns];
   		}
   	else return "ColumnName not exist or Row beyond max!";
}
/**   get special column data from  vRes
   	*	<p>取出特指列的数据
   	*    @param    columnName   <p>name of column, string type
   	*    @return   String[]    <p>value of the exact  column , stringArrary type 
   	*    @author   zhanghan
   	*    @date     2001/4/10
   	*/
public String[] getSpecialValue(String columnName){
	
   	int iColumns = columnNameToNum(columnName);
   	return getSpecialValue(iColumns);
}
/**   get special column data from  vRes
   	*	<p>取出特指列的数据
   	*    @param    column   <p>Num of column, int type begin from 0
   	*    @return   String[]    <p>value of the exact  column , stringArrary type 
   	*    @author   zhanghan
   	*    @date     2001/4/10
   	*/
public String[] getSpecialValue(int column){
	
   	String[] strValue = new String[iRow];
   	if(column>-1)	for(int i=0;i<iRow;i++)strValue[i] = ((String[])vRes.elementAt(i))[column];
   	else {strValue[0] = "-1-1-1";strValue[1] = "NO this column name exist in result!";}
	return strValue;
}
/**   get special line data from  vRes