1. Overview

1.概述

There are many ways we can connect to a MySQL database from Java and in this tutorial, we’re going to explore several options to see how to achieve this.

我们有很多方法可以从Java连接到MySQL数据库，在本教程中，我们将探索几个选项，看看如何实现这一目标。

We’ll start by looking at arguably the most popular options using JDBC and Hibernate.

我们将从使用JDBC和Hibernate的可以说是最流行的选择开始。

Then, we’ll also look at some external libraries including MyBatis, Apache Cayenne and Spring Data. Along the way, we’ll provide a number of practical examples.

然后，我们还将考察一些外部库，包括MyBatis、Apache Cayenne和Spring Data。在此过程中，我们将提供一些实际的例子。

2. Preconditions

2.先决条件

We’ll assume that we already have a MySQL server installed and running on localhost (default port 3306) and that we have a test schema with the following person table:

我们将假设我们已经在localhost上安装并运行了一个MySQL服务器（默认端口为3306），并且我们有一个带有以下人表的测试模式。

CREATE TABLE person 
( 
    ID         INT, 
    FIRST_NAME VARCHAR(100), 
    LAST_NAME  VARCHAR(100)  
);

We’ll also need the mysql-connector-java artifact which as always is available from Maven Central:

我们还需要mysql-connector-java工件，它可以从Maven中心获得。

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.19</version>
</dependency>

3. Connecting Using JDBC

3. 使用JDBC连接

JDBC (Java Database Connectivity) is an API for connecting and executing queries on a database.

JDBC（Java数据库连接）是一个用于连接和执行数据库查询的API。

3.1. Common Properties

3.1.常见属性

During the course of this article, we’ll typically use several common JDBC properties:

在本文的过程中，我们通常会使用几个常见的JDBC属性。

Connection URL – a string that the JDBC driver uses to connect to a database. It can contain information such as where to search for the database, the name of the database to connect to and other configuration properties:
```
jdbc:mysql://[host][,failoverhost...]
    [:port]/[database]
    [?propertyName1][=propertyValue1]
    [&propertyName2][=propertyValue2]...
```
We’ll set this property like so: jdbc:mysql://localhost:3306/test?serverTimezone=UTC

我们将这样设置这个属性。jdbc:mysql://localhost:3306/test?serverTimezone=UTC
Driver class – the fully-qualified class name of the driver to use. In our case, we’ll use the MySQL driver: com.mysql.cj.jdbc.Driver
Username and password – the credentials of the MySQL account

3.2. JDBC Connection Example

3.2.JDBC连接实例

Let’s see how we can connect to our database and execute a simple select-all through a try-with-multiple-resources:

让我们看看如何通过try-with-multiple-resources连接到我们的数据库并执行一个简单的 select-all。

String sqlSelectAllPersons = "SELECT * FROM person";
String connectionUrl = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";

try (Connection conn = DriverManager.getConnection(connectionUrl, "username", "password"); 
        PreparedStatement ps = conn.prepareStatement(sqlSelectAllPersons); 
        ResultSet rs = ps.executeQuery()) {

        while (rs.next()) {
            long id = rs.getLong("ID");
            String name = rs.getString("FIRST_NAME");
            String lastName = rs.getString("LAST_NAME");

            // do something with the extracted data...
        }
} catch (SQLException e) {
    // handle the exception
}

As we can see, inside the try body, we iterate through the result set and extract the values from the person table.

正如我们所看到的，在try体内，我们遍历了结果集，并从人表中提取了值。

4. Connecting Using ORMs

4.使用ORM连接

More typically, we’ll connect to our MySQL database using an Object Relational Mapping (ORM) Framework. So, let’s see some connection examples using the more popular of these frameworks.

更典型的是，我们将使用对象关系映射（ORM）框架连接到我们的MySQL数据库。因此，让我们看看一些使用这些框架中比较流行的连接例子。

4.1. Native Hibernate APIs

4.1.本地Hibernate APIs

In this section, we’ll see how to use Hibernate to manage a JDBC connection to our database.

在本节中，我们将看到如何使用Hibernate来管理与数据库的JDBC连接。

First, we need to add the hibernate-core Maven dependency:

首先，我们需要添加hibernate-coreMaven依赖项。

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>5.4.10.Final</version>
</dependency>

Hibernate requires that an entity class must be created for each table. Let’s go ahead and define the Person class:

Hibernate要求必须为每个表创建一个实体类。让我们继续下去，定义Person类。

@Entity
@Table(name = "Person")
public class Person {
    @Id
    Long id;
    @Column(name = "FIRST_NAME")
    String firstName;

    @Column(name = "LAST_NAME")
    String lastName;
    
