JDBC和mysql及oracle中的Date,Time,Timestamp处理

JDBC和mysql及oracle中的Date,Time,Timestamp处理

1.MySQL的JDBC类型映射

mysql时间类型	jdbc时间类型
DATE	java.sql.Date
DATETIME	java.sql.Timestamp
TIMESTAMP	java.sql.Timestamp
TIME	java.sql.Time

2.Oracle与JDBC之间的类型映射

oracle时间类型	jdbc时间类型
DATE	java.sql.Date
TIMESTAMP	java.sql.Timestamp
TIME	java.sql.Time

  JDBC读取的数据保存为java类型时候，应该定义为java.util.Date，这样可以保持原有的精度，尽量避免使用java.sql.Date，java.sql.Date会则造成时分秒丢失。

  mysql数据库，时间类型为DATETIME，jdbc插入一条记录写法示例：

 public int insert(Register register)
    {
        Timestamp createTime = null;
        Timestamp dueTime = null;
        Timestamp sendTime = null;
        Timestamp operateTime = null;
        if (register.getCreateTime() != null)
        {
            java.util.Date createdate = new java.util.Date(register.getCreateTime().getTime());
            createTime = new Timestamp(createdate.getTime());
        }
        if (register.getDueTime() != null)
        {
            java.util.Date dueDate = new java.util.Date(register.getDueTime().getTime());
            dueTime = new Timestamp(dueDate.getTime());
        }
        if (register.getSendTime() != null)
        {
            java.util.Date sendDate = new java.util.Date(register.getSendTime().getTime());
            sendTime = new Timestamp(sendDate.getTime());
        }
        if (register.getOperateTime() != null)
        {
            java.util.Date operateDate = new java.util.Date(register.getOperateTime().getTime());
            operateTime = new Timestamp(operateDate.getTime());
        }
        String sql = "insert into dataview_register_info(ID, NAME, EMAIL, COMPANY_NAME, INDUSTRY, INTENDED_USE, CREATE_TIME, URL, LICENSE_STATE, PRODUCT_NAME,VERSION, DUE_TIME,"
                + "LICENSE_TYPE, AUTHORIZED_MODULES, APPLY_AMOUNT, MACHINE_CODE,LICENSE_FILE,SEND_STATE,SEND_TIME,OPERATOR_ACCOUNT,OPERATE_TIME,SERVER_INFO,APPLY_SOURCE) "
                + "values('" + register.getId() + "', '" + register.getName() + "','" + register.getEmail() + "','"
                + register.getCompanyName() + "','" + register.getIndustry() + "', '" + register.getIntendedUse()
                + "','" + createTime + "','" + register.getUrl() + "','" + register.getLicenseState() + "','"
                + register.getProductName() + "','" + register.getVersion() + "','" + dueTime + "','"
                + register.getLicenseType() + "','" + register.getAuthorizedModules() + "','"
                + register.getApplyAmount() + "','" + register.getMachineCode() + "','" + register.getLicenseFile()
                + "','" + register.getSendState() + "','" + sendTime + "','" + register.getOperatorAccount() + "','"
                + operateTime + "','" + register.getServerInfo() + "','" + register.getApplySource() + "')";
        int state = 0;
        state = DBUtil.insert(sql);
        LOG.info(sql);
        return state;
    }