parentbean.java

“JSP数据库项目案例导航”一书从第一章到第十一章各章实例的源程序文件以及数据库文件。 注意： 1. 本书中的案例提供的数据库环境不同

/**********************************************************
Copyright (C),2003-10-14, Beijing  USTB.
All rights reserved.
Filename: ParentBean.java
Author: 
Version 1.0
Date:2003-10-15
Description:封装与数据库类之上,Bean之下,用于选择连接,释放连接
Other:
Variable List:
1.DataBase db = new DataBase();
2.DealString ds = new DealString()//字符串处理时使用对象

Function List:
1.public ParentBean()//构造函数,初始化连接
2.public Connection getConn()//返回一个数据库连接
3.public void createConn(int type,String url,String usr,String pwd)//取得不同数据库的连接
4.public void closeConn()//释放连接
5.public static void showConnNUM(boolean bool)//测试连接计数

//基本函数
6.protected ResultSet selectRecord(String sql)//查询记录
7.protected int insertRecord(Vector vect)//新增记录
8.protected int updateRecord(Vector vect)//修改记录
9.protected void deleteRecord(String sql)//删除记录
10.protected int executeUpdate(String sql)//执行语句

//辅助使用函数
11.public int makeID(String table,String field1,String field2,String value1,boolean type1)//产生唯一编号
   public int makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)
   public int makeID_Add1(String table,String field1,String field2,String value1,boolean type1)//产生唯一编号
   public int makeID_Add1(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)
12.public int toID(String table,String field1,String field2,String value1)//将名称转换为编号
13.public String toName(String table,String field1,String field2,String value1)//将编号转换为名称
14.protected Vector addVector(String field,String value,String type)//生成4、5的域值
15.public Vector getOnePage(String sql,int page,int records)//分页时取得一页的数据量
16.public int setSort(String table,String field1,String field2,String wherestr,String orderstr,boolean b)//为某一个字段进行重新排序

//查询与更新LOB类型
17.public String QueryCLOB(String table,String wherestr,String clobfield)//查询CLOB类型值
18.public int UpdateCLOB(String table,String wherestr,String blobfield,String blobvalue)//修改CLOB类型值
19.public String QueryBLOB(String table,String wherestr,String clobfield)//查询BLOB类型值
20.public int UpdateBLOB(String table,String wherestr,String blobfield,String blobvalue)//修改BLOB类型值	
21.public String QueryBLOB_JNDI(String table,String wherestr,String blobfield)//查询BLOB类型值
22.public int UpdateBLOB_JNDI(String table,String wherestr,String blobfield,String blobvalue)//修改BLOB类型值

//数据库结构信息
23.public Hashtable getDataBaseInfo()//数据库信息
24.public Vector getTableList()//数据表列表
25.public Vector getTableStruct(String table)//数据表的结构
26.public Vector getResultSetData(ResultSet rs)//取得数据集内容

//对sql语句的分布处理
27.public void prepareStatement(String sql)//创建申明对象
28.public void executeQuery()//执行查询
29.public boolean next()//转向下一条
30.public String getObject(String field,String sqlType)//取得数据并根据数据类型转化为字符串
31.public void setObject(int index,String value,String sqlType)//根据数据类型保存到数据库
32.public void executeUpdate()//执行更新
33.public void closePstm()//关闭申明对象
34.public void closeRs()//关闭游标

1.public void createStatement();//专门使用													//
2.public void clearBatch();																	//
3.public void addBatch(String sql);															//
4.public int[] executeBatch();																//
0.public void closeStm();//专门使用															//

35.public Vector getDataBySql(String sql)//由sql语句得到向量纪录

History:
***********************************************************/
package oa.main;

import java.lang.*;
import java.util.*;
import java.sql.*;

public class ParentBean extends Object
{
	//数据库处理对象*/
	protected DataBase db = new DataBase();
	//字符串处理时使用对象*/
	protected static DealString ds = new DealString();

	public static int count=0;
	private static float memcount = 0.0f; 
	/**当前连接的数据库类型*/
	public static String DBType = "NULL";
	public static String DBName = "";
	public static String FilesPath = "";

