mysql类型与jdbc类型对照


环境: mysql-connector-java 5.1.35


官方文档参考 http://dev.mysql.com/doc/connector-j/en/connector-j-reference-type-conversions.html


1.参照mysql jdbc驱动表源码  com.mysql.jdbc.MysqlDefs.java,查看mysql类型与jdbc类型对照关系

        

private static Map<String, Integer> mysqlToJdbcTypesMap = new HashMap<String, Integer>();

    static {
        mysqlToJdbcTypesMap.put("BIT", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_BIT)));
        mysqlToJdbcTypesMap.put("TINYINT", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_TINY)));
        mysqlToJdbcTypesMap.put("SMALLINT", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_SHORT)));
        mysqlToJdbcTypesMap.put("MEDIUMINT", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_INT24)));
        mysqlToJdbcTypesMap.put("INT", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_LONG)));
        mysqlToJdbcTypesMap.put("INTEGER", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_LONG)));
        mysqlToJdbcTypesMap.put("BIGINT", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_LONGLONG)));
        mysqlToJdbcTypesMap.put("INT24", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_INT24)));
        mysqlToJdbcTypesMap.put("REAL", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_DOUBLE)));
        mysqlToJdbcTypesMap.put("FLOAT", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_FLOAT)));
        mysqlToJdbcTypesMap.put("DECIMAL", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_DECIMAL)));
        mysqlToJdbcTypesMap.put("NUMERIC", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_DECIMAL)));
        mysqlToJdbcTypesMap.put("DOUBLE", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_DOUBLE)));
        mysqlToJdbcTypesMap.put("CHAR", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_STRING)));
        mysqlToJdbcTypesMap.put("VARCHAR", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_VAR_STRING)));
        mysqlToJdbcTypesMap.put("DATE", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_DATE)));
        mysqlToJdbcTypesMap.put("TIME", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_TIME)));
        mysqlToJdbcTypesMap.put("YEAR", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_YEAR)));
        mysqlToJdbcTypesMap.put("TIMESTAMP", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_TIMESTAMP)));
        mysqlToJdbcTypesMap.put("DATETIME", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_DATETIME)));
        mysqlToJdbcTypesMap.put("TINYBLOB", Integer.valueOf(java.sql.Types.BINARY));
        mysqlToJdbcTypesMap.put("BLOB", Integer.valueOf(java.sql.Types.LONGVARBINARY));
        mysqlToJdbcTypesMap.put("MEDIUMBLOB", Integer.valueOf(java.sql.Types.LONGVARBINARY));
        mysqlToJdbcTypesMap.put("LONGBLOB", Integer.valueOf(java.sql.Types.LONGVARBINARY));
        mysqlToJdbcTypesMap.put("TINYTEXT", Integer.valueOf(java.sql.Types.VARCHAR));
        mysqlToJdbcTypesMap.put("TEXT", Integer.valueOf(java.sql.Types.LONGVARCHAR));
        mysqlToJdbcTypesMap.put("MEDIUMTEXT", Integer.valueOf(java.sql.Types.LONGVARCHAR));
        mysqlToJdbcTypesMap.put("LONGTEXT", Integer.valueOf(java.sql.Types.LONGVARCHAR));
        mysqlToJdbcTypesMap.put("ENUM", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_ENUM)));
        mysqlToJdbcTypesMap.put("SET", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_SET)));
        mysqlToJdbcTypesMap.put("GEOMETRY", Integer.valueOf(mysqlToJavaType(FIELD_TYPE_GEOMETRY)));
    }



