Java data type and sql data type

Mapping between SQL and Java data types

We saw that when reading from a result set via JDBC, we pull out the column values as Java primitives and objects. So this leads to the question: for a given SQL data type, what is the corresponding type of Java primitive or object that you will obtain from the ResultSet? The answer can be complex, but in practice things aren’t so bad thanks to automatic conversion in many cases:

• there are standard mappings between standard SQL and Java data types for standard data types;
• if you use a non-standard SQL data type, the Java type will depend on the JDBC driver;
• luckily, the getXXX() methods on ResultSet perform automatic conversion in many cases.

Standard SQL-Java data type mappings

The table opposite shows standard mappings between SQL data types and Java primitive and object types. In practice, many databases provide these standard types plus some subtly non-standard variants. You might find that on your column you are using the subtly non-standard variant!

If you are not sure of the type, you can always retrieve getObject() and then find out what type it is (via getClass()). Luckily, JDBC will generally come to the rescue by converting between the type returned by the JDBC driver and the type requested.

Data type conversion

The ResultSet.getXXX() methods are generally slightly «lenient». If you ask for a different type to that returned by the JDBC driver (e.g. if you ask for an int when the actual data type was a short), then JDBC will automatically convert the value to the requested type. This conversion works:

The types that can’t be converted automatically to a String include Blob and other types that aren’t «simple» pieces of data.

1. In principle, there are standard ANSI SQL data types; most database systems then support a number of additional data types. In practice, I think most programmers don’t worry about whether the types they are using are standard or not, and just choose among those available for the specific database system they’re using.
2. Sun’s guide to getting started with JDBC includes full tables showing which conversions are performed.

Источник

Understanding data type differences

There are a number of differences between the Java programming language data types and SQL Server data types. The Microsoft JDBC Driver for SQL Server helps to facilitate those differences through various types of conversions.

Character types

The JDBC character string data types are CHAR, VARCHAR, and LONGVARCHAR. The JDBC driver provides support for the JDBC 4.0 API. In the JDBC 4.0, the JDBC character string data types can also be NCHAR, NVARCHAR, and LONGNVARCHAR. These new character string types maintain Java native character types in Unicode format and remove the need to perform any ANSI-to-Unicode or Unicode-to-ANSI conversion.

Binary string types

The JDBC binary-string types are BINARY, VARBINARY, and LONGVARBINARY.

Exact numeric types

The JDBC exact numeric types map directly to their corresponding SQL Server types.

Approximate numeric types

The JDBC approximate numeric types are REAL, DOUBLE, and FLOAT.

Type	Description
REAL	The JDBC REAL type has seven digits of precision (single precision) and maps directly to the SQL Server real type.
DOUBLE	The JDBC DOUBLE type has 15 digits of precision (double precision) and maps to the SQL Server float type. The JDBC FLOAT type is a synonym of DOUBLE. Because there can be confusion between FLOAT and DOUBLE, DOUBLE is preferred.

Datetime Types

The JDBC TIMESTAMP type maps to the SQL Server datetime and smalldatetime types. The datetime type is stored in two 4-byte integers. The smalldatetime type holds the same information (date and time), but with less accuracy, in two 2-byte small integers.

The SQL Server timestamp type is a fixed-length binary-string type. It does not map to any of the JDBC time types: DATE, TIME, or TIMESTAMP.

Custom type mapping

The custom type mapping feature of JDBC that uses the SQLData interfaces for the JDBC advanced types (UDTs, Struct, and so on). is not implemented in the JDBC driver.

Источник

Java data type and sql data type

A long time ago at university, I learned my first high-level programming language, Pascal, from the book by Niklaus Wirth:

But I didn’t really learn much about databases studying computer science, as they were taught by another department (business systems). So maybe now’s the time to start with an imaginary book called something like:

Queries + Data Types = Databases?

Data types have a long and important history in computing, driven initially by word lengths and machine data types but becoming more powerful and abstract as computer science matured. One popular 1980’s magazine was even named after a data type (“BYTE”, early microprocessors such as the 8008, Z80, and 6800 were characterized by BYTE/8-bit word sizes, in an era when the PDP-11 had 16-bit words, the VAX had 32-bit words, and the Cray-1 a massive 64-bit word size).

2. PostgreSQL Data Types

“Data Types” is a popular PostgreSQL search, so I decided to do some investigation of my own into why they are so important. First of all, why do data types matter in PostgreSQL? Doing some preliminary research I found out that data types in PostgreSQL are important for at least the following aspects (possibly more!):

1. As column data types when creating a table
2. For functions and operators
3. For constraints
4. For creating types and domains, and
5. When using PostgreSQL from a programming language (e.g. PostgreSQL to/from Python, and “C”).

PostgreSQL has a lot of built-in data types that are described in Chapter 8 of the documentation. And you can add new data types, so I guess there are really an infinite number of data types possible.

There’s a table that enumerates at least 43 built-in data types, and reveals that along with the official name some types have aliases (used internally for historical reasons). For example “real” has the alias “float4” (a single precision 4-byte floating-point number).

Here’s the full table which shows the variety of data types available:

Name	Aliases	Description
bigint	int8	signed eight-byte integer
bigserial	serial8	autoincrementing eight-byte integer
bit [ (n) ]	fixed-length bit string
bit varying [ (n) ]	varbit [ (n) ]	variable-length bit string
boolean	bool	logical Boolean (true/false)
box	rectangular box on a plane
bytea	binary data (“byte array”)
character [ (n) ]	char [ (n) ]	fixed-length character string
character varying [ (n) ]	varchar [ (n) ]	variable-length character string
cidr	IPv4 or IPv6 network address
circle	circle on a plane
date	calendar date (year, month, day)
double precision	float8	double precision floating-point number (8 bytes)
inet	IPv4 or IPv6 host address
integer	int, int4	signed four-byte integer
interval [ fields ] [ (p) ]	time span
json	textual JSON data
jsonb	binary JSON data, decomposed
line	infinite line on a plane
lseg	line segment on a plane
macaddr	MAC (Media Access Control) address
macaddr8	MAC (Media Access Control) address (EUI-64 format)
money	currency amount
numeric [ (p, s) ]	decimal [ (p, s) ]	exact numeric of selectable precision
path	geometric path on a plane
pg_lsn	PostgreSQL Log Sequence Number
pg_snapshot	user-level transaction ID snapshot
point	geometric point on a plane
polygon	closed geometric path on a plane
real	float4	single precision floating-point number (4 bytes)
smallint	int2	signed two-byte integer
smallserial	serial2	autoincrementing two-byte integer
serial	serial4	autoincrementing four-byte integer
text	variable-length character string
time [ (p) ] [ without time zone ]	time of day (no time zone)
time [ (p) ] with time zone	timetz	time of day, including time zone
timestamp [ (p) ] [ without time zone ]	date and time (no time zone)
timestamp [ (p) ] with time zone	timestamptz	date and time, including time zone
tsquery	text search query
tsvector	text search document
txid_snapshot	user-level transaction ID snapshot (deprecated; see pg_snapshot)
uuid	universally unique identifier
xml	XML data

Table 1: Postgres Data Types (Name, Alias, Description)

But how do you know what you can do with each data type? Chapter 9 documents which functions and operators are applicable to each data type. The documentation also says that each data type has an external representation, which raises the question of what these “external representations” are either in standard SQL data types or for a specific programming language.

3. Using PostgreSQL From Java—the PgJDBC Driver

How do you use PostgreSQL from Java? With JDBC! (Java Database Connectivity). There’s a PostgreSQL JDBC Driver (PgJDBC for short) which allows Java programs to connect using standard, database independent, Java code. It’s an open source Pure Java (Type 4, which talks native PostgreSQL protocol) driver and is well documented.

It’s easy to download PostgreSQL, install it, and start the database server running. You also need to download the JDBC driver.

Connecting to the database is easy from jdbc:

Источник