Java et bases de données: un aperçu des bibliothèques et des API

Nous invitons les futurs étudiants du cours "Java Developer. Professional" et toutes les personnes intéressées à participer à la leçon ouverte sur le thème "Introduction à Spring Data Jdbc" .



Et maintenant, nous partageons la traduction traditionnelle de matériel utile.














Cet article fournit une vue d'ensemble des bibliothèques et API populaires pour travailler avec des bases de données en Java, notamment JDBC, Hibernate, JPA, jOOQ, Spring Data, etc.





Java et bases de données: une introduction

Chaque fois que vous avez besoin d'interagir avec des bases de données, trois questions se posent:





  • Quelle approche de développement utiliser: java-first ou database-first? Écrire d'abord des classes Java ou des requêtes SQL? La base de données existante sera-t-elle utilisée?





  • SQL-: CRUD- (select from, insert into, update where), ?





  • - (object-relational mapping, ORM)? Java / ?





- :





public class User {

    private Integer id;

    private String firstName;

    private String lastName;

    // Constructor/Getters/Setters....

}
      
      



USERS, :





id





first_name





last_name





1





hansi





huber





2





max





mutzke





3





donald





trump





Java- ?





:





  1. JDBC — .





  2. SQL-, jOOQ Spring JDBC.





  3. ORM, Hibernate JPA





, JDBC. ? , Spring Hibernate, JDBC.





JDBC:

JDBC?

Java — JDBC API (Java Database Connectivity). , , JDBC . , , SQL-, JDBC . 





JDBC , , JDBC JDK / JRE. JDBC- . 





, JDBC, , SQL-, What is JDBC?  (" JDBC?") . 





JDBC

, Users, , , , List<User>



— Java.





: JDBC SQL Java- ( ). :





package com.marcobehler;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JdbcQueries {

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager
                .getConnection("jdbc:mysql://localhost/test?serverTimezone=UTC",
                        "myUsername", "myPassword")) {

            PreparedStatement selectStatement = conn.prepareStatement("select * from users");
            ResultSet rs = selectStatement.executeQuery();

            List<User> users = new ArrayList<>();

            while (rs.next()) { // will traverse through all rows
                Integer id = rs.getInt("id");
                String firstName = rs.getString("first_name");
                String lastName = rs.getString("last_name");

                User user = new User(id, firstName, lastName);
                users.add(user);
            }

        }
    }
}
      
      



:





try (Connection conn = DriverManager
        .getConnection("jdbc:mysql://localhost/test?serverTimezone=UTC",
                "myUsername", "myPassword")) {
      
      



MySQL. DriverManager.getConnection



try-with-resources, , .





PreparedStatement selectStatement = conn.prepareStatement("select * from users");
ResultSet rs = selectStatement.executeQuery();
      
      



SQL- PreparedStatement



. (PreparedStatement



?



, .)





List<User> users = new ArrayList<>();

while (rs.next()) { // will traverse through all rows
    Integer id = rs.getInt("id");
    String firstName = rs.getString("first_name");
    String lastName = rs.getString("last_name");

    User user = new User(id, firstName, lastName);
    users.add(user);
}
      
      



ResultSet



( , SQL-), Java, ResultSet



(getString()



, getInt()



).





( ) :





  • SQL- (: select * from USERS where name = ? and registration_date = ?



    ). SQL-.





  • , , . 





, JDBC : SQL Java .





JDBC

JDBC " ". SQL JDBC, SQL- Java. .





, .





ORM-: Hibernate, JPA

Java-, , Java, SQL. java-first, , Java-, . 





, , - : Java- ( )? Java- ( , ). 





ORM, Hibernate JPA.





Hibernate?

Hibernate — ORM- (Object-Relational Mapping, - ), 2001 (!). 5.4.X, 6.x .





Hibernate , :





  1. () java- - , .





  2. SQL- CRUD-, , .





  3. (HQL, Criteria API) SQL. , "-" SQL.





, . , , , , , JDBC.





create table users (
    id integer not null,
    first_name varchar(255),
    last_name varchar(255),
    primary key (id)
)
      
      



Java.





public class User {

        private Integer id;

        private String firstName;

        private String lastName;

        //Getters and setters are omitted for brevity
}
      
      



, hibernate-core.jar . Hibernate, User.java



Users? Hibernate.





Hibernate

Hibernate , . User.java



Invoices () Users ()?





xml-. xml-, .





, @Entity, @Column @Table. , User.java



, , .





import javax.persistence.Entity;
import javax.persistence.Table;
import javax.persistence.GeneratedValue;
import javax.persistence.Column;
import javax.persistence.Id;

@Entity
@Table(name="users")
public static class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name="first_name")
    private String firstName;

    @Column(name="last_name")
    private String lastName;

        //Getters and setters are omitted for brevity
}
      
      



