- Шаг 3. Подтверждение концепции: подключение к SQL с помощью Java
- Шаг 1. Подключение
- Шаг 2. Выполнение запроса
- Шаг 3. Вставка строки
- Часть 1. Введение в SQL
- Виды СУБД
- SQL
- JDBC
- Первая программа
- Разбор кода
- Блок констант:
- Обработка ошибок:
- Логика работы:
- Соединение базы данных в java
- Возможные проблемы с часовыми поясами и SSL
- Файлы конфигурации
Шаг 3. Подтверждение концепции: подключение к SQL с помощью Java
Этот пример следует рассматривать только как подтверждение концепции. Пример кода упрощен для ясности и для него не гарантируется соблюдение рекомендаций корпорации Майкрософт.
Шаг 1. Подключение
Используйте класс подключения для подключения к базе данных SQL.
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class SQLDatabaseConnection < // Connect to your database. // Replace server name, username, and password with your credentials public static void main(String[] args) < String connectionUrl = "jdbc:sqlserver://yourserver.database.windows.net:1433;" + "database=AdventureWorks;" + "user=yourusername@yourserver;" + "password=yourpassword;" + "encrypt=true;" + "trustServerCertificate=false;" + "loginTimeout=30;"; try (Connection connection = DriverManager.getConnection(connectionUrl);) < // Code here. >// Handle any errors that may have occurred. catch (SQLException e) < e.printStackTrace(); >> >
Шаг 2. Выполнение запроса
В этом примере следует подключиться к базе данных SQL Azure, выполнить инструкцию SELECT и вернуть выбранные строки.
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class SQLDatabaseConnection < // Connect to your database. // Replace server name, username, and password with your credentials public static void main(String[] args) < String connectionUrl = "jdbc:sqlserver://yourserver.database.windows.net:1433;" + "database=AdventureWorks;" + "user=yourusername@yourserver;" + "password=yourpassword;" + "encrypt=true;" + "trustServerCertificate=false;" + "loginTimeout=30;"; ResultSet resultSet = null; try (Connection connection = DriverManager.getConnection(connectionUrl); Statement statement = connection.createStatement();) < // Create and execute a SELECT SQL statement. String selectSql = "SELECT TOP 10 Title, FirstName, LastName from SalesLT.Customer"; resultSet = statement.executeQuery(selectSql); // Print results from select statement while (resultSet.next()) < System.out.println(resultSet.getString(2) + " " + resultSet.getString(3)); >> catch (SQLException e) < e.printStackTrace(); >> >
Шаг 3. Вставка строки
В этом примере следует выполнить инструкцию INSERT, передать параметры и извлечь автоматически созданное значение первичного ключа.
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class SQLDatabaseConnection < // Connect to your database. // Replace server name, username, and password with your credentials public static void main(String[] args) < String connectionUrl = "jdbc:sqlserver://yourserver.database.windows.net:1433;" + "database=AdventureWorks;" + "user=yourusername@yourserver;" + "password=yourpassword;" + "encrypt=true;" + "trustServerCertificate=false;" + "loginTimeout=30;"; String insertSql = "INSERT INTO SalesLT.Product (Name, ProductNumber, Color, StandardCost, ListPrice, SellStartDate) VALUES " + "('NewBike', 'BikeNew', 'Blue', 50, 120, '2016-01-01');"; ResultSet resultSet = null; try (Connection connection = DriverManager.getConnection(connectionUrl); PreparedStatement prepsInsertProduct = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);) < prepsInsertProduct.execute(); // Retrieve the generated key from the insert. resultSet = prepsInsertProduct.getGeneratedKeys(); // Print the ID of the inserted row. while (resultSet.next()) < System.out.println("Generated: " + resultSet.getString(1)); >> // Handle any errors that may have occurred. catch (Exception e) < e.printStackTrace(); >> >
Часть 1. Введение в SQL
Эта статья открывает небольшой цикл, посвященный азам взаимодействия с базами данных (БД) в Java и введению в SQL. Многие программы заняты обработкой и модификацией информации, её поддержкой в актуальном состоянии. Поскольку данные — весьма важная часть логики программ, то под них зачастую выделяют отдельное хранилище. Информация в нём структурирована и подчинена специальным правилам, чтобы обеспечить правильность обработки и хранения. Доступ к данным и их изменение осуществляется с помощью специального языка запросов — SQL (Structured Query Language).
Система управления базами данных — это ПО, которое обеспечивает взаимодействие разных внешних программ с данными и дополнительные службы (журналирование, восстановление, резервное копирование и тому подобное), в том числе посредством SQL. То есть программная прослойка между данными и внешними программами с ними работающими. В этой части ответим на вопросы что такое SQL, что такое SQL сервер и создадим первую программу для взаимодействия с СУБД.
Виды СУБД
- Иерархические. Данные организованы в виде древовидной структуры. Пример — файловая система, которая начинается с корня диска и далее прирастает ветвями файлов разных типов и папок разной степени вложенности.
- Сетевые. Видоизменение иерархической, у каждого узла может быть больше одного родителя.
- Объектно-ориентированные. Данные организованы в виде классов/объектов c их атрибутами и принципами взаимодействия согласно ООП.
- Реляционные. Данные этого вида СУБД организованы в таблицах. Таблицы могут быть связаны друг с другом, информация в них структурирована.
SQL
- Что такое SQL-Сервер и как он работает? Взаимодействие с СУБД происходит по клиент-серверному принципу. Некая внешняя программа посылает запрос в виде операторов и команд на языке SQL, СУБД его обрабатывает и высылает ответ. Для упрощения примем, что SQL Сервер = СУБД.
- Data Definition Language (DDL) – определения данных. Создание структуры БД и её объектов;
- Data Manipulation Language(DML) – собственно взаимодействие с данными: вставка, удаление, изменение и чтение;
- Transaction Control Language (TCL) – управление транзакциями;
- Data Control Language(DCL) – управление правами доступа к данным и структурам БД.
JDBC
В 80-е годы прошлого века персональные компьютеры типа PC XT/AT завоевали рынок. Во многом это произошло благодаря модульности их конструкции. Это означает, что пользователь мог довольно просто менять ту или иную составную часть своего компьютера (процессор, видеокарту, диски и тому подобное). Это замечательное свойство сохранилось и поныне: мы меняем видеокарту и обновляем драйвер (иногда он и вовсе обновляется сам, в автоматическом режиме). Чаще всего при таких манипуляциях ничего плохого не происходит, и существующие программы продолжат работать с обновившейся системой без переустановки. Аналогично и для работы в Java с СУБД. Для стандартизации работы с SQL-серверами взаимодействие с ней можно выполнять через единую точку — JDBC (Java DataBase Connectivity). Она представляет собой реализацию пакета java.sql для работы с СУБД. Производители всех популярных SQL-серверов выпускают для них драйверы JDBC. Рассмотрим схему ниже. Приложение использует экземпляры классов из java.sql. Затем мы передаем необходимые команды для получения/модификации данных. Далее java.sql через jdbc-драйвер взаимодействует с СУБД и возвращает нам готовый результат. Для перехода на СУБД другого производителя часто достаточно сменить JDBC и выполнить базовые настройки. Остальные части программы при этом не меняются.
Первая программа
Приступим к практической части. Создадим Java-проект с помощью IDE JetBrains IntelliJ IDEA. Заметим, что редакция Ultimate Edition содержит в своём составе замечательный инструмент для работы с SQL и БД — Data Grip. Однако она платная для большинства пользователей. Так что нам для учебных целей остается использовать общедоступную IntelliJ IDEA Community Edition. Итак:
- Запускаем IDE и создадём новый проект:
- Выбираем Java-проект, указываем версию SDK (в примере JDK8, однако это не критично):
- На следующем шаге выбираем в качестве типа консольное приложение:
- Указываем имя проекта, пакет и его размещение на диске (я создал специально для этого отдельную директорию):
- Отложим на минуту IDE и загрузим c www.h2database.com необходимый JDBC-файл для работы c СУБД H2 (download platform independent ZIP):
- Заходим внутрь скачанного файла (нас интересует jar-файл по пути h2\bin, который нам далее понадобится, скопируем его):
- Возвращаемся в IDE и создаём в корне проекта директории: db, где будут размещены файлы с данными СУБД; lib – здесь JAR-библиотека JDBC:
- Переносим в директорию lib jar-файл из шага 6, и добавим его в проект как библиотеку:
- Переименуем java-файл в src/sql/demo на StockExchange.java (если забыли, мы собираемся эмулировать простую «биржу»), поменяем его содержимое и запустим:
Теперь мы умеем подключаться к СУБД и отключаться от неё. Каждый шаг отражается в консоли. При первом подключении к СУБД создаётся файл базы данных stockExchange.mv.db.
Разбор кода
package sql.demo; import java.sql.*; public class StockExchangeDB < // Блок объявления констант public static final String DB_URL = "jdbc:h2:/c:/JavaPrj/SQLDemo/db/stockExchange"; public static final String DB_Driver = "org.h2.Driver"; public static void main(String[] args) < try < Class.forName(DB_Driver); //Проверяем наличие JDBC драйвера для работы с БД Connection connection = DriverManager.getConnection(DB_URL);//соединениесБД System.out.println("Соединение с СУБД выполнено."); connection.close(); // отключение от БД System.out.println("Отключение от СУБД выполнено."); >catch (ClassNotFoundException e) < e.printStackTrace(); // обработка ошибки Class.forName System.out.println("JDBC драйвер для СУБД не найден!"); >catch (SQLException e) < e.printStackTrace(); // обработка ошибок DriverManager.getConnection System.out.println("Ошибка SQL !"); >> >
Блок констант:
- DB_Driver: Здесь мы определили имя драйвера, которое можно узнать, например, кликнув мышкой на подключенную библиотеку и развернув её структуру в директории lib текущего проекта.
- DB_URL: Адрес нашей базы данных. Состоит из данных, разделённых двоеточием:
- Протокол=jdbc
- Вендор (производитель/наименование) СУБД=h2
- Расположение СУБД, в нашем случае путь до файла (c:/JavaPrj/SQLDemo/db/stockExchange). Для сетевых СУБД тут дополнительно указываются имена или IP адреса удалённых серверов, TCP/UDP номера портов и так далее.
Обработка ошибок:
Вызов методов нашего кода может вернуть ошибки, на которые следует обратить внимание. На данном этапе мы просто информируем о них в консоли. Заметим, что ошибки при работе с СУБД — это чаще всего SQLException.
Логика работы:
- Class.forName(DB_Driver) – убеждаемся в наличии соответствующего JDBC-драйвера (который мы ранее загрузили и установили).
- DriverManager.getConnection(DB_URL) – устанавливаем соединение СУБД. По переданному адресу, JDBC сама определит тип и местоположение нашей СУБД и вернёт Connection, который мы можем использовать для связи с БД.
- connection.close() – закрываем соединение с СУБД и завершаем работу с программой.
Соединение базы данных в java
Вначале создадим на сервере MySQL пустую базу данных, которую назовем store и с которой мы будет работать в приложении на Java. Для создания базы данных применяется выражение SQL:
Его можно выполнить либо из консольного клиента MySQL Command Line Client, либо из графического клиента MySQL Workbench, которые устанавливются вместе с сервером MySQL. Подробнее про создание базы данных можно прочитать в статье Создание и удаление базы данных.
Для подключения к базе данных необходимо создать объект java.sql.Connection . Для его создаия применяется метод:
Connection DriverManager.getConnection(url, username, password)
Метод DriverManager.getConnection в качестве параметров принимает адрес источника данных, логин и пароль. В качестве логина и пароля передаются логин и пароль от сервера MySQL. Адрес локальной базы данных MySQL указывается в следующем формате: jdbc:mysql://localhost/название_базы данных
Пример создания подключения к созданной выше локальной базе данных store:
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/store", "root", "password");
После завершения работы с подключением его следует закрыть с помощью метода close() :
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/store", "root", "password"); // работа с базой данных connection.close();
Либо мы можем использовать конструкцию try :
try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/store", "root", "password")) < // работа с базой данных >
Возможные проблемы с часовыми поясами и SSL
При подключении к базе данных MySQL мы можем столкнуться с рядом проблем. Например, определим следующий код подключения:
import java.sql.Connection; import java.sql.DriverManager; public class Program < public static void main(String[] args) < try< String url = "jdbc:mysql://localhost/store"; String username = "root"; String password = "password"; Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance(); try (Connection conn = DriverManager.getConnection(url, username, password))< System.out.println("Connection to Store DB succesfull!"); >> catch(Exception ex) < System.out.println("Connection failed. "); System.out.println(ex); >> >
Даже если указаны правильно адрес базы данных, логин, пароль, мы все равно можем столкнуться с ошибками:
Из консольного вывода видно, что проблема заключается с SSL и часовым поясом. Чтобы решить данную проблему, необходимо указать в адресе подключения часовой пояс бд и параметры для использования ssl. В частности, я указываю, что SSL не будет использоваться и что часовым поясом будет московский часовой пояс:
String url = "jdbc:mysql://localhost/store?serverTimezone=Europe/Moscow&useSSL=false";
Параметры подключения указываются после вопросительного знака после названия базы данных. Параметр serverTimezone указывает на название часового пояса сервера бд. В данном случае «Europe/Moscow», cписок всех допустимых названий часовых поясов можно найти на странице https://gist.github.com/kinjal/9105369. И параметр useSSL=false указывает, что SSL не будет применяться.
Файлы конфигурации
Мы можем определить все данные для подключения непосредственно в программе. Однако что если какие-то данные были изменены? В этом случае потребуется перекомпиляция приложения. Иногда это не всегда удобно, например, отсутствует досуп к исходникам, или перекомпиляция займет довольно продолжительное время. В этом случае мы можем хранить настройки в файле.
Так, создадим в папке программы новый текстовый файл database.properties , в котором определим настройки подключения:
url = jdbc:mysql://localhost/store?serverTimezone=Europe/Moscow&useSSL=false username = root password = password
Загрузим эти настройки в программе:
import java.sql.*; import java.nio.file.*; import java.io.*; import java.util.*; public class Program < public static void main(String[] args) < try< Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance(); try (Connection conn = getConnection())< System.out.println("Connection to Store DB succesfull!"); >> catch(Exception ex) < System.out.println("Connection failed. "); System.out.println(ex); >> public static Connection getConnection() throws SQLException, IOException < Properties props = new Properties(); try(InputStream in = Files.newInputStream(Paths.get("database.properties")))< props.load(in); >String url = props.getProperty("url"); String username = props.getProperty("username"); String password = props.getProperty("password"); return DriverManager.getConnection(url, username, password); > >