- Sql bit to java
- 8.2 Dynamic data access
- 8.3 CHAR, VARCHAR, and LONGVARCHAR
- 8.4 DECIMAL and NUMERIC
- 8.5 BINARY, VARBINARY, and LONGVARBINARY
- 8.6 BIT
- 8.7 TINYINT, SMALLINT, INTEGER, and BIGINT
- 8.8 REAL, FLOAT, and DOUBLE
- 8.9 DATE, TIME, and TIMESTAMP
- Sql bit to java
- 2. PostgreSQL Data Types
- 3. Using PostgreSQL From Java—the PgJDBC Driver
Sql bit to java
We need to provide reasonable Java mappings for the common SQL data types. We also need to make sure that we have enough type information so that we can correctly store and retrieve parameters and recover results from SQL statements.
However, there is no particular reason that the Java data type needs to be exactly isomorphic to the SQL data type. For example, since Java has no fixed length arrays, we can represent both fixed length and variable length SQL arrays as variable length Java arrays. We also felt free to use Java Strings even though they don’t precisely match any of the SQL CHAR types.
Table 2 shows the default Java mapping for various common SQL data types. Not all of these types will necessarily be supported by all databases. The various mappings are described more fully in the following sections.
Table 2: Standard mapping from SQL types to Java types.
Similarly table 3 shows the reverse mapping from Java types to SQL types. Table 3: Standard mapping from Java types to SQL types.
The mapping for String will normally be VARCHAR but will turn into LONGVARCHAR if the given value exceeds the drivers limit on VARCHAR values. Similarly for byte[] and VARBINARY and LONGVARBINARY.
8.2 Dynamic data access
This chapter focuses on access to results or parameters whose types are known at compile time. However, some applications, for example generic browsers or query tools, are not compiled with knowledge of the database schema they will access, so JDBC also provides support for fully dynamically typed data access. See Section 14.2.
8.3 CHAR, VARCHAR, and LONGVARCHAR
There is no need for Java programmers to distinguish among the three different flavours of SQL strings CHAR, VARCHAR, and LONGVARCHAR. These can all be expressed identically in Java. It is possible to read and write the SQL correctly without needing to know the exact data type that was expected.
These types could be mapped to either String or char[]. After considerable discussion we decided to use String, as this seemed the more appropriate type for normal use. Note that the Java String class provides a method for converting a String to a char[] and a constructor for turning a char[] into a String.
For fixed length SQL strings of type CHAR(n), the JDBC drivers will perform appropriate padding with spaces. Thus when a CHAR(n) field is retrieved from the database the resulting String will always be of length «n» and may include some padding spaces at the end. When a String is sent to a CHAR(n) field, the driver and/or the database will add any necessary padding spaces to the end of the String to bring it up to length «n».
The ResultSet.getString method allocates and returns a new String. This is suitable for retrieving normal data, but the LONGVARCHAR SQL type can be used to store multi-megabyte strings. We therefore needed to provide a way for Java programmers to retrieve a LONGVARCHAR value in chunks. We handle this by allowing programmers to retrieve a LONGVARCHAR as a Java input stream from which they can subsequently read data in whatever chunks they prefer. Java streams can be used for either Unicode or Ascii data, so the programmer may chose to use either getAsciiStream or getUnicodeStream.
8.4 DECIMAL and NUMERIC
The SQL DECIMAL and NUMERIC data types are used to express fixed point numbers where absolute precision is required. They are often used for currency values.
These two types can be expressed identically in Java. The most convenient mapping uses the java.math.BigDecimal extended precision number type provided in JDK1.1
We also allow access to DECIMAL and NUMERIC as simple Strings and arrays of chars. Thus Java programmers can use getString to receive a NUMERIC or DECIMAL result.
8.5 BINARY, VARBINARY, and LONGVARBINARY
There is no need for Java programmers to distinguish among the three different flavours of SQL byte arrays BINARY, VARBINARY, and LONGVARBINARY. These can all be expressed identically as byte arrays in Java. (It is possible to read and write the SQL correctly without needing to know the exact BINARY data type that was expected.)
As with the LONGVARCHAR SQL type, the LONGVARBINARY SQL type can sometimes be used to return multi-megabyte data values. We therefore allow a LONGVARBINARY value to be retrieved as a Java input stream, from which programmers can subsequently read data in whatever chunks they prefer.
8.6 BIT
8.7 TINYINT, SMALLINT, INTEGER, and BIGINT
The SQL TINYINT, SMALLINT, INTEGER, and BIGINT types represent 8 bit, 16 bit, 32 bit, and 64 bit values. These therefore can be mapped to Java’s byte, short, int, and long data types.
8.8 REAL, FLOAT, and DOUBLE
We map REAL to Java float, and FLOAT and DOUBLE to Java double.
REAL is required to support 7 digits of mantissa precision. FLOAT and DOUBLE are required to support 15 digits of mantissa precision.
8.9 DATE, TIME, and TIMESTAMP
SQL defines three time related data types. DATE consists of day, month, and year. TIME consists of hours, minutes and seconds. TIMESTAMP combines DATE and TIME and also adds in a nanosecond field.
There is a standard Java class java.util.Date that provides date and time information. However, this class doesn’t perfectly match any of the three SQL types, as it includes both DATE and TIME information, but lacks the nanosecond granularity required for TIMESTAMP.
We therefore define three subclasses of java.util.Date. These are:
- java.sql.Date for SQL DATE information
In the case of java.sql.Date the hour, minute, second, and milli-second fields of the java.util.Date base class are set to zero.
In the case of java.sql.Time the year, month, and day fields of the java.util.Date base class are set to 1970, January, and 1, respectively. This is the «zero» date in the Java epoch.
The java.sql.Timestamp class extends java.util.Date by adding a nanosecond field.
Sql bit to java
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!):
- As column data types when creating a table
- For functions and operators
- For constraints
- For creating types and domains, and
- 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: