Connection conn = db.getConnectionSQL_SERVER();
String sql = "SELECT purchase_date FROM Customers WHERE purchase_date=?";
try {
SQLServerPreparedStatement ps = (SQLServerPreparedStatement) conn.prepareStatement(sql);
SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
java.util.Date pd= formatter.parse("07/04/2019");
ps.setDateTime(1, new java.sql.Timestamp(pd.getTime()));
ResultSet rs = ps.executeQuery();
while(rs.next()) {
System.out.println(rs.getDate(1));
} catch (Exception e) { e.printStackTrace(); }
<result property="purchaseDate" javaType="java.util.Date" jdbcType="DATE" typeHandler="DateTypeHandler" column="purchase_date"/>
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The data types datetime encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Encryption_Key', column_encryption_key_database_name = customerdb) and datetime2(7) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Encryption_Key', column_encryption_key_database_name = customerdb) are incompatible in the equal to operator.
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/77983ff5-f62b-4043-b92e-ba7d03276cd0%40googlegroups.com.
There is no setDateTime handler in mybatis by default. The jdbc api doesn't define that.You can create a custom typehandler and use it by replacing typeHandler="DateTypeHandler"It seems a similar discussion is still open on mssql-jdbc github https://github.com/microsoft/mssql-jdbc/issues/443I'm not actually sure about the semantic difference between setTimestamp and setDateTimeAre you able to change the schema from datetime to datetime2 as suggested in the linked mssql-jdbc issue?
On Mon, Nov 18, 2019 at 12:31 PM Crater Void <crater...@gmail.com> wrote:
--I have a SQL Server database with Always Encrypted columns and I want to do a date search. The following code works with a jdbc PreparedStatement but not mybatis:
Connection conn = db.getConnectionSQL_SERVER();
String sql = "SELECT purchase_date FROM Customers WHERE purchase_date=?";
try {
SQLServerPreparedStatement ps = (SQLServerPreparedStatement) conn.prepareStatement(sql);
SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
java.util.Date pd= formatter.parse("07/04/2019");
ps.setDateTime(1, new java.sql.Timestamp(pd.getTime()));
ResultSet rs = ps.executeQuery();
while(rs.next()) {
System.out.println(rs.getDate(1));
} catch (Exception e) {e.printStackTrace();}
In order for the above to work I had to use a SQLServerPreparedStatement and ps.setDateTime. The Always Encrypted documentation says that if my column is defined as datetime (vs datetime2), I must use this method and not ps.setTimestamp.My question is what configuration should I use in my MyBatis xml file for this to work?I have tried:
<result property="purchaseDate" javaType="java.util.Date" jdbcType="DATE" typeHandler="DateTypeHandler" column="purchase_date"/>
Changing the javaType and jdbType to timestamp did not help. The error I get with mybatis is:
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The data types datetime encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Encryption_Key', column_encryption_key_database_name = customerdb) and datetime2(7) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Encryption_Key', column_encryption_key_database_name = customerdb) are incompatible in the equal to operator.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybati...@googlegroups.com.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/72fd3f2f-4504-4e84-96cc-2210fae18fb9%40googlegroups.com.
Hello,
I haven’t tested it with Always Encrypted, but calling unwrap() should work.
public void setNonNullParameter(PreparedStatement ps, int i,
Date parameter, JdbcType jdbcType) throws SQLException {
ps.unwrap(SQLServerPreparedStatement.class)
.setDateTime(i, new java.sql.Timestamp(parameter.getTime());
}
Regards,
Iwao
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAPqCCxNbvm1w513n%2BJGmFRzVjQ4sOf3TDHMvr5tMwS7NjSSGrA%40mail.gmail.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/72fd3f2f-4504-4e84-96cc-2210fae18fb9%40googlegroups.com.
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybati...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAPqCCxNbvm1w513n%2BJGmFRzVjQ4sOf3TDHMvr5tMwS7NjSSGrA%40mail.gmail.com.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/e09eef70-54eb-493b-b7c5-acf32b7a771c%40googlegroups.com.