	//构造函数,初始化连接*/
	public ParentBean()
	{
	//	System.gc();
		//连接池取得连接
		//db.getConnPool();
		//System.out.println("从连接池创建连接成功!");

		//从类里取得连接
		//db.createConn("oracle.jdbc.driver.OracleDriver","jdbc:oracle:thin:@10.0.0.94:1521:hg","oaadminuser","lancom4454");
		//db.createConn("oracle.jdbc.driver.OracleDriver","jdbc:oracle:thin:@192.168.0.53:1521:jw","jw","jw");
		//db.createConn(oracle.jdbc.driver.OracleDriver","jdbc:oracle:thin:@10.2.4.253:1521:jw","jw","u2s0t0B3");
		//db.createConn("oracle.jdbc.driver.OracleDriver","jdbc:oracle:thin:@192.168.0.63:1521:hg","oa","oa");

		//从Access取得连接
		//db.createConn("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:HANFENG","admin","");
		
		//System.out.println("通过自身创建连接成功!");
		db.getMyConnPool();

		DBType = "Oracle";
		DBName = "jw";
		init();
//		System.out.print("\r当前实际连接数:["+ds.toLengthStr(curnum+"",5)+"]                接受请求连接数:["+ds.toLengthStr(sumnum+"",10)+"]");
	}

	public void init()
	{
		ResultSet rs = selectRecord("select XMMC from CODE_ZDB where ZDMC='上传文件存放路径'");
		Statement stmt = null;
		try{
		if(rs.next())
		{
			FilesPath = rs.getString(1);
			//System.out.println("\n路径:"+FilesPath);

		}
		}catch(Exception e){System.out.println("ParentBean::init(void)运行时出错："+e);}
		finally{
			if(rs!=null)try{ stmt = rs.getStatement(); rs.close();}catch(Exception e){System.out.println("ParentBean::init(void)关闭记录集rs时出错"+e);}
			if(stmt!=null) try{stmt.close();}catch(Exception e){System.out.println("ParentBean::init(void)关闭声明时statement出错"+e);}		
		}
	}

	/**返回一个数据库连接*/
	public Connection getConn()
	{
		DBType = "Oracle";
		DBName = "jw";
		return db.conn;
	}

	/**取得不同数据库的连接*/
	public void createConn(int type,String ip,String port,String dbsrv,String dbname,String usr,String pwd)
	{
		/**
		 *type: 数据库类型
		 *      1. Oracle		url示例:"jdbc:oracle:thin:@localhost:1521:orcl"
		 *      2. Access		url示例:"jdbc:odbc:HANFENG"
		 *      3. SQL Server	url示例:"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=pubs"
		 *      4. DB2			url示例:"jdbc:db2://localhost:5000/sample"
		 *      5. Informix		url示例:"jdbc:informix-sqli://123.45.67.89:1533/testDB:INFORMIXSERVER=myserver"
		 *      6. Sybase		url示例:"jdbc:sybase:Tds:localhost:5007/tsdata"
		 *		7. MySQL		url示例:"jdbc:mysql://localhost/softforum?user=soft&password=soft1234&useUnicode=true&characterEncoding=8859_1"
		 *		8. PostgreSQL	url示例:"jdbc:postgresql://localhost/soft"
		 *		9. Dbtools		url示例:
		 *		10.Foxpro		url示例:
		 *		11.Paradox		url示例:
		 *		12.Excel		url示例:
		 *		13.Text			url示例:
		 *		14.XBase		url示例:
		 *		15.dBase		url示例:
		 *		16.FoxBase		url示例:
		 *		17.SQLBase		url示例:
		 *		18.Approach		url示例:
		 *
		 *ip:		地址，如192.168.0.1,或localhost
		 *port:		端口号
		 *dbsrv:	数据库服务器(Informix专有)
		 *dbname:	数据库名,或数据源名
		 *
		 *参数用法:
		 *			1.type不同数据库使用不同序号；
		 *			2.Access只是用数据源dbname；
		 *			3.dbsrv只有使用Informix；
		 *			4.port对MySQL,PostgreSQL不用；
		 *			5.ip,port,dbnme除了以上2和4之外都要使用；
		*/
		this.DBName = dbname;

		if(type==1)//Oracle
		{
			db.createConn("oracle.jdbc.driver.OracleDriver","jdbc:oracle:thin:@"+ip+":"+port+":"+dbname,usr,pwd);
			DBType =  "Oralce";
		}
		else if(type==2)//Access
		{
			db.createConn("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:"+dbname);
			DBType =  "Access";
		}
		else if(type==3)//SQL Server
		{
			db.createConn("com.microsoft.jdbc.sqlserver.SQLServerDriver","jdbc:microsoft:sqlserver://"+ip+":"+port+";DatabaseName="+dbname,usr,pwd);
			DBType =  "SQLServer";
		}
		else if(type==4)//DB2
		{
			db.createConn("com.ibm.db2.jdbc.app.DB2Driver","jdbc:db2://"+ip+":"+port+"/"+dbname,usr,pwd);
			DBType =  "DB2";
		}
		else if(type==5)//Informix
		{
			db.createConn("com.informix.jdbc.IfxDriver","jdbc:informix-sqli://"+ip+":"+port+"/"+dbname+":INFORMIXSERVER="+dbsrv,usr,pwd);
			DBType =  "Informix";
		}
		else if(type==6)//Sybase
		{
			db.createConn("com.sybase.jdbc.SybDriver","jdbc:sybase:Tds:"+ip+":"+port+"/"+dbname,usr,pwd);
			DBType =  "Sybase";
		}
		else if(type==7)//MySQL
		{
			db.createConn("org.gjt.mm.mysql.Driver","jdbc:mysql://"+ip+"/"+dbname+"user="+usr+"&password="+pwd+"&useUnicode=true&characterEncoding=8859_1");
			DBType =  "MySQL";
		}
		else if(type==8)//PostgreSQL
		{
			db.createConn("org.postgresql.Driver","jdbc:postgresql://"+ip+"/s"+dbname,usr,pwd);
			DBType =  "PostgreSQL";
		}
		else if(type==9)//Dbtools
		{
			DBType =  "Dbtools";
		}
		else if(type==10)//Foxpro
		{
			DBType =  "Foxpro";
		}
		else if(type==11)//Paradox
		{
			DBType =  "Paradox";
		}
		else if(type==12)//Excel
		{
			DBType =  "Excel";
		}
		else if(type==13)//Text
		{
			DBType =  "Text";
		}
		else if(type==14)//XBase
		{
			DBType =  "XBase";
		}
		else if(type==15)//dBase
		{
			DBType =  "dBase";
		}
		else if(type==16)//FoxBas
		{
			DBType =  "FoxBas";
		}
		else if(type==17)//SQLBase
		{
			DBType =  "SQLBase";
		}
		else if(type==18)//Approach
		{
			DBType =  "Approach";
		}
		else
		{
			DBType =  "NULL";
		}
	}

