在参数化查询中使用CONVERT

Question

我有一些与Oracle数据库交互的java代码，正在将其转换为与SQL Server一起使用。

代码为：

String parameterisedSQL = "INSERT INTO T(a,b) VALUES (?,?)";
try (Connection con = DriverManager.getConnection(connectionString, databaseUser, databasePassword);
PreparedStatement stmt = con.prepareStatement(parameterisedSQL);)
{       
stmt.setString(0,"test");
stmt.setString(1,"CONVERT(DATE, '20201230', 112)");
stmt.executeUpdate();
return true;
} 

显然，当它是Oracle代码时，代码读取stmt.setString(1,"TO_DATE('20201230','YYYYMMDD')");

针对Oracle数据库的Oracle代码运行良好，但现在我正在针对SQL Server数据库运行SQL Server代码，我得到了一个异常

从字符串转换日期和/或时间时转换失败

我认为SQL Server没有正确地将CONVERT作为参数进行解析。有什么办法让它做到这一点吗？(我的代码实际上比显示的更通用，所以很难使用.setDate等(

Answer 1

传递函数的方式如下所示：

INSERT INTO T(a,b) VALUES ('test', 'CONVERT(DATE, '20201230', 112)')

这显然是错误的，因为您试图在date列中插入值'CONVERT(DATE, '20201230', 112)'

你应该这样做：

String parameterisedSQL = "INSERT INTO T(a,b) SELECT ?, CONVERT(DATE, ?, 112)";

和：

stmt.setString(1,"test");
stmt.setString(2,"20201230");

指数以1为基础。

Answer 2

@forpas给出了详细的答案。

此外，对于您的情况，在插入到表之前，不需要转换短日期格式(ISO8601(。您可以直接插入。

关于ISO8601日期格式

ISO 8601描述YYYY-MM-DD

YYYYMMDD与SQL标准相同。此格式是唯一的格式定义为国际标准。

declare @TABLE table(T date)
insert into @TABLE (T) VALUES('20201230')
SELECT * FROM @table

对于您的情况，

stmt.setString(0,"test");
stmt.setString(1,"20201230");

Answer 3

这个问题和其他两个答案都是次优的。您不应该将日期值作为字符串发送到数据库。您应该将其作为日期值发送。

在这种情况下，由于它是一个仅限日期的值，没有时间部分，因此您将使用java.sql.Date或java.time.LocalDate，具体取决于JDBC驱动程序的功能。

还要注意，参数索引在JDBC API中是以1为基础的。

// Old code: Wrong
stmt.setString(0,"test");
stmt.setString(1,"CONVERT(DATE, '20201230', 112)");
// Using java.sql.Date
SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMdd");
stmt.setString(1, "test");
stmt.setDate(2, new java.sql.Date(fmt.parse("20201230")));
// Using java.time.LocalDate
DateTimeFormatter fmt = new DateTimeFormatter.ofPattern("uuuuMMdd");
stmt.setString(1, "test");
stmt.setObject(2, LocalDate.parse("20201230", fmt));