Skip to content

SQL.md

Latest commit

 

History

History
1393 lines (950 loc) · 106 KB

SQL.md

File metadata and controls

1393 lines (950 loc) · 106 KB
Raw

SQL

1. Что такое первичный ключ?

2. Что такое внешний ключ?

3. Что такое нормализация БД?

4. Что такое денормализация БД? Для чего она нужна?

5. Что такое кластерный и некластерный индекс?

6. Какие типы соединений (join) таблиц существуют? В чем их разница?

7. Что такое SQL курсор?

8. Опишите шаги по созданию и использованию курсора?

9. Что такое транзакция?

10. Что такое триггер? Какие типы триггеров вы знаете?

11. В чем разница между WHERE и HAVING?

12. Что такое подзапрос (sub-query)?

13. Что такое union?

14. Что такое group by?

15. Что такое хранимые процедуры?

16. Что такое view (Представление)?

17. Опишите процесс создания запроса через JDBC?

18. Для чего используется конструкция try-with-resources?

19. ANSI SQL?

20. Основные элементы баз данных – таблицы, процедуры, функции, констрейнты и тд?

21. Как вы понимаете null в базах данных?

22. Агрегатные функции, как они работают с null. Не забудьте о group by и having

23. Каким образом лучше добавлять большое количество записей в таблицу?

24. Что такое первая нормальная форма и процесс нормализации? Какие бывают нормальные формы?

25. В чем смысл индекса СУБД, как они устроены, как хранятся? Как бы вы реализовали тот же функционал?

26. Что такое JDBC API и когда его используют?

27. Что такое JDBC Driver и какие различные типы драйверов JDBC вы знаете?

28. Как JDBC API помогает достичь слабой связи между Java программой и JDBC Drivers API?

29. Что такое JDBC Connection? Покажите шаги для подключения программы к базе данных.

30. Как используется JDBC DriverManager class?

31. Как получить информацию о сервере базы данных из java программы?

32. Что такое JDBC Statement?

33. Какие различия между execute, executeQuery, executeUpdate?

34. Что такое JDBC PreparedStatement?

35. Как установить NULL значения в JDBC PreparedStatement?

36. Как используется метод getGeneratedKeys в Statement?

37. Какие преимущества в использовании PreparedStatement над Statement?

38. Какие есть ограничения PreparedStatement и как их преодолеть?

39. Что такое JDBC ResultSet?

40. Какие существуют различные типы JDBC ResultSet?

41. Как используются методы setFetchSize и SetMaxRows в Statement?

42. Как вызвать Stored Procedures используя JDBC API?

43. Что такое JDBC Batch Processing и каковы его преимущества?

44. Что такое JDBC Transaction Management и зачем он нужен?

45. Как откатить JDBC транзакцию?

46. Что такое JDBC Savepoint и как он используется?

47. Расскажите о JDBC DataSource. Какие преимущества он дает?

48. Расскажите про Apache DBCP API.

49. Какие вы знаете уровни изоляции соединений в JDBC?

50. Что вы знаете о JDBC RowSet? Какие существуют различные типы RowSet?

51. В чем разница между ResultSet и RowSet?

52. Приведите пример наиболее распространенных исключений в JDBC.

53. Расскажите о типах данных CLOB и BLOB в JDBC.

54. Что вы знаете о «грязном чтении» dirty read в JDBC? Какой уровень изоляции предотвращает этот тип чтения?

55. Какие есть две фазы commit?

56. Приведите пример различных типов блокировки в JDBC.

57. Как вы понимаете DDL и DML выражения?

58. Какая разница между java.util.Date и java.sql.Date?

59. Как вставить изображение или необработанные данные в базу данных?

60. Что вы можете рассказать о фантомном чтении? Какой уровень изоляции его предотвращает?

61. Что такое SQL Warning? Как возвратить SQL предупреждения в JDBC программе?

62. Как запустить Oracle Stored Procedure с объектами базы данных INOUT?

63. Приведите пример возникновения java.sql.SQLException: No suitable driver found.

64. Некоторые Best Practices в JDBC.

1. Что такое первичный ключ?

Первичный ключ (primary key) является полем в таблице, которое однозначно идентифицирует каждую строку/запись в таблице базы данных. Первичные ключи должны содержать уникальные значения. Первичный ключ столбец не может иметь значения NULL.

Таблица может иметь только один первичный ключ, который может состоять из одного или нескольких полей. Когда несколько полей используются в качестве первичного ключа, их называют составным ключом. Поэтому в качестве первичного ключа, как правило, выбирают тот, который имеет наименьший размер (физического хранения) и/или включает наименьшее количество атрибутов. Другой критерий выбора первичного ключа — сохранение его уникальности со временем. Поэтому в качестве первичного ключа стараются выбирать такой потенциальный ключ, который с наибольшей вероятностью никогда не утратит уникальность.

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

https://postgrespro.ru/docs/postgresql/9.5/ddl-constraints#ddl-constraints-primary-keys

к оглавлению

2. Что такое внешний ключ?

Внешний ключ (foreign key) — это столбец или комбинация столбцов, значения которых соответствуют Первичному ключу в другой таблице. Связь между двумя таблицами задается через соответствие Первичного ключа в одной из таблиц внешнему ключу во второй.

Пример:

img

Первичным ключом таблицы Themes является id_theme, а таблицы posts - id_posts. Обрати внимание id_theme есть также в таблице Posts - каждое значение этого поля является внешним ключом. Внешний ключ таблицы Posts ссылается на первичный ключ таблицы Themes.

к оглавлению

3. Что такое нормализация БД?

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

Итого:

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

Нормализация баз данных https://dic.academic.ru/dic.nsf/ruwiki/665089

хорошо объяснено https://github.com/codedokode/pasta/blob/master/db/normalization.md

Описание основных приемов нормализации базы данных https://support.microsoft.com/ru-ru/help/283878/description-of-the-database-normalization-basics

к оглавлению

4. Что такое денормализация БД Для чего она нужна?

Это намеренное нарушение нормальных форм, для увеличения производительности за счёт добавления избыточных данных.

Денормализация нужна для:

  1. Повышения производительности запросов за счет экономии на объединениях.
  2. Предварительных вычислений часто запрашиваемых значений.
  3. Для сохранения истории.

к оглавлению

5. Что такое кластерный и некластерный индекс?

