postgresql 将数据库类型映射到具体的 Java 类

Map database type to concrete Java class

提问by Dave Jarvis

Background

背景

Map a column data type to its corresponding Java class.

将列数据类型映射到其对应的 Java 类。

Problem

问题

A query returns meta information from a database:

查询从数据库返回元信息：

SELECT
  rb.object_schema,
  rb.object_name,
  rb.column_name
FROM
  dictionary.resource_bundle rb

For example, this query returns (the self-referential):

例如，此查询返回（自引用）：

dictionary, resource_bundle, column_name

Where 'dictionary' is the schema name, 'resource_bundle' is the object_name, and 'column_name' is the column_name.

其中“dictionary”是模式名称，“resource_bundle”是 object_name，而“column_name”是 column_name。

It would be great to do something like:

做这样的事情会很棒：

SELECT
  rb.object_schema,
  rb.object_name,
  rb.column_name,
  rb.column_type
FROM
  dictionary.resource_bundle rb

And have this query return:

并让这个查询返回：

dictionary, resource_bundle, column_name, varchar

Then use JDBC to discover that varcharis mappedto java.lang.String.

然后使用JDBC来发现varchar被映射到java.lang.String。

Questions

问题

1. In PostgreSQL, how do you determine what type is used to store the data, given a schema name, object name (guaranteed to be table or view), and column name?
2. In a database-neutral fashion (leveraging JDBC), how do you determine the mapping a database uses for a given data type?

1. 在 PostgreSQL 中，给定模式名称、对象名称（保证是表或视图）和列名称，您如何确定使用什么类型来存储数据？
2. 以数据库中立的方式（利用 JDBC），您如何确定数据库对给定数据类型使用的映射？

回答by Dave Jarvis

Solution

解决方案

The answer is more complicated than using the getMetaDatamethod because there is no direct mapping from the integer types returned by the getMetaDatamethod and the full class name. This solution requires two pieces of code:

答案比使用getMetaData方法更复杂，因为方法返回的整数类型getMetaData和完整的类名没有直接映射。这个解决方案需要两段代码：

• Implementing a method to obtain the java.sql.Typesconstant integer value.
• Creating a method to translate that value to a class name.

• 实现一个方法来获取java.sql.Types常量整数值。
• 创建一个方法来将该值转换为类名。

Java Type Method

Java 类型方法

The following method retrieves the meta information:

以下方法检索元信息：

  public String getJavaType( String schema, String object, String column )
    throws Exception {
    String fullName = schema + '.' + object + '.' + column;
    DatabaseMetaData metaData = getConnection().getMetaData();
    ResultSet columnMeta = metaData.getColumns( null, schema, object, column );
    String javaType = null;

    if( columnMeta.first() ) {
      int dataType = columnMeta.getInt( "DATA_TYPE" );
      javaType = SQLTypeMap.convert( dataType );
    }
    else {
      throw new Exception( "Unknown database column " + fullName + '.' );
    }

    return javaType;
  }

Static Conversion Method

静态转换方法

The constant integer values must be translated to a class name. This can be accomplished as follows:

常量整数值必须转换为类名。这可以按如下方式完成：

import java.sql.Types;

/**
 * Converts database types to Java class types.
 */
public class SQLTypeMap {
    /**
     * Translates a data type from an integer (java.sql.Types value) to a string
     * that represents the corresponding class.
     * 
     * @param type
     *            The java.sql.Types value to convert to its corresponding class.
     * @return The class that corresponds to the given java.sql.Types
     *         value, or Object.class if the type has no known mapping.
     */
    public static Class<?> toClass(int type) {
        Class<?> result = Object.class;

        switch (type) {
            case Types.CHAR:
            case Types.VARCHAR:
            case Types.LONGVARCHAR:
                result = String.class;
                break;

            case Types.NUMERIC:
            case Types.DECIMAL:
                result = java.math.BigDecimal.class;
                break;

            case Types.BIT:
                result = Boolean.class;
                break;

            case Types.TINYINT:
                result = Byte.class;
                break;

            case Types.SMALLINT:
                result = Short.class;
                break;

            case Types.INTEGER:
                result = Integer.class;
                break;

            case Types.BIGINT:
                result = Long.class;
                break;

            case Types.REAL:
            case Types.FLOAT:
                result = Float.class;
                break;

            case Types.DOUBLE:
                result = Double.class;
                break;

            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                result = Byte[].class;
                break;

            case Types.DATE:
                result = java.sql.Date.class;
                break;

            case Types.TIME:
                result = java.sql.Time.class;
                break;

            case Types.TIMESTAMP:
                result = java.sql.Timestamp.class;
                break;
        }

        return result;
    }
}

Note that different databases can have different variations on the mapping.

请注意，不同的数据库在映射上可能有不同的变化。

回答by Brent Worden

JDBC provides the means to introspect database meta information.

JDBC 提供了内省数据库元信息的方法。

From a JDBC Connection, call getMetaDataand getColumnsdrill down from there to get information for schemas, tables and, columns.

从 JDBC 连接，调用getMetaData并从那里向下钻取getColumns以获取模式、表和列的信息。