Java type and postgresql
PostgreSQL to Java Type Mapping
Type mapping from PostgreSQL to Java is the process of mapping the data types used in a PostgreSQL to Java Type Mapping in Java programming language. This mapping is necessary when working with a PostgreSQL database in a Java application, as the data types used in the database may not be directly compatible with the data types used in the Java code.
For example, in a PostgreSQL database, a column might be defined as a “BIGINT” data type, which can store very large integer values. In Java, the equivalent data type for this would be a “long”. So when retrieving data from the database into a Java application, the “BIGINT” value would need to be mapped to a “long” value in the Java code.
Similarly, if a Java application needs to store data in a PostgreSQL database, the data types used in the Java code may need to be mapped to equivalent data types in the database. For example, a Java “LocalDate” object would need to be mapped to a “DATE” data type in the PostgreSQL database.
| PostgreSQL Data Type | Java Data Type | Java Nullable Data Type | Java Primitive Type |
|---|---|---|---|
| bigint | Long | long | |
| bit | Boolean | bool | |
| boolean | Boolean | bool | |
| bytea | byte[] | ||
| character | String | ||
| character varying | String | ||
| date | LocalDateTime | ||
| double precision | BigDecimal | ||
| integer | Integer | int | |
| money | BigDecimal | ||
| numeric | BigDecimal | ||
| real | Float | float | |
| serial | Integer | int | |
| smallint | Short | short | |
| smallserial | Short | short | |
| text | String | ||
| time | LocalTime | ||
| timestamp | Timestamp |
Type mapping between PostgreSQL and Java is typically handled by a database driver or an ORM (Object-Relational Mapping) framework, which provide APIs for working with the database and automatically perform the necessary type conversions between the two systems.
Prerequisites
To follow along with the examples and code snippets in this article, you should have a basic understanding of Java programming and familiarity with PostgreSQL and its data types.
Overview of PostgreSQL Data Types
PostgreSQL offers a rich set of data types, ranging from primitive types such as integers and strings to complex types like arrays, JSON, and geometric data. Each PostgreSQL data type has its own characteristics and mapping rules when working with Java.
Let’s examine some commonly used PostgreSQL data types and their corresponding Java types:
Please note that this is not an exhaustive list of all PostgreSQL data types, but it covers the most commonly used ones.
Handling PostgreSQL to Java Type Conversion
When working with PostgreSQL in Java, you’ll typically interact with the database using a JDBC (Java Database Connectivity) driver. The JDBC driver handles the conversion between PostgreSQL data types and Java types automatically, ensuring seamless data exchange.
Here’s an example that demonstrates retrieving data from a PostgreSQL table and mapping it to Java types:
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class PostgreSQLExample < public static void main(String[] args) < try < // Establish a connection to the PostgreSQL database Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/mydatabase", "username", "password"); // Prepare a SQL statement PreparedStatement statement = connection.prepareStatement("SELECT id, name, age FROM users"); // Execute the query and retrieve the result set ResultSet resultSet = statement.executeQuery(); // Iterate over the result set while (resultSet.next()) < int String name = resultSet.getString("name"); int age = resultSet.getInt("age"); // Process the data as needed System.out.println("User ID: " + id); System.out.println("User Name: " + name); System.out.println("User Age: " + age); >// Close the resources resultSet.close(); statement.close(); connection.close(); > catch (SQLException e) < e.printStackTrace(); >> > In the example above, the JDBC driver handles the conversion of PostgreSQL types (int, varchar, etc.) to their corresponding Java types (int, String, etc.) when retrieving data from the users table.
Custom Mapping and Advanced Scenarios
In some cases, you may need to handle custom mapping between PostgreSQL and Java types or deal with more complex scenarios. JDBC provides several mechanisms to handle such situations.
For example, you can register a custom org.postgresql.util.PGobject class to map PostgreSQL’s json or jsonb types to Java objects using libraries like Gson or Jackson. This allows you to work with JSON data in a more convenient manner.
Additionally, if you need to store or retrieve binary data (e.g., images) from PostgreSQL, you can use the bytea type in combination with the java.sql.Blob interface or byte[ ] arrays.
Understanding the PostgreSQL to Java type mapping is essential for developing robust and efficient applications that interact with PostgreSQL databases. In this article, we have covered the mapping rules for commonly used PostgreSQL data types and explored how to handle data retrieval and conversion using JDBC. Remember to consult the official documentation of your JDBC driver and PostgreSQL for more specific details and advanced use cases.
By following the guidelines presented here, you can confidently work with PostgreSQL databases in your Java applications, ensuring accurate and reliable data manipulation and persistence.
Saved searches
Use saved searches to filter your results more quickly
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.
PostgreSQL Types
Clone this wiki locally
Supported Java types and their destination types on PostgreSQL
All types also support their array versions, but they are returned as IndexedSeq of the type and not pure Array types.
| PostgreSQL type | Java type |
|---|---|
| boolean | Boolean |
| smallint | Short |
| integer (or serial) | Int |
| bigint (or bigserial) | Long |
| numeric | BigDecimal |
| real | Float |
| double | Double |
| text | String |
| varchar | String |
| bpchar | String |
| timestamp | LocalDateTime |
| timestamp_with_timezone | DateTime |
| date | LocalDate |
| time | LocalTime |
| bytea | Array[Byte] (PostgreSQL 9.0 and above only) |
All other types are returned as String.
Now from Scala/Java types to PostgreSQL types (when using prepared statements):
- Array types are encoded with the kind of object they hold and not the array type itself. Java Collection and Scala Traversable objects are also assumed to be arrays of the types they hold and will be sent to PostgreSQL like that.
- null values are supported, In prepared statement pass null .
- Spatial / Geometry data types (postgisjts) are supported via module jasync-postgis-jts . In order to use geometry types import the module and init with the following code:
JasyncPostgisRegister.init(connection) Java type and postgresql
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: