遇到插入时间戳时的ORA-01858错误。

huangapple go评论51阅读模式
英文:

Getting ORA-01858 error while inserting time stamp

问题

我的前端时间戳格式如下:

2020-7-23 14:36:43.132000000

我的 Oracle 数据库中的时间戳格式如下:

23-07-20 02:36:43.132000000 PM

我已经更改了时间格式如下:

private String OracleTsString(String createdDate) {
    SimpleDateFormat inputFormat = null;
    SimpleDateFormat outputFormat = null;
    try {
        inputFormat = new SimpleDateFormat("yyyy-M-dd HH:mm:ss.SSSSSSSSS");
        Date date;
        date = inputFormat.parse(createdDate);
        outputFormat = new SimpleDateFormat("MM-dd-yy hh:mm:ss.SSSSSSSSS aa");
    }
    // 异常处理
}

但是当我尝试将字符串插入数据库时,我收到了 ORA-01858: 非数字字符出现在应该是数字的位置** 的错误。

以下是我的查询:

jdbcTemplate.update(query, 
    editSftpBean.getSftpName(),
    editSftpBean.getIp_address(),
    editSftpBean.getPort_number(),
    editSftpBean.getUserName(),
    editSftpBean.getAuthentication(),
    oracleTs//我的转换后的字符串
);

插入查询:

INSERT INTO tabl_two (SFTP_NAME, IP_ADDRESS, PORT_NUMBER, USER_NAME, AUTH_TYPE, CREATED_DATE) VALUES (?, ?, ?, ?, ?)

我该如何解决这个问题?

英文:

My timestamp format in front end is like this

2020-7-23 14:36:43. 132000000

My timestamp in oracle db is like this

23-07-20 02:36:43.132000000 PM

I have changed the time format like below

private String OracleTsString(String createdDate)  {
		SimpleDateFormat inputFormat=null;
		SimpleDateFormat outputFormat = null;
		try {
		
		  inputFormat = new SimpleDateFormat("yyyy-M-dd HH:mm:ss.SSSSSSSSS");
		  Date date;
		  date = inputFormat.parse(createdDate);
		   outputFormat = new SimpleDateFormat("MM-dd-yy hh:mm:ss.SSSSSSSSS aa");
		
		}
   //Exception Handling

But when I try to insert the string into database , I am getting ORA-01858: a non-numeric character was found where a numeric was expected

Below is my query

jdbcTemplate.update(query, 
				editSftpBean.getSftpName(),
				editSftpBean.getIp_address(),
				editSftpBean.getPort_number(),
				editSftpBean.getUserName(),
				editSftpBean.getAuthentication(),
				oracleTs//My converted string
);

insert query::

 INSERT INTO tabl_two (SFTP_NAME, IP_ADDRESS, PORT_NUMBER, USER_NAME, AUTH_TYPE,CREATED_DATE ) VALUES (?, ?, ?, ?, ?)

How can I resolve this?

答案1

得分: 3

不要使用String将日期/时间值插入数据库,而要使用Timestamp

private static Timestamp parseTsString(String createdDate) {
    DateTimeFormatter fmt = new DateTimeFormatterBuilder()
            .appendPattern("uuuu-M-d H:mm:ss")
            .appendFraction(ChronoField.NANO_OF_SECOND, 0, 9, true)
            .toFormatter();
    return Timestamp.valueOf(LocalDateTime.parse(createdDate, fmt));
}
英文:

Don't use a String to insert a date/time value into the database, use a Timestamp.

private static Timestamp parseTsString(String createdDate) {
	DateTimeFormatter fmt = new DateTimeFormatterBuilder()
			.appendPattern("uuuu-M-d H:mm:ss")
			.appendFraction(ChronoField.NANO_OF_SECOND, 0, 9, true)
			.toFormatter();
	return Timestamp.valueOf(LocalDateTime.parse(createdDate, fmt));
}

huangapple
  • 本文由 发表于 2020年7月30日 11:42:21
  • 转载请务必保留本文链接:https://go.coder-hub.com/63165858.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定