	/**释放连接*/
	public void closeConn()
	{
		DBType = "NULL";
		//db.releaseConn();
		db.releaseMyConnPool();
		//System.out.println("释放连接");

	//	System.out.print("\r当前实际连接数:["+ds.toLengthStr(curnum+"",5)+"]                接受请求连接数:["+ds.toLengthStr(sumnum+"",10)+"]");
	
	}
	
	/**查询记录*/
	public ResultSet selectRecord(String sql)
	{
		return db.QuerySQL(sql);
	}

	/**新增记录*/
	public int insertRecord(Vector vect)
	{
		/**Vector:第1项 表名(String)
		//		 第2项 列名(Vector[Field(String),Value(String,CLOB,BLOB),Type("CHAR","NUM","TIME","CLOB","BLOB")])*/

		//临时变量
		String sqlField = "";//形如(F1,F2)
		String sqlValue = "";//形如(V1,V2)
		String field = "";
		String value = "";
		String type = "";
		
		for(int i=1;i<vect.size();i++)
		{
			//对某一个字段
			Vector v_t = (Vector)vect.get(i);
			field = (String)v_t.get(0);
			value = (String)v_t.get(1);
			if (value.indexOf("'")!=-1)
			{
				value = value.replaceAll("'","''");
			}
			type = (String)v_t.get(2);

			//组合字段SQL
			if(sqlField.equals(""))sqlField = "(";
			else sqlField = sqlField + ",";
			sqlField = sqlField + field;

			//组合值SQL
			if(sqlValue.equals(""))sqlValue = "(";
			else sqlValue = sqlValue + ",";
			if(value.equals(""))//为空时
			{
				sqlValue = sqlValue + "null";
			}
			else if(type.equals("CHAR"))//字符串
			{
				sqlValue = sqlValue + "'" + value + "'";
			}
			else if(type.equals("NUM"))//数值
			{
				sqlValue = sqlValue + value;
			}
			else if(type.equals("TIME"))//日期
			{
				sqlValue = sqlValue + "to_date('yyyy-MM-dd HH:mm:ss','" + value + "')";
			}
			else if(type.equals("CLOB"))//clob类型
			{
				sqlValue = sqlValue + "empty_clob()";
			}
			else if(type.equals("BLOB"))//blob类型
			{
				sqlValue = sqlValue + "empty_blob()";
			}
		}

		sqlField = sqlField + ")";
		sqlValue = sqlValue + ")";
		String sql = "insert into " + (String)vect.get(0) + sqlField + " values" + sqlValue;
		return db.ExecuteSQL(sql);
	}