Программирование на Java часть 2

NOT NULL. Давайте напишем запрос, используя в качестве аргумента столбец id_topic:

SELECT COUNT(id_topic) FROM posts;

Итак, в наших темах имеется 4 сообщения. Но что, если мы хотим узнать сколько сообщений имеется в каждой теме. Для этого нам понадобится сгруппировать наши сообщения по темам и вычислить для каждой группы количество сообщений. Для группировки в SQL используется оператор GROUP BY. Наш запрос теперь будет выглядеть так:

SELECT id_topic, COUNT(id_topic) FROM posts GROUP BY id_topic;

Оператор GROUP BY указывает СУБД сгруппировать данные по столбцу id_topic (т.е. каждая тема - отдельная группа) и для каждой группы подсчитать количество строк:

Ну вот, в теме с id=1 у нас 3 сообщения, а с id=4 - одно. Кстати, если бы в поле id_topic были возможны отсутствия значений, то такие строки были бы объединены в отдельную группу со значением NULL. Предположим, что нас интересуют только те группы, в которых больше двух сообщений. В обычном запросе мы указали бы условие с помощью оператора WHERE, но этот оператор умеет работать только со строками, а для групп те же функции выполняет оператор HAVING:

SELECT id_topic, COUNT(id_topic) FROM posts GROUP BY id_topic HAVING COUNT(id_topic) > 2;

В результате имеем:

41

Чуть выше мы рассматривали, какие условия можно задавать оператором WHERE, те же условия можно задавать и оператором HAVING, только надо запомнить, что WHERE фильтрует строки, а HAVING - группы.

Тема 1.10. Редактирование, обновление и удаление данных

Редактирование, обновление и удаление данных Предположим, мы решили, что нашему форуму нужны модераторы. Для этого в таблицу users надо добавить столбец с ролью пользователя. Для добавления столбцов в таблицу используется оператор ALTER TABLE - ADD COLUMN. Его синтаксис следующий:

ALTER TABLE имя_таблицы

ADD COLUMN имя_столбца тип;

Давайте добавим столбец role в таблицу users:

ALTER TABLE users ADD COLUMN role varchar(20);

Столбец появился в конце таблицы:

Для того, чтобы указать местоположение столбца используются ключевые слова: FIRST - новый столбец будет первым, и AFTER - указывает после какого столбца поместить новый. Давайте добавим еще два столбца: один - kol - количество оставленных сообщений, а другой - rating - рейтинг пользователя. Оба столбца вставим после поля password:

42

Теперь надо назначить роль модератора какому-нибудь пользователю, пусть это будет sergey с id=1. Для обновления уже существующих данных служит оператор UPDATE. Его синтаксис следующий:

UPDATE имя_таблицы

SET имя_столбца=значение_столбца WHERE условие;

Давайте сделаем Сергея модератором:

UPDATE users SET role='модератор' WHERE id_user=1;

Изменять данные можно и сразу в нескольких строках и во всей таблице. Например, мы решили давать рейтинг в зависимости от количества оставленных пользователем сообщений. Давайте в нашу таблицу сначала внесем значения столбца kol так, как мы уже умеем:

43

А теперь давайте зададим рейтинг Профи тем, у кого количество сообщений больше 30:

UPDATE users SET rating='Профи' WHERE kol>30;

Данные изменились в двух строках, согласно заданному условию. Понятно, что если в запросе опустить условие, то данные будут обновлены во всех строках таблицы. Предположим, что нам не нравится название Рейтинг у нашего столбца, и мы хотим переименовать столбец в Репутация - reputation. Для изменения имени существующего столбца используется оператор CHANGE. Его синтаксис следующий:

44

ALTER TABLE имя_таблицы

CHANGE старое_имя_столбца новое_имя_столбца тип;

Давайте поменяем rating на reputation:

ALTER TABLE users CHANGE rating reputation varchar(20);

Обратите внимание, что тип столбца надо указывать даже, если он не меняется. Кстати, если нам понадобится изменить только тип столбца, то мы будем использовать оператор MODIFY. Его синтаксис следующий:

ALTER TABLE имя_таблицы

MODIFY имя_столбца новый_тип;

Последнее, что мы рассмотрим - оператор DELETE, который позволяет удалять строки из таблицы. Его синтаксис следующий:

DELETE FROM имя_таблицы

WHERE условие;

Давайте из таблицы сообщений удалим те записи, которые оставлял пользователь valera (id=2):

DELETE FROM posts WHERE id_author='2';

Понятно, если опустить условие, то из таблицы будут удалены все данные. Следует помнить, что данные СУБД даст удалить только в том

45

случае, если они не являются внешними ключами для данных из других таблиц (поддержка целостности БД). Например, если мы захотим удалить из таблицы users пользователя, который оставлял сообщения, то нам это не удастся. Сначала надо удалить его сообщения, а уж потом и его самого.

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

46

ГЛАВА 2 JDBC (JAVA DATABASE CONNECTIVITY)

Тема 2.1 Драйверы, соединения и запросы

JDBC (Java DataBase Connectivity) – стандартный прикладной интерфейс

(API) языка Java для организации взаимодействия между приложением и СУБД. Это взаимодействие осуществляется с помощью драйверов JDBC, обеспечивающих реализацию общих интерфейсов для конкретных СУБД и конкретных протоколов.

JDBC предоставляет интерфейс для разработчиков, использующих различные СУБД. С помощью JDBC отсылаются SQL-запросы только к реляционным базам данных (БД), для которых существуют драйверы, знающие способ общения с реальным сервером базы данных.

Строго говоря, JDBC не имеет прямого отношения к J2EE, но так как взаимодействие с СУБД является неотъемлемой частью Web-приложений, то эта технология рассматривается в данном контексте.

Последовательность действий:

1.Загрузка класса драйвера базы данных при отсутствии экземпляра этого класса.

Например:

String driverName = "com.mysql.jdbc.Driver";

для СУБД MySQL,

String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";

для СУБД MSAccess или

String driverName = "org.postgreesql.Driver";

для СУБД PostgreeSQL.

После этого выполняется собственно загрузка драйвера в память:

Class.forName(driverName);

и становится возможным соединение с СУБД.

Эти же действия можно выполнить, импортируя библиотеку и создавая объект явно.

2.Установка соединения с БД.

Для установки соединения с БД вызывается статический метод getConnection() класса DriverManager. В качестве параметров методу передаются URL базы данных, логин пользователя БД и пароль доступа. Метод возвращает объект Connection. URL базы данных, состоящий из типа и адреса физического расположения БД, может создаваться в виде отдельной строки или извлекаться из файла ресурсов.

Connection cn = DriverManager.getConnection( "jdbc:mysql://localhost/my_db" , "root", "pass");

В результате будет возвращен объект Connection и будет одно установленное соединение с БД my_db. Класс DriverManager предоставляет средства для управления набором драйверов баз данных. С помощью метода registerDriver() драйверы регистрируются, а методом getDrivers() можно получить список всех драйверов.

47

3. Создание объекта для передачи запросов.

После создания объекта Connection и установки соединения можно начинать работу с БД с помощью операторов SQL. Для выполнения запросов применяется объект Statement, создаваемый вызовом метода createStatement()

класса Connection.

Statement st = cn.createStatement();

Объект класса Statement используется для выполнения SQL-запроса без его предварительной подготовки. Могут применяться также объекты класса PreparedStatement для выполнения подготовленных запросов. Созданные объекты можно использовать для выполнения запроса SQL, передавая его в один из методов executeQuery (String sql) или executeUpdate(String sql).

4. Выполнение запроса.

Результаты выполнения запроса помещаются в объект ResultSet:

ResultSet rs = st.executeQuery("SELECT * FROM my_table");

Для добавления, удаления или изменения информации в таблице вместо метода executeQuery() запрос помещается в метод executeUpdate().

5. Обработка результатов выполнения запроса производится методами интерфейса ResultSet, где самыми распространенными являются next() и getString(int pos) а также аналогичные методы, начинающиеся с getТип(int pos) (getInt(int pos), getFloat(int pos) и др.) и updateТип(). Эффективным способом извлечения значения поля из таблицы ответа является обращение к этому полю по его позиции в строке.

При первом вызове метода next() указатель перемещается на таблицу результатов выборки в позицию первой строки таблицы ответа. Когда строки закончатся, метод возвратит значение false.

6. Закрытие соединения cn.close();

После того как база больше не нужна, соединение закрывается.

Для того чтобы правильно пользоваться приведенными методами, программисту требуется знать типы полей БД. В распределенных системах это знание предполагается изначально.

СУБД MySQL совместима c JDBC и будет применяться для создания экспериментальных БД. Последняя версия CУБД может быть загружена с сайта www.mysql.com. Для корректной установки необходимо следовать инструкциям мастера установки. Каталог лучше выбирать по умолчанию. В процессе установки следует создать администратора СУБД с именем root и паролем pass. Если планируется разворачивать реально работающее приложение, необходимо исключить тривиальных пользователей сервера БД (иначе злоумышленники могут получить полный доступ к БД).

Дополнительно требуется подключить библиотеку, содержащую драйвер MySQL

mysql-connector-java-3.1.12.jar

Тема 2.2 Простое соединение и простой запрос

48

Теперь следует воспользоваться всеми предыдущими инструкциями и создать простое соединение с БД.

Листинг 2.1 import java.sql.*; public class Main {

public static void main(String[] args) { Connection cn = null;

Statement st = null; try {

Class.forName("com.mysql.jdbc.Driver"); try {

cn = DriverManager.getConnection( "jdbc:mysql://localhost/my_db", "root", "");

try {

st = cn.createStatement();

st.executeUpdate("CREATE TABLE user (id INT(3) PRIMARY KEY, name VARCHAR(20) )");

st.executeUpdate("INSERT INTO user VALUES (1,'user1')"); st.close();

}catch (SQLException ex) { System.out.println("Error in Statement " + ex);

}

cn.close();

} catch (SQLException ex) {

System.out.println("Error in create Connection " + ex);

}

} catch (ClassNotFoundException ex) { System.out.println("Error in download Driver " + ex);

}

}

}

В результате выполнения данной программы в базе данных my_db(должна быть создана на сервере заранее) будет создана таблица test и в нее внесены данные. В данном примере используется вложенная система блоков try-catch, что позволяет остановить выполнение, как только произошла какая-то ошибка. В случае большого количества запросов это не очень удобно. В листинге 2.2 приведен класс позволяющий более удачно работать с базой данных.

Листинг 2.2 import java.sql.*; public class DB {

private Connection cn; private Statement st;

public DB(String path, String nameDB, String login, String pass) {

49

try { Class.forName("com.mysql.jdbc.Driver"); try {

cn = DriverManager.getConnection(path + nameDB, login, pass); try {

st = cn.createStatement(); } catch (SQLException ex) {

System.out.println("Error in Statement " + ex);

}

} catch (SQLException ex) {

System.out.println("Error in create Connection " + ex);

}

} catch (ClassNotFoundException ex) { System.out.println("Error in download Driver " + ex);

}

}

public void update(String sql) { try {

st.executeUpdate(sql);

} catch (SQLException ex) { System.out.println("Error in update " + ex);

}

}

public void close() { try {

st.close();

cn.close();

} catch (SQLException ex) { System.out.println("Error in close " + ex);

}

}

}

public class Main {

public static void main(String[] args) {

DB db = new DB("jdbc:mysql://localhost/", "my_db", "root", ""); db.update("CREATE TABLE user (id INT(3) PRIMARY KEY, name

VARCHAR(20) )");

db.update("INSERT INTO user VALUES (1,'user1')"); db.update("INSERT INTO user VALUES (2,'user2')"); db.update("INSERT INTO user VALUES (3,'user3')"); db.close();

}

}

В результате выполнения данной программы будет создана такая же таблица, как и в листинге 2.1 и в нее занесены данные.

50