将数据库类型映射到具体的Java类 [英] Map database type to concrete Java class

问题描述

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

Map a column data type to its corresponding Java class.

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

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

其中'dictionary'是模式名称，'resource_bundle'是object_name，'column_name '是column_name。

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

这样做很棒：

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

让此查询返回：

dictionary, resource_bundle, column_name, varchar

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

Then use JDBC to discover that varchar is mapped to java.lang.String.

1. 在PostgreSQL中，给定模式名称，对象名称（保证是表格或视图）和列名称，如何确定用于存储数据的类型？


2. 在数据库中立的方式（利用JDBC），如何确定数据库用于给定数据类型的映射？

谢谢！

推荐答案

解决方案

答案比使用<更复杂code> getMetaData 方法，因为没有来自 getMetaData 方法返回的整数类型的直接映射以及完整的类名。此解决方案需要两段代码：

Solution

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

• 实现获取 java.sql.Types 常量整数值。


• 创建一个将该值转换为类名的方法。

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

以下方法检索元信息：

  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.

这篇关于将数据库类型映射到具体的Java类的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！