Data type negotiation in JDBC

Data types

Let's take a look at the table:

In the third column we see types: INT , VARCHAR , DATE . These are SQL server types. The server means gives the data with those types about which he knows. How are these types converted to Java types?

This is exactly one of the things that has been standardized with JDBC. The creators of JDBC started by fixing a list of SQL types. There is a special class with constants:

class java.sql.Types {
   public static final int CHAR         =   1;
   public static final int NUMERIC    	=   2;
   public static final int DECIMAL     	=   3;
   public static final int INTEGER      =   4;
   public static final int FLOAT        =   6;
   public static final int REAL         =   7;
  …
}

The number is not a serial number in the class, but an ID type in the list of SQL types in the SQL specification. These are the numbers you saw in the example at the beginning of the lecture.

Also in the ResultSet class there are methods that can convert one data type to another. Not all types can be converted to each other, but the logic is clear enough. Here's a good spreadsheet for you:

Among all SQL types, some groups can be clearly distinguished:

• numbers
• time
• lines
• byte objects

By the way, have you noticed the getInt() method ?

JDBC and NULL

Have you noticed what's wrong with the getInt() method of the ResultSet class ? Let's look at its signature again:

int getInt(column)

This method returns an int , not an Integer. Because at the time the JDBC type Integer was created, it didn't exist yet. Okay, let's say. Then I have a question:

I have a table in a database that has an INT NULL column that can be INT but can also be NULL. How can I get the null value from this column?

Don't worry, everything has been thought of for you.

Solution one . If the SQL type in Java is represented by a reference type such as Date or String, then there is no problem . Variables of this type can take null values.

Solution two . Primitive types cannot be null, so methods like getInt() will simply return a default value . For int it is 0, for float = 0.0f, for double = 0.0d and the like.

And how then to understand what was in the column: 0 or NULL? And the party has an answer to this question.

Solution three . The ResultSet class has a special wasNull() method that returns true if the method just returned a different value instead of NULL .

Everything works exactly as I wrote here. Example:

ResultSet results = staatement.executeQuery("SELECT * FROM user");
    int level = results.getInt("level");

if (results.wasNull()) {
    System.out.println("Level is null");
} else {
   System.out.println("Level is " + level);
    }

If it was supposed to return null when calling the getInt() method, then the wasNull() method would return true, otherwise the wasNull() method would return false.

This works not only for primitive types:

ResultSet results = staatement.executeQuery("SELECT * FROM user");
    String name = results.getString("name");

 	if (results.wasNull()) {
 	    System.out.println("Name is null");
 	} else {
 	   System.out.println("User name is " + name);
    }

This is of course a crutch. But no problem with NullPointerException . See the positives in everything :)

What's wrong with data types in JDBC?

Let's continue with the test. Look closely at the getDate(column) method ? What's wrong with him? This method has the following type of result:

java.sql.Date

It can store null, which is good enough. But still, something is wrong with him. Clue! Here's what the correct Date type looks like:

java.util.Date

They have different packages! These are generally different types of data. And here's the reason...

Databases since the 70s of the 20th century support 3 types of data for storing time:

• DATE - stores the date: year, month, day.
• TIME - stores time: hours, minutes, seconds.
• TIMESTAMP - stores a specific point in time: date, time and milliseconds.

The Java language for the first 10 years of its existence had only one data type, java.util.Date , which stored a point in time in UNIX TIME format: the number of milliseconds since the beginning of 1970.

Therefore, the creators of the JDBC standard added three more data types to Java - specifically for JDBC:

• java.sql.date
• java.sql.Time
• java.sqlTimestamp

And so the methods of the ResultSet interface contain fixed data types:

And this is the type you see here:

Guess what's missing here? Data types that have appeared in the Java DateTime API:

• LocalDate
• LocalTime
• LocalDateTime

1

Task

Module 4. Working with databases, level 7, lesson 3

Locked

wasNull method

task0707

Open