Шаг 5: Изменение и вставка в ваш запрос


Вы заметите, что приведенный выше код на вид отличается от того, что используется в программе. Это происходит потому, что инструмент запроса использует полную квалификацию для всех имен, даже когда используется только одна таблица. (Когда привлечена более, чем одна таблица, квалификация предотвращает коллизию между колонками разных таблиц, которые имеют одно и то же имя.) Так как этот запрос вовлекает только одну таблицу, вы можете, по своему усмотрению, удалить квалификатор "people" из большинства имен, например так:

SELECT FIRST, LAST, EMAIL FROM people.csv people WHERE (LAST='Eckel') AND (EMAIL Is Not Null) ORDER BY FIRST

Кроме того, вы не хотите, чтобы это программа была жестко запрограммирована на поиск только одного имени. Вместо этого, она должна охотится за именем, заданным как аргумент командной строки. Сделав эти изменения и включив SQL выражение в динамически создаваемую строку, получим:

"SELECT FIRST, LAST, EMAIL " +
"FROM people.csv people " +
"WHERE " +
"(LAST='" + args[0] + "') " +
" AND (EMAIL Is Not Null) " +
"ORDER BY FIRST");

В SQL существует другой способ вставки имен в запрос, называемый хранимой процедурой, которая используется для увеличения скорости. Но для большинства ваших экспериментов с базами данных и для первой пробы, построение вашей собственной строки запроса в Java - это великолепно.

Вы можете видеть из этого примера, что при использовании инструментов, доступных в настоящее время - для определенных инструментов поддержки базы данных - программирование баз дынных с использованием SQL и JDBC может быть достаточно прямолинейным.

GUI версия программы поиска

Более полезно оставить программу поиска работающей все время, и просто переключаться в нее и печатать имя, которое вы хотите найти. Следующая программа создает программу поиска в виде приложения/апплета, она также добавляет завершение имен, так что данные будут показываться, не заставляя вас печатать полностью фамилию:

//: c15:jdbc:VLookup.java
// GUI version of Lookup.java.
// <applet code=VLookup
// width=500 height=200></applet>
// {Broken}
import javax.swing.*;

import java.awt.*;

import java.awt.event.*;

import javax.swing.event.*;

import java.sql.*;

import com.bruceeckel.swing.*;

public class VLookup extends JApplet {
   String dbUrl = "jdbc:odbc:people";
   String user = "";
   String password = "";
   Statement s;
   JTextField searchFor = new JTextField(20);
   JLabel completion = new JLabel("                         ");
   JTextArea results = new JTextArea(40, 20);
  
   public void init() {
      searchFor.getDocument().addDocumentListener(new SearchL());
      JPanel p = new JPanel();
      p.add(new Label("Last name to search for:"));
      p.add(searchFor);
      p.add(completion);
      Container cp = getContentPane();
      cp.add(p, BorderLayout.NORTH);
      cp.add(results, BorderLayout.CENTER);
      try {
         // Загружаем драйвер (регистрирует себя)
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         Connection c = DriverManager.getConnection(dbUrl, user, password);
         s = c.createStatement();
      }
      catch (Exception e) {
         results.setText(e.toString());
      }
   }
  
   class SearchL implements DocumentListener {
      public void changedUpdate(DocumentEvent e) {
      }
     
      public void insertUpdate(DocumentEvent e) {
         textValueChanged();
      }
     
      public void removeUpdate(DocumentEvent e) {
         textValueChanged();
      }
   }
  
   public void textValueChanged() {
      ResultSet r;
      if (searchFor.getText().length() == 0) {
         completion.setText("");
         results.setText("");
         return;
      }
      try {
         // Автозавершение:
         r = s.executeQuery("SELECT LAST FROM people.csv people "
               + "WHERE (LAST Like '" + searchFor.getText()
               + "%') ORDER BY LAST");
         if (r.next())
            completion.setText(r.getString("last"));
         r = s.executeQuery("SELECT FIRST, LAST, EMAIL "
               + "FROM people.csv people " + "WHERE (LAST='"
               + completion.getText() + "') AND (EMAIL Is Not Null) "
               + "ORDER BY FIRST");
      }
      catch (Exception e) {
         results.setText(searchFor.getText() + "n");
         results.append(e.toString());
         return;
      }
      results.setText("");
      try {
         while (r.next()) {
            results.append(r.getString("Last") + ", "
                  + r.getString("fIRST") + ": " + r.getString("EMAIL")
                  + "n");
         }
      }
      catch (Exception e) {
         results.setText(e.toString());
      }
   }
  
   public static void main(String[] args) {
      Console.run(new VLookup(), 500, 200);
   }
} // /:~

Большая часть логики работы с базой данных та же самая, но вы можете видеть, что добавлен DocumentListener для отслеживания JTextField (смотрите описание javax.swing.JTextField в HTML документации по Java на java.sun.com, чтобы получить больше информации), так что в то время, когда вы напечатаете новый символ, сначала будет совершена попытка выполнить автозавершение имени путем поиска фамилии в базе данных и отображения первой найденной. (Она помещается в JLabel завершения и используется как текст поиска.) Таким образом, как только вы напечатаете достаточное количество символов для программы для уникального поиска имени, которое вы ищите, вы можете остановиться.

Почему JDBC API выглядит таким сложным

Когда вы просмотрите документацию для JDBC, она может привести в уныние. Обычно в интерфейсте DatabaseMetaData - который просто громоздок, в противовес большинству интерфейсов, которые вы видели в Java - существуют методы, такие как dataDefinitionCausesTransactionCommit( ), getMaxColumnNameLength( ), getMaxStatementLength( ), storesMixedCaseQuotedIdentifiers( ), supportsANSI92IntermediateSQL( ), supportsLimitedOuterJoins( ) и так далее. Что можно сказать об этом?

Как упомянуто раньше, базы данных, по своей сущности, находятся в постоянном состоянии суматохи, прежде всего потому, что это требуется для приложений, работающих с базами данных, и поэтому инструменты работы с базами данных так великолепны. Только в последнее время были достигнуты некоторые договоренности в сторону общего языка SQL (и есть множество других языков работы с базами данных общего использования). Но даже со "стандартным" языком есть много вариаций на эту тему, поэтому JDBC должен предоставлять громоздкий интерфейс DatabaseMetaData, так что ваш код может обнаружить совместимость базы данных, с которой в настоящее время выполнено соединение, с определенным SQL "стандартом". Короче говоря, вы можете написать простой, переносимый SQL, но если вы хотите оптимизировать, скорость вашего кодирования значительно повысится, если вы постигните возможности базы данных определенного поставщика.

Конечно же, это не ошибка Java. Различия между продуктами баз данных является просто тем, что JDBC пробует помочь скомпенсировать. Но имейте в виду, что ваша жизнь будет облегчена, если вы можете либо писать общие запросы и не беспокоиться о производительности, либо, если вы должны настраивать производительности, необходимо знать платформу, для которой вы пишите, так что вам нет необходимости писать весь код для исследования.

Более сложный пример

Более интересный пример [3] вовлекает многотабличную базу данных, которая расположена на сервере. Здесь база данных подразумевает обеспечение ответственности за общую активность и за то, чтобы позволить людям назначать события, так что она называется Community Interests Database (CID). Этот пример предназначен только для обзора базы данных и ее реализации, и не предназначен для подробной инструкции по разработке базы данных. Есть много книг, семинаров и пакетов программного обеспечения, которые помогут вам в дизайне и разработке базы данных.

Кроме того, этот пример предполагает, что у вас уже есть установленная SQL база данных на сервере (хотя она может быть также запущена на локальной машине), а также предполагается, что вами был найден и исследован соответствующий JDBC драйвер для этой базы данных. Существует несколько бесплатных SQL баз данных, и некоторые из них даже устанавливаются автоматически с некоторыми разновидностями Linux. Вы ответственны за выбор базы данных и нахождение JDBC драйвера; приведенных здесь пример основывается на SQL базе данных, называемой "Cloudscape", которая может быть бесплатно скачена для разработки (не развертывания) по адресу http://www.Cloudscape.com. Вам необходимо следовать инструкции, чтобы правильно установить и настроить Cloudscape.

Чтобы сохранить простоту внесения изменений в информацию для соединения, драйвер базы данных, URL базы данных, имя пользователя и пароль помещаются в отдельном классе:

//: c15:jdbc:CIDConnect.java
// Информация для соединения с базой данных
// community interests database (CID).
public class CIDConnect {
   // Вся информация специфична для CloudScape:
   public static String dbDriver = "COM.cloudscape.core.JDBCDriver";
   public static String dbURL = "jdbc:cloudscape:d:/docs/_work/JSapienDB";
   public static String user = "";
   public static String password = "";
} // /:~

В этом примере нет пароля защиты для базы данных, так что имя пользователя и пароль являются пустыми строками. Для Cloudscape dbURL содержит путь к директории, в которой расположена база данных, но другие JDBC драйверы будут использовать другие способы для передачи этой информации. Этот пример предполагает, что база данных "JSapienDB" уже создана, но чтобы заставить базу данных работать, вам необходимо использовать инструмент cview, который поставляется вместе Cloudscape, чтобы создать новую базу, а затем вы должны изменить указанный выше dbURL в соответствии с путем, где вы создадите базу данных.

База данных состоит из набора таблиц, которые имеют вот такую структуру:

"Member" содержит общую информацию о членах, "Events" и "Locations" содержат информацию об активности и в каком месте она имела место, а "Evtmems" содержит события и членов, которые хотят следить за этими событиями. Вы можете видеть, что данные о членах в одной таблице являются ключами в другой таблицы.

Следующий класс содержит SQL строки, которые создадут эти таблицы базы данных (обратитесь к руководству по SQL, чтобы понять, что делает этот SQL код):

//: c15:jdbc:CIDSQL.java
// SQL строки для создания таблиц для CID.
public class CIDSQL {
   public static String[] sql = {
         // Создание таблицы MEMBERS:
         "drop table MEMBERS",
         "create table MEMBERS " + "(MEM_ID INTEGER primary key, "
               + "MEM_UNAME VARCHAR(12) not null unique, "
               + "MEM_LNAME VARCHAR(40), " + "MEM_FNAME VARCHAR(20), "
               + "ADDRESS VARCHAR(40), " + "CITY VARCHAR(20), "
               + "STATE CHAR(4), " + "ZIP CHAR(5), " + "PHONE CHAR(12), "
               + "EMAIL VARCHAR(30))",
         "create unique index " + "LNAME_IDX on MEMBERS(MEM_LNAME)",
         // Создание таблицы EVENTS
         "drop table EVENTS",
         "create table EVENTS " + "(EVT_ID INTEGER primary key,"
               + "EVT_TITLE VARCHAR(30) not null,"
               + "EVT_TYPE VARCHAR(20), " + "LOC_ID INTEGER, "
               + "PRICE DECIMAL, " + "DATETIME TIMESTAMP)",
         "create unique index " + "TITLE_IDX on EVENTS(EVT_TITLE)",
         // Создание таблицы EVTMEMS
         "drop table EVTMEMS",
         "create table EVTMEMS " + "(MEM_ID INTEGER not null,"
               + "EVT_ID INTEGER not null, " + "MEM_ORD INTEGER)",
         "create unique index " + "EVTMEM_IDX on EVTMEMS(MEM_ID, EVT_ID)",
         // Создание таблицы LOCATIONS
         "drop table LOCATIONS",
         "create table LOCATIONS " + "(LOC_ID INTEGER primary key,"
               + "LOC_NAME VARCHAR(30) not null,"
               + "CONTACT VARCHAR(50), " + "ADDRESS VARCHAR(40), "
               + "CITY VARCHAR(20), " + "STATE VARCHAR(4), "
               + "ZIP VARCHAR(5), " + "PHONE CHAR(12), "
               + "DIRECTIONS VARCHAR(4096))",
         "create unique index " + "NAME_IDX on LOCATIONS(LOC_NAME)", };
} // /:~

Следующая программа использует информацию CIDConnect и CIDSQL для загрузки JDBC драйвера, создания соединения с базой данных и для создания структуры таблиц в соответствии с приведенной выше диаграммой. Для соединения с базой данных вы вызываете статический метод DriverManager.getConnection( ), передаете ему URL базы данных, имя пользователя и пароль, чтобы войти в базу данных. Назад вы получаете объект Connection, который вы можете использовать для запросов и манипуляции с базой данных. Как только соединение будет установлено, вы можете просто передать SQL в базу данных, в данном случае путем передачи через массив CIDSQL. Однако, когда мы запустим эту программу в первый раз, команда "dtop table" не сработает, а станет причиной исключения, которое будет перехвачено, отрапортовано, а затем проигнорировано. Причина использования команды "drop table" состоит в том, что с ней легко экспериментировать: вы можете изменить SQL, затем определить таблицу, а затем запустить программу, которая заменит старые таблицы новыми.

В этом примере имеет смысл выбрасывать исключения на консоль:

//: c15:jdbc:CIDCreateTables.java
// Создание таблиц базы данных для
// community interests database.
// {Broken}
import java.sql.*;

public class CIDCreateTables {
   public static void main(String[] args) throws SQLException,
         ClassNotFoundException, IllegalAccessException {
      // Загружаем драйвер (регистрирует себя)
      Class.forName(CIDConnect.dbDriver);
      Connection c = DriverManager.getConnection(CIDConnect.dbURL,
            CIDConnect.user, CIDConnect.password);
      Statement s = c.createStatement();
      for (int i = 0; i < CIDSQL.sql.length; i++) {
         System.out.println(CIDSQL.sql[i]);
         try {
            s.executeUpdate(CIDSQL.sql[i]);
         }
         catch (SQLException sqlEx) {
            System.err.println("Probably a 'drop table' failed");
         }
      }
      s.close();
      c.close();
   }
} // /:~

Обратите внимание, что все изменения в базе данных могут быть внесены путем изменения строк в таблице CIDSQL без изменения CIDCreateTables.

executeUpdate( ) обычно будет возвращать количество строк, которые обрабатываются SQL выражением. executeUpdate( ) чаще всего используется для выполнения инструкций INSERT, UPDATE или DELETE, которые изменяют одну или несколько строк. Для таких инструкций, как CREATE TABLE, DROP TABLE и CREATE INDEX метод executeUpdate( ) всегда вернет ноль.

Для тестирования базы данных она загружается некоторыми простыми данными. Для этого необходимо выполнить серию INSERT'ов, за которыми следуют SELECT'ы, производящие результирующее множество. Чтобы сделать добавление и изменение тестовых данных легче, тестовые данные организованы в виде двумерного массива элементов типа Object, а метод executeInsert( ) может затем использовать информацию из одной строки таблицы для создания соответствующей SQL команды.

//: c15:jdbc:LoadDB.java
// Загрузка и тестирование базы данных.
// {Broken}
import java.sql.*;

class TestSet {
   Object[][] data = {
         { "MEMBERS", new Integer(1), "dbartlett", "Bartlett", "David",
               "123 Mockingbird Lane", "Gettysburg", "PA", "19312",
               "123.456.7890", "bart@you.net" },
         { "MEMBERS", new Integer(2), "beckel", "Eckel", "Bruce",
               "123 Over Rainbow Lane", "Crested Butte", "CO", "81224",
               "123.456.7890", "beckel@you.net" },
         { "MEMBERS", new Integer(3), "rcastaneda", "Castaneda", "Robert",
               "123 Downunder Lane", "Sydney", "NSW", "12345",
               "123.456.7890", "rcastaneda@you.net" },
         { "LOCATIONS", new Integer(1), "Center for Arts", "Betty Wright",
               "123 Elk Ave.", "Crested Butte", "CO", "81224",
               "123.456.7890", "Go this way then that." },
         { "LOCATIONS", new Integer(2), "Witts End Conference Center",
               "John Wittig", "123 Music Drive", "Zoneville", "PA",
               "19123", "123.456.7890", "Go that way then this." },
         { "EVENTS", new Integer(1), "Project Management Myths",
               "Software Development", new Integer(1), new Float(2.50),
               "2000-07-17 19:30:00" },
         { "EVENTS", new Integer(2), "Life of the Crested Dog",
               "Archeology", new Integer(2), new Float(0.00),
               "2000-07-19 19:00:00" },
         // Match some people with events
         { "EVTMEMS", new Integer(1), // Dave is going to
               new Integer(1), // the Software event.
               new Integer(0) }, { "EVTMEMS", new Integer(2), // Bruce is
                                                   // going to
               new Integer(2), // the Archeology event.
               new Integer(0) }, { "EVTMEMS", new Integer(3), // Robert is
                                                   // going to
               new Integer(1), // the Software event.
               new Integer(1) }, { "EVTMEMS", new Integer(3), // ... and
               new Integer(2), // the Archeology event.
               new Integer(1) }, };
  
