Database

PostgreSQL
MongoDB

JDBC - Java Database Connectivity
ORM - Object Relational Mapping
JPA - Java Persistency API
Hibernate - популярная библиотека ORM, implements JPA
DAO - Data Access Object

Декомпация
Одна строка должна отражать один факт об окружающем мире

Нормальная форма Бойса-Кодда
В любой нетривиальной функциональной зависимости детерминантом является ключ.

**DAO"" (Data Access Object)
Объект доступа к данным
Шаблон проектирования скрывающий детали работы с базой
Обычно один DAO на одну таблицу
Высокоуровневый доступ к данным через DataSet-ы

Варианты операции над базой
Вставка строки - добавление DataSet
Поиск строки по ключу - возврат DataSet-а
Поиск строки по признаку - возврат List<DataSet>
Удаление строки

Анализ запроса

EXPLAIN ANALYZE
-- SQL query

JDBC

API для работы с базами из приложения Java
Предназначена для работы с реляционными базами данных
Не зависит от конкретного типа базы

Connection - объект отвечает за соединение с базой данных и режим работы с ней
Statement - объект представляет выражения обращения к базу
ResultSet - Объект с результатом запроса, который вернула база

Update statement: CREATE, DELETE, INSERT - возвращает число измененных строк
Query statement: SELECT - возвращает ResultSet

Интерфейсы
Statement
PreparedStatement
CallableStatement - для работы с процедурами

URL

            StringBuilder url = new StringBuilder();
 
            url.
                    append("jdbc:mysql://").        //db type
                    append("localhost:").           //host name
                    append("3306/").                //port
                    append("db_example?").          //db name
                    append("user=tully&").          //login
                    append("password=tully");       //password
 
            System.out.println("URL: " + url + "\n");
 
            Connection connection = DriverManager.getConnection(url.toString());

Простой пример JDBC

Connection con = DriverManager.getConnection(urlJdbc, userJdbc, passJdbc);
Statement st = con.createStatement();
st.execute("select * from users");
ResultSet rs = st.getResultSet();
while (rs.next()) {
 System.out.println(rs.getRow() + ". " + rs.getString("column"));
}

Использование PreapareStatement

        Connection connection = ds.getConnection();
        String update = "insert into users(login, password) values(? , ?)";
        PreparedStatement statement = connection.prepareStatement(update);
        statement.setString(1, "login");
        statement.setString(2, "password");
        statement.execute();

Транзакции в JDBC

try (Connection con = DriverManager.getConnection(urlJdbc, userJdbc, passJdbc))
{
    con.setAutoCommit(false); //не сохранять примененные изменения
    Statement st = con.createStatement();
    try
    {
        st.execute(sql);
    }
    catch (SQLException ex)
    {
    }
    con.rollback();  //отменить сделанные изменения
    con.commit(); //сохранить сделанные изменения
}

Тестирование JDBC Spring:

@RunWith(SpringJUnit4ClassRunner.class)
@TestExecutionListeners({DependencyInjectionTestExecutionListener.class})
@ContextConfiguration(locations={"classpath:ApplicationContext-test-DAOs.xml"})
@TransactionConfiguration(defaultRollback = true)
public class ConfirmationMatchingDAOImplTest{
    @Autowired
    private DataSource dataSource;
 
    @Test
    public void shouldTest() throws Exception {
        final Connection connection = dataSource.getConnection();
        final Statement statement = connection.createStatement();
        statement.executeUpdate("insert into TEST_INSERT values (1, 'hello')");
        statement.close();
        connection.close();
    }
}

Тестирование с помощью h2
для тестирование JDBC можно использовать h2, создавая базу данных в памяти

String driverJdbc = "org.h2.Driver";
String urlJdbc = "jdbc:h2:mem:test";
String userJdbc = "su";
String passJdbc = "";
 
Class.forName(driverJdbc);
Connection con = DriverManager.getConnection(urlJdbc, userJdbc, passJdbc);

JPA

Аннотации

@Entity    //Объект класса можно переложить в таблицу
@Table    //связывает класс и таблицу
@Id          //поле является первичным ключом в таблицу
@Column //связывает поле и колонку в таблице

H2

Установка
Downloads
запуск в режиме сервера:

./h2.sh -tcp -tcpAllowOthers -webAllowOthers &

для подключения с удаленных машин:
создать файл ~/.h2.server.properties
добавить в файл webAllowOthers=true

