Работа с базой данных через JDBC

Синопсис

JDBC предоставляет java-приложениям стандартный способ доступа к данным, хранящимся в базе данных. Для того, чтобы инфраструктура JDBC могла работать с базой данных, нужен специфичный для конкретной базы данных драйвер.
Сначала этот драйвер загружается в память, затем он регистрируется с помощью класса java.sql.DriverManager. Класс java.sql.DriverManager управляет списком драйверов и предоставляет статические методы для установления подключения к базе данных.
Статический метод getConnection() класса java.sql.DriverManager возвращает интерфейс java.sql.Connection реализация которого предоставляется драйвером. Этот интерфейс позволяет запускать SQL-операторы для любой базы данных (в данном случае для той, для которой зарегистрирован драйвер).
В этом посте рассмотрим взаимодействие с базой данных через чистый JDBC. Так как подключение к базе данных через JDBC является затратным, кроме того для каждого запроса к базе данных будет создаваться новое подключение, то вариант работы приложения с базой данных приведенный в этом посте не является правильным решением, смысл тут в том, чтобы по работать с базой данных через чистый JDBC чтобы иметь представление о том, какие действия предпринимаются для управления подключением JDBC, потому что в обычных проектах работать с JDBC на прямую не придется, а в следующем посте рассмотрим более правильные способы, например, через пул подключений.

Похожие посты

Работа с базой данных через Spring

Hibernate (конфигурация в стиле xml)

Hibernate (конфигурация в стиле аннотации)

Hibernate (конфигурация spring)

Основы JPA

JPA и Spring

Простой Spring MVC

Весь проект можно взять с gitHub: https://github.com/dev-blogs/database/tree/master/simple-jdbc-database

Структура базы данных Warehouse

Создадим базу данных складского учета. База данных будет состоять и трех таблицы: items — в которой будут храниться товары, providers в которой будет храниться информация о поставщиках, warehouses в коротой будет храниться информация о складе, например адрес его расположения и одной связывающей таблицы items_providers для связки таблиц items и providers по отношению много ко многим. Так как таблицы items и providers связаны по типу многие ко многим, это означает, что один поставщик может поставить энное количество разных типов товаров, тогда как один товар может быть необязательно поставлен только одним поставщиком. Для аналогии представьте себе, что вы пошли в магазин и накупили много разных товаров, а на сладующий день вы пошли в другой магазин и там накупили много разных товаров, некоторые из которых так же были купленны в первом магазине днем ранее.
А таблица warehouses связана с таблицей items связкой один ко многим, то есть один склад может содержать в себе множество товаров, тогда как один товар может храниться только на одном складе.

Схема базы данных

Диаграмма сущность-отношение или entity-relationship ER:

    ┌──────────────┐                                  ┌──────────────┐
    │ items        │                                  │ providers    │
    ├──────────────┤ 1                              1 ├──────────────┤
    │*id           │<──┐                          ┌──>│*id           │
  M │ name         │   │                          │   │ name         │
┌──>│∞warehouse_id │   │                          │   └──────────────┘
│   └──────────────┘   │                          │
│                      │    ┌─────────────────┐   │
│                      │    │ items_providers │   │
│                      │ M  ├─────────────────┤   │
│                      └───>│∞item_id         │ M │
│                           │∞provider_id     │<──┘
│                           └─────────────────┘
│   ┌──────────────┐
│   │ warehouses   │
│ 1 ├──────────────┤
└──>│*id           │
    │ address      │
    └──────────────┘

В таблице items имеется отношение внешнего ключа с таблицей warehouse, которое поддерживает связь по столбцу warehouse_id с первичным ключом таблицы warehouses. Поле address в таблице warehouse уникальное, чтобы исключить случаи, когда у разных складов одинаковые адреса.
sql код для создания базы данных приведен в листинге ниже. Этот листинг надо просто скопировать в любой удобный sql редактор, и вся структура базы данных будет готова.

sql код

SQL-сценарий для создания простой модели данных:

create-data-model.sql

CREATE DATABASE warehouse CHARACTER SET utf8;

USE warehouse;

CREATE TABLE warehouses (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    address VARCHAR(255) NOT NULL,
    UNIQUE UQ_ADDRESS_1 (address),
    PRIMARY KEY (id)
);

CREATE TABLE items (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    warehouse_id INT UNSIGNED,
    PRIMARY KEY (id),
    FOREIGN KEY (warehouse_id) REFERENCES warehouses (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE providers (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE items_providers (
    item_id INT UNSIGNED NOT NULL,
    provider_id INT UNSIGNED NOT NULL,
    FOREIGN KEY (item_id) REFERENCES items (id)
    	ON DELETE CASCADE
    	ON UPDATE CASCADE,
    FOREIGN KEY (provider_id) REFERENCES providers (id)
    	ON DELETE CASCADE
    	ON UPDATE CASCADE
);

SQL-сценарий для наполнения данными:

fill-database.sql

USE warehouse;

INSERT INTO warehouses (address) VALUES ('ul. one, 5');
INSERT INTO warehouses (address) VALUES ('ul. two, 4');
INSERT INTO warehouses (address) VALUES ('ul. three, 7');

INSERT INTO items (name, warehouse_id) VALUES ('red table', 1);
INSERT INTO items (name, warehouse_id) VALUES ('blue table', 1);
INSERT INTO items (name, warehouse_id) VALUES ('green table', 1);
INSERT INTO items (name, warehouse_id) VALUES ('black chair', 1);
INSERT INTO items (name, warehouse_id) VALUES ('red chair', 1);
INSERT INTO items (name, warehouse_id) VALUES ('blue chair', 1);
INSERT INTO items (name, warehouse_id) VALUES ('green chair', 1);
INSERT INTO items (name, warehouse_id) VALUES ('yellow chair', 1);
INSERT INTO items (name, warehouse_id) VALUES ('white chair', 1);
INSERT INTO items (name, warehouse_id) VALUES ('black plates', 1);
INSERT INTO items (name, warehouse_id) VALUES ('green plates', 1);
INSERT INTO items (name, warehouse_id) VALUES ('red plates', 1);
INSERT INTO items (name, warehouse_id) VALUES ('yellow plates', 1);
INSERT INTO items (name, warehouse_id) VALUES ('grey plates', 1);
INSERT INTO items (name, warehouse_id) VALUES ('large scissors', 2);
INSERT INTO items (name, warehouse_id) VALUES ('small scissors', 2);
INSERT INTO items (name, warehouse_id) VALUES ('red spoon', 2);
INSERT INTO items (name, warehouse_id) VALUES ('grey spoon', 2);
INSERT INTO items (name, warehouse_id) VALUES ('green spoon', 2);
INSERT INTO items (name, warehouse_id) VALUES ('yellow spoon', 2);
INSERT INTO items (name, warehouse_id) VALUES ('white spoon', 2);
INSERT INTO items (name, warehouse_id) VALUES ('yellow fork', 2);
INSERT INTO items (name, warehouse_id) VALUES ('red fork', 2);
INSERT INTO items (name, warehouse_id) VALUES ('black fork', 2);
INSERT INTO items (name, warehouse_id) VALUES ('green fork', 2);
INSERT INTO items (name, warehouse_id) VALUES ('blue fork', 2);
INSERT INTO items (name, warehouse_id) VALUES ('brown fork', 2);
INSERT INTO items (name, warehouse_id) VALUES ('yellow blinds', 3);
INSERT INTO items (name, warehouse_id) VALUES ('red blinds', 3);
INSERT INTO items (name, warehouse_id) VALUES ('green blinds', 3);
INSERT INTO items (name, warehouse_id) VALUES ('black blinds', 3);
INSERT INTO items (name, warehouse_id) VALUES ('white blinds', 3);
INSERT INTO items (name, warehouse_id) VALUES ('brown blinds', 3);
INSERT INTO items (name, warehouse_id) VALUES ('grey sofa', 3);
INSERT INTO items (name, warehouse_id) VALUES ('red sofa', 3);
INSERT INTO items (name, warehouse_id) VALUES ('yellow sofa', 3);
INSERT INTO items (name, warehouse_id) VALUES ('black sofa', 3);
INSERT INTO items (name, warehouse_id) VALUES ('white sofa', 3);
INSERT INTO items (name, warehouse_id) VALUES ('white cupboard', 3);
INSERT INTO items (name, warehouse_id) VALUES ('red cupboard', 3);
INSERT INTO items (name, warehouse_id) VALUES ('blue cupboard', 3);
INSERT INTO items (name, warehouse_id) VALUES ('green cupboard', 3);

INSERT INTO providers (name) VALUES ('Provider 1');
INSERT INTO providers (name) VALUES ('Provider 2');
INSERT INTO providers (name) VALUES ('Provider 3');
INSERT INTO providers (name) VALUES ('Provider 4');
INSERT INTO providers (name) VALUES ('Provider 5');

INSERT INTO items_providers (item_id, provider_id) VALUES (1, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (2, 2);
INSERT INTO items_providers (item_id, provider_id) VALUES (3, 2);
INSERT INTO items_providers (item_id, provider_id) VALUES (4, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (5, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (6, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (7, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (8, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (9, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (10, 2);
INSERT INTO items_providers (item_id, provider_id) VALUES (11, 2);
INSERT INTO items_providers (item_id, provider_id) VALUES (12, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (13, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (14, 2);
INSERT INTO items_providers (item_id, provider_id) VALUES (15, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (16, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (17, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (18, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (19, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (20, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (21, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (22, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (23, 2);
INSERT INTO items_providers (item_id, provider_id) VALUES (24, 2);
INSERT INTO items_providers (item_id, provider_id) VALUES (25, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (26, 2);
INSERT INTO items_providers (item_id, provider_id) VALUES (27, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (28, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (29, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (30, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (31, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (32, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (33, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (34, 2);
INSERT INTO items_providers (item_id, provider_id) VALUES (35, 2);
INSERT INTO items_providers (item_id, provider_id) VALUES (36, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (37, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (38, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (39, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (40, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (41, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (42, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (1, 3);
INSERT INTO items_providers (item_id, provider_id) VALUES (7, 1);
INSERT INTO items_providers (item_id, provider_id) VALUES (7, 2);

Для того, чтобы быстро создать базу данных и заполнить ее данными нужно последовательно выполнить сценарии create-data-model.sql и fill-database.sql.
Открываем командную строку и вводим:

mysql -u databaseUser -p < create-data-model.sql
mysql -u databaseUser -p < fill-database.sql

После выполнения этих скриптов, должна создаться база, показанная ниже:

providers:        warehouses:
┌────┬───────┐    ┌────┬───────────────────────┐
│ id │ name  │    │ id │    address            │
├────┼───────┤    ├────┼───────────────────────┤
│  1 │ Vasya │    │  1 │ ul. one, 5            │
│  2 │ Petya │    │  2 │ ul. two, 4            │
│  3 │ Sasha │    │  3 │ ul. tree, 7           │
│  4 │ Anna  │    └────┴───────────────────────┘
│  5 │ Lena  │
└────┴───────┘  
items:                                    items_providers:
┌────┬────────────────┬──────────────┐    ┌──────────┬───────────────┐
│ id │ name           │ warehouse_id │    │ item _id │ provider_id   │
├────┼────────────────┼──────────────┤    ├──────────┼───────────────┤
│  1 │ red table      │       1      │    │     1    │       1       │
│  2 │ blue table     │       1      │    │     2    │       2       │
│  3 │ green trable   │       1      │    │     3    │       2       │
│  4 │ black chair    │       1      │    │     4    │       1       │
│  5 │ red chair      │       1      │    │     5    │       3       │
│  6 │ blue chair     │       1      │    │     6    │       1       │
│  7 │ green chair    │       1      │    │     7    │       3       │
│  8 │ yellow chair   │       1      │    │     8    │       1       │
│  9 │ white chair    │       1      │    │     9    │       1       │
│ 10 │ black plates   │       1      │    │    10    │       2       │
│ 11 │ green plates   │       1      │    │    11    │       2       │
│ 12 │ red plates     │       1      │    │    12    │       1       │
│ 13 │ yellow plates  │       1      │    │    13    │       3       │
│ 14 │ grey plates    │       1      │    │    14    │       2       │
│ 15 │ large scissors │       2      │    │    15    │       1       │
│ 16 │ small scissors │       2      │    │    16    │       1       │
│ 17 │ red spoon      │       2      │    │    17    │       3       │
│ 18 │ grey spoon     │       2      │    │    18    │       1       │
│ 19 │ green spoon    │       2      │    │    19    │       3       │
│ 20 │ yellow spoon   │       2      │    │    20    │       3       │
│ 21 │ white spoon    │       2      │    │    21    │       3       │
│ 22 │ yellow fork    │       2      │    │    22    │       1       │
│ 23 │ red fork       │       2      │    │    23    │       2       │
│ 24 │ black fork     │       2      │    │    24    │       2       │
│ 25 │ green fork     │       2      │    │    25    │       1       │
│ 26 │ blue fork      │       2      │    │    26    │       2       │
│ 27 │ brown fork     │       2      │    │    27    │       3       │
│ 28 │ yellow blinds  │       3      │    │    28    │       1       │
│ 29 │ red blinds     │       3      │    │    29    │       1       │
│ 30 │ green blinds   │       3      │    │    30    │       3       │
│ 31 │ black blinds   │       3      │    │    31    │       1       │
│ 32 │ white blinds   │       3      │    │    32    │       3       │
│ 33 │ brown blinds   │       3      │    │    33    │       3       │
│ 34 │ grey sofa      │       3      │    │    34    │       2       │
│ 35 │ red sofa       │       3      │    │    35    │       2       │
│ 36 │ yellow sofa    │       3      │    │    36    │       3       │
│ 37 │ black sofa     │       3      │    │    37    │       3       │
│ 38 │ white sofa     │       3      │    │    38    │       1       │
│ 39 │ white cupboard │       3      │    │    39    │       1       │
│ 40 │ red cupboard   │       3      │    │    40    │       3       │
│ 41 │ blue cupboard  │       3      │    │    41    │       3       │
│ 42 │ green cupboard │       3      │    │    42    │       1       │
└────┴────────────────┴──────────────┘    │     1    │       3       │
                                          │     7    │       1       │
                                          │     7    │       2       │
                                          └──────────┴───────────────┘

Структура проекта

simple-jdbc-database
    ├──src
    │   ├─main
    │   │   ├─java
    │   │   │   └─com
    │   │   │       └─dev
    │   │   │           └─blogs
    │   │   │               ├─DataSource.java
    │   │   │               ├─dao
    │   │   │               │   ├─ItemDao.java
    │   │   │               │   ├─WarehouseDao.java
    │   │   │               │   └─impl
    │   │   │               │       ├─ItemDaoImpl.java
    │   │   │               │       └─WarehouseDaoImpl.java
    │   │   │               └─model
    │   │   │                   ├─Item.java
    │   │   │                   ├─Provider.java
    │   │   │                   └─Warehouse.java
    │   │   └─resources
    │   │       ├─jdbc.properties
    │   │       ├─log4j.properties
    │   │       ├─spring-context.xml
    │   │       ├─create-data-model.sql
    │   │       └─fill-database.sql
    │   └─test
    │       └─java
    │           └─com
    │               └─dev
    │                   └─blogs
    │                       └─TestSimpleJdbc.java
    └──pom.xml

Java код

Классы предметной области. Пакет com.dev.blogs.model

Item.java

package com.dev.blogs.model;

import java.security.Provider;
import java.util.HashSet;
import java.util.Set;

public class Item {
	public static final String TABLE_NAME = "items";
	public static final String ID_COLUMN = "id";
	public static final String NAME_COLUMN = "name";
	public static final String WAREHOUSE_ID_COLUMN = "warehouse_id";
	
	private Long id;
    private String name;
    private Long warehouse_id;
    private Set<Provider> providers = new HashSet<Provider>();
 
    public Long getId() {
        return id;
    }
 
    public void setId(Long id) {
        this.id = id;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public Set<Provider> getProviders() {
        return providers;
    }
 
    public void setProviders(Set<Provider> providers) {
        this.providers = providers;
    }
    
    public Long getWarehouse_id() {
		return warehouse_id;
	}

	public void setWarehouse_id(Long warehouse_id) {
		this.warehouse_id = warehouse_id;
	}

	public String toString() {
    	return "Item[id=" + this.id + ", name=" + this.name + "]";
    }
}

Provider.java

package com.dev.blogs.model;

import java.util.HashSet;
import java.util.Set;

public class Provider {
	private Long id;
    private String name;
    private Set<Item> items = new HashSet<Item>();
 
    public Long getId() {
        return id;
    }
 
    public void setId(Long id) {
        this.id = id;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public Set<Item> getItems() {
        return items;
    }
 
    public void setItems(Set<Item> items) {
        this.items = items;
    }
    
    public String toString() {
    	return "Provider[id=" + this.id + ", name=" + this.name + "]";
    }
}

Warehouse.java

package com.dev.blogs.model;

import java.util.HashSet;
import java.util.Set;

public class Warehouse {
	public static final String TABLE_NAME = "warehouses";
	public static final String ID_COLUMN = "id";
	public static final String ADDRESS_COLUMN = "address";
	
	private Long id;
    private String address;
    private Set<Item> items = new HashSet<Item>();
 
    public Long getId() {
        return id;
    }
 
    public void setId(Long id) {
        this.id = id;
    }
 
    public String getAddress() {
        return address;
    }
 
    public void setAddress(String address) {
        this.address = address;
    }
 
    public Set<Item> getItems() {
        return items;
    }
 
    public void setItems(Set<Item> items) {
        this.items = items;
    }
    
    public String toString() {
    	return "Warehouse[id=" + this.id + ", address=" + this.address + "]";
    }
}

Пакет com.dev.blogs.dao - уровень доступа к данным.
Добавим интерфейсы Dao, которые инкапсулируют все службы доступа к данным для информации о складе. В этих интерфейсах определены все методы которые объединяются в термин CRUD (Create, Read, Update, Delete) - создание, чтение, обновление и удаление.

ItemDao.java

package com.dev.blogs.dao;

import java.util.List;
import com.dev.blogs.model.Item;

public interface ItemDao {
	public static final String SQL_FIND_ALL = "select * from " + Item.TABLE_NAME;
	public static final String SQL_FIND_BY_ID = SQL_FIND_ALL + " where " + Item.ID_COLUMN + " = ?";
	public static final String SQL_INSERT = "insert into " + Item.TABLE_NAME + " (" + Item.NAME_COLUMN + ", " + Item.WAREHOUSE_ID_COLUMN + ") values (?, ?)";
	public static final String SQL_UPDATE = "update " + Item.TABLE_NAME + " set " + Item.NAME_COLUMN + " where " + Item.ID_COLUMN + " = ?";
	public static final String SQL_DELETE = "delete from " + Item.TABLE_NAME + " where " + Item.ID_COLUMN + " = ?";
	
	public List<Item> findAll();
	public Item findById(Long id);
	public void insert(Item item);
	public void update(Item item);
	public void delete(Item item);
}

WarehouseDao.java

package com.dev.blogs.dao;

import java.util.List;
import com.dev.blogs.model.Warehouse;

public interface WarehouseDao {
	public static final String SQL_FIND_ALL = "select * from " + Warehouse.TABLE_NAME;
	public static final String SQL_FIND_BY_ID = SQL_FIND_ALL + " where " + Warehouse.ID_COLUMN + " = ?";
	public static final String SQL_INSERT = "insert into " + Warehouse.TABLE_NAME + " (" + Warehouse.ADDRESS_COLUMN + ") values (?)";
	public static final String SQL_UPDATE = "update " + Warehouse.TABLE_NAME + " set " + Warehouse.ADDRESS_COLUMN +" = ? where " + Warehouse.ID_COLUMN + " = ?";
	public static final String SQL_DELETE = "delete from " + Warehouse.TABLE_NAME + " where " + Warehouse.ID_COLUMN + " = ?";	
	
	public List<Warehouse> findAll();
	public Warehouse findById(Long id);
	public void insert(Warehouse warehouse);
	public void update(Warehouse warehouse);
	public void delete(Warehouse warehouse);
}

Пакет com.dev.blogs.dao.impl
Реализация интерфейсов предоставляющих службы доступа к данным.

ItemDaoImpl.java

package com.dev.blogs.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.dev.blogs.dao.ItemDao;
import com.dev.blogs.model.Item;

@Component
public class ItemDaoImpl implements ItemDao {
	@Autowired
	private DataSource dataSource;

	public List<Item> findAll() {
		List<Item> result = new ArrayList<Item>();
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			PreparedStatement statement = connection.prepareStatement(SQL_FIND_ALL);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				Item item = new Item();
				item.setId(rs.getLong(Item.ID_COLUMN));
				item.setName(rs.getString(Item.NAME_COLUMN));
				result.add(item);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	public Item findById(Long id) {
		Item item = null;
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			PreparedStatement statement = connection.prepareStatement(SQL_FIND_BY_ID);
			statement.setLong(1, id);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				item = new Item();
				item.setId(rs.getLong(Item.ID_COLUMN));
				item.setName(rs.getString(Item.NAME_COLUMN));
				item.setWarehouse_id(rs.getLong(Item.WAREHOUSE_ID_COLUMN));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return item;
	}

	public void insert(Item item) {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			PreparedStatement statement = connection.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS);
			statement.setString(1, item.getName());
			statement.setLong(2, item.getWarehouse_id());
			statement.execute();
			
			ResultSet generatedkeys = statement.getGeneratedKeys();
			if (generatedkeys.next()) {
				item.setId(generatedkeys.getLong(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void update(Item item) {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			PreparedStatement statement = connection.prepareStatement(SQL_UPDATE);
			statement.setString(1, item.getName());
			statement.setLong(2, item.getId());
			statement.execute();			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void delete(Item item) {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			PreparedStatement statement = connection.prepareStatement(SQL_DELETE);
			statement.setLong(1, item.getId());
			statement.execute();			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

WarehouseDaoImpl.java

package com.dev.blogs.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.dev.blogs.dao.WarehouseDao;
import com.dev.blogs.model.Warehouse;

@Component
public class WarehouseDaoImpl implements WarehouseDao {
	@Autowired
	private DataSource dataSource;
	
	public List<Warehouse> findAll() {
		List<Warehouse> result = new ArrayList<Warehouse>();
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			PreparedStatement statement = connection.prepareStatement(SQL_FIND_ALL);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				Warehouse warehouse = new Warehouse();
				warehouse.setId(rs.getLong("id"));
				warehouse.setAddress(rs.getString("address"));
				result.add(warehouse);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}
	
	public Warehouse findById(Long id) {
		Warehouse warehouse = null;
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			PreparedStatement statement = connection.prepareStatement(SQL_FIND_BY_ID);
			statement.setLong(1, id);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				warehouse = new Warehouse();
				warehouse.setId(rs.getLong("id"));
				warehouse.setAddress(rs.getString("address"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return warehouse;
	}

	public void insert(Warehouse warehouse) {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			PreparedStatement statement = connection.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS);
			statement.setString(1, warehouse.getAddress());
			statement.execute();
			
			ResultSet generatedkeys = statement.getGeneratedKeys();
			if (generatedkeys.next()) {
				warehouse.setId(generatedkeys.getLong(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void update(Warehouse warehouse) {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			PreparedStatement statement = connection.prepareStatement(SQL_UPDATE);
			statement.setString(1, warehouse.getAddress());
			statement.setLong(2, warehouse.getId());
			statement.execute();			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void delete(Warehouse warehouse) {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			PreparedStatement statement = connection.prepareStatement(SQL_DELETE);
			statement.setLong(1, warehouse.getId());
			statement.execute();			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

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

PreparedStatement

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

// todo:

Пакет com.dev.blogs

Класс DataSource оборачивает класс java.sql.DriverManager и возвращает соединение с базой данных в виде класса реализующего интерфейс java.sql.Connection. По сути он управляет соединением с базой данных. В место него можно было бы использовать спринговый класс org.springframework.jdbc.datasource.DriverManagerDataSource, который делает тоже самое, но я решил использовать свой класс, чтобы лучше понять как реализованы спринговые источники данных, единственное чем отличается мой класс, от библиотечного тем, что я не реализовывал интерфейс javax.sql.DataSource и не придерживался ни каких конвенций, поэтому имена моих методов будут отличаться от методов библиотечных классов. Так же в место него можно и даже нужно использовать класс org.apache.commons.dbcp.BasicDataSource который доступный в Apache или класс источника данных, реализованный одним из серверов приложений такими как JBoss, WebSphere, WebLogic, GlassFish и пр. которые могут увеличить производительность приложения.

DataSource.java

package com.dev.blogs;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DataSource {
	private String url;
	private String login;
	private String password;
	
	public void setUrl(String url) {
		this.url = url;
	}

	public void setLogin(String login) {
		this.login = login;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, login, password);
	}
	
	public void closeConnection(Connection connection) {
		if (connection == null) return;
		try {
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

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

Тест

Пакет com.dev.blogs

TestSimpleJdbc.java

package com.dev.blogs;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNull;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractJUnit4SpringContextTests;
import com.dev.blogs.dao.ItemDao;
import com.dev.blogs.dao.WarehouseDao;
import com.dev.blogs.model.Item;
import com.dev.blogs.model.Warehouse;

@ContextConfiguration("classpath:spring-context.xml")
public class TestSimpleJdbc extends AbstractJUnit4SpringContextTests {
	@Autowired
	private WarehouseDao warehouseDao;
	@Autowired
	private ItemDao itemDao;
	
	@Test
	public void testWarehouseCRUD() {
		String testData = "test address";
		// Создать тестовый объект
		Warehouse testWarehouse = new Warehouse();
		testWarehouse.setAddress(testData);
		// Сохранить тестовый объект в базе данных
		warehouseDao.insert(testWarehouse);
		// Вытащить тестовый объект из базы данных
		Warehouse warehouseFromDb = warehouseDao.findById(testWarehouse.getId());
		// Сравнить вытащенный объект из базы данных с тестовым объектом
		assertEquals(testWarehouse.getAddress(), warehouseFromDb.getAddress());
		// Удалить тестовый объект в базе данных
		warehouseDao.delete(warehouseFromDb);
		// Найти удаленный объект в базе данных
		Warehouse removedWarehouse = warehouseDao.findById(warehouseFromDb.getId());
		// Сравнить вытащенный объект после удаления из базы данных на null
		assertNull(removedWarehouse);
	}
	
	@Test
	public void testItemCRUD() {
		String testData = "test address";
		// Создать тестовый объект
		Item testItem = new Item();
		testItem.setName(testData);
		testItem.setWarehouse_id(1l);
		// Сохранить тестовый объект в базе данных
		itemDao.insert(testItem);
		// Вытащить тестовый объект из базы данных
		Item itemFromDb = itemDao.findById(testItem.getId());
		// Сравнить вытащенный объект из базы данных с тестовым объектом
		assertEquals(testItem.getName(), itemFromDb.getName());
		// Удалить тестовый объект в базе данных
		itemDao.delete(itemFromDb);
		// Найти удаленный объект в базе данных
		Item removedItem = itemDao.findById(itemFromDb.getId());
		// Сравнить вытащенный объект после удаления из базы данных на null
		assertNull(removedItem);
	}
}

spring-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"	
	xmlns:jdbc="http://www.springframework.org/schema/jdbc"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
						http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
						http://www.springframework.org/schema/context
						http://www.springframework.org/schema/context/spring-context-2.5.xsd
						http://www.springframework.org/schema/jdbc
						http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd">

	<jdbc:embedded-database id="dataSource" type="H2">
		<jdbc:script location="classpath:create-data-model.sql" />
		<jdbc:script location="classpath:fill-database.sql" />
	</jdbc:embedded-database>

	<context:component-scan base-package="com.dev.blogs" />
</beans>

jdbc.properties

jdbc.url=jdbc:mysql://localhost:3306/warehouse
jdbc.login=user
jdbc.password=password

Сборка и запуск

Для того чтобы запустить проект, нужно добавить зависимость mysql-connector-java (строчки 35-39):

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.dev.blogs</groupId>
	<artifactId>inner-database</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>inner-database</name>
	<url>http://maven.apache.org</url>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<spring.version>3.2.7.RELEASE</spring.version>
	</properties>

	<dependencies>
		<!-- jdbc:embedded-database -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<version>1.3.160</version>
		</dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.11</version>
			<scope>test</scope>
		</dependency>
	</dependencies>
</project>

Теперь запустим проект. Так как у нас только тесты, то тесты и будем запускать. Откройте командную строку и введите:

mvn test
-------------------------------------------------------
 T E S T S
-------------------------------------------------------
Running com.dev.blogs.TestSimpleJdbc
Aug 10, 2015 12:56:54 PM org.springframework.test.context.TestContextManager ret
rieveTestExecutionListeners
INFO: Could not instantiate TestExecutionListener class [org.springframework.tes
t.context.web.ServletTestExecutionListener]. Specify custom listener classes or
make the default listener classes (and their dependencies) available.
Aug 10, 2015 12:56:54 PM org.springframework.beans.factory.xml.XmlBeanDefinition
Reader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-context.xml]

Aug 10, 2015 12:56:55 PM org.springframework.context.support.AbstractApplication
Context prepareRefresh
INFO: Refreshing org.springframework.context.support.GenericApplicationContext@5
ba23b66: startup date [Mon Aug 10 12:56:55 EEST 2015]; root of context hierarchy

Aug 10, 2015 12:56:55 PM org.springframework.core.io.support.PropertiesLoaderSup
port loadProperties
INFO: Loading properties file from class path resource [jdbc.properties]
Aug 10, 2015 12:56:55 PM org.springframework.beans.factory.support.DefaultListab
leBeanFactory preInstantiateSingletons
INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.
DefaultListableBeanFactory@4abdb505: defining beans [org.springframework.beans.f
actory.config.PropertyPlaceholderConfigurer#0,com.dev.blogs.DatabaseUtility#0,it
emDaoImpl,warehouseDaoImpl,org.springframework.context.annotation.internalConfig
urationAnnotationProcessor,org.springframework.context.annotation.internalAutowi
redAnnotationProcessor,org.springframework.context.annotation.internalRequiredAn
notationProcessor,org.springframework.context.annotation.internalCommonAnnotatio
nProcessor,org.springframework.context.annotation.ConfigurationClassPostProcesso
r$ImportAwareBeanPostProcessor#0]; root of factory hierarchy
Tests run: 2, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 1.785 sec

Results :

Tests run: 2, Failures: 0, Errors: 0, Skipped: 0 <---

[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 5.730 s
[INFO] Finished at: 2015-08-10T12:56:56+03:00
[INFO] Final Memory: 15M/169M
[INFO] ------------------------------------------------------------------------

Результатом запуска тестов должно быть Tests run: 2, Failures: 0, Errors: 0, Skipped: 0.

Недостатки JDBC

Очевидный недостаток использования JCDB помимо того, что он не экономит ресурсы базы данных, это то, что приходится повторять одинаковый код.
Когда я готовил код для этого примера сначала я добавил логику доступа к данным в классе WarehouseDaoImpl.java, а потом пришлось тоже самое делать в классе ItemDaoImpl.java, то есть пришлось два раза переписывать одну и то же логику для слоя DAO. Не каждому хватит терпения повторять один и тот же код в каждом классе DAO. Поэтому в следующем посте мы рассмотрим пример доступа к данным по средствам spring, который берет на себя все служебные действия.