Sql query to java string

Работа с БД с помощью JDBC

Взаимодействовать с БД мы можем с помощью трёх интерфейсов, которые реализуются каждым драйвером:

1. Statement — этот интерфейс используется для доступа к БД для общих целей. Он крайне полезен, когда мы используем статические SQL – выражения во время работы программы. Этот интерфейс не принимает никаких параметров.
2. PreparedStatement — этот интерфейс может принимать параметры во время работы программы.
3. CallableStatement — этот интерфейс становится полезным в случае, когда мы хотим получить доступ к различным процедурам БД. Он также может принимать параметры во время работы программы.

2. Интерфейс Statement

Statement statement = connection.createStatement();

После этого мы можем использовать наш экземпляр statement для выполнения SQL – запросов. Для этой цели интерфейс Statement имеет три метода, которые реализуются каждой конкретной реализацией JDBC драйвера:

• boolean execute(String SQL) — позволяет вам выполнить Statement, когда неизвестно заранее, является SQL-строка запросом или обновлением. Метод возвращает true, если команда создала результирующий набор.
• int executeUpdate(String SQL) используется для выполнения обновлений. Он возвращает количество обновленных строк. Для выполнения операторов INSERT, UPDATE или DELETE.
• ResultSet executeQuery(String SQL) — используется для выполнения запросов (SELECT). Он возвращает для обработки результирующий набор.

Пример 1. Создание таблицы

import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; import static jdbc.ConnectionData.URL; import static jdbc.ConnectionData.USER; import static jdbc.ConnectionData.PASSWORD; public class CreatingTable < private static final String CREATE_TABLE_QUERY = "CREATE TABLE users " + "(id INT(5) NOT NULL AUTO_INCREMENT," + " username VARCHAR(50), " + "PRIMARY KEY(id));"; public static void main(String[] args) < try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD); Statement statement = connection.createStatement()) < statement.executeUpdate(CREATE_TABLE_QUERY); >catch (Exception e) < System.out.println(e.getMessage()); >> >

public class ConnectionData < public static final String DRIVER = "com.mysql.jdbc.Driver"; public static final String DB = "catalog"; public static final String URL = "jdbc:mysql://localhost:3306/" + DB; public static final String USER = "root"; public static final String PASSWORD = "admin"; >

3. Интерфейс ResultSet

Этот интерфейс представляет результирующий набор базы данных. Он обеспечивает приложению построчный доступ к результатам запросов в базе данных.

Во время обработки запроса ResultSet поддерживает указатель на текущую обрабатываемую строку. Приложение последовательно перемещается по результатам, пока они не будут все обработаны или не будет закрыт ResultSet.

Основные методы интерфейса ResultSet:

1. public boolean absolute(int row) throws SQLException — метод перемещает курсор на заданное число строк от начала, если число положительно, и от конца — если отрицательно.
2. public void afterLast() throws SQLException — этот метод перемещает курсор в конец результирующего набора за последнюю строку.
3. public void beforeFirst() throws SQLException — этот метод перемещает курсор в начало результирующего набора перед первой строкой.
4. public void deleteRow() throws SQLException — удаляет текущую строку из результирующего набора и базы данных.
5. public ResultSetMetaData getMetaData() throws SQLException — предоставляет объект метаданных для данного ResultSet. Класс ResultSetMetaData содержит информацию о результирующей таблице, такую как количество столбцов, их заголовок и т.д.
6. public int getRow() throws SQLException — возвращает номер текущей строки.
7. public Statement getStatement() throws SQLException — возвращает экземпляр Statement, который произвел данный результирующий набор.
8. public boolean next() throws SQLException, public boolean previous() throws SQLException — эти методы позволяют переместиться в результирующем наборе на одну строку вперед или назад. Во вновь созданном результирующем наборе курсор устанавливается перед первой строкой, поэтому первое обращение к методу next() влечет позиционирование на первую строку. Эти методы возвращают true, если остается строка для дальнейшего перемещения. Если строк для обработки больше нет, возвращается false.
9. public void close() throws SQLException — осуществляет немедленное закрытие ResultSet вручную. Обычно этого не требуется, так как закрытие Statement, связанного с ResultSet, автоматически закрывает ResultSet.

Пример 2. Использование интерфейса ResultSet

public class RetrieveData < private static final String SELECT_QUERY = "SELECT * FROM users;"; public static void main(String[] args) < try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD); Statement statement = connection.createStatement()) < ResultSet resultSet = statement.executeQuery(SELECT_QUERY); System.out.printf("%-20s%s%n", "id", "username"); System.out.println("-------------------------------"); while (resultSet.next()) < int String name = resultSet.getString("username"); System.out.printf("%-20d%s%n", id, name); >> catch (SQLException e) < System.out.println(e.getMessage()); >> >

4. Пакетное выполнение запросов

Для выполнения набора из нескольких запросов на обновление данных в интерфейс Statement были добавлены методы:

void addBatch(String) int[] executeBatch()‏