   // Используем набор данных по умолчанию:
   public TestSet() {
   }
  
   // Используем другой набор данных:
   public TestSet(Object[][] dat) {
      data = dat;
   }
}

public class LoadDB {
   Statement statement;
   Connection connection;
   TestSet tset;
  
   public LoadDB(TestSet t) throws SQLException {
      tset = t;
      try {
         // Загрузка драйвера (регистрирует себя)
         Class.forName(CIDConnect.dbDriver);
      }
      catch (java.lang.ClassNotFoundException e) {
         e.printStackTrace(System.err);
      }
      connection = DriverManager.getConnection(CIDConnect.dbURL,
            CIDConnect.user, CIDConnect.password);
      statement = connection.createStatement();
   }
  
   public void dispose() throws SQLException {
      statement.close();
      connection.close();
   }
  
   public void executeInsert(Object[] data) {
      String sql = "insert into " + data[0] + " values(";
      for (int i = 1; i < data.length; i++) {
         if (data[i] instanceof String)
            sql += "'" + data[i] + "'";
         else
            sql += data[i];
         if (i < data.length - 1)
            sql += ", ";
      }
      sql += ')';
      System.out.println(sql);
      try {
         statement.executeUpdate(sql);
      }
      catch (SQLException sqlEx) {
         System.err.println("Insert failed.");
         while (sqlEx != null) {
            System.err.println(sqlEx.toString());
            sqlEx = sqlEx.getNextException();
         }
      }
   }
  
   public void load() {
      for (int i = 0; i < tset.data.length; i++)
         executeInsert(tset.data[i]);
   }
  
   // Выбрасывается исключение на консоль:
   public static void main(String[] args) throws SQLException {
      LoadDB db = new LoadDB(new TestSet());
      db.load();
      try {
         // Получаем ResultSet из загруженной базы данных:
         ResultSet rs = db.statement.executeQuery("select "
               + "e.EVT_TITLE, m.MEM_LNAME, m.MEM_FNAME "
               + "from EVENTS e, MEMBERS m, EVTMEMS em "
               + "where em.EVT_ID = 2 " + "and e.EVT_ID = em.EVT_ID "
               + "and m.MEM_ID = em.MEM_ID");
         while (rs.next())
            System.out.println(rs.getString(1) + " " + rs.getString(2)
                  + ", " + rs.getString(3));
      }
      finally {
         db.dispose();
      }
   }
} // /:~

Класс TestSet содержит набор данных по умолчанию, который будет воспроизводиться, если вы используете конструктор по умолчанию; однако, вы можете также создать объект TestSet, используя альтернативный набор данных, с помощью второго конструктора. Набор данных располагается в двумерном массиве типа Object, поскольку данные могут быть любого типа, включая String или числовые типы. Метод executeInsert( ) использует RTTI для того, чтобы различать данные типа String (которые должны быть в кавычках) и не строковые данные, которые встраиваются в SQL команды. После распечатки этих не строковых данных на консоль, используется executeUpdate( ) для отправки в базу данных.

Конструктор для LoadDB создает соединение, а load( ) перебирает данные и вызывает метод executeInsert( ) для каждой записи. dispose( ) закрывает выражение и соединение; чтобы гарантировать этот вызов, он помещен внутри выражения finally.

Как только база данных будет загружена, выражения в executeQuery( ) производят простое результирующее множество. Так как запрос комбинирует несколько таблиц, он является примером объединения.

Есть гораздо более обширная информация, доступная в электронной документации, которая поставляется, как часть дистрибутива Java от Sun. Кроме того, вы можете найти больше информации в книге "JDBC Database Access with Java" (Hamilton, Cattel, and Fisher, Addison-Wesley, 1997). Другие книги по JDBC появляются регулярно.

← Шаг 4: Генерация вашего SQL запроса Упражнения →