, :





  1. @Entity — Hibernate, .





  2. @Table — Hibernate, .





  3. @Column — Hibernate, .





  4. @Id @GeneratedValue — Hibernate, .





, , . Hibernate , .





Hibernate (5.x)

Hibernate. , Hibernate, SessionFactory



, .





SessionFactory



Session



. Session, , ( , JDBC-) . Session SQL / HQL / Criteria — .





Hibernate.





Hibernate (> 5.x) , Spring . Hibernate, .





public static void main(String[] args) {
    // Hibernate specific configuration class
    StandardServiceRegistryBuilder standardRegistry
        = new StandardServiceRegistryBuilder()
            .configure()
            .build();

    // Here we tell Hibernate that we annotated our User class
    MetadataSources sources = new MetadataSources( standardRegistry );
    sources.addAnnotatedClass( User.class );
    Metadata metadata = metadataSources.buildMetadata();

    // This is what we want, a SessionFactory!
    SessionFactory sessionFactory = metadata.buildSessionFactory();
}
      
      



.





Hibernate

, SessionFactory



, Session



(, ) SessionFactory



, , , . 





Hibernate / JPA "persistence" (, ), Java . , , : , .. SQL INSERT ().





, SQL : Hibernate .





Session session = sessionFactory.openSession();
User user = new User();
user.setFirstName("Hans");
user.setLastName("Dampf");
// this line will generate and execute the "insert into users" sql for you!
session.save( user );
      
      



JDBC PreparedStatement



, Hibernate , SQL ( !).





, SQL- (select



, update



delete



).





// Hibernate generates: "select from users where id = 1"
User user = session.get( User.class, 1 );

// Hibernate generates: "update users set...where id=1"
session.update(user);

// Hibernate generates: "delete from useres where id=1"
session.delete(user);
      
      



Hibernate Query Language (HQL)

, User



. , SQL-. Hibernate , HQL (Hibernate Query Language).





HQL SQL, Java- . , HQL- (MySQL, Oracle, Postgres . .), , .





, "HQL Java-"? :





List<User> users = session.createQuery("select from User u where u.firstName = 'hans'", User.class).list();

session.createQuery("update User u set u.lastName = :newName where u.lastName = :oldName")
            .executeUpdate();
      
      



SQL-, , (first_name



), (u.firstName



) User.java



! Hibernate HQL SQL . SELECT User.





HQL HQL Hibernate.





Criteria API

HQL- , , ( IDE, IntelliJ). — HQL / SQL ( WHERE ).





Hibernate — Criteria API. Criteria API (1 2), . 1 - Hibernate 6.x, , 2.





Criteria API (v2) . " " . .





, HQL Criteria API.





CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<User> criteria = builder.createQuery( User.class );
Root<User> root = criteria.from( User.class );
criteria.select( root );
criteria.where( builder.equal( root.get( User_.firstName ), "hans" ) );
List<User> users = entityManager.createQuery( criteria ).getResultList();
      
      



, . , if-else



where



.





, "select * from users where firstName =?



" .





Hibernate

Hibernate , . . Hibernate : , (lazy load), . , - .





:





  1. , "Hibernate - , " - . , , Hibernate.





  2. , Hibernate SQL. , , SQL Hibernate' SQL- .





: Hibernate SQL.





Hibernate?

"Java Persistence with Hibernate". 608 , Hibernate. Hibernate.





, (Vlad Mihalcea) (Thorben Janssen). Hibernate .





, Hibernate . , Hibernate.





Java Persistence API (JPA)?

Hibernate, JPA? Hibernate?





JPA — , . JPA , , JPA. JPA, , Hibernate, EclipseLink TopLink.





, , , , (, Criteria API . .) ., JPA-.





, , Hibernate EclipseLink, JPA- . JPA- (Hibernate) , . , JPA — Hibernate.





JPA

  • JPA 1.0 — 2006 .





  • JPA 2.0 — 2009 .





  • JPA 2.1 — 2013 .





  • JPA 2.2 — 2017 .





, , Vlad Mihalcea Thorben Janssen .





Hibernate JPA?

JPA , persistance- (Hibernate, EclipseLink ..) .





JPA Hibernate, JPA Hibernate. , JPQL — HQL . JPQL HQL, .





, JPA , — , , , , Hibernate. Hibernate, EclipseLink TopLink .





: JPA Hibernate?

, , :





  • JPA Hibernate- , JPA.





  • Hibernate ( ).





JPA

JPA , , EntityManagerFactory



, EntityManager



.





, JDBC Hibernate API. JPA API.





EntityManagerFactory factory = Persistence.createEntityManagerFactory( "org.hibernate.tutorial.jpa" );

EntityManager entityManager = factory.createEntityManager();
entityManager.getTransaction().begin();
entityManager.persist( new User( "John Wayne") );
entityManager.persist( new User( "John Snow" ) );
entityManager.getTransaction().commit();
entityManager.close();
      
      



(persist save, EntityManager Session), , Hibernate.





Hibernate, :





package org.hibernate;

public interface Session extends SharedSessionContract, EntityManager, HibernateEntityManager, AutoCloseable {
  // methods
}

// and

public interface SessionFactory extends EntityManagerFactory, HibernateEntityManagerFactory, Referenceable, Serializable, java.io.Closeable {
    // methods
}
      
      



:





  • Hibernate SessionFactory — JPA EntityManagerFactory





  • Hibernate Session — JPA EntityManager





.





JPQL

, JPA — JPQL. , HQL (Hibernate), JPQL HQL, .





, :





// HQL
int updatedEntities = session.createQuery(
        "update Person " +
        "set name = :newName " +
        "where name = :oldName" )
.setParameter( "oldName", oldName )
.setParameter( "newName", newName )
.executeUpdate();

// JPQL
int updatedEntities = entityManager.createQuery(
        "update Person p " +
        "set p.name = :newName " +
        "where p.name = :oldName" )
.setParameter( "oldName", oldName )
.setParameter( "newName", newName )
.executeUpdate();
      
      



Criteria API JPA

HQL JPQL, Criteria API JPA Criteria API Hibernate. Criteria API Hibernate .





JPA?

JPA , Hibernate. EclipseLink (. Hibernate vs Eclipselink) ( ) TopLink.





Hibernate , . , BatooJPA. , JPA .





, , , . Hibernate, , 2020 .





QueryDSL

, QueryDSL, JPA? HQL / JPQL (.. ), Criteria API (2.0).





QueryDSL : Criteria API, .





, QueryDSL - , 2020 , . JPQ, NoSQL , MongoDB Lucene.





QueryDSL, SQL  "select * from users where first_name =: name"







QUser user = QUser.user;
JPAQuery<?> query = new JPAQuery<Void>(entityManager);
List<User> users = query.select(user)
  .from(user)
  .where(user.firstName.eq("Hans"))
  .fetch();
      
      



QUser



? QueryDSL User



, JPA / Hibernate, , .





. JPA Criteria 2.0?





ORM- Java:

ORM- — . — , - JPA SQL.





, , ORM . , , . 





, , Hibernate, SQL .





SQL-:  

, , , (database-first), ORM, Java (java-first).





, () Java-.





jOOQ

jOOQ — (Lukas Eder). , SQL, Java.





, jOOQ :





  1. Java, , jOOQ.





  2. SQL- JDBC .





  3. jOOQ SQL, .





, , jOOQ Users, . jOOQ USERS , :





// "select u.first_name, u.last_name, s.id from USERS u inner join SUBSCRIPTIONS s
// on u.id = s.user_id where u.first_name = :name"
Result<Record3<String, String, String>> result =
create.select(USERS.FIRST_NAME, USERS.LAST_NAME, SUBSCRIPTIONS.ID)
      .from(USERS)
      .join(SUBSCRIPTIONS)
      .on(USERS.SUBSCRIPTION_ID.eq(SUBSCRIPTIONS.ID))
      .where(USERS.FIRST_NAME.eq("Hans"))
      .fetch();
      
      



jOOQ SQL- , CRUD-, POJO .





( , pivot, flashback-, OLAP, , ).





jOOQ.





MyBatis

MyBatis — database-first. MyBatis — IBATIS 3.0, Apache Attic.





MyBatis SQLSessionFactory ( SessionFactory Hibernate). SQLSessionFactory SQL- . SQL- XML, .





:





package org.mybatis.example;
public interface UserMapper {
  @Select("SELECT * FROM users WHERE id = #{id}")
  User selectUser(int id);
}
      
      



XML-:





<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.mybatis.example.UserMapper">
  <select id="selectUser" resultType="User">
    select * from users where id = #{id}
  </select>
</mapper>

      
      



:





UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.selectUser(1);
      
      



MyBatis , User. , , , . XML- .





MyBatis SQL, , , XML SQL- (- if-else-when SQL-).





Jdbi

Jdbi — JDBC, . SQL-. API .





-, Fluent API:





Jdbi jdbi = Jdbi.create("jdbc:h2:mem:test"); // (H2 in-memory database)

List<User> users = jdbi.withHandle(handle -> {
    handle.execute("CREATE TABLE user (id INTEGER PRIMARY KEY, name VARCHAR)");

    // Named parameters from bean properties
    handle.createUpdate("INSERT INTO user(id, name) VALUES (:id, :name)")
            .bindBean(new User(3, "David"))
            .execute();

    // Easy mapping to any type
    return handle.createQuery("SELECT * FROM user ORDER BY name")
            .mapToBean(User.class)
            .list();
});
      
      



-, API:





// Define your own declarative interface
public interface UserDao {
    @SqlUpdate("CREATE TABLE user (id INTEGER PRIMARY KEY, name VARCHAR)")
    void createTable();

    @SqlUpdate("INSERT INTO user(id, name) VALUES (:id, :name)")
    void insertBean(@BindBean User user);

    @SqlQuery("SELECT * FROM user ORDER BY name")
    @RegisterBeanMapper(User.class)
    List<User> listUsers();
}

public class MyApp {
    public static void main(String[] args) {
        Jdbi jdbi = Jdbi.create("jdbc:h2:mem:test");
        jdbi.installPlugin(new SqlObjectPlugin());

        List<User> userNames = jdbi.withExtension(UserDao.class, dao -> {
            dao.createTable();
            dao.insertBean(new User(3, "David"));
            return dao.listUsers();
        });
    }
}

      
      



fluent-jdbc

fluent-jdbc — , Jdbi. JDBC. .





FluentJdbc fluentJdbc = new FluentJdbcBuilder()
        .connectionProvider(dataSource)
        .build();

Query query = fluentJdbc.query();

query
        .update("UPDATE CUSTOMER SET NAME = ?, ADDRESS = ?")
        .params("John Doe", "Dallas")
        .run();

List<Customer> customers = query.select("SELECT * FROM CUSTOMER WHERE NAME = ?")
        .params("John Doe")
        .listResult(customerMapper);
      
      



SimpleFlatMapper

SimpleFlatMapper , , ResultSet JDBC Record jOOQ POJO. , " " , , JDBC, jOOQ, queryDSL, JDBI Spring JDBC.





JDBC:





// will map the resultset to User POJOs
JdbcMapper<DbObject> userMapper =
    JdbcMapperFactory
        .newInstance()
        .newMapper(User.class)


try (PreparedStatement ps = con.prepareStatement("select * from USERS")) {
    ResultSet rs = ps.executeQuery());
    userMapper.forEach(rs, System.out::println);  //prints out all user pojos
}
      
      



Spring JDBC Spring Data

Spring , Spring Data, Spring.





Spring JDBC Template

JDBCTemplate



— Spring (, spring-jdbc). 2001 , Spring Data JDBC.





JDBC- ResultSet, , , @Transactional- Spring.





: JdbcTemplate



NamedParameterJdbcTemplate



. , , .





// plain JDBC template with ? parameters

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

jdbcTemplate.execute("CREATE TABLE users(" +
            "id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))"); (1)

jdbcTemplate.batchUpdate("INSERT INTO users(first_name, last_name) VALUES (?,?)", Arrays.asList("john", "wayne"));  (2)

jdbcTemplate.query(
            "SELECT id, first_name, last_name FROM users WHERE first_name = ?", new Object[] { "Josh" },
            (rs, rowNum) -> new User(rs.getLong("id"), rs.getString("first_name"), rs.getString("last_name"))
    ).forEach(user -> log.info(user.toString()));   (3)

// named JDBC template with :named parameters

NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(datasource);

SqlParameterSource namedParameters = new MapSqlParameterSource().addValue("id", 1);
namedParameterJdbcTemplate.queryForObject(              (4)
  "SELECT * FROM USERS WHERE ID = :id", namedParameters, String.class);
      
      



  1. SQL . , JDBC SQLException, Spring RuntimeException



    .





  2. (?



    ) .





  3. RowMapper



    JDBC ResultSet POJO .





  4. NamedParameterJdbcTemplate



    SQL- (, :id



    ), (?



    ).





