Build a higher level JDBC object
It will be apparent from the example from the example. If we can encapsulate some of the methods we use in several higher level objects, it will be very helpful, we can not only pack
Try
Blocks, and can be accessed more simply
Resultset
method.
In this section, we will build a new RESULTSET object that encapsulates the JDBC ResultSet object and returns a row of data in the form of a String array. We found that you always need to get the serial number and name of the column from the ResultSetMetadata object, so new objects that create a packaging metadata are very reasonable.
In addition, we often need to extract the elements of a row by name or integer index. If you don't have to always include these access statements, it will be greatly helpful. The last point, if we need the content of the whole line, it is more convenient to return the entire line in the form of a string array. In the ResultSet object shown below, we are committed to implementing these goals:
Class ResultSet
{
// This class is the more advanced abstraction of the JDBC Resultset object
ResultSet RS;
ResultSetMetadata RSMD;
Int numcols;
Public ResultSet (ResultSet Rset)
{
RS = RSET;
Try
{
/ / Get metadata and column numbers simultaneously
RSMD = rs.getMetAdata ();
Numcols = rsmd.getcolumncount ();
}
Catch (Exception E)
{System.out.println ("ResultSet Error"
E.getMessage ());
}
//
Public string [] getMetadata ()
{
/ / Returns to contain all column names or other metadata
// An array
String md [] = new string [numcolls];
Try
{
For (int i = 1; i <= numcols; i )
MD [i-1] = rsmd.getColumnname (i);
}
Catch (Exception E)
{System.out.println ("Meta Data Error"
E.getMessage ());
Return MD;
}
//
Public Boolean HasmoreElements ()
{
Try {
Return rs.next ();
}
Catch (Exception E) {Return False;}
}
//
Public String [] nextElement ()
{
// Copy the contents of the row to the array of strings
String [] row = new string [numcols];
Try
{
For (int i = 1; i <= numcols; i )
ROW [i-1] = rs.getstring (i);
}
Catch (Exception E)
{System.out.println ("Next Element Error"
E.getMessage ());
Return row;
}
//
Public String getColumnValue (String ColumnName)
{
String res = "";
Try
{
Res = rs.getstring (columnname);
}
Catch (Exception E)
{System.out.println ("Column Value Error:" ColumnName E.getMessage ());}
Return res;
}
//
Public String GetColumnValue (INT i)
{
String res = "";
Try
{
RES = rs.getstring (i);
}
Catch (Exception E)
{System.Out.println ("Column Value Error:"
ColumnName E.getMessage ());
Return res;
}
//
Public void finalize ()
{
Try {r.close ();
Catch (Exception E)
{System.out.println (E.getMessage ());
}
}
By using simple use
New
Operator creates one on site
Resultset
Object, we can easily
Resultset
Object package in this class:
ResultSet Results = .. / / Resultsset is obtained according to the usual method
// Use it to create a more useful object
ResultSet RS = New ResultSet (RESULTS);
And it is easy to
JDBC
This object is used in the program.
Build a Database object
We along
00
Another part of the chain moved to the top of the effort is to create a
Database
Object, it will package the behavior of the following objects:
Connection
,
Statement
with
DatabaseMetadata
Object, and we have just built
SQL
Query and
Resultset
. our
Database
Object allows us to create a connection, get a table name, move in the database, and more simply obtain the values of rows and columns. Please note,
EXECUTE
Method returns one
Resultset
Object, you can do it directly.
Class Database
{
// This is a class that encapsulates all the features of the JDBC database in a single object.
CONNECTION CON;
ResultSet Results;
ResultSetMetadata RSMD;
DatabaseMetadata DMA;
String catalog;
String Types [];
Public Database (String Driver)
{
Types = new string [1];
Types [0] = "Tables"; // Initialization Type
Try {class.forname (driver);} // load JDBC-ODBC bridge driver
Catch (Exception E)
{System.out.println (E.getMessage ());
}
//
Public Void Open (String Url, String Cat)
{
Catalog = cat;
Try {con = DriverManager.getConnection (URL);
DMA = con.getMetadata (); // Get metadata
}
Catch (Exception E)
{System.out.println (E.getMessage ());
}
//
Public string [] gettablenames ()
{
String [] tbnames = null;
Vector tname = new vector ();
// Add a table name to a vector,
// Because we don't know how many tables Try {
Results =
New ResultSet (Dma.gettables (Catalog, Null,
"%", TYPES);
While (results.hasmorelements ())
TName.AddeElement (Results.getColumnValue ("Table_Name");
}
Catch (Exception E) {system.out.println (e);
/ / Copy the table name to a string array
TBNAMES = new string [tname.size ()];
For (int i = 0; i TBNAMES [I] = (String) TName.Elementat (i); Return TBNames; } // Public string [] getTableMetadata () { // Return to the table type information Results = NULL; Try { Results = New ResultSet (Dma.gettables (Catalog, Null, "%", TYPES); } Catch (Exception E) {System.out.println (E.getMessage ()); Return results.getMetAdata (); } // Public string [] getColumnMetadata (String Tablename) { // Return to a column data Results = NULL; Try { Results = New ResultSet (Dma.getColumn (Catalog, NULL, TableName, NULL); } Catch (Exception E) {System.out.println (E.getMessage ()); Return results.getMetAdata (); } // Public string [] getColumnNames (String Table) { / / Return a column name String [] tbnames = null; Vector tname = new vector (); Try { Results = New ResultSet (Dma.getColumn (Catalog, NULL, Table, NULL); While (results.hasmorelements ()) TName.AddeElement (Results.getColumnValue); } Catch (Exception E) {system.out.println (e); TBNAMES = new string [tname.size ()]; For (int i = 0; i TBNAMES [I] = (String) TName.Elementat (i); Return TBNames; } // Public String getColumnValue (String Table, String columnname) { / / Return to the value of the given column String res = NULL; Try { IF (Table.length ()> 0) Results = Execute ("SELECT" ColumnName "from" Table "ORDER BY" ColumnName); if (Results.haASMoreElements ()) Res = results.getColumnValue (ColumnName); } Catch (Exception E) {System.out.println ("Column Value Error" ColumnName E.getMessage ()); Return res; } // Public String getNextValue (String ColumnName) { // use stored ResultSet / / Return to the next value of the column String res = ""; Try { IF (Results.haASMoreElements ()) Res = results.getColumnValue (ColumnName); } Catch (Exception E) {System.out.println ("Next Value Error" ColumnName E.getMessage ()); Return res; } // Public ResultSet Execute (String SQL) { / / Execute a SQL query on this database Results = NULL; Try { Statement Stmt = con.createstatement (); Results = new resultSet (Stmt.executeQuery (SQL)); } Catch (Exception E) {System.out.println ("Execute Error" E.getMessage ()); Return Results; } } A visual database program In order to summarize the content of our learning, we write a simple GUI Program, it can display the table name, column name, and column content of the database. We will also include a text area where you can type it in it. SQL Inquire. in Companion CD-ROM Up / chapter20 In the subdirectory, you can find this program (called dbframe.java )Used Resultset with Database class. The display interface of the program is shown 3 Indicated. Figure 3: DBFrame.java program used to display data in a database connected with JDBC. In this program, the table name of the default database (grocery ".mdb) is displayed in the column on the left. When you click on one of the table names, the column name is displayed in the middle of the column. Finally, when you click on a row in the middle column, the content of the line is displayed in the column on the right. The key to this program is to receive the list selection, then clear and populate the correct list box: Public Void ItemStateChanged (ItemEvent E) { Object obj = E.GETSource (); IF (Obj == Tables) // put in column name Showcolumns (); IF (Obj == Column) // Put the contents of the column SHOWDATA (); } // Private void loadingList (list list, string [] s) { / / Clear and populate the specified list box List.removeall (); For (int i = 0; i List.add (s [i]); } // Private void showcolumns () { // Display column name String cnames [] = DB.GetColumnNames (Tables.getSelectedItem ()); LoadList (Column, CNames); } // Private void showdata () { String colname = columns.getSelectedItem (); String colval = DB.GetColumnValue (Tables.getSelectedItem (), ColName); Data.setVisible (false); Data.RemoveAll (); Data.setVisible (TRUE); Colval = DB.GetNextValue (Column.getSelectedItem ()); While (colval.length ()> 0) { Data.Add (Colval); Colval = DB.GetNextValue (Column.getSelectedItem ()); } } Execute query Display the text area at the bottom of the screen allows you to type anything SQL Inquire. One query built in the demo program is as follows: String querytext = "SELECT DISTINCTROW FOODNAME, STORENAME, PRICE" "From (Food Inner Join Foodprice ON" "Food.foodkey = foodprice.foodkey)" "Inner Join Stores on" "FoodPrice.StoreKey = Stores.StoreKey" "Where ((food.foodname) = / 'ORANGES /'))" "Order by foodprice.price;" This query simply lists the orange prices of each grocery store. When you click the Run Query button, it will perform this query and transfer the ResultSet object to a dialog for display: Public Void ActionPerformed (ActionEvent E) { Object obj = E.GETSource (); IF (Obj == quit) System.exit (0); IF (Obj == Search) ClickedSearch (); } // Private void clickedSearch () { ResultSet RS = db.execute (query.getText ()); String cnames [] = rs.getMetadata (); QueryDialog Q = New QueryDialog (this, RS); Q.Show (); } Query Results Dialog Query dialog Resultset Object, put each line into one String In arrays, then these String Array put into one Vector In this way Paint () These rows are quickly accessed during the subroutine run. Private void maketables () { // put each line into a string array and will // These strings are all in one vector. TABLES = New Vector (); String t [] = result.getMetadata (); TABLES.AddeElement (t); While (results.hasmorelements ()) Tables.addelement (results.nextelement ()); } We pass Graphics of Drawstring () Method Draw data in one Panel in. Just like PRINTER Like the object, we must track yourself x with y s position. Public void Paint (Graphics G) { String s. INT x = 0; / / Calculate the height of the font INT Y = g.getfontmetrics (). getHeight (); // Estimated column height INT DelTax = (int) 1.5F * (g.GetFontMetrics (). StringWidth ("wwwwwwwwwww"))); // Traversing table vector For (int i = 0; i { s = (String []) Tables.Elementat (i); / / Draw a row in the string array For (int J = 0; j { String st = s [j]; g.drawstring (ST, X, Y); X = deltax; // Move to the next column } X = 0; // Start a new line Y = g.getfontmetrics (). GetHeight (); // Extra space between column tags and column data IF (i == 0) y = g.getFontMetrics (). getHeight (); } } Built-in query QueryDialog Figure 4 Indicated. Figure 4: QueryDialog displayed in the dbframe program, which shows the result of the default query. Sample file Groceries.zip dbframe.zip JDBC-ODBC Bridge summary In this article, we discussed the database and the verification of the database and performing a query on the database. We have seen, JDBC We have also learned a kind of data-oriented method that is unrelated to platform and database. We have also learned JDBC The main object: Resultset , ResultSetmetadata with DatabaseMetadata. After writing a simple program with these objects, we have designed a higher level Resultset with Database Objects, we have built a simple visual interface to display database information. If you are familiar with the powerful features of the database, you will recognize that the SQL language allows you to perform more powerful tasks than we described here. For example, you can create a new table, add, change, or delete rows, columns, or individual tables of the table. With JDBC, all all this becomes universal and easy to handle. If you are using a specific platform's database driver, such as JDBC-ODBC Bridge, you will be restricted when writing applications, because Applets cannot be connected to this bridge running on another computer. Other client-server databases, such as the IB2 DB2, allows you to connect to JDBC in the applet.