    // getters & setters
}

Another essential aspect is to create the Hibernate resource file, typically named hibernate.cfg.xml, where we’ll define configuration information:

另一个重要方面是创建Hibernate资源文件，通常命名为hibernate.cfg.xml，我们将在这里定义配置信息。

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        <!-- Database connection settings -->
        <property name="connection.driver_class">com.mysql.cj.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost:3306/test?serverTimezone=UTC</property>
        <property name="connection.username">username</property>
        <property name="connection.password">password</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.MySQL5Dialect</property>

        <!-- Validate the database schema on startup -->
        <property name="hbm2ddl.auto">validate</property>

        <!-- Names the annotated entity class -->
        <mapping class="Person"/>
    </session-factory>
</hibernate-configuration>

Hibernate has many configuration properties. Apart from the standard connection properties, it is worth mentioning the dialect property which allows us to specify the name of the SQL dialect for the database.

Hibernate有许多配置属性>。除了标准的连接属性外，值得一提的是方言属性，它允许我们为数据库指定SQL方言的名称。

This property is used by the framework to correctly convert Hibernate Query Language (HQL) statements into the appropriate SQL for our given database. Hibernate ships with more than 40 SQL dialects. As we’re focussing on MySQL in this article, we’ll stick with the MySQL5Dialect dialect.

该属性被框架用来将Hibernate查询语言 (HQL)语句正确转换为适合我们给定数据库的SQL。Hibernate带有40多种SQL方言。由于我们在本文中主要关注的是 MySQL，因此我们将坚持使用 MySQL5Dialect 方言。

Finally, Hibernate also needs to know the fully-qualified name of the entity class via the mapping tag. Once we complete the configuration, we’ll use the SessionFactory class, which is the class responsible for creating and pooling JDBC connections.

最后，Hibernate还需要通过映射标签知道实体类的全称。完成配置后，我们将使用SessionFactory类，它是负责创建和池化JDBC连接的类。

Typically, this only needs to be set up once for an application:

一般来说，这只需要为一个应用程序设置一次。

SessionFactory sessionFactory;
// configures settings from hibernate.cfg.xml 
StandardServiceRegistry registry = new StandardServiceRegistryBuilder().configure().build(); 
try {
    sessionFactory = new MetadataSources(registry).buildMetadata().buildSessionFactory(); 
} catch (Exception e) {
    // handle the exception
}

Now that we have our connection set up, we can run a query to select all the people from our person table:

现在我们已经建立了连接，我们可以运行一个查询，从我们的人表中选择所有的人。

Session session = sessionFactory.openSession();
session.beginTransaction();

List<Person> result = session.createQuery("from Person", Person.class).list();
        
result.forEach(person -> {
    //do something with Person instance...   
});
        
session.getTransaction().commit();
session.close();

4.2. MyBatis

MyBatis was introduced in 2010 and is a SQL mapper framework with simplicity as its strength. In another tutorial, we talked about how to integrate MyBatis with Spring and Spring Boot. Here, we’ll focus on how to configure MyBatis directly.

MyBatis于2010年推出，是一个以简单为优势的SQL映射器框架。在另一篇教程中，我们谈到了如何将MyBatis与Spring和Spring Boot集成。在这里，我们将重点讨论如何直接配置MyBatis。

To use it, we need to add the mybatis dependency:

为了使用它，我们需要添加mybatis依赖性。

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.3</version>
</dependency>

Assuming that we reuse the Person class above without annotations, we can proceed to create a PersonMapper interface:

假设我们重用上面的Person类而不加注，我们可以继续创建一个PersonMapper接口。

public interface PersonMapper {
    String selectAll = "SELECT * FROM Person"; 
    
    @Select(selectAll)
    @Results(value = {
       @Result(property = "id", column = "ID"),
       @Result(property = "firstName", column = "FIRST_NAME"),
       @Result(property = "lastName", column = "LAST_NAME")
    })
    List<Person> selectAll();
}

The next step is all about the MyBatis configuration:

下一步是所有关于MyBatis的配置。

Configuration initMybatis() throws SQLException {
    DataSource dataSource = getDataSource();
    TransactionFactory trxFactory = new JdbcTransactionFactory();
    
    Environment env = new Environment("dev", trxFactory, dataSource);
    Configuration config = new Configuration(env);
    TypeAliasRegistry aliases = config.getTypeAliasRegistry();
    aliases.registerAlias("person", Person.class);

    config.addMapper(PersonMapper.class);
    return config;
}

DataSource getDataSource() throws SQLException {
    MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setDatabaseName("test");
    dataSource.setServerName("localhost");
    dataSource.setPort(3306);
    dataSource.setUser("username");
    dataSource.setPassword("password");
    dataSource.setServerTimezone("UTC");
    
    return dataSource;
}