По способу хранения данных отличают кластерные и обычные индексы.

  • Обычный индекс — это отсортированный набор данных колонки, по которой он построен. Индекс будет хранить ссылки на данные каждой строки для каждого значения ID.
  • Кластерный индекс (или кластерный ключ) сохраняет не только значения колонки в отсортированном виде, а и данные всей строки. Это позволяет минимизировать количество операций чтения с диска при работе с таким индексом. В таблице может быть только один кластерный индекс.

https://habr.com/ru/post/141767/

типы индексов в PostgreSQL https://postgrespro.ru/docs/postgresql/9.5/indexes-types

к оглавлению

6. Какие типы соединений join таблиц существуют В чем их разница?

img

  • INNER JOIN Выбираются только совпадающие данные из объединяемых таблиц.

  • OUTER JOIN Такое объединение вернет совпадающие данные из объединяемых таблиц ПЛЮС дополнит выборку оставшимися данными из внешней таблицы, которые по условию не подходят, заполнив недостающие данные значением NULL.

  • Существует два типа внешнего объединения OUTER JOIN - LEFT OUTER JOIN и RIGHT OUTER JOIN.

  • FULL JOIN возвращает объединение объединений LEFT и RIGHT таблиц, комбинируя результат двух запросов.

  • CROSS JOIN возвращает перекрестное (декартово) объединение двух таблиц. Результатом будет выборка всех записей первой таблицы, объединенная с каждой строкой второй таблицы. Важным моментом является то, что для кросса не нужно указывать условие объединения.

  • SELF JOIN соединение таблицы с этой же таблице.

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

https://ru.wikipedia.org/wiki/Join_(SQL)#INNER_JOIN

JOIN в PostgreSQL https://postgrespro.ru/docs/postgresql/9.5/queries-table-expressions#queries-join

еще статья про JOIN http://www.skillz.ru/dev/php/article-Obyasnenie_SQL_obedinenii_JOIN_INNER_OUTER.html

к оглавлению

7. Что такое SQL курсор?

Команды манипулирования данными SELECT, UPDATE, DELETE работают сразу с группами строк. Эти группы, вплоть до отдельных строк, можно выбрать с помощью опции WHERE. А если надо перебрать строки некоторой таблицы последовательно, одну за другой? На этот случай в языке SQL существуют курсоры.

Курсор (current set of record) – временный набор строк, которые можно перебирать последовательно, с первой до последней. Это эффективный способ получать большие наборы строк из функций по частям.

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

  1. Просто объявить её как переменную типа refcursor.
  2. Использовать синтаксис объявления курсора, который в общем виде выглядит так: 
    имя [ [ NO ] SCROLL ] CURSOR [ ( аргументы ) ] FOR запрос;

Курсоры в PL/pgSQL https://postgrespro.ru/docs/postgresql/9.5/plpgsql-cursors

Еще разное:

Основы T-SQL и примеры — функции (UDF), триггеры, процедуры, курсоры, циклы https://ivan-shamaev.ru/t-sql-fundamentals-and-examples/#i-6

Курсоры в MSSQL - перебор выборки в цикле. https://kbss.ru/blog/mssql/55.html

Что такое курсор? https://docs.microsoft.com/ru-ru/sql/ado/guide/data/what-is-a-cursor?view=sql-server-2017

Понятие курсора http://datasql.ru/basesql/13.htm

к оглавлению

8. Опишите шаги по созданию и использованию курсора?

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

  • Объявление курсора: DECLARE имя_курсора CURSOR FOR SELECT текст_запроса Любой курсор создается на основе некоторого оператора SELECT.

  • Открытие курсора: OPEN имя_курсора Для того чтобы с помощью курсора можно было читать строки, его надо обязательно открыть.

  • Чтение следующей строки из курсора: FETCH имя_курсора INTO список_переменных Переменные в списке должны быть в том же количестве и того же типа, что и столбцы курсора. Глобальная переменная @@FETCH_STATUS принимает ненулевое значение, если строк в курсоре больше нет. Если же набор строк еще не исчерпан, то @@FETCH_STATUS равна нулю, и оператор FETCH перепишет значения полей из текущей строки в переменные.

  • Закрытие курсора: CLOSE имя_курсора

  • Для удаления курсора из памяти используется команда DEALLOCATE имя_курсора Для иллюстрации использования курсора создадим процедуру, которая будет выбирать данные из одной таблицы, перебирать их в курсоре анализируя, есть ли такие данные во второй таблице и вставлять в третью таблицу, если данные записи удовлетворяют определённым критериям.

CREATE PROCEDURE [dbo].[MyProcedure] AS
DECLARE @ID INT
DECLARE @QUA INT
DECLARE @VAL VARCHAR (500)
DECLARE @NAM VARCHAR (500)
/*Объявляем курсор*/
DECLARE @CURSOR CURSOR
/*Заполняем курсор*/
SET @CURSOR  = CURSOR SCROLL
FOR
SELECT INDEX, QUANTITY, VALUE, NAME  
  FROM My_First_Table WHERE  QUANTITY > 1
/*Открываем курсор*/
OPEN @CURSOR
/*Выбираем первую строку*/
FETCH NEXT FROM @CURSOR INTO @ID, @QUA, @VAL, @NAM
/*Выполняем в цикле перебор строк*/
WHILE @@FETCH_STATUS = 0
BEGIN
        IF NOT EXISTS(SELECT VAL FROM My_Second_Table WHERE ID=@ID)
        BEGIN
/*Вставляем параметры в третью таблицу если условие соблюдается*/
                INSERT INTO My_Third_Table (VALUE, NAME) VALUE(@VAL, @NAM)
        END
/*Выбираем следующую строку*/
FETCH NEXT FROM @CURSOR INTO @ID, @QUA, @VAL, @NAM
END
CLOSE @CURSOR

к оглавлению

9. Что такое транзакция?

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

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

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

https://postgrespro.ru/docs/postgresql/9.5/tutorial-transactions

Свойства транзакций обозначаются аббревеатурой ACID (Atomicity, Consistency, Isolation, Durability):

  • Atomicity - атомарность обеспечивает неделимость набора инструкций, который модифицирует данные в БД и является частью транзакции. Т.е. или выполняются все изменения данных в транзакции, или в случае любой ошибки происходит откат всех выполненных изменений.
  • Consistency - согласованность обеспечивает, что в результате выполнения транзакции база данных не будет содержать несогласованных данных. Т.е. выполняемые транзакцией трансформации данных переводят базу данных из одного согласованного состояния в другое.
  • Isolation - изолированность отделяет все параллельные транзакции друг от друга, т.е. активная транзакция не может видеть модификации данных в параллельной или незавершенной транзакции. Это означает, что для обеспечения изоляции для некоторых транзакций может потребоваться выполнить откат.
  • Durability долговечность обеспечивает сохраняемость данных. Т.е. эффект транзакции должен оставаться действенным даже в случае системной ошибки. По этой причине, если в процессе выполнения транзакции происходит системная ошибка, то осуществляется откат для всех выполненных инструкций этой транзакции.

https://ru.wikipedia.org/wiki/ACID

https://geekbrains.ru/posts/acid_cap_transactions

к оглавлению

10. Что такое триггер Какие типы триггеров вы знаете?

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

Обзор механизма работы триггеров в PostgreSQL https://postgrespro.ru/docs/postgresql/9.5/trigger-definition

Обзор механизма работы триггеров событий в PostgreSQL https://postgrespro.ru/docs/postgresql/9.5/event-trigger-definition

Триггеры — это части кода, запускаемые автоматически и основанные на каком-либо действии или событии в таблице базы данных.

Триггеры могут применять в таблице во время выполнения операторов INSERT, UPDATE или DELETE, а затем работать или перед, или после этих действий.

Некоторые СУБД также позволяют работать триггерам на уровне оператора или на уровне каждой строки данных во время ее изменения.

Пример использования триггеров: https://habr.com/ru/post/146717/

к оглавлению

11. В чем разница между WHERE и HAVING?

  • WHERE - сначала отбирает сторки а потом применяет к ним GROUP BY и агрегатные функции типа COUNT(*).
  • HAVING - наоборот сначала все функции и группировки, а потом выборка.

WHERE https://postgrespro.ru/docs/postgresql/9.5/queries-table-expressions#queries-where

HAVING https://postgrespro.ru/docs/postgresql/9.5/queries-table-expressions#queries-group

к оглавлению

12. Что такое подзапрос sub query?

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

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

Подзапросы в PostgreSQL https://postgrespro.ru/docs/postgresql/9.5/queries-table-expressions#queries-subqueries

Скалярные подзапросы https://postgrespro.ru/docs/postgresql/9.5/sql-expressions#sql-syntax-scalar-subqueries

Выражения подзапросов https://postgrespro.ru/docs/postgresql/9.5/functions-subquery

к оглавлению

13. Что такое union?

В языке SQL операция UNION применяется для объединения двух наборов строк, возвращаемых SQL-запросами. Оба запроса должны возвращать одинаковое число столбцов, и столбцы с одинаковым порядковым номером должны иметь совместимые типы данных. Результат получает структуру (названия и типы столбцов) первого (левого) запроса, то есть операция не является симметричной.

При объединении нескольких запросов подряд результат последовательно вычисляется слева направо.

https://postgrespro.ru/docs/postgresql/9.5/typeconv-union-case

к оглавлению

14. Что такое group by?

Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, MAX, AVG и SUM). все столбцы списка SELECT, не вошедшие в агрегатные функции, должны быть указаны в предложении GROUP BY.

В результате чего все выходные строки запроса разбиваются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах. После чего к каждой группе будут применены агрегатные функции. Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, то есть при группировке по полю, содержащему NULL-значения, все такие строки попадут в одну группу.

Предложения GROUP BY и HAVING https://postgrespro.ru/docs/postgresql/9.5/queries-table-expressions#queries-group

к оглавлению

15. Что такое хранимые процедуры?

Объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения. Хранимые процедуры похожи на определяемые пользователем функции (UDF). Основное различие заключается в том, что пользовательские функции можно использовать как и любое другое выражение в SQL запросе, в то время как хранимые процедуры должны быть вызваны с помощью функции CALL:

CALL процедура(…)

или

EXECUTE процедура(…)

Хранимые процедуры могут возвращать множества результатов, то есть результаты запроса SELECT. Такие множества результатов могут обрабатываться, используя курсоры, другими сохранёнными процедурами, возвращая указатель результирующего множества, либо же приложениями. Хранимые процедуры могут также содержать объявленные переменные для обработки данных и курсоров, которые позволяют организовать цикл по нескольким строкам в таблице. Стандарт SQL предоставляет для работы выражения IF, LOOP, REPEAT, CASE и многие другие. Хранимые процедуры могут принимать переменные, возвращать результаты или изменять переменные и возвращать их, в зависимости от того, где переменная объявлена.

https://habr.com/ru/post/79340/

http://javatutor.net/articles/stored-procedures-for-java-programmers

к оглавлению

16. Что такое view Представление?

Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению. Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему.

https://habr.com/ru/post/47031/

к оглавлению

17. Опишите процесс создания запроса через JDBC?

  • Загрузка драйвера базы данных. Для загрузки/использования нашего драйвера можно пойти двумя путями. Первый – это вписать имя драйвера в код программы. В этом случае мы не сможем изменить СУБД без перекомпиляции. Второй – это подключать драйвер из командной строки Java (JVM). Следующей строчкой мы загружаем драйвер, и он автоматически зарегистрирует себя для использования вместе с JDBC.
Class.forName(“postgresql.Driver”);

Во втором же случае мы используем ключ –D для подключения драйвера.

java –Djdbc.drivers=postgresql.Driver myClass
  • Для соединения с базой данных используют класс Connection:
Connection dbh = DriverManager.getConnection(url, user, passwd);
  • Установив соединение, мы можем выполнять sql-запросы к нашей базе данных. Для примера, рассмотрим небольшой кусок кода на Java:
Statement st = dbh.createStatement();
ResultSet rs = st.executeQuery(
  "select * from flowers_tbl where (flowers_id in (1,2,3,4,5,6,7,8,9))"
);
while (rs.next()) {
  System.out.println(rs.getString(1));
}
rs.close();
st.close();

Для того, чтобы посылать запросы к базе данных, необходимо создать экземпляр класса Statement st. Метод executeQuery этого класса отправляет переданный ему запрос к базе данных и в качестве ответа возвращает результат в виде класса ResultSet.

В конце надо закрыть все.

The PostgreSQL JDBC Interface https://jdbc.postgresql.org/documentation/head/index.html

к оглавлению

18. Для чего используется конструкция try with resources?

Данная конструкция(try-with-resources) появилась в java 7, она позволяет использовать блок try-catch не заботясь о закрытии ресурсов, используемых в данном сегменте кода. Ресурсы объявляются в скобках сразу после try, а компилятор уже сам неявно создаёт секцию finally в которой и происходит закрытие без участия разработчика. Конструкция является так называемым «синтаксическим сахаром» и создана для облегчения жизни программиста. Под ресурсами подразумеваются сущности, реализующие интерфейс java.lang.Autocloseable.

