SQL和java.sql.Types对应关系
SQL Java
CHAR String
VARCHAR String
LONGVARCHAR String
NUMERIC java.math.BigDecimal
DECIMAL java.math.BigDecimal
BIT 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
例子：JdbcTemplate使用时
public String updateProuducMonitorInfo(int id) {
String monitorInfo = null;
try {
StringBuffer sql = new StringBuffer();
sql.append("update CMSMONITOR.CMS_A_CONTENT_JMS_MONITOR ");
sql.append(" set STATE_PRODUCT_BEGIN = ? ,TIME_PRODUCT_BEGIN = sysdate ,STATE = ?");
sql.append(" where ID = ?");
Object[] paraArr = {1, Method.MONITOR_PRODUCT_BEGIN_STATE, id};
int[] paraTypes = new int[] { Types.VARCHAR,Types.INTEGER, Types.INTEGER};
if (sql != null && sql.toString().trim().length() != 0) {
this.jdbcTemplateNewWeb.update(sql.toString(), paraArr, paraTypes);
//*******
}
} catch (Exception e) {
e.printStackTrace();
}
return monitorInfo;
}
****************
public HashMap getProuducMonitorInfo(String mdmc_id, int opera_type) {
HashMap map = new HashMap();
try {
StringBuffer sql = new StringBuffer();
sql.append("select ID ,STATE ,BIZ_TYPE from CMSMONITOR.CMS_A_CONTENT_JMS_MONITOR ");
sql.append(" where MDMC_ID = ? and OPERATE_TYPE = ?");
sql.append(" and STATE >= ? and STATE < ? and IS_AGAIN = ?");
Object[] paraArr = { mdmc_id, opera_type, Method.MONITOR_RECEIVE_BEGIN_STATE,
Method.MONITOR_CMS_END_STATE, 0 };
int[] paraTypes = new int[] { Types.VARCHAR, Types.INTEGER,Types.INTEGER, Types.INTEGER ,Types.INTEGER };
SqlRowSet rs = null;
if (sql != null && sql.toString().trim().length() != 0) {
// rs = (SqlRowSet) this.jdbcTemplateNewWeb.query(sql.toString(),paraArr,paraTypes,new SqlRowSetOracleResultSetExtractor());
rs = this.jdbcTemplateNewWeb.queryForRowSet(sql.toString(),paraArr, paraTypes);
}
if(rs!=null&&rs.next()){
map.put("monitor_id", rs.getInt("ID"));
map.put("monitor_state", rs.getInt("STATE"));
map.put("bizType", rs.getInt("BIZ_TYPE"));
}else{
}
} catch (Exception e) {
e.printStackTrace();
}
return map;
}
**************
public BigDecimal getProFullSongSysId(String proId,String contentID) throws SQLException {
BigDecimal id = null;
ResultSet resultSet = null;
try {
if (this.getFullSongIdPs == null) {
StringBuffer sql = new StringBuffer();
sql.append("select ID");
sql.append(" from CMS_DOWNLOADCONTENT ");
sql.append(" where ID = ? OR CONTENT_ID = ? ");
this.getFullSongIdPs = this.connection.prepareStatement(sql.toString());
}
this.getFullSongIdPs.setBigDecimal(1, Method.strToBigDecimal(proId));
this.getFullSongIdPs.setString(2, contentID);
resultSet = this.getFullSongIdPs.executeQuery();
if (resultSet.next()) {
id = resultSet.getBigDecimal("ID"); //oracle 的id 字段类型是number ---->java中BigDecimal
}
}
catch (SQLException e) {
throw e;
}
finally {
closeStatement(resultSet, null);
}
return id;
}
数据库和java.sql.Types之间的对应
Oracle与java.sql.Types的对应
Oracle java.sql.Types
blob blob
char char
clob clob
date date
number decimal
long varbinary
nclob,nvarchar2 other
smallint smallint
timestamp timstamp
raw varbinary
varchar2 varchar
Sql server与java.sql.Types的对应
Sql server java.sql.Types
bigint (2005,2008) bigint
timstamp,binary binary
bit bit
char,nchar,unqualified char
datetime date
money,smallmoney,decimal decimal
float (2005,2008) double
float(2000) float
int integer
image longvarbinary
text,ntext,xml longvarchar
numeric numeric
real real
smallint smallint
datetime,smalldatetime timestamp
tinyint tinyint
varbinary varbinay
nvarchar,varchar varchar
DB2与java.sql.Types的对应
bigint bigint
blob blob
character,graphic char
clob clob
date date
decimal decimal
double double
integer integer
longvargraphic longvarchar
longvarchar
real real
smallint smallint
time time
timestamp timestamp
vargraphic varchar
varchar
MySQL与java.sql.Types的对应
MySQL java.sql.Types
bigint bigint
tinyblob binary
bit bit
enum,set,char char
date,year date
decimal,numeric decimal
double,real double
mediumint,int integer
blob,mediumblob blob
longblob
float real
smallint smallint
time time
timestamp,datetime timestamp
tinyint tinyint
varbinary,binary varbinay
varchar,tinytext,text varchar
Sybase与java.sql.Types的对应
Sybase java.sql.Types
binary binary
bit bit
char,nchar, char
money,smallmoney,decimal decimal
float double
int integer
image longvarbinary
text longvarchar
numeric numeric
real real
smallint smallint
datetime,smalldatetime timestamp
tinyint tinyint
varbinar,timestamp varbinay
nvarchar,varchar ,sysname varchar