Руководство по Spring. Шаблон JDBC в Spring.

Когда нам приходится работать с простым JDBC, такие операции, как открытие/закрытие соединения с базой данных (далее – БД), обработка исключений и т.д. делают код крайне громоздким и сложным для чтения.

Реализация JDBC в Spring Framework берёт на себя работу со многими низкоуровневыми операциями (открытие/закрытие соединений, выполнение SQL-запросов и т.д.).

Благодаря этому при работе с БД в Spring Framework, нам необходимо только  определить параметры соединения с БД и прописать SQL-запрос, остальную работу за нас выполняет Spring.

Сам класс находится в пакете

org.springframework.jdbc.core

JDBC в Spring имеет несколько классов (несколько подходов) для взаимодействия с БД. Наиболее распространённые из них – это использование класса JdbcTemplate. Это базовый класс, который управляет обработкой всех событий и связями с БД.

Класс JdbcTemplate выполняет SQL-запросы, выполняет итерации по ResultSet и извлекает вызываемые значения, обновляет инструкции и вызовы процедур, “ловит” исключения и транслирует их в исключения, определённые в пакете org.springframwork.dao.

Экземпляры класса JdbcTemplate являются потокозащищёнными. Это значит, что настроив единственный экземпляр класса JdbcTemplate, мы можем затем его использовать для нескольких объектов DAO.

При использовании JdbcTemplate, чаще всего, он конфигурируется в конфигурационном файле Spring. После этого он внедряется помощью как бин в классы DAO.

Для более глубокого понимания JdbcTemplate рассмотрим пример небольшого приложения.

Пример приложения:

Прежде всего, нам необходимо добавить зависимости MySQL-connector в наш файл pom.xml

Зависимость можно найти ЗДЕСЬ.

Для начала создадим базу данных с таблицей DEVELOPERS:

SQL-запрос для создания таблицы DEVELOPERS

CREATE TABLE DEVELOPERS(
   ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(50) NOT NULL,
   SPECIALTY VARCHAR(50) NOT NULL,
   EXPERIENCE INT NOT NULL,
   PRIMARY KEY (ID)
);

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

Класс Developer.java

package net.proselyte.jdbc.model;

public class Developer {
    private Integer id;
    private String name;
    private String specialty;
    private Integer experience;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSpecialty() {
        return specialty;
    }

    public void setSpecialty(String specialty) {
        this.specialty = specialty;
    }

    public Integer getExperience() {
        return experience;
    }

    public void setExperience(Integer experience) {
        this.experience = experience;
    }

    @Override
    public String toString() {
        return "Developer:" + "\n" +
                "ID: " + id + "\n" +
                "Name: " + name + "\n" +
                "Specialty: " + specialty + "\n" +
                "Experience: " + experience + "\n";
    }
}

Интерфейс DeveloperDao.java

package net.proselyte.jdbc.dao;

import net.proselyte.jdbc.model.Developer;

import javax.sql.DataSource;
import java.util.List;

public interface DeveloperDao {
    public void setDataSource(DataSource dataSource);

    public void createDeveloper(String name, String specialty, Integer experience);

    public Developer getDeveloperById(Integer id);

    public List listDevelopers();

    public void removeDeveloper(Integer id);

    public void updateDeveloper(Integer id, String name, String specialty, Integer experience);
}

Класс-имплементация интерфейса DeveloperDao – JdbcTemplateDeveloperDaoImpl.java

package net.proselyte.jdbc.dao.jdbc;

import net.proselyte.jdbc.dao.DeveloperDao;
import net.proselyte.jdbc.model.Developer;
import net.proselyte.jdbc.util.DeveloperMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.util.List;

public class JdbcTemplateDeveloperDaoImpl implements DeveloperDao {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    @Override
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public void createDeveloper(String name, String specialty, Integer experience) {
        String SQL = "INSERT INTO DEVELOPERS (name, specialty, experience) VALUES (?,?,?)";

        jdbcTemplate.update(SQL, name, specialty, experience);
        System.out.println("Developer successfully created.\nName: " + name + ";\nSpecilaty: " +
                specialty + ";\nExperience: " + experience + "\n");
    }

    @Override
    public Developer getDeveloperById(Integer id) {
        String SQL = "SELECT * FROM DEVELOPERS WHERE id = ?";
        Developer developer = jdbcTemplate.queryForObject(SQL, new Object[]{id}, new DeveloperMapper());
        return developer;
    }

    @Override
    public List listDevelopers() {
        String SQL = "SELECT * FROM DEVELOPERS";
        List developers = jdbcTemplate.query(SQL, new DeveloperMapper());
        return developers;
    }

    @Override
    public void removeDeveloper(Integer id) {
        String SQL = "DELETE FROM DEVELOPERS WHERE id = ?";
        jdbcTemplate.update(SQL, id);
        System.out.println("Developer with id: " + id + " successfully removed");
    }

    @Override
    public void updateDeveloper(Integer id, String name, String specialty, Integer experience) {
        String SQL = "UPDATE DEVELOPERS SET name = ?, specialty = ?, experience = ? WHERE id = ?";
        jdbcTemplate.update(SQL, name, specialty, experience, id);
        System.out.println("Developer with id: " + id + " successfully updated.");
    }
}

Класс DeveloperMapper.java

package net.proselyte.jdbc.util;

import net.proselyte.jdbc.model.Developer;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

public class DeveloperMapper implements RowMapper{
    @Override
    public Developer mapRow(ResultSet rs, int rowNum) throws SQLException {
        Developer developer = new Developer();
        developer.setId(rs.getInt("id"));
        developer.setName(rs.getString("name"));
        developer.setSpecialty(rs.getString("specialty"));
        developer.setExperience(rs.getInt("experience"));
        return developer;
    }
}

Конфигурационный файл jdbctemplate-developer-config.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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">

    <!-- Data source initialization -->
    <bean id="dataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/вставьте_имя_вашей_базы_данных"/>
        <property name="username" value="вставьте_ваше_имя_пользователя"/>
        <property name="password" value="вставьте_ваш_пароль"/>
    </bean>

    <!-- Here we're defining studentJDBCTemplate bean -->
    <bean id="jdbcTemplateDeveloperDao"
          class="net.proselyte.jdbc.dao.jdbc.JdbcTemplateDeveloperDaoImpl">
        <property name="dataSource"  ref="dataSource" />
    </bean>

</beans>

Класс JdbcDeveloperRunner.java

package net.proselyte.jdbc;

import net.proselyte.jdbc.dao.jdbc.JdbcTemplateDeveloperDaoImpl;
import net.proselyte.jdbc.model.Developer;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;

public class JdbcDeveloperRunner {
    public static void main(String[] args) {
        ApplicationContext context =
                new ClassPathXmlApplicationContext("jdbctemplate-developer-config.xml");

        JdbcTemplateDeveloperDaoImpl jdbcTemplateDeveloperDao =
                (JdbcTemplateDeveloperDaoImpl) context.getBean("jdbcTemplateDeveloperDao");

        System.out.println("========Creating new records to DB========");
        jdbcTemplateDeveloperDao.createDeveloper("Proselyte", "Java Developer", 3);
        jdbcTemplateDeveloperDao.createDeveloper("Petr", "C++ Developer", 2);
        jdbcTemplateDeveloperDao.createDeveloper("DesignerAsya", "UI Developer", 1);

        System.out.println("========Developers List========");
        List developers = jdbcTemplateDeveloperDao.listDevelopers();
        for (Developer developer : developers) {
            System.out.println(developer);
        }

        System.out.println("========Some changes to DB========");
        jdbcTemplateDeveloperDao.updateDeveloper(33, "DesignerAsya", "UI Developer", 2);
        jdbcTemplateDeveloperDao.removeDeveloper(32);

        System.out.println("========Final Developers List========");
        List finalDevelopers = jdbcTemplateDeveloperDao.listDevelopers();
        for (Developer developer : finalDevelopers) {
            System.out.println(developer);
        }
    }
}

Результат работы программы

/usr/lib/jvm/java-8-oracle/bin/java -Didea.launcher.port=7533 -Didea.launcher.bin.path=/home/proselyte/Programming/Soft/IntellijIdea/bin -Dfile.encoding=UTF-8 -classpath /usr/lib/jvm/java-8-oracle/jre/lib/management-agent.jar:/usr/lib/jvm/java-8-oracle/jre/lib/plugin.jar:/usr/lib/jvm/java-8-oracle/jre/lib/rt.jar:/usr/lib/jvm/java-8-oracle/jre/lib/jsse.jar:/usr/lib/jvm/java-8-oracle/jre/lib/charsets.jar:/usr/lib/jvm/java-8-oracle/jre/lib/jce.jar:/usr/lib/jvm/java-8-oracle/jre/lib/resources.jar:/usr/lib/jvm/java-8-oracle/jre/lib/deploy.jar:/usr/lib/jvm/java-8-oracle/jre/lib/jfxswt.jar:/usr/lib/jvm/java-8-oracle/jre/lib/javaws.jar:/usr/lib/jvm/java-8-oracle/jre/lib/jfr.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/dnsns.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/sunpkcs11.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/sunec.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/sunjce_provider.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/jaccess.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/nashorn.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/localedata.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/zipfs.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/cldrdata.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/jfxrt.jar:/home/proselyte/Programming/IdeaProjects/ProselyteTutorials/target/classes:/home/proselyte/.m2/repository/org/springframework/spring-core/4.1.1.RELEASE/spring-core-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/commons-logging/commons-logging/1.1.3/commons-logging-1.1.3.jar:/home/proselyte/.m2/repository/org/springframework/spring-web/4.1.1.RELEASE/spring-web-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/org/springframework/spring-beans/4.1.1.RELEASE/spring-beans-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/org/springframework/spring-context/4.1.1.RELEASE/spring-context-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/org/springframework/spring-aop/4.1.1.RELEASE/spring-aop-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/aopalliance/aopalliance/1.0/aopalliance-1.0.jar:/home/proselyte/.m2/repository/org/springframework/spring-jdbc/4.1.1.RELEASE/spring-jdbc-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/org/springframework/spring-tx/4.1.1.RELEASE/spring-tx-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/org/springframework/spring-webmvc/4.1.1.RELEASE/spring-webmvc-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/org/springframework/spring-expression/4.1.1.RELEASE/spring-expression-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/org/aspectj/aspectjtools/1.8.8/aspectjtools-1.8.8.jar:/home/proselyte/.m2/repository/javax/servlet/servlet-api/2.5/servlet-api-2.5.jar:/home/proselyte/.m2/repository/mysql/mysql-connector-java/5.1.38/mysql-connector-java-5.1.38.jar:/home/proselyte/Programming/Soft/IntellijIdea/lib/idea_rt.jar com.intellij.rt.execution.application.AppMain net.proselyte.jdbc.JdbcDeveloperRunner
Feb 08, 2016 5:48:11 PM org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@246b179d: startup date [Mon Feb 08 17:48:11 EET 2016]; root of context hierarchy
Feb 08, 2016 5:48:11 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [jdbctemplate-developer-config.xml]
Feb 08, 2016 5:48:11 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
========Creating new records to DB========
Mon Feb 08 17:48:11 EET 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Developer successfully created.
Name: Proselyte;
Specilaty: Java Developer;
Experience: 3

Mon Feb 08 17:48:12 EET 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Mon Feb 08 17:48:12 EET 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Developer successfully created.
Name: Petr;
Specilaty: C++ Developer;
Experience: 2

Mon Feb 08 17:48:13 EET 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Developer successfully created.
Name: DesignerAsya;
Specilaty: UI Developer;
Experience: 1

========Developers List========
Developer:
ID: 31
Name: Proselyte
Specialty: Java Developer
Experience: 3

Developer:
ID: 32
Name: Petr
Specialty: C++ Developer
Experience: 2

Developer:
ID: 33
Name: DesignerAsya
Specialty: UI Developer
Experience: 1

========Some changes to DB========
Mon Feb 08 17:48:13 EET 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Developer with id: 33 successfully updated.
Mon Feb 08 17:48:13 EET 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Developer with id: 32 successfully removed
========Final Developers List========
Mon Feb 08 17:48:13 EET 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Developer:
ID: 31
Name: Proselyte
Specialty: Java Developer
Experience: 3

Developer:
ID: 33
Name: DesignerAsya
Specialty: UI Developer
Experience: 2

В этой статье мы ознакомились с основами использования JDBC в Spring Framework.