Общий вид конструкции выглядит следующим образом:

try(тут объявляются ресурсы) {
    ...
    ...
} catch (Exception ex) {
    ...
} finally {
    ...
}

внимание, блоки catch и явный finally выполняются уже после того, как закрываются ресурсы в неявном finnaly.

к оглавлению

19. ANSI SQL?

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

к оглавлению

20. Основные элементы баз данных таблицы процедуры функции констрейнты и тд?

Поле — это минимальный элемент базы данных, содержащий один неделимый квант информации. Каждое поле характеризуется именем и типом хранящихся в нем данных.

Запись — это совокупность нескольких разнородных полей, описывающая некоторую сущность предметной области.

Таблица базы данных — это набор однородных записей.

Хранимая процедура — объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения.

CREATE [OR REPLACE] PROCEDURE имя_процедуры
[ (параметр [, параметр, …]) ] IS
            [локальные объявления]
        BEGIN
            исполняемые предложения
        [EXCEPTION
            обработчики исключений]
        END [имя_процедуры];

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

CREATE [OR REPLACE] FUNCTION имя_функции
[ (параметр [, параметр, …]) ]
RETURN тип_данных IS | AS
            [локальные объявления]
        BEGIN
            исполняемые предложения
        [EXCEPTION
            обработчики исключений]
        END [имя_функции];

Констрейнты (constraints) — объявление правил (ограничения), которым должны соответствовать данные в таблице.

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

Например для задания первичного ключа, можно использовать такую запись:

CONSTRAINT <имя ограничения> PRIMARY KEY (<список столбцов, являющихся первичным ключом>)

Типы констрейнтов:

  • NOT NULL — колонка не может содержать NULL значений.
  • UNIQUE — каждая строка в колонке должна иметь уникальное значение.
  • PRIMARY KEY — комбинация NOT NULL и UNIQUE.
  • FOREIGN KEY — обеспечивает ссылочную целостность. Означает, что ссылающиеся данные имеют соответствие в другой таблице.
  • CHECK — проверка на определенное выполнение правил.
  • DEFAULT — задает значение по умолчанию для колонки.

к оглавлению

21. Как вы понимаете null в базах данных?

Смысл NULL-значения — это отсутствие информации или неприменимость данного атрибута в данном кортеже.

NULL-значение может означать неприменимость значения к этому столбцу (например в колонке «скорость полета» для таблицы животные и записи «Слон»)

NULL-значение так же может означать отсутствие информации. Заменять отсутствующие значения, например на -1, ‘ ‘ или что-то такое, некорректно.

к оглавлению

22. Агрегатные функции как они работают с null Не забудьте о group by и having?

Стандартом предусмотрены следующие агрегатные функции:

  • COUNT(*) Возвращает количество строк источника записей.
  • COUNT Возвращает количество значений в указанном столбце.
  • SUM Возвращает сумму значений в указанном столбце.
  • AVG Возвращает среднее значение в указанном столбце.
  • MIN Возвращает минимальное значение в указанном столбце.
  • MAX Возвращает максимальное значение в указанном столбце.

Все эти функции возвращают единственное значение. При этом функции COUNT, MIN и MAX применимы к данным любого типа, в то время как SUM и AVG используются только для данных числового типа. Разница между функцией COUNT(*) и COUNT(имя столбца | выражение) состоит в том, что вторая (как и остальные агрегатные функции) при подсчете не учитывает NULL-значения.

Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, MAX, AVG и SUM).

Если предложение WHERE определяет предикат для фильтрации строк, то предложение HAVING применяется после группировки для определения аналогичного предиката, фильтрующего группы по значениям агрегатных функций. Это предложение необходимо для проверки значений, которые получены с помощью агрегатной функции не из отдельных строк источника записей, определенного в предложении FROM, а из групп таких строк. Поэтому такая проверка не может содержаться в предложении WHERE.

к оглавлению

23. Каким образом лучше добавлять большое количество записей в таблицу?

  • Отключить autocommit.
  • Выполнить insert.
  • Закоммитить вручную.
  • Включить autocommit.

к оглавлению

24. Что такое первая нормальная форма и процесс нормализации Какие бывают нормальные формы?

Первая нормальная форма (1NF) — базовая нормальная форма отношения в реляционной модели данных.

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

Нормальная форма определяется как совокупность требований, которым должно удовлетворять отношение.

Нормальные формы: первая нормальная форма, вторая нормальная форма, третья нормальная форма, нормальная форма Бойса — Кодда, четвёртая нормальная форма, пятая нормальная форма.

к оглавлению

25. В чем смысл индекса СУБД как они устроены как хранятся Как бы вы реализовали тот же функционал?

Индекс (англ. index) — объект базы данных, создаваемый с целью повышения производительности поиска данных. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в произвольном порядке, и их поиск по заданному критерию путем последовательного просмотра таблицы строка за строкой может занимать много времени. Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы и, таким образом, позволяет искать строки, удовлетворяющие критерию поиска. Ускорение работы с использованием индексов достигается в первую очередь за счёт того, что индекс имеет структуру, оптимизированную под поиск — например, сбалансированного дерева.

Обзор типов индексов Oracle, MySQL, PostgreSQL, MS SQL: http://habrahabr.ru/post/102785/

к оглавлению

26. Что такое JDBC API и когда его используют?

JDBC – это стандарт взаимодействия приложения с различными СУБД. JDBC основан на концепции драйверов, позволяющей получать соединение с БД по специальному URL.

JDBC API находятся в пакетах java.sql и javax.sql. С помощью JDBC API можно создавать соединения с БД, выполнять SQL-запросы, хранимые процедуры и обрабатывать результаты. JDBC API упрощает работу с базами данных из Java программ.

к оглавлению

27. Что такое JDBC Driver и какие различные типы драйверов JDBC вы знаете?

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

к оглавлению

28. Как JDBC API помогает достичь слабой связи между Java программой и JDBC Drivers API?

JDBC API используют рефлексию в java для достижения слабой связи между джава программой и JDBC драйверами. Драйвер фактически загружается один раз с помощью Class.forName(), а дальше используются возможности JDBC API в Java. Таким образом мы пишем код не особо задумываясь с какой базой данных мы будем работать. При необходимости достаточно указать другой драйвер и не переписывать большое количество кода.

к оглавлению

29. Что такое JDBC Connection Покажите шаги для подключения программы к базе данных?

JDBC Connection — соединение, установленное с сервером базы данных. Это своего рода сессия или Socket Connection.

Для создания JDBC connection требуется всего два шага:

  1. Зарегистрировать и загрузить драйвер с помощью Class.forName(). Класс драйвера будет зарегистрирован для DriverManager и загружен в память.
  2. Используя DriverManager.getConnection() получить объект Connection. В метод необходимо передать URL базы данных, имя и пароль.
Connection con = null;
try {
    // load the Driver Class
    Class.forName("com.mysql.jdbc.Driver");
 
    // create the connection now
    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/UserDB",
                    "pankaj",
                    "pankaj123");
    } catch (SQLException e) {
            System.out.println("Check database is UP and configs are correct");
            e.printStackTrace();
    } catch (ClassNotFoundException e) {
            System.out.println("Please include JDBC MySQL jar in classpath");
            e.printStackTrace();
    }

к оглавлению

30. Как используется JDBC DriverManager class?

JDBC DriverManager — фабрика, через которую можно получить объект Database Connection. После загрузки JDBC драйвера в память, он регистрирует себя в DriverManager (что можно проверить, посмотрев в исходники JDBC Driver class). DriverManager используется для получения подключения с помощью зарегистрированных драйверов (метод getConnection()).

к оглавлению

31. Как получить информацию о сервере базы данных из java программы?

С помощью объекта интерфейса DatabaseMetaData можно получить детальную информацию о сервере. После подключения к БД мы можем вызывать метод getMetaData() и получить объект DatabaseMetaData. Существует множество методов для получения различной информации, например о версии БД, конфигурации и т.п.

DatabaseMetaData metaData = con.getMetaData();
String dbProduct = metaData.getDatabaseProductName();

к оглавлению

32. Что такое JDBC Statement?

JDBC API Statement используется для выполнения SQL запросов к базе данных. Объект Statement можно получить с помощью метода Connection.getStatement(). Вызывая методы execute(), executeQuery(), executeUpdate() и др., можно выполнять различные статичные SQL запросы.

В случае динамически созданных SQL запросов внутри java программы, когда введенные пользователем данные могут быть не проверенными, можно использовать SQL injection.

По умолчанию только один объект ResultSet для каждого Statement может быть открыт в одно и то же время. Таким образом, если необходимо работать с несколькими объектами ResultSet одновременно, мы должны использовать различные объекты Statement. Все execute() методы в интерфейсе Statement закроют текущий открытый объект ResultSet при выполнении.

к оглавлению

33. Какие различия между execute executeQuery executeUpdate?

Существует несколько способов выполнять SQL-запросы в зависимости от типа этого запроса. Для этого у интерфейса Statement существует три различных метода: executeQuery(), executeUpdate(), а так же execute(). Рассмотрим их отдельно:

  • Самый базовый метод executeQuery() необходим для запросов, результатом которых является один единственный набор значений, таких как у запросов SELECT. Возвращает ResultSet, который не может быть null даже если у результата запроса не было найдено значений.

  • Метод execute() используется, когда операторы SQL возвращают более одного набора данных, более одного счетчика обновлений или и то, и другое. Метод возвращает true, если результатом является ResultSet, как у запроса SELECT. Вернет false, если ResultSet отсутствует, например при запросах вида INSERT, UPDATE. С помощью методов getResultSet() мы можем получить ResultSet, а getUpdateCount() — количество обновленных записей.

  • Метод executeUpdate() используется для выполнения операторов INSERT, UPDATE или DELETE, а также для операторов DDL (Data Definition Language — язык определения данных), например, CREATE TABLE и DROP TABLE. Результатом оператора INSERT, UPDATE, или DELETE является модификация одной или более колонок в нуле или более строках таблицы.

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

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

к оглавлению

34. Что такое JDBC PreparedStatement?

Объект PreparedStatement используется для выполнения прекомпилированных SQL-запросов с или без входных (IN) параметров. Мы можем использовать сеттеры для установки значений в запрос. Т.к. PreparedStatement является предкомпилированным, то он может быть эффективно использован множество раз.

PreparedStatement считается лучшим выбором нежели Statement, т.к. он автоматически обрабатывает специальные символы, а так же предотвращает, так называемые, SQL injection attack (когда в запрос можно подставить свой код).

к оглавлению

35. Как установить NULL значения в JDBC PreparedStatement?

Используя метод setNull() для установки null переменной в качестве параметра. Этот метод принимает индекс и SQL тип в качестве аргументов: s.setNull(10, java.sql.Types.INTEGER);.

к оглавлению

36. Как используется метод getGeneratedKeys в Statement?

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

к оглавлению

37. Какие преимущества в использовании PreparedStatement над Statement?

  • PreparedStatement позволяет предотвратить атаки типа SQL injection, т.к. он автоматически экранирует специальные символы.

  • PreparedStatement позволяет использовать динамические запросы с внедрением параметров.

  • PreparedStatement быстрее Statement. Это особенно заметно при частом использовании PreparedStatement или при использовании для вызова группы запросов.

  • PreparedStatement позволяет писать объектно ориентированный код с использованием сеттеров\геттеров. А при применении Statement необходимо использовать конкатенацию строк для создания запроса. Для больших запросов конкатенация выглядит, как минимум, большой, а так же несет в себе большой риск ошибки в запросе.

к оглавлению

38. Какие есть ограничения PreparedStatement и как их преодолеть?

В PreparedStatement нельзя использовать напрямую запросы с IN (входными) параметрами. Есть некоторые обходные пути:

  • Выполнить Single Queries – низкая производительность и вообще не рекомендуется так делать.

  • Использовать Stored Procedure (хранимые процедуры) – являются специфичными для конкретной базы данных и следовательно плохи для приложений с возможностью подключения к различным БД.

  • Создание PreparedStatement Query динамически – это хорошее решение, но с потерей кэширования PreparedStatement.

  • Использование NULL в PreparedStatement Query – хорошее решение, если вы знаете максимальное число переменных IN. Можно расширить до использования неограниченного кол-ва параметров с помощью разбиения на части.

к оглавлению

39. Что такое JDBC ResultSet?

JDBC ResultSet — интерфейс, объект которого создается в результате запроса к базе данных. Его можно представить в виде таблицы данных, которая была сформирована в ответ на запрос.

Объект ResultSet поддерживает курсор, который указывает на текущую строку данных. При инициализации курсор устанавливается до первой строки. Для движения по строкам используется метод next(). При наличии строк после текущей позиции, метод next() возвращает true, что можно использовать для итерации по таблице полученных результатов.

