Sql type to java type mapping

Using Customized Type Mappings

Note: MySQL currently does not support user-defined types. MySQL and Java DB currently do not support structured types, or the DISTINCT SQL data type. No JDBC tutorial example is available to demonstrate the features described in this section.

With business booming, the owner of The Coffee Break is regularly adding new stores and making changes to the database. The owner has decided to use a custom mapping for the structured type ADDRESS . This enables the owner to make changes to the Java class that maps the ADDRESS type. The Java class will have a field for each attribute of ADDRESS . The name of the class and the names of its fields can be any valid Java identifier.

The following topics are covered:

Implementing SQLData

The first thing required for a custom mapping is to create a class that implements the interface SQLData .

The SQL definition of the structured type ADDRESS looks like this:

CREATE TYPE ADDRESS ( NUM INTEGER, STREET VARCHAR(40), CITY VARCHAR(40), STATE CHAR(2), ZIP CHAR(5) );

A class that implements the SQLData interface for the custom mapping of the ADDRESS type might look like this:

public class Address implements SQLData < public int num; public String street; public String city; public String state; public String zip; private String sql_type; public String getSQLTypeName() < return sql_type; >public void readSQL(SQLInput stream, String type) throws SQLException < sql_type = type; num = stream.readInt(); street = stream.readString(); city = stream.readString(); state = stream.readString(); zip = stream.readString(); >public void writeSQL(SQLOutput stream) throws SQLException < stream.writeInt(num); stream.writeString(street); stream.writeString(city); stream.writeString(state); stream.writeString(zip); >>

Using a Connection’s Type Map

After writing a class that implements the interface SQLData , the only other thing you have to do to set up a custom mapping is to make an entry in a type map. For the example, this means entering the fully qualified SQL name for the ADDRESS type and the Class object for the class Address . A type map, an instance of the java.util.Map interface, is associated with every new connection when it is created, so you use that one. Assuming that con is the active connection, the following code fragment adds an entry for the UDT ADDRESS to the type map associated with con .

java.util.Map map = con.getTypeMap(); map.put("SchemaName.ADDRESS", Class.forName("Address")); con.setTypeMap(map);

Whenever you call the getObject method to retrieve an instance of the ADDRESS type, the driver will check the type map associated with the connection and see that it has an entry for ADDRESS . The driver will note the Class object for the Address class, create an instance of it, and do many other things in the background to map ADDRESS to Address . You do not have to do anything more than generate the class for the mapping and then make an entry in a type map to let the driver know that there is a custom mapping. The driver will do all the rest.

The situation is similar for storing a structured type that has a custom mapping. When you call the method setObject , the driver will check to see if the value to be set is an instance of a class that implements the interface SQLData . If it is (meaning that there is a custom mapping), the driver will use the custom mapping to convert the value to its SQL counterpart before returning it to the database. Again, the driver does the custom mapping behind the scenes; all you need to do is supply the method setObject with a parameter that has a custom mapping. You will see an example of this later in this section.

Look at the difference between working with the standard mapping, a Struct object, and the custom mapping, a class in the Java programming language. The following code fragment shows the standard mapping to a Struct object, which is the mapping the driver uses when there is no entry in the connection’s type map.

ResultSet rs = stmt.executeQuery( "SELECT LOCATION " + "WHERE STORE_NO = 100003"); rs.next(); Struct address = (Struct)rs.getObject("LOCATION");

The variable address contains the following attribute values: 4344 , «First_Street» , «Verona» , «CA» , «94545» .

The following code fragment shows what happens when there is an entry for the structured type ADDRESS in the connection’s type map. Remember that the column LOCATION stores values of type ADDRESS .

ResultSet rs = stmt.executeQuery( "SELECT LOCATION " + "WHERE STORE_NO = 100003"); rs.next(); Address store_3 = (Address)rs.getObject("LOCATION");

The variable store_3 is now an instance of the class Address , with each attribute value being the current value of one of the fields of Address . Note that you must remember to convert the object retrieved by the getObject method to an Address object before assigning it to store_3 . Note also that store_3 must be an Address object.

Compare working with the Struct object to working with the instance of the Address class. Suppose the store moved to a better location in the neighboring town and therefore you must update the database. With the custom mapping, reset the fields of store_3 , as in the following code fragment:

ResultSet rs = stmt.executeQuery( "SELECT LOCATION " + "WHERE STORE_NO = 100003"); rs.next(); Address store_3 = (Address)rs.getObject("LOCATION"); store_3.num = 1800; store_3.street = "Artsy_Alley"; store_3.city = "Arden"; store_3.state = "CA"; store_3.zip = "94546"; PreparedStatement pstmt = con.prepareStatement( "UPDATE STORES " + "SET LOCATION = ? " + "WHERE STORE_NO = 100003"); pstmt.setObject(1, store_3); pstmt.executeUpdate();

Values in the column LOCATION are instances of the ADDRESS type. The driver checks the connection’s type map and sees that there is an entry linking ADDRESS with the class Address and consequently uses the custom mapping indicated in Address . When the code calls the method setObject with the variable store_3 as the second parameter, the driver checks and sees that store_3 represents an instance of the class Address , which implements the interface SQLData for the structured type ADDRESS , and again automatically uses the custom mapping.

Without a custom mapping for ADDRESS , the update would look more like this:

PreparedStatement pstmt = con.prepareStatement( "UPDATE STORES " + "SET LOCATION.NUM = 1800, " + "LOCATION.STREET = 'Artsy_Alley', " + "LOCATION.CITY = 'Arden', " + "LOCATION.STATE = 'CA', " + "LOCATION.ZIP = '94546' " + "WHERE STORE_NO = 100003"); pstmt.executeUpdate;

Using Your Own Type Map

Up to this point, you have used only the type map associated with a connection for custom mapping. Ordinarily, that is the only type map most programmers will use. However, it is also possible to create a type map and pass it to certain methods so that the driver will use that type map instead of the one associated with the connection. This allows two different mappings for the same user-defined type (UDT). In fact, it is possible to have multiple custom mappings for the same UDT, just as long as each mapping is set up with a class implementing the SQLData interface and an entry in a type map. If you do not pass a type map to a method that can accept one, the driver will by default use the type map associated with the connection.

There are very few situations that call for using a type map other than the one associated with a connection. It could be necessary to supply a method with a type map if, for instance, several programmers working on a JDBC application brought their components together and were using the same connection. If two or more programmers had created their own custom mappings for the same SQL UDT, each would need to supply his or her own type map, thus overriding the connection’s type map.

Источник

Sql type to java type mapping

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:

Источник