The configuration consists of creating a Configuration object which is a container for settings such as the Environment. It also contains the data source settings.

配置包括创建一个Configuration对象，它是诸如Environment等设置的容器。它还包含数据源设置。

We can then use the Configuration object, which is normally set up once for an application to create a SqlSessionFactory:

然后我们可以使用Configuration对象，该对象通常为一个应用程序设置一次，以创建一个SqlSessionFactory。

Configuration configuration = initMybatis();
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
try (SqlSession session = sqlSessionFactory.openSession()) {
    PersonMapper mapper = session.getMapper(PersonMapper.class);
    List<Person> persons = mapper.selectAll();
    
    // do something with persons list ...
}

4.3. Apache Cayenne

4.3.Apache Cayenne

Apache Cayenne is a persistence framework whose first release dates back to 2002. To learn more about it, we suggest reading our introduction to Apache Cayenne.

Apache Cayenne是一个持久性框架，其首次发布可以追溯到2002年。要了解更多关于它的信息，我们建议阅读我们的Apache Cayenne介绍。

As usual, let’s add the cayenne-server Maven dependency:

像往常一样，让我们添加 cayenne-serverMaven依赖项。

<dependency>
    <groupId>org.apache.cayenne</groupId>
    <artifactId>cayenne-server</artifactId>
    <version>4.0.2</version>
</dependency>

We’re going to specifically focus on the MySQL connection settings. In this case, we’ll configure the cayenne-project.xml:

我们将特别关注MySQL的连接设置。在这种情况下，我们将配置cayenne-project.xml。

<?xml version="1.0" encoding="utf-8"?>
<domain project-version="9"> 
    <map name="datamap"/> 
	<node name="datanode" 
	    factory="org.apache.cayenne.configuration.server.XMLPoolingDataSourceFactory" 
		schema-update-strategy="org.apache.cayenne.access.dbsync.CreateIfNoSchemaStrategy"> 
	    <map-ref name="datamap"/> 
		<data-source>
		    <driver value="com.mysql.cj.jdbc.Driver"/> 
			<url value="jdbc:mysql://localhost:3306/test?serverTimezone=UTC"/> 
			<connectionPool min="1" max="1"/> 
			<login userName="username" password="password"/> 
		</data-source> 
	</node> 
</domain>

After the automatic generation of the datamap.map.xml and Person class in the form of a CayenneDataObject, we can execute some queries.

在自动生成datamap.map.xml和Person类的CayenneDataObject形式后，我们可以执行一些查询。

For example, we’ll continue as previously with a select all:

例如，我们将继续像以前一样，用一个选择全部。

ServerRuntime cayenneRuntime = ServerRuntime.builder()
    .addConfig("cayenne-project.xml")
    .build();

ObjectContext context = cayenneRuntime.newContext();
List<Person> persons = ObjectSelect.query(Person.class).select(context);

// do something with persons list...

5. Connecting Using Spring Data

5. 使用Spring Data进行连接

Spring Data is a Spring-based programming model for data access. Technically, Spring Data is an umbrella project which contains many subprojects that are specific to a given database.

Spring Data是一种基于Spring的数据访问编程模型。从技术上讲，Spring Data是一个伞状项目，它包含许多针对特定数据库的子项目。

Let’s see how to use two of these projects to connect to a MySQL database.

让我们看看如何使用这些项目中的两个项目来连接到MySQL数据库。

5.1. Spring Data / JPA

5.1 Spring Data / JPA

Spring Data JPA is a robust framework that helps reduce boilerplate code and provides a mechanism for implementing basic CRUD operations via one of several predefined repository interfaces. In addition to this, it has many other useful features.

Spring Data JPA是一个强大的框架，它有助于减少模板代码，并通过几个预定义的存储库接口之一提供了实现基本CRUD操作的机制。除此以外，它还有许多其他有用的功能。

Be sure to check out our introduction to Spring Data JPA to learn more.

请务必查看我们的Spring Data JPA 介绍，以了解更多信息。

The spring-data-jpa artifact can be found on Maven Central:

spring-data-jpa工件可以在Maven中心找到。

<dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-jpa</artifactId>
    <version>2.2.4.RELEASE</version>
</dependency>

We’ll continue using the Person class. The next step is to configure JPA using annotations:

我们将继续使用Person类。下一步是使用注解来配置JPA。