По умолчанию объект ResultSet НЕмодифицируемый и поддерживает курсор, который способен только к движение вперед. Для обхода такого ограничения можно использовать следующую конструкцию, которая даст возможность двунаправленного движения по таблице, а так же возможности обновления:

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                   ResultSet.CONCUR_UPDATABLE);

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

к оглавлению

40. Какие существуют различные типы JDBC ResultSet?

При создании Statement можно задать различные типы получаемого ResultSet.

Три типа объектов ResultSet:

  1. ResultSet.TYPE_FORWARD_ONLY: тип по умолчанию. Поддерживает движение курсора только в прямом направлении.
  2. ResultSet.TYPE_SCROLL_INSENSITIVE: Двунаправленный курсор. Объект не чувствителен к изменениям, которые произошли с таблицей после получения результата.
  3. ResultSet.TYPE_SCROLL_SENSITIVE: Двунаправленный курсор. Объект чувствителен к изменениям, которые произошли с базой данных после создания объекта ResultSet.

Два типа потокобезопасных объектов ResultSet:

  • ResultSet.CONCUR_READ_ONLY: Поддерживает только чтение (read only). Применяется по умолчанию.
  • ResultSet.CONCUR_UPDATABLE: Поддерживает метод ResultSet update для обновления строк в таблице данных.

к оглавлению

41. Как используются методы setFetchSize и SetMaxRows в Statement?

Для ограничения количества строк, которые может вернуть запрос, применяется метод setMaxRows(int i). Конечно, этот результат можно получить используя SQL запрос (например для MySQL существует команда LIMIT).

Для понимания метода setFetchSize() необходимо разобраться с работой Statement и ResultSet. Когда выполняется запрос к базе данных, результат обрабатывается и сохраняется в кэше базы данных и возвращается в виде ResultSet. ResultSet является курсором, который ссылается на результат в базе данных. Допустим, мы имеем запрос, который возвращает 100 строк и мы установили setFetchSize(10). Теперь для каждого обращения к базе данных выделено только 10 строк и понадобится 10 запросов, чтобы получить все данные. Выбор оптимального количества fetchSize() может улучшить производительность выполнения большого кол-ва обращений к каждой строке и в случае большого количества строк в выходном результате.

Значение fetchSize можно указать внутри объекта Statement, но оно может быть переопределено в объекте ResultSet с помощью setFetchSize().

к оглавлению

42. Как вызвать Stored Procedures используя JDBC API?

Хранимые процедуры — это группы SQL запросов, которые компилируются в базе данных и могут быть вызваны с помощью JDBC API. Для вызова хранимых процедур используется объект CallableStatement. Нам необходимо задать параметры выхода OUT до выполнения CallableStatement.

CallableStatement stmt = con.prepareCall("{call insertEmployee(?,?,?,?,?,?)}");
stmt.setInt(1, id);
stmt.setString(2, name);
stmt.setString(3, role);
stmt.setString(4, city);
stmt.setString(5, country);
 
//register the OUT parameter before calling the stored procedure
stmt.registerOutParameter(6, java.sql.Types.VARCHAR);
              
stmt.executeUpdate();

к оглавлению

43. Что такое JDBC Batch Processing и каковы его преимущества?

Иногда необходимо выполнить сразу группу похожих запросов, например, при загрузке данных из CSV файлов реляционной базы данных. Это можно сделать просто используя Statement или PreparedStatement для пошагового выполнения этих запросов. В JDBC API существует другой вариант, который предоставляет возможность выполнить группу запросов за один раз. Выполнение такого рода задачи происходит с помощью JDBC API Batch Processing.

JDBC API поддерживает пакетную обработку с помощью методов addBatch() и executeBatch() у Statement и PreparedStatement. К преимуществам такого подхода относится более быстрая работа, т.к. кол-во обращений к базе данных можно существенно уменьшить.

к оглавлению

44. Что такое JDBC Transaction Management и зачем он нужен?

По умолчанию, при создании подключения к базе данных будет выбран auto-commit mode. Это означает, что при каждом выполнении запроса он будет подтвержден автоматически по завершению. Каждый SQL запрос является транзакционным и выполняя какие-либо DML или DDL запросы по их завершению изменения будут приняты (сохранены) базой данных. Если у нас есть необходимость отказываться от сохранения выполнения какого-либо запроса (или групп запросов) в случае, если что-то пошло не так, то мы можем воспользоваться поддержкой транзакций в JDBC API.

С помощью метода setAutoCommit(boolean flag) можно отключить автокоммит в конкретном соединении. Следует отметить, что при отключении auto-commit ни одно изменение не будет сохранено в базе данных до вызова метода commit() и за этим необходимо следить. Сервер базы данных будет блокировать необходимую часть базы данных до подтверждения транзакции, а т.к. это ресурсоёмкая задача, то подтверждать транзакцию необходимо сразу после выполнения задачи.

к оглавлению

45. Как откатить JDBC транзакцию?

Для этого предусмотрен метод объекта Connection rollback(), который откатывает транзакцию. Будут отменены все изменения в транзакции и отменен lock базы данных от этого объекта Connection.

к оглавлению

46. Что такое JDBC Savepoint и как он используется?

JDBC Savepoint позволяет создавать «чекпоинты» в транзакции с помощью которых мы можем откатить не всю транзакцию целиком, а только часть до точки сохранения. Любая точка сохранения автоматически освобождается и становится недоступной после подтверждения транзакции или её роллбека. Откат к точке сохранения делает все последующие сейвы недоступными и к ним уже нельзя будет вернуться.

к оглавлению

47. Расскажите о JDBC DataSource Какие преимущества он дает?

JDBC DataSource является интерфейсом пакета javax.sql и является более продвинутым в сравнении с DriverManager для подключения к базе данных. Мы можем использовать DataSource для создания подключения к базе данных и реализацию класса драйвера, которая будет выполнять всю работу по поддержанию соединения.

В дополнение к соединению через Database, DataSource предоставляет следующие дополнительные возможности:

  • Кэширование PreparedStatement для ускорения обработки запросов;
  • Настройки Connection timeout;
  • Возможности логирования;
  • Порог максимального размера ResultSet;
  • Поддержка Connection Pooling в контейнере сервлетов, использующий поддержку JNDI.

к оглавлению

48. Расскажите про Apache DBCP API?

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

Apache DBCP API помогает нам избавиться от этих проблем, предоставляя реализацию DataSource, который работает в качестве уровня абстракции между нашей программой и различными драйверами JDBC. Библиотека Apache DBCP API основана на библиотеке Commons Pool library, поэтому необходимо удостовериться, что все необходимые зависимости правильно подключены к проекту.

к оглавлению

49. Какие вы знаете уровни изоляции соединений в JDBC?

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

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

Такие блокировки необходимы, чтобы предотвратить:

  • грязное чтение (Dirty Read),
  • неповторяющиеся чтение (Non-Repeatable Read),
  • и фантомное чтение (Phantom-Read).

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

Isolation Level Transaction Dirty Read Non-Repeatable Read Phantom Read
TRANSACTION_NONE Not Supported Not Applicable Not Applicable Not Applicable
TRANSACTION_READ_COMMITTED Supported Prevented Allowed Allowed
TRANSACTION_READ_UNCOMMITTED Supported Allowed Allowed Allowed
TRANSACTION_REPEATABLE_READ Supported Prevented Prevented Allowed
TRANSACTION_SERIALIZABLE Supported Prevented Prevented Prevented

к оглавлению

50. Что вы знаете о JDBC RowSet Какие существуют различные типы RowSet?

JDBC RowSet содержит табличные данные в более гибком формате по сравнению с ResultSet. Все объекты RowSet являются производными из ResultSet, так что они имеют все возможности ResultSet с некоторыми дополнительными функциями. RowSet интерфейс определяется в пакете javax.sql. Можно выделить следующие дополнительные функции, предоставляемые RowSet:

Функции похожие на Java Beans со свойствами и get\set методами для работы с ними. RowSet использует модель событий JavaBeans. В результате можно посылать уведомления любого зарегистрированного компонента для таких событий, как движение курсора, обновления/вставка/удаление из строки и изменять содержимое RowSet. Объекты RowSet поддерживают скроллинг по данным (scrollable), а так же являются обновляемыми по умолчанию. Так что, если СУБД не поддерживает скроллинг или обновляемый ResultSet, мы можем использовать RowSet, чтобы получить эти функции.

RowSet можно разделить на два типа:

  • Connected RowSet Objects – эти объекты подключаются к БД и очень похожи на объекты ResultSet. JDBC API предоставляет только одно подключение объекта RowSet — javax.sql.rowset.JdbcRowSet и это является стандартной реализации класса com.sun.rowset.JdbcRowSetImpl.
  • Disconnected RowSet Objects – эти объекты RowSet не требуют подключения к базе данных. Они более легковесные и могут быть сериализованы. Такие объекты хорошо подходят для передачи данных по сети. Существуют несколько реализаций такого типа объектов.

Кратко рассмотрим четыре реализации Disconnected RowSet Objects:

  1. CachedRowSet — объекты могут получить соединение и выполнить запрос, считать данные ResultSet для заполнения данных RowSet. Мы можем управлять и обновлять данные на время отключения от БД и записать измененные данные при очередном подключении.
  2. WebRowSet получены из CachedRowSet — такие объекты могут читать и записывать XML документы.
  3. JoinRowSet получены из WebRowSet — могут образовывать SQL JOIN без подключения к источнику данных.
  4. FilteredRowSet получены из WebRowSet — поддержка применения критериев фильтрации, поэтому видны только выбранные (полученные) данные.

к оглавлению

51. В чем разница между ResultSet и RowSet?

Объекты RowSet являются производными от ResultSet, поэтому они имеют все возможности ResultSet с некоторыми дополнительными функциями. Одним из существенных преимуществ является возможность работы с данными без соединения с базой, а так же их легковесность и возможность пересылки данных объектов по сети.

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

к оглавлению

52. Приведите пример наиболее распространенных исключений в JDBC?

Некоторые из наиболее распространенных исключений JDBC:

  • java.sql.SQLException — это базовый класс для исключений JDBC.

  • java.sql.BatchUpdateException — возникает при исключительных ситуациях пакетной обработки запросов. Может зависеть от типа драйвера JDBC, который может выбросить взамен базовый SQLException.

  • java.sql.SQLWarning — для предупреждающих сообщений различных SQL операций.

  • java.sql.DataTruncation — когда значения данных неожиданно усекаются по причинам, не зависящим от превышения MaxFieldSize.

к оглавлению

53. Расскажите о типах данных CLOB и BLOB в JDBC?

Character Large Objects (CLOBs) — тип данных (внутренний символьный объект), используемый для хранения больших объектов. При выборе значения любого LOB-типа посредством оператора SELECT возвращается указатель, а не само значение; кроме того, типы LOB могут быть и внешними. Этот тип данных является подходящим для хранения текстовой информации, которая может выходить за пределы обычного типа данных VARCHAR (верхний предел 32 Кбайт).

Внутренний большой двоичный объект (BLOB - Binary Large Object — двоичный большой объект) — двоичный объект большого размера, который может содержать переменное количество данных. Этот тип данных может хранить данные объемом более VARBINARY (32K предел). Тип данных, предназначенный, в первую очередь, для хранения изображений, аудио и видео, а также компилированного программного кода.

к оглавлению

54. Что вы знаете о грязном чтении dirty read в JDBC Какой уровень изоляции предотвращает этот тип чтения?

«Грязное» чтение (англ. dirty read) — чтение данных, добавленных или изменённых транзакцией, которая впоследствии не подтвердится (откатится). Получение недействительного в последствии значения (после отката транзакции) может приводить к непредвиденным результатам.

Транзакция 1 Транзакция 2
SELECT f2 FROM tbl1 WHERE f1=1;
UPDATE tbl1 SET f2=f2+1 WHERE f1=1;
SELECT f2 FROM tbl1 WHERE f1=1;
ROLLBACK WORK;

Dirty Read можно предотвратить, используя следующие уровни изоляции:

  • TRANSACTION_READ_COMMITTED
  • TRANSACTION_REPEATABLE_READ
  • TRANSACTION_SERIALIZABLE

к оглавлению

55. Какие есть две фазы commit?

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

На первом этапе, менеджер транзакций передает commit-request всем ресурсам транзакции.

Если все ресурсы транзакции ответили Оk, то менеджер транзакций фиксирует изменения транзакций для всех ресурсов.

Если какой-либо из ресурсов транзакций уведомляет об отмене, то менеджер транзакций может откатить все изменения транзакций.

к оглавлению

56. Приведите пример различных типов блокировки в JDBC?

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

Оптимистическая блокировка — не ограничивает модификацию обрабатываемых данных сторонними сессиями, однако перед началом предполагаемой модификации запрашивает значение некоторого выделенного атрибута каждой из строк данных (обычно используется наименование VERSION и целочисленный тип с инициальным значением 0). Перед записью модификаций в базу данных перепроверяется значение выделенного атрибута, и если оно изменилось, то транзакция откатывается или применяются различные схемы разрешения коллизий. Если значение выделенного атрибута не изменилось — производится фиксация модификаций с одновременным изменением значения выделенного атрибута (например, инкрементом) для сигнализации другим сессиям о том, что данные изменились.

Пессимистическая блокировка — накладывается перед предполагаемой модификацией данных на все строки, которые такая модификация предположительно затрагивает. Всё время действия такой блокировки исключена модификация данных из сторонних сессий, данные из блокированных строк доступны согласно уровню изолированности транзакции. По завершению предполагаемой модификации гарантируется непротиворечивая запись результатов.

к оглавлению

57. Как вы понимаете DDL и DML выражения?

Data Definition Language (DDL) (язык описания данных) — это семейство компьютерных языков, используемых в компьютерных программах для описания структуры баз данных. Функции языков DDL определяются первым словом в предложении (часто называемом запросом), которое почти всегда является глаголом.

В случае с SQL это глаголы — «create» («создать»), «alter» («изменить»), «drop» («удалить»).

Data Manipulation Language (DML) (язык управления (манипулирования) данными) — это семейство компьютерных языков, используемых в компьютерных программах или пользователями баз данных для получения, вставки, удаления или изменения данных в базах данных. Функции языков DML определяются первым словом в предложении (часто называемом запросом), которое почти всегда является глаголом.

В случае с SQL эти глаголы — «select» («выбрать»), «insert» («вставить»), «update» («обновить»), и «delete» («удалить»). Это превращает природу языка в ряд обязательных утверждений (команд) к базе данных.

img

к оглавлению

58. Какая разница между javautilDate и javasqlDate?

java.util.Date содержит информацию о дате и времени, тогда как java.sql.Date содержит информацию только о дате, но не имеет информации о времени. Если необходимо сохранить информацию о времени в базе данных, то желательно использовать поля Timestamp или DateTime.

Java.util.Date — основной универсальный объект. В нем просто сохраняется дата (как long).

java.sql.Date расширяет java.util.Date и добавляет следующую функциональность:

  1. toString выводит дату в виде «yyyy-mm-dd», а не как у конкретного языкового стандарта строки (локали Locale).
  2. Добавлен метод valueOf для чтения строк формата «yyyy-mm-dd» и дальнейшего разбора ее в объект sql.Date.

к оглавлению

59. Как вставить изображение или необработанные данные в базу данных?

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

к оглавлению

60. Что вы можете рассказать о фантомном чтении Какой уровень изоляции его предотвращает?

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

Транзакция 1 Транзакция 2
SELECT SUM(f2) FROM tbl1;
INSERT INTO tbl1 (f1,f2) VALUES (15,20);
COMMIT;
SELECT SUM(f2) FROM tbl1;

В транзакции 2 выполняется SQL-оператор, использующий все значения поля f2. Затем в транзакции 1 выполняется вставка новой строки, приводящая к тому, что повторное выполнение SQL-оператора в транзакции 2 выдаст другой результат. Такая ситуация называется фантомным чтением. От неповторяющегося чтения оно отличается тем, что результат повторного обращения к данным изменился не из-за изменения/удаления самих этих данных, а из-за появления новых (фантомных) данных. Phantom read можно предотвратить только на уровне изоляции — TRANSACTION_SERIALIZABLE.

к оглавлению

61. Что такое SQL Warning Как возвратить SQL предупреждения в JDBC программе?

SQLWarning - это подкласс SQLException, который мы можем получить вызвав метод getWarnings() у объектов Connection, Statement, ResultSet. SQL Предупреждения не останавливает выполнение запроса, но показывает предупреждающие сообщения для пользователя.

к оглавлению

62. Как запустить Oracle Stored Procedure с объектами базы данных INOUT?

Если хранимая процедура Oracle содержит IN / OUT параметры как DB объекты, то мы должны создать массив объектов такого же размера в программе, а затем использовать его для создания Oracle STRUCT объекта. Тогда мы можем установить этот STRUCT объект в объект базы данных, вызвав метод setSTRUCT().

к оглавлению

63. Приведите пример возникновения javasqlSQLException No suitable driver found?

Исключение java.sql.SQLException: No suitable driver found может быть вызвано, например, неправильно отформатированной строкой SQL-адреса. Вы можете получить это исключение в простом Java приложении как через DriverManager, так и используя JNDI DataSource.

Трассировка стека исключений приведена ниже:

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class 'com.mysql.jdbc.Driver' for connect URL ''jdbc:mysql://localhost:3306/UserDB'
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1452)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
 
 
java.sql.SQLException: No suitable driver found for 'jdbc:mysql://localhost:3306/UserDB
    at java.sql.DriverManager.getConnection(DriverManager.java:604)
    at java.sql.DriverManager.getConnection(DriverManager.java:221)
    at com.journaldev.jdbc.DBConnection.getConnection(DBConnection.java:24)
    at com.journaldev.jdbc.DBConnectionTest.main(DBConnectionTest.java:15)
Exception in thread "main" java.lang.NullPointerException

к оглавлению

64. Некоторые Best Practices в JDBC?

Некоторые Best Practices в JDBC:

  • Всегда закрывайте ресурсы базы данных сразу после работы с ними. Connection, Statement, ResultSet и другие JDBC объекты имеют метод close(), который позволяет закрыть их.

  • Всегда явно закрывайте результирующий набор ResultSet, Statement и Connection в коде, потому что если вы используете пул соединений, то соединение может быть возвращено в пул, оставляя открытые result sets и statement objects и будет происходить утечка.

  • Закрывайте ресурсы в конце finally блока, чтобы убедиться, что они закрыты даже в случае возникновения исключения.

  • Используйте пакетную обработку (batch processing) для повторяющихся запросов.

  • Всегда используйте PreparedStatement вместо Statement, чтобы избежать SQL Injection и получить преимущества заранее прекомпилированного и кэшированного запроса PreparedStatement.

  • Если вы извлекаете большие массивы данных в ResultSet, то заранее установите оптимальное значение fetchSize, что поможет получить лучшую производительность.

  • Сервер базы данных может не поддерживать все уровни изоляции, так что проверьте их заранее.

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

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

  • Попробуйте использовать отключенный (disconnected) RowSet, когда вам нужно работать с ResultSet в течение длительного времени.

к оглавлению

Источники:

  1. Head First SQL, Линн Бейли

  2. Официальная документация по Postgresql.

  3. Java промышленное программирование 2013 - И. Н. Блинов, В. С. Романчик. Глава 12 JDBC (стр. 342) и Приложение 4 (стр. 793)