Inserting Null Into an Integer Column Using JDBC – 使用JDBC向一个整数列插入空值

1. Introduction

1.绪论

In this article, we’ll examine how we can store null values in our database using plain JDBC. We’ll start by describing the reasons for using null values, followed by several code examples.

在这篇文章中，我们将研究如何使用普通的JDBC在数据库中存储null值。我们将首先描述使用null值的原因，然后是几个代码示例。

2. Using null Values

2.使用null值

null is a keyword that transcends all programming languages. It represents a special value. It’s a common perception that null has no value or that it represents nothing. Having a null stored in a database column means that space is reserved on the hard disk. If an appropriate value becomes available, we can store it in that space.

null是一个超越了所有编程语言的关键词。它代表了一个特殊的值。人们普遍认为null没有价值，或者说它什么都不代表。在数据库列中存储一个null意味着在硬盘上保留了空间。如果有合适的值，我们可以把它存储在这个空间里。

Another perception is that null is equal to zero or a blank string. Zero or a blank string in a specific context can have meaning, for example, zero items in the warehouse. Also, we can execute operations like sum or concat on these two values. But those operations have no meaning when dealing with null.

另一种看法是，null等于零或一个空白字符串。零或一个空白字符串在特定的环境中可以有意义，例如，仓库中的物品为零。另外，我们可以对这两个值执行像sum或concat这样的操作。但这些操作在处理null时没有意义。

Using null values to represent special cases in our data has many advantages. One of those advantages is that most database engines exclude null values from internal functions such as sum or avg. On the other hand, when null is in our code, we can program special actions to mitigate the missing values.

使用null值来表示我们数据中的特殊情况有很多优点。其中一个优点是，大多数数据库引擎将null值排除在内部函数（如sum或avg）之外。另一方面，当null出现在我们的代码中时，我们可以通过编程进行特殊操作来缓解缺失的值。

Bringing null to the table also brings a couple of disadvantages. When writing code that deals with data containing null values, we have to handle that data differently. This can lead to bad-looking code, clutter, and bugs. Also, null values can have a variable length in the database. null stored in Integer and Byte columns will have different lengths.

将null带入桌面也会带来一些缺点。在编写处理含有null值的数据的代码时，我们必须以不同的方式处理这些数据。这可能会导致代码不好看、杂乱无章，以及出现错误。另外，null值在数据库中的长度是可变的。存储在Integer和Byte列中的null会有不同的长度。

3. Implementation

3.实施

For our example, we’ll use a simple Maven module with an H2 in-memory database. No other dependencies are required.

在我们的例子中，我们将使用一个简单的Maven模块，其中有一个H2内存数据库。不需要其他依赖性。

First, let’s create our POJO class named Person. This class will have four fields. Id used as the primary key for our database, name, and lastName, which are strings and age represented as Integer. Age is not a required field and can be null:

首先，让我们创建一个名为Person的POJO类。这个类将有四个字段。Id作为我们数据库的主键，name,和lastName，是字符串，age表示为Integer。Age不是一个必填字段，可以是null：。

public class Person {
    private Integer id;
    private String name;
    private String lastName;
    private Integer age;
    //getters and setters
}

To create a database table that reflects this Java class, we’ll use the following SQL query:

为了创建一个反映这个Java类的数据库表，我们将使用以下SQL查询。

CREATE TABLE Person (id INTEGER not null, name VARCHAR(50), lastName VARCHAR(50), age INTEGER, PRIMARY KEY (id));

With all that out of the way, now we can focus on our main goal. To set a null value into the Integer column, there are two defined ways in the PreparedStatement interface.

说完了这些，现在我们可以专注于我们的主要目标。要在Integer列中设置一个null值，在PreparedStatement接口中有两种定义的方法。

3.1. Using the setNull Method

3.1.使用setNull方法

With the setNull method, we’re always sure that our field value is null before executing the SQL query. This allows us for more flexibility in the code.

有了setNull方法，我们在执行SQL查询之前总是确定我们的字段值是null。这允许我们在代码中获得更多的灵活性。

With the column index, we must also supply the PreparedStatement instance with information about the underlying column type. In our case, this is java.sql.Types.INTEGER.

通过列索引，我们还必须向PreparedStatement实例提供关于底层列类型的信息。在我们的例子中，这就是java.sql.Types.INTEGER。

This method is reserved only for null values. For any other, we must use the appropriate method of PreparedStatement instance:

这个方法只保留给null值。对于任何其他，我们必须使用PreparedStatement实例的适当方法。

@Test
public void givenNewPerson_whenSetNullIsUsed_thenNewRecordIsCreated() throws SQLException {
    Person person = new Person(1, "John", "Doe", null);

    try (PreparedStatement preparedStatement = DBConfig.getConnection().prepareStatement(SQL)) {
        preparedStatement.setInt(1, person.getId());
        preparedStatement.setString(2, person.getName());
        preparedStatement.setString(3, person.getLastName());
        if (person.getAge() == null) {
            preparedStatement.setNull(4, Types.INTEGER);
        }
        else {
            preparedStatement.setInt(4, person.getAge());
        }
        int noOfRows = preparedStatement.executeUpdate();

        assertThat(noOfRows, equalTo(1));
    }
}

In the case that we don’t check whether or not the getAge method returns null and calls the setInt method with a null value, we’ll get a NullPointerException.

如果我们不检查getAge方法是否返回null，而是用null值调用setInt方法，我们将得到一个NullPointerException。

3.2. Using the setObject Method

3.2.使用setObject方法

The setObject method gives us less flexibility to deal with missing data in our code. We can pass the data we have, and the underlying structure will map Java Object types to SQL types.

setObject方法给了我们较少的灵活性来处理我们代码中的缺失数据。我们可以传递我们拥有的数据，而底层结构将把Java Object类型映射为SQL类型。

Note that not all databases will allow passing null without specifying a SQL type. For example, the JDBC driver cannot infer SQL types from null.

请注意，并非所有的数据库都允许在不指定SQL类型的情况下传递null。例如，JDBC驱动程序不能从null.中推断出SQL类型。

To be on the safe side with this method, it’s best to pass a SQL type to the setObject method:

为了安全起见，最好向setObject方法传递一个SQL类型。

@Test
public void givenNewPerson_whenSetObjectIsUsed_thenNewRecordIsCreated() throws SQLException {
    Person person = new Person(2, "John", "Doe", null);

    try (PreparedStatement preparedStatement = DBConfig.getConnection().prepareStatement(SQL)) {
        preparedStatement.setInt(1, person.getId());
        preparedStatement.setString(2, person.getName());
        preparedStatement.setString(3, person.getLastName());
        preparedStatement.setObject(4, person.getAge(), Types.INTEGER);
        int noOfRows = preparedStatement.executeUpdate();

        assertThat(noOfRows, equalTo(1));
    }
}

4. Conclusion

4.总结

In this tutorial, we explained some basic usages of null values in databases. Then we provided examples of how to store null values inside Integer columns with plain JDBC.

在本教程中，我们解释了数据库中null值的一些基本用法。然后，我们举例说明了如何使用普通的JDBC在Integer列中存储null值。

As always, all code can be found over on GitHub.

一如既往，所有的代码都可以在GitHub上找到over。