使用 JDBC 创建数据库对象

        SELECT DISTINCTROW Employees.Name, SalaryRanges.Min,
SalaryRanges.Max FROM Employees INNER JOIN SalaryRanges ON Employees.SalaryKey = SalaryRanges.SalaryKey
ORDER BY SalaryRanges.Min;

    
      

          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    
        

        String url = "jdbc:odbc:Grocery prices";
Connection con = DriverManager.getConnection(url);
    
      

        jdbc:
          subprotocol:
          subname
        
    
      

        jdbc:bark//doggie/elliott
    
      

        jdbc:bark//doggie/elliot;UID=GoodDog;PWD=woof
    
      

        //
        从元数据中获得列数
    
        ResultSetMetaData rsmd;
    rsmd = results.getMetaData();
    numCols = rsmd.getColumnCount();

    
      

        try
{
rsmd = results.getMetaData();
numCols = rsmd.getColumnCount();
boolean more = results.next();
while (more)
 {
 for (i = 1; i <= numCols; i++)
 System.out.print(results.getString(i)+" ");
 System.out.println();
 more = results.next();
 }
results.close();
}
catch(Exception e)
{System.out.println(e.getMessage());}
    
      

        results =
dma.getTables(catalog, schema, tablemask, types[]);
      

        con = DriverManager.getConnection(url);
//获取数据库的元数据
        dma =con.getMetaData();
//将数据库中的表的名称转储出来
        String[] types = new String[1];
types[0] = "TABLES"; //设置查询类型
        //请注意通配符是 
        % 符号（而不是
        “*”）
        results = dma.getTables(null, null, "%", types);
    
      

        boolean more = results.next();
while (more)
 {
 for (i = 1; i <= numCols; i++)
 System.out.print(results.getString(i)+" ");
 System.out.println();
 more = results.next();
 }
    
      

        String query = "SELECT FoodName FROM Food;";
