当数据库字段的值为NULL时，我们使用jdbc获取到的值为什么呢？对于varchar、char等类型当使用getString时，根据常识轻松地知道值应该是NULL。但是，对于int、float等类型，当我们使用getInt、getFloat方法时应该返回的值也是NULL么。答案是否定的，我们根据这几个的方法的注释可以知道，当数据库字段的值为NULL,通过jdbc获取到的值为0。
float java.sql.ResultSet.getFloat(String columnLabel) throws SQLException方法的注释如下。
Retrieves the value of the designated column in the current row of this ResultSet object as a float in the Java programming language.
Parameters:
columnLabel the label for the column specified with the SQL AS clause. If the SQL AS clause was not specified, then the label is the name of the column
Returns:
the column value; if the value is SQL NULL, the value returned is 0
Throws:
SQLException - if the columnLabel is not valid; if a database access error occurs or this method is called on a closed result set
但是当某一些字段的类型为INT、FLOAT时，比如表示价格的字段，我们想要用NULL值表示该字段未填写值，那该怎么办呢？这时我们可以使用getObject或者getString方法，当结果为null时表示未填写值，当结果不为null时再将其转换为相应的基本类型。
java中的类型和sql的类型的映射关系定义在JDBC规范中。接下来需要研究一下这个文档。
jdbc类型映射到java类型
| JDBC Type | Java Type |
|---|---|
| CHAR | String |
| VARCHAR | String |
| LONGVARCHAR | String |
| NUMERIC | java.math.BigDecimal |
| DECIMAL | java.math.BigDecimal |
| BIT | boolean |
| BOOLEAN | boolean |
| TINYINT | byte |
| SMALLINT | short |
| INTEGER | int |
| BIGINT | long |
| REAL | float |
| FLOAT | double |
| DOUBLE | double |
| BINARY | byte[] |
| VARBINARY | byte[] |
| LONGVARBINARY | byte[] |
| DATE | java.sql.Date |
| TIME | java.sql.Time |
| TIMESTAMP | java.sql.Timestamp |
| CLOB | java.sql.Clob |
| BLOB | java.sql.Blob |
| ARRAY | java.sql.array |
| DISTINCT | Mapping of underlying type |
| STRUCT | java.sql.Struct |
| REF | java.sql.Ref |
| DATALINK | java.net.URL |
| JAVA_OBJECT | Underlying Java class |
| ROWID | java.sql.RowId |
| NCHAR | String |
| NVARCHAR | String |
| LONGNVARCHAR | String |
| NCLOB | java.sql.NClob |
| SQLXML | java.sql.SQLXML |
jdbc类型映射到java对象
| JDBC Type | Java Object Type |
|---|---|
| CHAR | String |
| VARCHAR | String |
| LONGVARCHAR | String |
| NUMERIC | java.math.BigDecimal |
| DECIMAL | java.math.BigDecimal |
| BIT | Boolean |
| BOOLEAN | Boolean |
| TINYINT | Integer |
| SMALLINT | Integer |
| INTEGER | Integer |
| BIGINT | Long |
| REAL | Float |
| FLOAT | Double |
| DOUBLE | Double |
| BINARY | byte[] |
| VARBINARY | byte[] |
| LONGVARBINARY | byte[] |
| DATE | java.sql.Date |
| TIME | java.sql.Time |
| TIMESTAMP | java.sql.Timestamp |
| DISTINCT | Object type of underlying type |
| CLOB | java.sql.Clob |
| BLOB | java.sql.Blob |
| ARRAY | java.sql.Array |
| STRUCT | java.sql.Struct or java.sql.SQLData |
| REF | java.sql.Ref |
| DATALINK | java.net.URL |
| JAVA_OBJECT | Underlying Java class |
| ROWID | java.sql.RowId |
| NCHAR | String |
| NVARCHAR | String |
| LONGNVARCHAR | String |
| NCLOB | java.sql.NClob |
| SQLXML | java.sql.SQLXML |
测试
创建测试用表，环境是mysql。
create table test(id int,price float,name varchar(2000));
插入数据
insert into test(id,price,name) values(12,12.3456,'阿里巴巴');
insert into test(id,price,name) values(1,312.3456,'阿里巴巴');
insert into test(id,price,name) values(null,142.3456,'阿里巴巴');
insert into test(id,price,name) values(3,null,'阿里巴巴');
数据表
| id | price | name |
|---|---|---|
| 12 | 12.3456 | 阿里巴巴 |
| 1 | 312.346 | 阿里巴巴 |
| NULL | 142.346 | 阿里巴巴 |
| 3 | NULL | 阿里巴巴 |
java使用jdbc连接
Statement st = conn.createStatement();
String sql = "select * from test where id = 3";
java.sql.ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
String name = rs.getString("name");
String idStr = rs.getString("id");
Object idObj = rs.getObject("id");
int id = rs.getInt("id");
float price = rs.getFloat("price");
System.out.println("---------------------------");
System.out.println(idObj instanceof Integer);
System.out.println("id = " + id);
System.out.println("idStr = " + idStr);
System.out.println("idObj = " + idObj);
}
结果
---------------------------
true
id = 3
idStr = 3
idObj = 3
参考
- java.sql.ResultSet相应方法的注释
- JSR 221 JDBC™ 4.2 Specification下载地址