Kodomo

Пользователь

Учебная страница курса биоинформатики,
год поступления 2010

Изменение и добавление данных в MySQL. Работа с MySQL из Java.

В качестве примера на этом занятии мы будем работать с базой данных, являющейся чем-то вроде записной книжки. Обращаясь к ней из Java можно будет сделать интерактивную записную книжку, в которую можно будет добавлять и извлекать записи через какой-нибудь интерфейс (например, графический). Основная сущность в этой базе данных будет человек - Person, про которого есть разнообразная информация (имя, ДР, телефоны, эл.адреса, интересы...). Всю эту информацию можно будет редактировать и просматривать, а также осуществлять поиск человека по какой-то информации о нем.

Структура базы будет примерно такая.

MySQL

Основные типы данных

Основные действия с данными

Ключи, индексы и атрибуты

  1. INDEX (синоним - KEY) используется для ускорения выполнения операций SELECT по проиндексированным полям. В MySQL могут быть проиндексированы столбцы всех типов. Может быть несколько столбцов в индексе. Для столбцов типов CHAR и VARCHAR можно индексировать префикс столбца.

  2. UNIQUE [INDEX] - индекс, для которого вводится ограничение на уникальность для значений полей, перечисленных в индексе. После этого СУБД будет следить за тем, чтобы не появилось двух записей, для которых в этих полях стоит одинаковый набор значений.

  3. Первичный ключ таблицы (PRIMARY KEY) - особый тип уникального индекса - может быть только один в таблице (но при этом включать несколько полей). Используется для однозначной организации связи между таблицами и ускорения поиска.

  4. AUTO_INCREMENT (атрибут для целочисленных столбцов). При записи величины NULL (рекомендуется) или 0 в этот столбец, автоматически создается значение на 1 больше предыдущего. В таблице может быть только один столбец AUTO_INCREMENT, и он должен быть индексирован (как правило это PRIMARY KEY).

  5. Атрибут NOT NULL. Если в ячейке значения нет, то там находится специальное значение NULL. (Или то, что вы указали в DEFAULT). Ограничение NOT NULL вводится для поля на этапе создания таблицы. После этого СУБД следит за тем, чтобы для каждой записи в данном поле присутствовало какое-то ненулевое значение.

  6. FOREIGN KEY - внешний ключ (может быть несколько в таблице). Служит для того, чтобы обозначить связь между полем-ссылкой данной таблицы и первичным ключом другой таблицы. Задавая внешний ключ, мы возлагаем на СУБД заботу о проверке целостности связи. Т.е. если мы пытаемся добавить запись в таблицу с внешним ключом, и эта запись ссылается на несуществующую запись в таблице, на которую мы ссылаемся, то будет выдано сообщение об ошибке. Также СУБД должна сообщить об ошибке, если попытаться удалить запись из одной таблицы, на которую ссылаются некоторые записи из таблицы с внешним ключом. Для использования этой опции необходимо использовать архитектуру InnoDB, причем обе таблицы должны быть InnoDB-типа; обязательно также наличие индекса, в котором внешний ключ и ссылочный ключ должны находиться в первых столбцах (индексы не создаются автоматически: их создание требуется задавать явно).

Создание таблицы

Пример создания таблицы person: 

CREATE TABLE person
(person_id INT NOT NULL AUTO_INCREMENT,
        nick VARCHAR(50) NOT NULL,
        surname VARCHAR(50),
        name VARCHAR(50),
        second_name VARCHAR(50),
        birthday DATE,
        PRIMARY KEY (person_id),
        UNIQUE (nick),
        INDEX (surname)
)DEFAULT CHARSET=utf8; // для поддержки русского языка, но места занимает больше (+ не факт, что все сработает)

с внешним ключом: 

CREATE TABLE email (
person_id  INT NOT NULL,
email VARCHAR(50) NOT NULL,
FOREIGN KEY (person_id) REFERENCES person (person_id),
UNIQUE(person_id,email)
INDEX (email)
)DEFAULT CHARSET=utf8;

Синтаксис определения FOREIGN KEY, задающий действия при попытках удаления/изменения данных, приводящих к нарушению целостности данных: 

...FOREIGN KEY (...)  REFERENCES  ... (...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

Добавление индексов

Пример: 

CREATE INDEX birthday ON person (birthday);

Синтаксис: 

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_type]

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH | RTREE}

Просмотр индексов таблицы: 

SHOW INDEX FROM person;

Добавление записей

INSERT INTO person (nick, surname, name, birthday)
VALUES ('musya', 'Иванова','Маша','1998-10-10');

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

Изменение записей

UPDATE person 
SET  nick= 'mila'
WHERE nick = 'musya';

или 

UPDATE person 
SET  second_name = 'Владимировна'
WHERE nick = 'mila';

Удаление записей

DELETE FROM  person
WHERE person_id = 2;

Изменение структуры таблицы

ALTER TABLE ADD/RENAME/DROP/CHANGE COLUMN/INDEX/PRIMARY KEY...

Работа с MySQL из Java

Для того, чтобы подключиться к СУБД нам нужно следующее:

Для открытия соединения нужно выполнить следующий код: 

   1 import java.sql.*;
   2 …
   3 try{
   4   Class.forName("org.gjt.mm.mysql.Driver");
   5 }catch(Exception ex) {
   6   ex.printStackTrace();
   7 }
   8 try {
   9   String url = "jdbc:mysql://db.bioinf.fbb.msu.ru:3306/student3_db";
  10   Connection conn = DriverManager.getConnection(url,"student","");
  11   Statement stmt = conn.createStatement();
  12   …  // что-то делаем с базой
  13   stmt.close();
  14   conn.close();
  15 }catch(SQLException ex) {
  16   …
  17 }

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

   1   …
   2   ResultSet rs = stmt.executeQuery("SELECT person_id, name FROM person");
   3   while(rs.next()) {
   4     int id = rs.getInt(1);// доступ к полю по индексу
   5     String name = rs.getString("name"); // доступ к полю по имени
   6     System.out.println("id="+id+", name="+name);
   7   }
   8   rs.close();
   9   …

Метод rs.next() устанавливает указатель текущей записи на следующую запись. В самом начале, сразу после того, как мы получили объект ResultSet, его указатель стоит перед первой записью. Так что первый вызов rs.next() поставит указатель на первую запись.

Методы rs.get<тип>(<номер>/<имя>) возвращают значения, находящиеся в ячейках текущей записи. Тип бывает Int, Double, String, и т.д. Номер ячейки нумеруется с единицы.

Если команда не должна возвращать данных (например, команда INSERT), то нам нужно использовать метод класса Statement execute(<запрос>).

Если нужно узнать, какой AUTO_INCREMENT ID только что вставился (при выполнении вставки нулевого значения в поле AUTO_INCREMENT) можно использовать метод getGeneratedKeys(): 

   1 resultSet = st.getGeneratedKeys(); 
   2 if (resultSet.next()) 
   3 { 
   4     newid = resultSet.getInt(1); 
   5 }

Очень важно не забывать закрывать соединение (connection) и statement, когда они больше не нужны: 

  stmt.close();
  conn.close();

И нужно иметь в виду, что у одного объекта Statement может быть только один открытый ResultSet: при совершении следующего запроса у данного объекта Statement, предыдущий ResultSet закрывается. В этом случае выпадает ошибка: 

   1 java.sql.SQLException: Operation not allowed after ResultSet closed