ResultSet results;
try
 {
 Statement stmt = con.createStatement();
 results = stmt.executeQuery(query);
 }
 catch (Exception e)
 {System.out.println("query exception");}
    
      

        private void dumpResults(String head)
{
//
        这是打印列标头和每列的内容的

        //
        通用方法

        System.out.println(head);
 try
 {
 //
        从元数据中获取列数
 
        rsmd = results.getMetaData();
 numCols = rsmd.getColumnCount();
 //
        打印列名
 
        for (i = 1; i<= numCols; i++)
 System.out.print(rsmd.getColumnName(i)+" ");
 System.out.println();
 //
        打印列内容
 
        boolean more = results.next();
 while (more)
 {
 for (i = 1; i <= numCols; i++)
 System.out.print(results.getString(i)+" ";
 System.out.println();
 more = results.next();
 }
 }
catch(Exception e)
 {System.out.println(e.getMessage());}
}

    
      

        import java.net.URL;
import java.sql.*;
import java.util.*;
class JdbcOdbc_test
{
 ResultSet results;
 ResultSetMetaData rsmd;
 DatabaseMetaData dma;
 Connection con;
 int numCols, i;
//--
public JdbcOdbc_test()
{
String url = "jdbc:odbc:Grocery prices";
String query = "SELECT DISTINCTROW FoodName FROM Food " + "WHERE (FoodName like 'C%');";
try
 {
 //
        加载 JDBC-ODBC 桥驱动程序
 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 //连接数据库
 con = DriverManager.getConnection(url);
 //获取数据库的元数据
 dma =con.getMetaData();
 System.out.println("Connected to:"+dma.getURL());
 System.out.println("Driver "+dma.getDriverName());
//将数据库中的表的名称转储出来
 String[] types = new String[1];
 types[0] = "TABLES";
 //请注意通配符是 % 符号（而不是“*”）
 results = dma.getTables(null, null, "%", types);
 dumpResults("--Tables--");
 results.close();
 }
 catch (Exception e)
 {System.out.println(e);}
//获取表列的名称
System.out.println("--Column Names--");
try {
 results =
 dma.getColumns(null, null, "FoodPrice", null);
 ResultSetMetaData rsmd = results.getMetaData();
 int numCols = rsmd.getColumnCount();
 while (results.next() )
 String cname = results.getString("COLUMN_NAME");
 System.out.print(cname + " ");
 System.out.println();
 results.close();
 }
catch (Exception e)
 {System.out.println(e);}
//列出一个列的内容 -- 这是一个查询
try {
 Statement stmt = con.createStatement();
 results =
 stmt.executeQuery("SELECT FOODNAME FROM FOOD;");
 }
 catch (Exception e)
 {System.out.println("query exception");}
 dumpResults("--Contents of FoodName column--");
 //尝试实际的 SQL 语句
 try
 {
 Statement stmt = con.createStatement();
 results = stmt.executeQuery(query);
 }
 catch (Exception e)
 {System.out.println("query exception");}
 dumpResults("--Results of Query--");
}
    
      

        C:\Projects\objectJava\chapter19>java JdbcOdbc_test
Connected to:jdbc:odbc:Grocery prices
Driver JDBC-ODBC Bridge (ODBCJT32.DLL)
--Tables--
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS
groceries null Food TABLE null
groceries null FoodPrice TABLE null
groceries null Stores TABLE null
--Column Names--
FSKey StoreKey FoodKey Price
--Contents of FoodName column--
FOODNAME
Apples
Oranges
Hamburger
Butter
Milk
Cola
Green beans
--Results of Query--
FoodName
Cola
    
      

        class resultSet
{
//
        这个类是 
        JDBC ResultSet 
        对象的更高级抽象
 
        ResultSet rs;
 ResultSetMetaData rsmd;
 int numCols;
 public resultSet(ResultSet rset)
 {
 rs = rset;
 try
 {
 //
        同时获取元数据和列数
 
        rsmd = rs.getMetaData();
 numCols = rsmd.getColumnCount();
 }
 catch (Exception e)
 {System.out.println("resultset error"
 +e.getMessage());}
 }
 //--
 public String[] getMetaData()
 {
 //
        返回包含所有列名或其他元数据的
 
        //
        一个数组
 
        String md[] = new String[numCols];
 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()
 {
 //
        将行的内容复制到字符串数组中
 
        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{rs.close();}
 catch (Exception e)
 {System.out.println(e.getMessage());}
 }
}
    
      

        ResultSet results = .. //
        按通常的方法获得
        ResultsSet
//
        利用它创建一个更有用的对象

        resultSet rs = new resultSet(results);
    
      

        class Database
{
//
        这是一个将 
        JDBC 
        数据库的所有功能封装在单个对象中的类

        Connection con;
resultSet results;
ResultSetMetaData rsmd;
DatabaseMetaData dma;
String catalog;
String types[];
 public Database(String driver)
 {
 types = new String[1];
 types[0] = "TABLES"; //
        初始化类型
 
        try{Class.forName(driver);} //
        加载 
        JDBC-ODBC 
        桥驱动程序
 
        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(); //
        获取元数据
 
        }
 catch (Exception e)
 {System.out.println(e.getMessage());}
 }
 //--
 public String[] getTableNames()
 {
 String[] tbnames = null;
 Vector tname = new Vector();
 //
        将表名添加到一个 Vector 
        中，
 
        //
        因为我们不知道有多少个表
 
        try {
 results =
 new resultSet(dma.getTables(catalog, null,
 "%", types));
 while (results.hasMoreElements())
 tname.addElement(results.getColumnValue("TABLE_NAME"));
 }
 catch (Exception e) {System.out.println(e);}
 //
        将表名复制到一个 
        String 
        数组中
 
        tbnames = new String[tname.size()];
 for (int i=0; i< tname.size(); i++)
 tbnames[i] = (String)tname.elementAt(i);
 return tbnames;
 }
 //--
 public String[] getTableMetaData()
 {
 // 
        返回表类型的信息
 
        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)
 {
 //
        返回一个列的数据
 
        results = null;
 try {
 results =
 new resultSet(dma.getColumns(catalog, null,
 tablename, null));
 }
 catch (Exception e)
 {System.out.println(e.getMessage());}
 return results.getMetaData();
 }
 //--
 public String[] getColumnNames(String table)
 {
 //
        返回一个列名数组
 
        String[] tbnames = null;
 Vector tname = new Vector();
 try {
 results =
 new resultSet(dma.getColumns(catalog, null,
 table, null));
 while (results.hasMoreElements() )
 tname.addElement(results.getColumnValue("COLUMN_NAME"));
 }
 catch (Exception e) {System.out.println(e);}
 tbnames = new String[tname.size()];
 for (int i=0; i< tname.size(); i++)
 tbnames[i] = (String)tname.elementAt(i);
 return tbnames;
 }
//--
public String getColumnValue(String table,
 String columnName)
 {
 //
        返回给定列的值 
        String res = null;
 try
 {
 if (table.length()>0)
 results =
 Execute("Select " + columnName +
 " from " + table +
 " order by "+columnName);
 if (results.hasMoreElements())
 res = results.getColumnValue(columnName);
 }
 catch (Exception e)
 {System.out.println("Column value error" +
 columnName+ e.getMessage());}
 return res;
 }
//--
 public String getNextValue(String columnName)
 {
 // 
        使用存储的 
        resultSet
 //
        返回该列的下一个值
 
        String res = "";
 try
 {
 if (results.hasMoreElements())
 res = results.getColumnValue(columnName);
 }
 catch (Exception e)
 {System.out.println("next value error"+
 columnName+ e.getMessage());}
 return res;
}
//--
 public resultSet Execute(String sql)
 {
 //
        对此数据库执行一个 
        SQL 
        查询
 
        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;
 }
}
    
      

        public void itemStateChanged(ItemEvent e)
 {
 Object obj = e.getSource();
 if (obj == Tables) //
        放入列名
 
        showColumns();
 if (obj == Columns) //
        放入列的内容
 
        showData();
 }
//--
 private void loadList(List list, String[] s)
 {
 //
        清除并填充指定的列表框
 
        list.removeAll();
 for (int i=0; i< s.length; i++)
 list.add(s[i]);
 }
 //--
 private void showColumns()
 {
 //
        显示列名
 
        String cnames[] =
 db.getColumnNames(Tables.getSelectedItem());
 loadList(Columns, 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(Columns.getSelectedItem());
 while (colval.length()>0)
 {
 Data.add(colval);
 colval =
 db.getNextValue(Columns.getSelectedItem());
 }
 }
    
      

        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;";
    
      

        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();
 }
    
      

        private void makeTables()
{
//
        将每一行放入一个 
        String 
        数组中，并将

        //
        这些字符串数组全部放入一个 
        Vector 
        中
 
        tables = new Vector();
 String t[] = results.getMetaData();
 tables.addElement( t);
 while (results.hasMoreElements())
 tables.addElement(results.nextElement());
 }
    
      

        public void paint(Graphics g)
{
String s[];
int x=0;
//
        计算字体的高度

        int y =g.getFontMetrics().getHeight();
//
        估算列的高度

        int deltaX = (int)1.5f*
 (g.getFontMetrics().stringWidth("wwwwwwwwwwwwww"));
//
        遍历表矢量

        for (int i=0; i< tables.size(); i++)
 {
 s = (String[])tables.elementAt(i);
 //
        绘制字符串数组中的每一行
 
        for (int j =0; j< s.length; j++)
 {
 String st= s[j];
 g.drawString(st, x, y);
 x += deltaX; //
        移到下一列
 
        }
 x = 0; //
        开始一个新行
 
        y += g.getFontMetrics().getHeight();
 //
        列标签与列数据之间的额外空间
 
        if (i == 0) y += g.getFontMetrics().getHeight();
 }
}