, , JdbcTemplate



JDBC API.





Spring

Spring — @Transactional.





, , , Hibernate, jOOQ JPA, .





JPA, EntityManager (, EntityManager — Hibernate Session, JDBC ).





EntityManagerFactory factory = Persistence.createEntityManagerFactory( "org.hibernate.tutorial.jpa" );

EntityManager entityManager = factory.createEntityManager();
entityManager.getTransaction().begin();
entityManager.persist( new Event( "Our very first event!", new Date() ) );
entityManager.persist( new Event( "A follow up event", new Date() ) );
entityManager.getTransaction().commit();
entityManager.close();
      
      



Spring Hibernate / JPA :





@PersistenceContext
private EntityManager entityManager;

@Transactional
public void doSomeBusinessLogic() {
    entityManager.persist( new Event( "Our very first event!", new Date() ) );
    entityManager.persist( new Event( "A follow up event", new Date() ) );
}
      
      



, . , , .





, Java Database Connections & Transactions (" Java"). , .





Spring Data JPA

, Spring Data, " Spring , ". , Fortune 500. ?





Spring Data — :





  1. , : Spring Data JDBC Spring Data JPA.





  2. , Spring Data REST, Spring Data Redis Spring Data LDAP. -.





Spring Data JDBC Spring Data JPA?





Spring Data /DAO SQL-. ( , .)





: —   /DAO .





User.java



, UserRepository



. UserRepository



, findByEmail



, findById



. . , SQL- Users.





User user = userRepository.findByEmail("my@email.com")
      
      



Spring Data , JPA- User (@Entity, @Column, @Table ..) ! , CRUD- (save, delete, findBy) .





Spring Data JPA- 

. , classpath



spring-data-{jdbc|jpa}.jar



. :





import org.springframework.data.jpa.repository.JpaRepository;
import com.marcobehler.domain.User;

public interface MyUserRepository extends JpaRepository<User, Long> {

    // JpaRepository contains all of these methods, you do not have to write them yourself!

        List<T> findAll();

        List<T> findAll(Sort sort);

        List<T> findAllById(Iterable<ID> ids);

        <S extends T> List<S> saveAll(Iterable<S> entities);

    // and many more...that you can execute without implementing, because Spring Data JPA will
    // automatically generate an implementation for you - at runtime
}
      
      



JpaRepository



Spring Data ( , JPA), find



/save



( ), JpaRepository



( ).





Spring Data JPA / JDBC

, JPA ( SQL), . , , Ruby on Rails





import org.springframework.data.jpa.repository.JpaRepository;
import com.marcobehler.domain.User;

public interface MyUserRepository extends JpaRepository<User, Long> {

  List<User> findByEmailAddressAndLastname(String emailAddress, String lastname);
}
      
      



Spring . SQL: "select * from Users where email_address = :emailAddress and lastName = :lastName



".





Spring Data JDBC ( CrudRepository



). .





import org.springframework.data.repository.CrudRepository;
import org.springframework.data.jdbc.repository.Query;
import com.marcobehler.domain.User;

public interface MyUserRepository extends CrudRepository<User, Long> {

  @Query("select * from Users where email = :emailAddress and lastName = :lastName ")
  List<User> findByEmailAddressAndLastname(@Param("emailAddress") String emailAddress, @Param("lastName") String lastname);
}
      
      



Spring Data JPA ( @Query , ). - .





import org.springframework.data.jpa.repository.Query;

public interface MyUserRepository extends JpaRepository<User, Long> {
    @Query("select u from User u where u.emailAddress = ?1")
    User findByEmailAddress(String emailAddress);
}
      
      



Spring Data:

:





  1. Spring Data — , , . javax.persistence DAO.





  2. Spring Data JPA — JPA / Hibernate. , , , . Spring Data JPA - JPA-, ORM.





  3. Spring Data JDBC — JDBC. JDBC, ORM (, , …). .





, Spring Data Spring- Spring Boot-.





, , Spring Data, .





. . ( , , , ):





  • , , , SQL ( java-). 





  • , .





  • , .. 608 .





  • Hibernate, Hibernate, JPA.





  • jOOQ database-first .





  • . , JPA- jOOQ JDBC. , QueryDSL.






"Java Developer. Professional".



" Spring Data Jdbc".













All Articles