database.java

基于jsp-servlet-bean结构开发的,具备新闻发布管理、3级栏目管理、用户管理等功能。注意

		return vect;
	}

	/**取得数据集内容*/
	public Vector getResultSetData(ResultSet rs)
	{
		Vector vect = new Vector();
		try
		{
			//取得列数和列名
			ResultSetMetaData rsmd = rs.getMetaData();
			int cols = rsmd.getColumnCount();
			while(rs.next())
			{
				Hashtable hash = new Hashtable();
				for(int i = 1; i <= cols; i++)
				{
					DealString ds = new DealString();
					String field = ds.toString(rsmd.getColumnName(i));
					String value = ds.toString(rs.getString(i));
					hash.put(field, value);
				}
				vect.add(hash);
			}
		}
		catch(SQLException sqle)
		{
			System.out.println("调用DataBase.getResultSetData()函数错误:\r\n" + sqle);
		}
		return vect;
	}

	/**创建申明对象*/
	public void prepareStatement(String sql)
	{
		try
		{
			pstm = conn.prepareStatement(sql);
		}
		catch(SQLException sqle)
		{
			System.out.println("调用DataBase.prepareStatement()函数错误:\r\n" + sqle);
		}
	}

	/**执行查询*/
	public void executeQuery()
	{
		try
		{
			rs = pstm.executeQuery();
		}
		catch(SQLException sqle)
		{
			System.out.println("调用DataBase.executeQuery()函数错误:\r\n" + sqle);
		}
	}

	/**转向下一条*/
	public boolean next()
	{
		try
		{
			return rs.next();
		}
		catch(SQLException sqle)
		{
			System.out.println("调用DataBase.next()函数错误:\r\n" + sqle);
		}
		return true;
	}

	/**取得数据并根据数据类型转化为字符串*/
	public String getObject(String field, String sqlType)
	{
		try
		{
			if(rs == null)
			{
				return "";
			}
			if(sqlType.equals("BINARY") || sqlType.equals("VARBINARY"))
			{ //字节型
				byte b[] = rs.getBytes(field);
				return new String(b);
			}
			else if(sqlType.equals("LONGVARBINARY") || sqlType.equals("BLOB"))
			{ //未编码大字节型
				InputStream is = rs.getBinaryStream(field);
				return(new DealFile()).readCHStr(is);
			}
			else if(sqlType.equals("LONGVARCHAR") || sqlType.equals("CLOB"))
			{ //编码大字节型
				InputStream is = rs.getAsciiStream(field);
				return(new DealFile()).readCHStr(is);
			}
			else
			{ //字符串型
				return rs.getString(field);
			}
		}
		catch(Exception sqle)
		{
			System.out.println("调用DataBase.getObject()函数错误:\r\n" + sqle);
		}
		return "";
	}

	/**根据数据类型保存到数据库*/
	public void setObject(int index, String value, String sqlType)
	{
		try
		{
			if(sqlType.equals("ARRAY"))
			{ //数组型?????
			}
			else if(sqlType.equals("BIGINT"))
			{ //64位的有符号整数
				Long l = new Long(value);
				pstm.setObject(index, l);
			}
			else if(sqlType.equals("BINARY") || sqlType.equals("VARBINARY"))
			{ //字节型
				byte b[] = value.getBytes();
				pstm.setObject(index, b);
			}
			else if(sqlType.equals("BIT"))
			{ //布尔型
				Boolean b = new Boolean("true");
				if(value.equals("0"))
				{
					b = new Boolean("false");
				}
				pstm.setObject(index, b);
			}
			else if(sqlType.equals("BLOB") || sqlType.equals("LONGVARBINARY"))
			{ //未编码流类型
				FileInputStream fis = (new DealFile()).toInputStream(value);
				pstm.setBinaryStream(index, fis,
									 (int)(new File("tmp.txt")).length());
			}
			else if(sqlType.equals("CLOB") || sqlType.equals("LONGVARCHAR"))
			{ //编码流类型
				FileInputStream fis = (new DealFile()).toInputStream(value);
				pstm.setAsciiStream(index, fis,
									(int)(new File("tmp.txt")).length());
			}
			else if(sqlType.equals("BOOLEAN"))
			{ //????
			}
			else if(sqlType.equals("CHAR") || sqlType.equals("VARCHAR"))
			{ //字符串型
				pstm.setObject(index, value);
			}
			else if(sqlType.equals("DATALINK"))
			{ //????
			}
			else if(sqlType.equals("DATE"))
			{ //日期型
				int year = Integer.parseInt(value.substring(0, 4));
				int month = Integer.parseInt(value.substring(5, 7));
				int day = Integer.parseInt(value.substring(8, 10));
				java.sql.Date d = new java.sql.Date(year, month, day);
				pstm.setObject(index, d);
			}
			else if(sqlType.equals("DECIMAL") || sqlType.equals("NUMERIC"))
			{ //固定精度十进制型
				java.math.BigDecimal b = new java.math.BigDecimal(value);
				pstm.setObject(index, b);
			}
			else if(sqlType.equals("DISTINCT"))
			{ //????
			}
			else if(sqlType.equals("DOUBLE") || sqlType.equals("FLOAT"))
			{ //双精度浮点型
				Double d = new Double(value);
				pstm.setObject(index, d);
			}
			else if(sqlType.equals("INTEGER"))
			{ //32位的有符号整数
				Integer i = new Integer(value);
				pstm.setObject(index, i);
			}
			else if(sqlType.equals("JAVA_OBJECT"))
			{ //????
			}
			else if(sqlType.equals("NULL"))
			{ //????
			}
			else if(sqlType.equals("OTHER"))
			{ //????
			}
			else if(sqlType.equals("REAL"))
			{ //单精度浮点型
				Float f = new Float(value);
				pstm.setObject(index, f);
			}
			else if(sqlType.equals("REF"))
			{ //????
			}
			else if(sqlType.equals("SMALLINT"))
			{ //16位的有符号整数
				Short s = new Short(value);
				pstm.setObject(index, s);
			}
			else if(sqlType.equals("STRUCT"))
			{ //????
			}
			else if(sqlType.equals("TIME"))
			{ //时间型
				int hour = Integer.parseInt(value.substring(0, 2));
				int minute = Integer.parseInt(value.substring(3, 5));
				int second = Integer.parseInt(value.substring(6, 8));
				java.sql.Time t = new java.sql.Time(hour, minute, second);
				pstm.setObject(index, t);
			}
			else if(sqlType.equals("TIMESTAMP"))
			{ //日期时间型
				int year = Integer.parseInt(value.substring(0, 4));
				int month = Integer.parseInt(value.substring(5, 7));
				int date = Integer.parseInt(value.substring(8, 10));
				int hour = Integer.parseInt(value.substring(11, 13));
				int minute = Integer.parseInt(value.substring(14, 16));
				int second = Integer.parseInt(value.substring(17, 19));
				int nano = Integer.parseInt(value.substring(20, 21));
				java.sql.Timestamp t = new java.sql.Timestamp(year, month, date,
					hour,
					minute, second, nano);
				pstm.setObject(index, t);
			}
			else if(sqlType.equals("TINYINT"))
			{ //8位无符号整数
				Byte b = new Byte(value);
				pstm.setObject(index, b);
			}
		}
		catch(Exception sqle)
		{
			System.out.println("调用DataBase.setObject()函数错误:\r\n" + sqle);
		}
	}

	/**执行更新*/
	public void executeUpdate()
	{
		try
		{
			pstm.executeUpdate();
		}
		catch(SQLException sqle)
		{
			System.out.println("调用DataBase.executeUpdate()函数错误:\r\n" + sqle);
		}
	}

	/**关闭申明对象*/
	public void closePstm()
	{
		try
		{
			pstm.close();
		}
		catch(SQLException sqle)
		{
			System.out.println("调用DataBase.closePstm()函数错误:\r\n" + sqle);
		}
	}

	/**关闭游标*/
	public void closeRs()
	{
		try
		{
			rs.close();
		}
		catch(SQLException sqle)
		{
			System.out.println("调用DataBase.closeRs()函数错误:\r\n" + sqle);
		}
	}

	public void test() throws Exception
	{
		PreparedStatement pstm = conn.prepareStatement(
			"update test set name=?,time=?,d=?,bb=?");
		java.sql.Time d = new java.sql.Time(20031011);
		pstm.setObject(1, "fdafafdsa");
		Boolean b = new Boolean(true);
		pstm.setObject(2, b);
		pstm.setObject(3, d);
		pstm.setObject(4, (new String("testlzb")).getBytes());
		pstm.executeUpdate();
		pstm.close();

		ResultSet rs = QuerySQL("select * from test");
		while(rs.next())
		{
			//System.out.println("1=="+new String(rs.getObject("bb")));
			//System.out.println("2=="+rs.getObject("bb").toString());
			System.out.println((String)rs.getObject("name"));
			Object o = rs.getObject("bb");
			String t = new String((byte[])o);
			byte bv[] = t.getBytes();
			System.out.println("____" + bv[0]);
			String s = new String(bv);
			System.out.println("t" + t);
			System.out.println("s" + s);
		}
		rs.close();
		Statement stst = rs.getStatement();
		if(stst != null)
		{
			stst.close();
//输出:getString()数字，字符串，日期,getBytes(),getBinaryStream()BLOB,getAsciiStream()CLOB
//均转化为字符串＊＊＊＊＊＊＊＊＊１
//对二进制，输出的均为转化后的字符串，在输入时反转换为二进制流，只要在输出时二进制流和输入时的二进制流一样就行
//检验Binary即可验证是否正确
//输入:setObject();构造相应的对象＊＊＊＊＊＊＊＊＊２

//输出：根据类型来确定使用那个函数(ParentBean)
			//正转：将流转换为字符串(DealFile)
//输入：根据类型来将字符串构造成不同的数据类型(ParentBean)
			//反转：将字符串转换为流(DealFile)

		}

	}

	public void setBinaryStream(int index, InputStream is, int t) throws
		Exception
	{
		pstm.setBinaryStream(index, is, t);
	}

	public void setAsciiStream(int index, InputStream is, int t) throws
		Exception
	{
		pstm.setBinaryStream(index, is, t);
	}

	public String ttest() throws Exception
	{
		PreparedStatement pstm = conn.prepareStatement("select * from test1");
		ResultSet rs = pstm.executeQuery();
		rs.next();
		InputStream is = rs.getBinaryStream("ziduan");
		rs.close();
		Statement stmt = rs.getStatement();
		if(stmt != null)
		{
			stmt.close();
		}
		return(new DealFile()).readCHStr(is);
	}

	public static void main(String args[]) throws Exception
	{
		/*必须执行的代码
		 *stm = rs.getStatement();
		 *rs.close();
		 *stm.close();
		 */
		System.out.println("begin\r\n\r\n");

		DataBase db = new DataBase();
		//db.createConn("oracle.jdbc.driver.OracleDriver","jdbc:oracle:thin:@10.0.0.94:1521:hg","oaadminuser","lancom4454");
		//db.createConn("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:test");
		//db.createConn("com.microsoft.jdbc.sqlserver.SQLServerDriver","jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=test","test","test");
		//ParentBean.DBName = "hg";

		//System.out.println(db.UpdateCLOB("test","","content","ttew测试tewagdsagsad"));
		//System.out.println(db.QueryCLOB("test","","content"));

		//System.out.println(db.getDataBaseInfo());
		//System.out.println(db.getTableList());
		//System.out.println(db.getTableStruct("test"));
		//System.out.println(db.getResultSetData(db.QuerySQL("select * from test")));
		//System.out.println(db.getResultSetData(db.getTypeInfo()));

		/**?????
		 1.Access不能取得表列表
		 2.Oracle不能取得表结构:CLOB,BLOB
		 3.不能取得视图结构
		 4.clob,blob,time类型读出和写入时没有区分，都当作字符串处理了
		 5.字段精确度问题？如何区分和使用？
		 6.定义不同数据库的需求，
		 7.可视化的界面
		 8.字段映射列表，如何更合理？
		 １。字段类型，创建表时字段属性写法(长度，精确度，)，写入数据时所用的函数setObject
		 　　clob,blob,字符串（Ascii,unicode），数字（整数，浮点数），时间，布尔型，字节类型，数组
		 ２。如何取得access表列表
		 ３。数据库驱动,不同数据库，字段映射配置情况
		 ４。对不同的数据实现不同的配置选项，仿造SQLServer
		 */
		/*
		  //不用addBatch(),clearBatch()
		  //str长度<=file长度-2
		  //间接方式----------------(1)
		  db.prepareStatement("update test set dlxc=?");
		  db.setObject(1,"大类型测试","CLOB");
		  db.executeUpdate();
		  db.closePstm();
		  //----------------------
		  db.prepareStatement("select * from test");
		  db.executeQuery();
		  db.next();
		  System.out.println(db.getObject("dlxc","CLOB"));
		  db.closeRs();
		  db.closePstm();
		  //直接方式---------------------(2)
		  db.prepareStatement("update test set dlxc = ? ");
		  FileInputStream fis = new FileInputStream("out.txt");
		  db.setAsciiStream(1,fis,100);
		  db.executeUpdate();
		  //----------------------
		  System.out.println(db.QueryCLOB("test","","dlxc"));
		 */
//db.ExecuteSQL("create table test1 (bh integer,ziduan blob)");
//db.ExecuteSQL("insert into test1(bh) values(1)");
		/*
		 db.prepareStatement("update test1 set ziduan=?");
		 db.setObject(1,"大类型测试","BLOB");
		 db.executeUpdate();
		 db.closePstm();
		 db.prepareStatement("select * from test1");
		 db.executeQuery();
		 db.next();
		 System.out.println(db.getObject("ziduan","BLOB"));
		 db.closeRs();
		 db.closePstm();
		 */
//System.out.println(db.ttest());

//db.prepareStatement("update article set content=?");
//db.setObject(1,"大类型测试eee","BLOB");
//db.executeUpdate();
//db.closePstm();

//db.UpdateBLOB_JNDI("test1","bh=1","ziduan","大类型测试333");
//System.out.println(db.QueryBLOB_JNDI("article","id=60","content"));
//System.out.println(db.makeID_Add1("Article","ID","","",true));
		//db.releaseConn();

		//mysql测试
        db.createConn("org.gjt.mm.mysql.Driver","jdbc:mysql://localhost:3306/yj","admin","admin");
        System.out.println(db.getOnePage("select timestamp from pg where ipaddress='192.168.0.6' order by timestamp desc",1,100));
		//db.releaseMyConnPool();
		System.out.println("\r\n\r\nend");
	}
};