opview.java

本套光盘提供了本书各章实例的所需的部分源程序文件以及数据库文件。读者 需要使用JDK 1.4(必需）版

import java.sql.*;
import java.net.URL;

class opview{
public static void main (String args[]) {
String url="jdbc:microsoft:sqlserver://localhost:1433;User=JavaDB;Password=javadb;DatabaseName=northwind";
String query0 = "CREATE VIEW VIEW1(ID, name, others, des) AS SELECT f.id, f.name, f.others, 	f.description FROM javadb.firsttable as f";
 
String query1 = "create view view2(ID,name,types,others,des,details) as selectf.id,f.name,f.types,f.others,f.description,s.detail from javadb.firsttable as f,javadb.secondtable as s where f.id = s.id WITH CHECK OPTION";
String query2 = "Drop View view2";
String query = "SELECT * from view2";
	
//把视图中的name那一列的值都改成test view
String update = "Update view2  Set Name = 'test view'";
try {
		//加载SQL Server JDBC驱动程序	
		Class.forName ("com.microsoft.jdbc.sqlserver.SQLServerDriver");
				
		Connection con = DriverManager.getConnection (url);
		DatabaseMetaData dbmd = con.getMetaData();

		//建立一个Statement对象,用这个对象向驱动程序发送SQL语句

		Statement stmt = con.createStatement ();

		//执行SQL操作
		stmt.executeUpdate(query0);
		stmt.executeUpdate(query1);
		stmt.executeUpdate(update);

		//显示view2的内容
		ResultSet rs = stmt.executeQuery(query);
		dispResultSet (rs);
		
		//显示数据库的视图
		System.out.println("show all the views in the database:");
		displayMetadata(dbmd);
		
		//删掉视图view2
		stmt.executeUpdate(query2);

		//显示数据库元数据
		System.out.println("show all the views in the database after drop view2:");
		displayMetadata(dbmd);
		
		// 关闭结果集
		rs.close();

		// 关闭statement对象
		stmt.close();

		// 关闭连接
		con.close();
	}
	catch (SQLException ex) {
		System.out.println ("\n*** SQLException caught ***\n");
		while (ex != null) {
			System.out.println ("SQLState: " +	ex.getSQLState ());
			System.out.println ("Message:  " + ex.getMessage ());
			System.out.println ("Vendor:   " + ex.getErrorCode ());
			ex = ex.getNextException ();
			System.out.println ("");
		}
	}
	catch (java.lang.Exception ex) {

			// 捕获其他类型的例外
			ex.printStackTrace ();
	}
}
// dispResultSet显示整个结果届中的所有的行和列
private static void dispResultSet (ResultSet rs) throws SQLException
{
	int i;
	//取得结果集的元数据
	ResultSetMetaData rsmd = rs.getMetaData ();

	// 获得结果集的列数
	int numCols = rsmd.getColumnCount ();

	// 显示列的标题

	for (i=1; i<=numCols; i++) {
		if (i > 1) System.out.print(",     ");
		System.out.print(rsmd.getColumnLabel(i));
	}
	System.out.println("");
	
	//显示结果集中所有数据
	boolean more = rs.next ();
	while (more) {

	//循环显示结果集中的所有数据
	for (i=1; i<=numCols; i++) {
			if (i > 1) System.out.print(",  ");
			System.out.print(rs.getString(i));
		}
	System.out.println("");
	System.out.println("");
	//获得下一行数据
	more = rs.next ();
	}
}
//显示数据库元数据
private static void displayMetadata(DatabaseMetaData dbmd)
{
	String view[] = {"VIEW"};
	try
	{
		ResultSet rset =dbmd.getTables(null,null,null,view);
		//ResultSet rset =dbmd.getTables(null,null,"%"+"table",view);
	while(rset.next())
	{
		System.out.print(rset.getString("TABLE_NAME") + "   ");
	}
	System.out.println("");
	rset.close();
	}
	catch(Exception e)
	{
		System.out.println("error");
	}
}
}