Пакетное выполнение запросов уменьшает трафик между клиентом и СУБД и может привести к существенному повышению производительности.

Пример 3. Пакетное выполнение запросов

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import static jdbc.ConnectionData.URL; import static jdbc.ConnectionData.USER; import static jdbc.ConnectionData.PASSWORD; public class InsertBatchData < public static void main(String[] args) < try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD); Statement statement = connection.createStatement()) < statement.addBatch("INSERT INTO users (username) VALUES ('sidorov')"); statement.addBatch("INSERT INTO users (username) VALUES ('petrov')"); statement.addBatch("INSERT INTO users (username) VALUES ('kozlov')"); statement.executeBatch(); >catch (SQLException e) < System.out.println(e.getMessage()); >> >

5. Интерфейс PreparedStatement

Особенностью SQL-выражений в PreparedStatement является то, что они могут иметь параметры. Параметризованное выражение содержит знаки вопроса в своем тексте. Например:

SELECT name FROM persons WHERE age=?

Перед выполнением запроса значение каждого вопросительного знака явно устанавливается методами setXxx()‏, например:

Использование PreparedStatement приводит к более быстрому выполнению запросов при их многократном вызове с различными параметрами.

Пример 4. Использование интерфейса PreparedStatement

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import static jdbc.ConnectionData.URL; import static jdbc.ConnectionData.USER; import static jdbc.ConnectionData.PASSWORD; public class RetrieveDataPreparedStatement < private static final String SELECT_QUERY = "SELECT * FROM users WHERE id>? AND username LIKE ?"; public static void main(String[] args) < try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD); PreparedStatement preparedStatement = connection.prepareStatement(SELECT_QUERY)) < preparedStatement.setInt(1, 2); preparedStatement.setString(2, "P%"); ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()) < System.out.printf("%d%23s%n", resultSet.getInt("id"), resultSet.getString("username")); >> catch (Exception e) < e.printStackTrace(); >> >

Пример 5. Использование интерфейса PreparedStatement

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import static jdbc.ConnectionData.URL; import static jdbc.ConnectionData.USER; import static jdbc.ConnectionData.PASSWORD; public class InsertDataPreparedStatement < private static final String INSERT_QUERY = "INSERT INTO users (username) VALUES (?)"; public static void main(String[] args) < try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD); PreparedStatement preparedStatement = connection.prepareStatement(INSERT_QUERY)) < preparedStatement.setString(1, "misha"); preparedStatement.addBatch(); preparedStatement.setString(1, "grisha"); preparedStatement.addBatch(); preparedStatement.executeBatch(); >catch (SQLException e) < System.out.println(e.getMessage()); >> >

6. Использование properties файлов

Пример 6. Содержимое database.properties файла

db.driver = com.mysql.jdbc.Driver db.name = catalog db.url = jdbc:mysql://localhost:3306/ db.user = root db.password = admin 

Пример 7. Использование ResourceBundle для чтения данных для аутентификации

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.ResourceBundle; public class ConnectorDB < public static Connection getConnection() throws SQLException < ResourceBundle resource = ResourceBundle.getBundle("database"); String url = resource.getString("db.url"); String user = resource.getString("db.user"); String pass = resource.getString("db.password"); String dbName = resource.getString("db.name"); return DriverManager.getConnection(url + dbName, user, pass); >>

7. Data access object (DAO)

В программном обеспечении data access object (DAO) — это объект, который предоставляет абстрактный интерфейс к какому-либо типу базы данных или механизму хранения. DAO может использоваться для разных видов доступа к БД (JDBC, JPA).

Пример 8. Абстрактный класс DAO

import java.util.List; public abstract class AbstractDAO  < public abstract ListfindAll(); public abstract T findEntityById(K id); public abstract boolean delete(K id); public abstract boolean delete(T entity); public abstract boolean create(T entity); public abstract T update(T entity); >

Пример 9. Абстрактный класс DAO

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; public class UserDAO extends AbstractDAO  < public static final String SQL_SELECT_ALL_USERS = "SELECT * FROM users"; public static final String SQL_SELECT_USER_ID = "SELECT * FROM users WHERE @Override public ListfindAll() < Listusers = new ArrayList<>(); try (Connection connection = ConnectorDB.getConnection(); Statement statement = connection.createStatement()) < ResultSet rs = statement.executeQuery(SQL_SELECT_ALL_USERS); while (rs.next()) < int String name = rs.getString(2); users.add(new User(id, name)); >> catch (SQLException e) < System.out.println(e.getMessage()); >return users; > @Override public User findEntityById(Integer id) < User user = null; try (Connection connection = ConnectorDB.getConnection(); PreparedStatement statement = connection.prepareStatement(SQL_SELECT_USER_ID)) < statement.setInt(1, id); ResultSet rs = statement.executeQuery(); if (rs.next()) < String name = rs.getString(2); user = new User(id, name); >> catch (SQLException e) < System.out.println(e.getMessage()); >return user; > @Override public boolean delete(Integer id) < throw new UnsupportedOperationException(); >@Override public boolean delete(User entity) < throw new UnsupportedOperationException(); >@Override public boolean create(User entity) < throw new UnsupportedOperationException(); >@Override public User update(User entity) < throw new UnsupportedOperationException(); >>