2.jdbc类型与JAVA类型对照关系,参照com.mysql.jdbc.ResultSetImpl.java的Object getObject(int columnIndex)方法


    public Object getObject(int columnIndex) throws SQLException {
        checkRowPos();
        checkColumnBounds(columnIndex);

        int columnIndexMinusOne = columnIndex - 1;

        if (this.thisRow.isNull(columnIndexMinusOne)) {
            this.wasNullFlag = true;

            return null;
        }

        this.wasNullFlag = false;

        Field field;
        field = this.fields[columnIndexMinusOne];

        switch (field.getSQLType()) {
            case Types.BIT:
            case Types.BOOLEAN:
                if (field.getMysqlType() == MysqlDefs.FIELD_TYPE_BIT && !field.isSingleBit()) {
                    return getBytes(columnIndex);
                }

                // valueOf would be nicer here, but it isn't present in JDK-1.3.1, which is what the CTS uses.
                return Boolean.valueOf(getBoolean(columnIndex));

            case Types.TINYINT:
                if (!field.isUnsigned()) {
                    return Integer.valueOf(getByte(columnIndex));
                }

                return Integer.valueOf(getInt(columnIndex));

            case Types.SMALLINT:

                return Integer.valueOf(getInt(columnIndex));

            case Types.INTEGER:

                if (!field.isUnsigned() || field.getMysqlType() == MysqlDefs.FIELD_TYPE_INT24) {
                    return Integer.valueOf(getInt(columnIndex));
                }

                return Long.valueOf(getLong(columnIndex));

            case Types.BIGINT:

                if (!field.isUnsigned()) {
                    return Long.valueOf(getLong(columnIndex));
                }

                String stringVal = getString(columnIndex);

                if (stringVal == null) {
                    return null;
                }

                try {
                    return new BigInteger(stringVal);
                } catch (NumberFormatException nfe) {
                    throw SQLError.createSQLException(
                            Messages.getString("ResultSet.Bad_format_for_BigInteger", new Object[] { Integer.valueOf(columnIndex), stringVal }),
                            SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor());
                }

            case Types.DECIMAL:
            case Types.NUMERIC:
                stringVal = getString(columnIndex);

                BigDecimal val;

                if (stringVal != null) {
                    if (stringVal.length() == 0) {
                        val = new BigDecimal(0);

                        return val;
                    }

                    try {
                        val = new BigDecimal(stringVal);
                    } catch (NumberFormatException ex) {
                        throw SQLError.createSQLException(
                                Messages.getString("ResultSet.Bad_format_for_BigDecimal", new Object[] { stringVal, Integer.valueOf(columnIndex) }),
                                SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor());
                    }

                    return val;
                }

                return null;

            case Types.REAL:
                return new Float(getFloat(columnIndex));

            case Types.FLOAT:
            case Types.DOUBLE:
                return new Double(getDouble(columnIndex));

            case Types.CHAR:
            case Types.VARCHAR:
                if (!field.isOpaqueBinary()) {
                    return getString(columnIndex);
                }

                return getBytes(columnIndex);
            case Types.LONGVARCHAR:
                if (!field.isOpaqueBinary()) {
                    return getStringForClob(columnIndex);
                }

                return getBytes(columnIndex);

            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                if (field.getMysqlType() == MysqlDefs.FIELD_TYPE_GEOMETRY) {
                    return getBytes(columnIndex);
                } else if (field.isBinary() || field.isBlob()) {
                    byte[] data = getBytes(columnIndex);

                    if (this.connection.getAutoDeserialize()) {
                        Object obj = data;

                        if ((data != null) && (data.length >= 2)) {
                            if ((data[0] == -84) && (data[1] == -19)) {
                                // Serialized object?
                                try {
                                    ByteArrayInputStream bytesIn = new ByteArrayInputStream(data);
                                    ObjectInputStream objIn = new ObjectInputStream(bytesIn);
                                    obj = objIn.readObject();
                                    objIn.close();
                                    bytesIn.close();
                                } catch (ClassNotFoundException cnfe) {
                                    throw SQLError.createSQLException(
                                            Messages.getString("ResultSet.Class_not_found___91") + cnfe.toString()
                                                    + Messages.getString("ResultSet._while_reading_serialized_object_92"), getExceptionInterceptor());
                                } catch (IOException ex) {
                                    obj = data; // not serialized?
                                }
                            } else {
                                return getString(columnIndex);
                            }
                        }

                        return obj;
                    }

                    return data;
                }

                return getBytes(columnIndex);

            case Types.DATE:
                if (field.getMysqlType() == MysqlDefs.FIELD_TYPE_YEAR && !this.connection.getYearIsDateType()) {
                    return Short.valueOf(getShort(columnIndex));
                }

                return getDate(columnIndex);

            case Types.TIME:
                return getTime(columnIndex);

            case Types.TIMESTAMP:
                return getTimestamp(columnIndex);

            default:
                return getString(columnIndex);
        }
    }

默认情况下,SMALLINT,TINYINT都会转为Integer对象,Short是没戏了。


永远不会出现识别char类型为java的CHAR类型,哪怕是char(1),只会转成String对象。


如果需要自己细化这种类型转换,强制类型转换即可。



3.hibernate mysql dialect中有关数据类型映射,可以参考下


        







  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值