问题描述:
假如现在你要对mysql数据库进行插入操作。因为要插入很多数据,为了效率,因此,你创建了一个PreparedStatement:insert into table values(?,?,?,?,?)
其中table表各列的数据类型分别为INTEGER,BIGINT,CHAR,TEXT,BINARY
现在你要向创建的PreparedStatement中的五个 ? 赋值。你已经知道PreparedStatement类有setBlog()、setBoolean()、setInt()、setDate()、setFloat()等等set方法,但是当你要向一个Mysql数据类型为BINARY的列赋值是,你不知道用PreparedStatement类的哪个set方法,setInt()?setString?还是setBytes()?没错,是setBytes()方法。可是如果mysql类型是decimal呢?是image呢?是bit呢?这个时候,如果你手里有一张Mysql/SQLServer数据类型与java基本数据类型的对应关系的表,那么一切问题都迎刃而解了。
好了,废话不多说,下面的列表就给出了Mysql/SQLServer数据类型与java基本数据类型的对应,而且给出了用整型表示的列类型和列名。有了这张表,以后调用set方法就有据可循了:
****************************************************Mysql *******************************************
列 ClassName | ColumnType | DisplaySize | TypeName |
0: java.lang.Integer | ColumnType:4 | 11 | INTEGER |
1: java.lang.Integer | ColumnType:-6 | 4 | TINYINT |
2: java.lang.String | ColumnType:12 | 0 | UNKNOWN |
3: java.lang.Boolean | ColumnType:-7 | 1 | TINYINT |
4: java.lang.Integer | ColumnType:5 | 6 | SMALLINT |
5: java.lang.Integer | ColumnType:4 | 9 | MEDIUMINT |
6: java.lang.Integer | ColumnType:4 | 11 | INTEGER |
7: java.lang.Long | ColumnType:-5 | 20 | BIGINT |
8: java.lang.Double | ColumnType:8 | 22 | DOUBLE |
9: java.lang.Double | ColumnType:8 | 22 | DOUBLE |
10: java.lang.Float | ColumnType:7 | 12 | FLOAT |
11: java.lang.String | ColumnType:12 | 11 | UNKNOWN |
12: java.lang.String | ColumnType:12 | 11 | UNKNOWN |
13: java.lang.String | ColumnType:1 | 300 | CHAR |
14: java.lang.String | ColumnType:12 | 300 | VARCHAR |
15: java.sql.Date | ColumnType:91 | 10 | DATE |
16: java.sql.Time | ColumnType:92 | 8 | TIME |
17: java.sql.Date | ColumnType:91 | 4 | YEAR |
18: java.sql.Timestamp | ColumnType:93 | 19 | TIMESTAMP |
19: java.sql.Timestamp | ColumnType:93 | 19 | DATETIME |
20: [B | ColumnType:-3 | 255 | TINYBLOB |
21: [B | ColumnType:-4 | 65535 | BLOB |
22: [B | ColumnType:-4 | 16777215 | MEDIUMBLOB |
23: [B | ColumnType:-4 | -1 | BLOB |
24: java.lang.String | ColumnType:12 | 255 | TINYBLOB |
25: java.lang.String | ColumnType:-1 | 65535 | TEXT |
26: java.lang.String | ColumnType:-1 | 16777215 | MEDIUMBLOB |
27: java.lang.String | ColumnType:-1 | -1 | TEXT |
28: java.lang.String | ColumnType:1 | 3 | CHAR |
29: java.lang.String | ColumnType:1 | 3 | CHAR |
****************************************************MSSQL*******************************************
列 ClassName | ColumnType | DisplaySize | TYPENAME |
0: java.lang.Integer | ColumnType:4 | 11 | INT IDENTITY |
1: java.lang.Long | ColumnType:-5 | 20 | BIGINT |
2: byte[] | ColumnType:-2 | 100 | BINARY |
3: java.lang.Boolean | ColumnType:-7 | 1 | BIT |
4: java.lang.String | ColumnType:1 | 10 | CHAR |
5: java.sql.Timestamp | ColumnType:93 | 23 | DATETIME |
6: java.sql.Timestamp | ColumnType:93 | 23 | DATETIME |
7: java.math.BigDecimal | ColumnType:3 | 20 | DECIMAL |
8: java.lang.Double | ColumnType:6 | 22 | FLOAT |
9: byte[] | ColumnType:-4 | 2147483647 | IMAGE |
10:java.math.BigDecimal | ColumnType:3 | 21 | MONEY |
11: java.lang.String | ColumnType:1 | 10 | NCHAR |
12: java.lang.String | ColumnType:-1 | 1073741823 | NTEXT |
13:java.math.BigDecimal | ColumnType:2 | 20 | NUMERIC |
14: java.lang.String | ColumnType:12 | 50 | NVARCHAR |
15: java.lang.Float | ColumnType:7 | 13 | REAL |
16: java.sql.Timestamp | ColumnType:93 | 16 | SMALLDATETIME |
17: java.lang.Integer | ColumnType:5 | 6 | SMALLINT |
18:java.math.BigDecimal | ColumnType:3 | 12 | SMALLMONEY |
19: java.lang.String | ColumnType:12 | 8000 | SQL_VARIANT |
20: java.lang.String | ColumnType:-1 | 2147483647 | TEXT |
21: byte[] | ColumnType:-2 | 16 | TIMESTAMP |
22: java.lang.Integer | ColumnType:-6 | 3 | TINYINT |
23: java.lang.String | ColumnType:1 | 36 | UNIQUEIDENTIFIER |
24: byte[] | ColumnType:-3 | 100 | VARBINARY |
25: java.lang.String | ColumnType:12 | 50 | VARCHAR |