Примеры Java JDBC
Подключение к файлу "test" расположенному "~/h2/"

Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:~/h2/test", "sa", "");
// add application code here
conn.close();

Создание базы данных "test" в памяти

Class.forName("org.h2.Driver");
Connection con = DriverManager.getConnection("jdbc:h2:mem:test", "su", "");

Для тестирования лучше использовать url с параметров LOCK_MODE равном 0 (без блокировок)

Class.forName("org.h2.Driver");
Connection con = DriverManager.getConnection("jdbc:h2:mem:test;LOCK_MODE=0", "su", "");

JDBC url
Для работы в сетевом режиме, для множественных подключений к одной бд
h2 консоль должна быть запущена
для доступа с другого компьютера h2 консоль должна быть настроена для внешки

jdbc:h2:tcp://localhost/~\test-storage

DataSource

            Class.forName("org.h2.Driver");
 
            String url = "jdbc:h2:./h2db";
            String name = "test";
            String pass = "test";
 
            JdbcDataSource ds = new JdbcDataSource();
            ds.setURL(url);
            ds.setUser(name);
            ds.setPassword(pass);

SQLite

Основные команды

скрипт для обновление БД

#!/bin/bash                                                                     
DIRNAME=$(dirname "$0")                                                         
DIRNAME=$(readlink -f "$DIRNAME")                                               
cd "$DIRNAME"                                                                   

rm -f m3_hw3.db                                                                 
cat m3_hw3.sql | sqlite3 m3_hw3.db

SQL

Применение патчей без висения изменений в базу
BEGIN;
скрипт;
ROLLBACK;

SELECT * FROM имяТаблицы WHERE условие ORDER BY поле ASC/DESC
INSERT INTO имяТаблицы(поля...) VALUES (значения...)
UPDATE имяТаблицы SET поле=значение WHERE условие
DELETE FORM имяТаблицы WHERE условие

Создание таблицы с сиквенсом

CREATE SEQUENCE CACHE_ID_SEQ INCREMENT 1 START 1;
CREATE TABLE cache(ID bigint NOT NULL DEFAULT nextval('CACHE_ID_SEQ') PRIMARY KEY, NAME VARCHAR(255));

Типы данных:
VARCHAR(255) - строка
TIMESTAMP - время
INTEGER - целые числа
BOOLEAN - 0/1
DOUBLE - число с точкой
DATE - Дата в формате ГГГГ-ММ-ДД

примеры

DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(ID INT PRIMARY KEY,
NAME VARCHAR(255));
INSERT INTO TEST VALUES(1, 'Hello');
INSERT INTO TEST VALUES(2, 'World');
SELECT * FROM TEST ORDER BY ID;
UPDATE TEST SET NAME='Hi' WHERE ID=1;
DELETE FROM TEST WHERE ID=2;

сортировка
ORDER BY field1 DESC (или ASC), field2 DESC (или ASC)

определения ключей и внешних ключей

CREATE TABLE Foo(
    id INT, 
    another_id INT, 
    some_value VARCHAR(32), 
    PRIMARY KEY(id),
    UNIQUE(another_id));
 
CREATE TABLE Bar(
    id INT,
    foo_id INT,
    FOREIGN KEY(foo_id) REFERENCES Foo(id));
 
DROP TABLE Bar;
DROP TABLE Foo;

Транзакции

-- гарантируем атомарность: обе команды INSERT выполнятся или не выполнятся одновременно
BEGIN TRANSACTION; 
INSERT INTO T1(value) VALUES (200); 
INSERT INTO T2(t1_id) VALUES ((SELECT COUNT(*) FROM T1)); 
COMMIT;

SQL инъекции

тест на уязвимость - добавить в параметр символ '
добавить ' -- (комментирует все что после)

MyBatis (iBatis в девичестве)

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

Отличное стартовое руководство поможет вам сделать свой MyBatis - helloworld.

К особенностям стоит отнести:
конфигурирование в коде или в специальном файле;
удобные псевдонимы для имен классов;
корректное разделение и хранение ваших SQL по произвольному числу особенных файлов;
динамическая безопасная сборка SQL (целых секций) в зависимости от параметров, позволяет сократить число запросов;
SQL просто отлаживать;
Вы видите ошибки SQL отдельно от ошибок Java;
возможность использования аннотаций или map - файлов. Причем аннотации менее популярны.

Пока не указано иное, содержимое этой страницы распространяется по лицензии Creative Commons Attribution-ShareAlike 3.0 License