database.java

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

			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()
	{
		if ( pstm != null ) 
		try
		{
			pstm.close();
		}
		catch(SQLException sqle){System.out.println("调用DataBase.closePstm()函数错误:\r\n"+sqle);}
	}

	/**关闭游标*/
	public void closeRs()
	{
		if ( rs != null )
		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);
		}
		Statement stst = rs.getStatement(); 
		rs.close();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 boolean getAutoCommit()
	{
		try
		{
			return conn.getAutoCommit();
		}catch(SQLException e){}
		return true;
	}

	public void closeAutoCommit()
	{
		try
		{
			conn.setAutoCommit(false);
		}catch(SQLException e){}
	}
	
	public void commit()
	{
		try
		{
			conn.commit();
		}catch(SQLException e){}
	}
	
	public void rollback()
	{
		try
		{
			conn.rollback();
		}catch(SQLException e){}
	}

	public void openAutoCommit()
	{
		try
		{
			conn.setAutoCommit(true);
		}catch(SQLException e){}
	}

	public void createStatement()
	{
		try
		{
			stm = conn.createStatement();
		}catch(SQLException e){}
	}

	public void clearBatch()
	{
		try
		{
			stm.clearBatch();
		}catch(SQLException e){}
	}

	public void addBatch(String sql)
	{
		try
		{
			stm.addBatch(sql);
		}catch(SQLException e){}
	}
	public int[] executeBatch()
	{
		try
		{
			return stm.executeBatch();
		}catch(SQLException e){}
		return null;
	}

	public void closeStm()//专门使用	
	{
		if(stm!=null)
		try
		{
			stm.close();
		}catch(SQLException e){}
	}



	public String ttest() throws Exception
	{
		PreparedStatement pstm = conn.prepareStatement("select * from test1");
		ResultSet rs = pstm.executeQuery();
		rs.next();
		InputStream is = rs.getBinaryStream("ziduan");
		Statement stmt = rs.getStatement(); rs.close(); 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("ZZ_GRWJB", "ZGBH", "WJBH", "2340", false));
		db.releaseConn();
		System.out.println("\r\n\r\nend");
	}
};