Источник

Получаем данные из ResultSet

Первую программу мы написали и она отлично отработала. Мы написали запрос, выполнили его, и в результате метод executeQuery() вернул нам объект ResultSet , который содержит все результаты запроса. И теперь мы попробуем разобраться, как эти результаты из него получить.

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

Но начнем мы с самого простого — с понятия “текущей строки результата”. Так как строк у результата обычно очень много, то объект ResultSet имеет у себя внутри указатель на текущую строку. И последовательно переключает строки для их чтения с помощью метода next() .

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

Однако же, если тебе очень нужно, то файлы можно читать в любом месте с помощью класса RandomAccessFile .

Класс ResultSet тоже позволяет что-то подобное и позволяет двигать текущую строку по результату куда угодно. Для этого у него есть такие методы:

Метод	Описание
1	next()	Переключиться на следующую строку
2	previous()	Переключиться на предыдущую строку
3	isFirst()	Текущая строка первая?
4	isBeforeFirst()	Мы перед первой строкой?
5	isLast()	Текущая строка последняя?
6	isAfterLast()	Мы после последней сроки?
7	absolute(int n)	Делает N-ю строку текущей
8	relative(int n)	Двигает текущую строку на N позиций вперед. N может быть getRow()	Возвращает номер строки

Методы достаточно простые, однако нужно сделать два пояснения. Результаты как бы обрамлены пустыми строками с обоих сторон. Поэтому изначально текущая строка находится перед первой строкой результата. И чтобы получить первую строку, нужно хотя бы раз вызвать метод next() .

Если ты на последней стоке вызвал метод next() , то ты перешел на строку после последней. Данных из нее прочитать ты не можешь, но никакой ошибки не произойдет. Тут метод isAfterLast() будет возвещать true в качестве результата.

 Statement statement = connection.createStatement(); ResultSet results = statement.executeQuery("SELECT * FROM user"); System.out.println( results.getRow() ); // 0 System.out.println( results.isBeforeFirst() ); // true System.out.println( results.isFirst() ); // false results.next(); System.out.println( results.getRow() ); // 1 System.out.println( results.isBeforeFirst() ); // false System.out.println( results.isFirst() ); // true results.next(); System.out.println( results.getRow() ); // 2 System.out.println( results.isBeforeFirst() ); // false System.out.println( results.isFirst() ); // false 

Получение данных из текущей строки

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

Впрочем, если у колонки есть имя, то можно получать и по имени колонки:

Ниже я приведу таблицу, которая тебе поможет связать типы данных SQL и методы ResultSet:

SQL Datatype	getXXX() Methods
CHAR	getString()
VARCHAR	getString()
INT	getInt()
FLOAT	getDouble()
CLOB	getClob()
BLOB	getBlob()
DATE	getDate()
TIME	getTime()
TIMESTAMP	getTimestamp()

Получение разных данных о ResultSet

Как читать данные из текущей строки мы разобрались: и по номеру колонки, и по ее имени. Кстати, а как узнать имя колонки по ее номеру? Или количество колонок в результате?

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

Для этого у JDBC есть специальный объект – интерфейс ResultSetMetaData . Получить объект этого типа достаточно просто:

 Statement statement = connection.createStatement(); ResultSet results = statement.executeQuery("SELECT * FROM user"); ResultSetMetaData resultSetMetaData = results.getMetaData(); 

У интерфейса ResultSetMetaData есть очень интересные методы. Ниже приведу самые популярные из них:

1	getColumnCount()	Возвращает количество колонок результата
2	getColumnName(int column)	Возвращает имя колонки
3	getColumnLabel(int column)	Возвращает description колонки
4	getColumnType()	Возвращает тип колонки: число (специальный код)
5	getColumnTypeName()	Возвращает тип колонки: строка
6	getColumnClassName()	Возвращает имя java-класса для типа колонки
7	getTableName()	Возвращает имя таблицы
8	getCatalogName()	Возвращает имя каталога колонки
9	getSchemaName()	Возвращает имя схемы базы данных
10	isAutoIncrement(int column)	Колонка поддерживает AUTO INCREMENT?
11	isNullable()	Колонка может содержать NULL?

Давай с его помощью немного узнаем о нашей таблице:

 ResultSetMetaData metaData = results.getMetaData(); int columnCount = metaData.getColumnCount(); for (int column = 1; column

Важно! Обрати внимание, что колонки нумеруются с 1. Строки, кстати, тоже. Как это необычно, да?

И вот какой результат я получил после запуска программы:

«C:\Program Files\Java\jdk-17.0.3.1\bin\java.exe…
id	java.lang.Integer	INT	4
name	java.lang.String	VARCHAR	12
level	java.lang.Integer	INT	4
created_date	java.sql.Date	DATE	91
Process finished with exit code 0

Источник