@Configuration
@EnableJpaRepositories("packages.to.scan")
public class JpaConfiguration {
    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/test?serverTimezone=UTC");
        dataSource.setUsername( "username" );
        dataSource.setPassword( "password" );
        return dataSource;
    }

    @Bean
    public JpaTransactionManager transactionManager(EntityManagerFactory emf) {
      return new JpaTransactionManager(emf);
    }

    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
      HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
      jpaVendorAdapter.setDatabase(Database.MYSQL);
      jpaVendorAdapter.setGenerateDdl(true);
      return jpaVendorAdapter;
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
      LocalContainerEntityManagerFactoryBean lemfb = new LocalContainerEntityManagerFactoryBean();
      lemfb.setDataSource(dataSource());
      lemfb.setJpaVendorAdapter(jpaVendorAdapter());
      lemfb.setPackagesToScan("packages.containing.entity.classes");
      return lemfb;
    }
}

To allow Spring Data to implement the CRUD operations, we have to create an interface that extends the CrudRepository interface:

为了让Spring Data实现CRUD操作，我们必须创建一个扩展了CrudRepository接口的接口。

@Repository
public interface PersonRepository extends CrudRepository<Person, Long> {

}

And finally, let’s see an example of select-all with Spring Data:

最后，让我们看看一个用Spring Data进行select-all的例子。

personRepository.findAll().forEach(person -> {
    // do something with the extracted person
});

5.2. Spring Data / JDBC

5.2 Spring Data / JDBC

Spring Data JDBC is a limited implementation of the Spring Data family, with its primary goal to allow simple access to relational databases.

Spring Data JDBC是Spring Data系列的有限实现，其主要目标是允许简单地访问关系型数据库。

For this reason, it doesn’t provide features like caching, dirty tracking, lazy loading, and many other JPA features.

由于这个原因，它不提供像缓存、脏跟踪、懒加载和许多其他JPA功能。

This time the Maven dependency we need is spring-data-jdbc:

这次我们需要的Maven依赖项是spring-data-jdbc。

<dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-jdbc</artifactId>
    <version>1.1.4.RELEASE</version>
</dependency>

The configuration is lighter compared to the one we used in the previous section for Spring Data JPA:

与我们在上一节中用于Spring Data JPA的配置相比，该配置更轻。

@Configuration
@EnableJdbcRepositories("packages.to.scan")
public class JdbcConfiguration extends AbstractJdbcConfiguration {
    // NamedParameterJdbcOperations is used internally to submit SQL statements to the database
    @Bean
    NamedParameterJdbcOperations operations() {
        return new NamedParameterJdbcTemplate(dataSource());
    }

    @Bean
    PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/test?serverTimezone=UTC");
        dataSource.setUsername("username");
        dataSource.setPassword("password");
        return dataSource;
    }
}

In the case of Spring Data JDBC, we have to define a new Person class or modify the existing one to add some Spring specific annotations.

在Spring Data JDBC的情况下，我们必须定义一个新的Person类，或者修改现有的类以添加一些Spring的特定注释。

This is because Spring Data JDBC will take care directly of the entity mapping instead of Hibernate:

这是因为Spring Data JDBC将直接处理实体映射问题，而不是Hibernate。

import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Column;
import org.springframework.data.relational.core.mapping.Table;

@Table(value = "Person")
public class Person {
    @Id
    Long id;

    @Column(value = "FIRST_NAME")
    String firstName;

    @Column(value = "LAST_NAME")
    String lastName;

    // getters and setters
}

With Spring Data JDBC, we can also use the CrudRepository interface. So the declaration will be identical to the one we wrote above in the Spring Data JPA example. Likewise, the same applies to the select-all example.

通过Spring Data JDBC，我们也可以使用CrudRepository接口。所以声明将与我们上面在Spring Data JPA例子中写的声明相同。同样地，这也适用于select-all的例子。

6. Conclusion

6.结语

In this tutorial, we have seen several different ways to connect to a MySQL database from Java. We started with the essential JDBC connection. Then we looked at commonly used ORMs like Hibernate, Mybatis, and Apache Cayenne. Finally, we took a look at Spring Data JPA and Spring Data JDBC.

在本教程中，我们看到了从Java连接到MySQL数据库的几种不同方法。我们从基本的JDBC连接开始。然后我们研究了常用的ORM，如Hibernate、Mybatis和Apache Cayenne。最后，我们看了一下Spring Data JPA和Spring Data JDBC。

Using JDBC or Hibernate APIs means more boilerplate code. Using robust frameworks, such as Spring Data or Mybatis, require more configuration but give a significant advantage because they provide default implementations and features like caching and lazy loading.

使用JDBC或Hibernate APIs意味着更多的模板代码。使用健壮的框架，如Spring Data或Mybatis，需要更多的配置，但却有很大的优势，因为它们提供了默认的实现和功能，如缓存和懒惰加载。