Search SQL Server Always Encrypted DateTime with Java

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Search SQL Server Always Encrypted DateTime with Java

Crater Void
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/77983ff5-f62b-4043-b92e-ba7d03276cd0%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: Search SQL Server Always Encrypted DateTime with Java

Tim “h3adache” Chen
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"
I'm not actually sure about the semantic difference between setTimestamp and setDateTime  
It seems a similar discussion is still open on mssql-jdbc github https://github.com/microsoft/mssql-jdbc/issues/443
Are 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 <[hidden email]> 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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/77983ff5-f62b-4043-b92e-ba7d03276cd0%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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAPqCCxOe50DRvcX085GPBF1cWeSburevytx25gsMdDn2rLhyCQ%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: Search SQL Server Always Encrypted DateTime with Java

Crater Void


On Monday, November 18, 2019 at 4:43:25 PM UTC-7, h3adache wrote:
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"
I'm not actually sure about the semantic difference between setTimestamp and setDateTime  
It seems a similar discussion is still open on mssql-jdbc github <a onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmicrosoft%2Fmssql-jdbc%2Fissues%2F443\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGFVv9XDb7swmfKtBcrqMghk0ky4A&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmicrosoft%2Fmssql-jdbc%2Fissues%2F443\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGFVv9XDb7swmfKtBcrqMghk0ky4A&#39;;return true;" href="https://github.com/microsoft/mssql-jdbc/issues/443" target="_blank" rel="nofollow">https://github.com/microsoft/mssql-jdbc/issues/443
Are you able to change the schema from datetime to datetime2 as suggested in the linked mssql-jdbc issue?

Thanks, unfortunately I don't have access to change the schema.

I tried creating a custom type handler for dates but BaseTypeHandler expects PreparedStatement not SQLServerPreparedStatement, and simply casting a PreparedStatement to SQLServerPreparedStatement did not work. 


On Mon, Nov 18, 2019 at 12:31 PM Crater Void <<a onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;" href="javascript:" target="_blank" rel="nofollow" gdf-obfuscated-mailto="Ai0sD0MgCQAJ">crater...@...> 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 <a onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;" href="javascript:" target="_blank" rel="nofollow" gdf-obfuscated-mailto="Ai0sD0MgCQAJ">mybati...@googlegroups.com.
To view this discussion on the web visit <a onmousedown="this.href=&#39;https://groups.google.com/d/msgid/mybatis-user/77983ff5-f62b-4043-b92e-ba7d03276cd0%40googlegroups.com?utm_medium\x3demail\x26utm_source\x3dfooter&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/msgid/mybatis-user/77983ff5-f62b-4043-b92e-ba7d03276cd0%40googlegroups.com?utm_medium\x3demail\x26utm_source\x3dfooter&#39;;return true;" href="https://groups.google.com/d/msgid/mybatis-user/77983ff5-f62b-4043-b92e-ba7d03276cd0%40googlegroups.com?utm_medium=email&amp;utm_source=footer" target="_blank" rel="nofollow">https://groups.google.com/d/msgid/mybatis-user/77983ff5-f62b-4043-b92e-ba7d03276cd0%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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/72fd3f2f-4504-4e84-96cc-2210fae18fb9%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: Search SQL Server Always Encrypted DateTime with Java

Tim “h3adache” Chen
Yea that's unfortunate. It doesn't know to create a SQLServerPreparedStatement for you.
Can you create a sample github project?
It might be something you should raise a bug on mssql-jdbc about.
Past experiences they are very quick to respond and helpful.



On Tue, Nov 19, 2019 at 8:28 AM Crater Void <[hidden email]> wrote:


On Monday, November 18, 2019 at 4:43:25 PM UTC-7, h3adache wrote:
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"
I'm not actually sure about the semantic difference between setTimestamp and setDateTime  
It seems a similar discussion is still open on mssql-jdbc github https://github.com/microsoft/mssql-jdbc/issues/443
Are you able to change the schema from datetime to datetime2 as suggested in the linked mssql-jdbc issue?

Thanks, unfortunately I don't have access to change the schema.

I tried creating a custom type handler for dates but BaseTypeHandler expects PreparedStatement not SQLServerPreparedStatement, and simply casting a PreparedStatement to SQLServerPreparedStatement did not work. 


On Mon, Nov 18, 2019 at 12:31 PM Crater Void <[hidden email]> 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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/77983ff5-f62b-4043-b92e-ba7d03276cd0%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 [hidden email].
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAPqCCxNbvm1w513n%2BJGmFRzVjQ4sOf3TDHMvr5tMwS7NjSSGrA%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: Search SQL Server Always Encrypted DateTime with Java

Iwao AVE!

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


On Wed, Nov 20, 2019 at 3:51 AM Tim <[hidden email]> wrote:
Yea that's unfortunate. It doesn't know to create a SQLServerPreparedStatement for you.
Can you create a sample github project?
It might be something you should raise a bug on mssql-jdbc about.
Past experiences they are very quick to respond and helpful.



On Tue, Nov 19, 2019 at 8:28 AM Crater Void <[hidden email]> wrote:


On Monday, November 18, 2019 at 4:43:25 PM UTC-7, h3adache wrote:
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"
I'm not actually sure about the semantic difference between setTimestamp and setDateTime  
It seems a similar discussion is still open on mssql-jdbc github https://github.com/microsoft/mssql-jdbc/issues/443
Are you able to change the schema from datetime to datetime2 as suggested in the linked mssql-jdbc issue?

Thanks, unfortunately I don't have access to change the schema.

I tried creating a custom type handler for dates but BaseTypeHandler expects PreparedStatement not SQLServerPreparedStatement, and simply casting a PreparedStatement to SQLServerPreparedStatement did not work. 


On Mon, Nov 18, 2019 at 12:31 PM Crater Void <[hidden email]> 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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/77983ff5-f62b-4043-b92e-ba7d03276cd0%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 [hidden email].
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAPqCCxNbvm1w513n%2BJGmFRzVjQ4sOf3TDHMvr5tMwS7NjSSGrA%40mail.gmail.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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2SRvqZFFiOzc8MZV4GfifaMU_%2Ba4L%2BzAPVgUUGSdKcp1A%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: Search SQL Server Always Encrypted DateTime with Java

Crater Void
Hey, that worked.  Thanks much Iwao!

On Tuesday, November 19, 2019 at 5:35:41 PM UTC-7, Iwao AVE! wrote:

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


On Wed, Nov 20, 2019 at 3:51 AM Tim <<a onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;" href="javascript:" target="_blank" rel="nofollow" gdf-obfuscated-mailto="F86lV2d1BQAJ">che...@...> wrote:
Yea that's unfortunate. It doesn't know to create a SQLServerPreparedStatement for you.
Can you create a sample github project?
It might be something you should raise a bug on mssql-jdbc about.
Past experiences they are very quick to respond and helpful.



On Tue, Nov 19, 2019 at 8:28 AM Crater Void <<a onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;" href="javascript:" target="_blank" rel="nofollow" gdf-obfuscated-mailto="F86lV2d1BQAJ">crater...@...> wrote:


On Monday, November 18, 2019 at 4:43:25 PM UTC-7, h3adache wrote:
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"
I'm not actually sure about the semantic difference between setTimestamp and setDateTime  
It seems a similar discussion is still open on mssql-jdbc github <a onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmicrosoft%2Fmssql-jdbc%2Fissues%2F443\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGFVv9XDb7swmfKtBcrqMghk0ky4A&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmicrosoft%2Fmssql-jdbc%2Fissues%2F443\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGFVv9XDb7swmfKtBcrqMghk0ky4A&#39;;return true;" href="https://github.com/microsoft/mssql-jdbc/issues/443" target="_blank" rel="nofollow">https://github.com/microsoft/mssql-jdbc/issues/443
Are you able to change the schema from datetime to datetime2 as suggested in the linked mssql-jdbc issue?

Thanks, unfortunately I don't have access to change the schema.

I tried creating a custom type handler for dates but BaseTypeHandler expects PreparedStatement not SQLServerPreparedStatement, and simply casting a PreparedStatement to SQLServerPreparedStatement did not work. 


On Mon, Nov 18, 2019 at 12:31 PM Crater Void <[hidden email]> 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 [hidden email].
To view this discussion on the web visit <a onmousedown="this.href=&#39;https://groups.google.com/d/msgid/mybatis-user/77983ff5-f62b-4043-b92e-ba7d03276cd0%40googlegroups.com?utm_medium\x3demail\x26utm_source\x3dfooter&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/msgid/mybatis-user/77983ff5-f62b-4043-b92e-ba7d03276cd0%40googlegroups.com?utm_medium\x3demail\x26utm_source\x3dfooter&#39;;return true;" href="https://groups.google.com/d/msgid/mybatis-user/77983ff5-f62b-4043-b92e-ba7d03276cd0%40googlegroups.com?utm_medium=email&amp;utm_source=footer" target="_blank" rel="nofollow">https://groups.google.com/d/msgid/mybatis-user/77983ff5-f62b-4043-b92e-ba7d03276cd0%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 <a onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;" href="javascript:" target="_blank" rel="nofollow" gdf-obfuscated-mailto="F86lV2d1BQAJ">mybati...@googlegroups.com.
To view this discussion on the web visit <a onmousedown="this.href=&#39;https://groups.google.com/d/msgid/mybatis-user/72fd3f2f-4504-4e84-96cc-2210fae18fb9%40googlegroups.com?utm_medium\x3demail\x26utm_source\x3dfooter&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/msgid/mybatis-user/72fd3f2f-4504-4e84-96cc-2210fae18fb9%40googlegroups.com?utm_medium\x3demail\x26utm_source\x3dfooter&#39;;return true;" href="https://groups.google.com/d/msgid/mybatis-user/72fd3f2f-4504-4e84-96cc-2210fae18fb9%40googlegroups.com?utm_medium=email&amp;utm_source=footer" target="_blank" rel="nofollow">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 <a onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;" href="javascript:" target="_blank" rel="nofollow" gdf-obfuscated-mailto="F86lV2d1BQAJ">mybati...@googlegroups.com.
To view this discussion on the web visit <a onmousedown="this.href=&#39;https://groups.google.com/d/msgid/mybatis-user/CAPqCCxNbvm1w513n%2BJGmFRzVjQ4sOf3TDHMvr5tMwS7NjSSGrA%40mail.gmail.com?utm_medium\x3demail\x26utm_source\x3dfooter&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/msgid/mybatis-user/CAPqCCxNbvm1w513n%2BJGmFRzVjQ4sOf3TDHMvr5tMwS7NjSSGrA%40mail.gmail.com?utm_medium\x3demail\x26utm_source\x3dfooter&#39;;return true;" href="https://groups.google.com/d/msgid/mybatis-user/CAPqCCxNbvm1w513n%2BJGmFRzVjQ4sOf3TDHMvr5tMwS7NjSSGrA%40mail.gmail.com?utm_medium=email&amp;utm_source=footer" target="_blank" rel="nofollow">https://groups.google.com/d/msgid/mybatis-user/CAPqCCxNbvm1w513n%2BJGmFRzVjQ4sOf3TDHMvr5tMwS7NjSSGrA%40mail.gmail.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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/e09eef70-54eb-493b-b7c5-acf32b7a771c%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: Search SQL Server Always Encrypted DateTime with Java

Tim “h3adache” Chen
Neat Iwao! I’ve never had to use that before but that definitely something handy to remember 
On Nov 20, 2019, 10:45 -0500, Crater Void <[hidden email]>, wrote:
Hey, that worked.  Thanks much Iwao!

On Tuesday, November 19, 2019 at 5:35:41 PM UTC-7, Iwao AVE! wrote:

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


On Wed, Nov 20, 2019 at 3:51 AM Tim <<a href="javascript:" target="_blank" rel="nofollow" gdf-obfuscated-mailto="F86lV2d1BQAJ">che...@...> wrote:
Yea that's unfortunate. It doesn't know to create a SQLServerPreparedStatement for you.
Can you create a sample github project?
It might be something you should raise a bug on mssql-jdbc about.
Past experiences they are very quick to respond and helpful.



On Tue, Nov 19, 2019 at 8:28 AM Crater Void <<a href="javascript:" target="_blank" rel="nofollow" gdf-obfuscated-mailto="F86lV2d1BQAJ">crater...@...> wrote:


On Monday, November 18, 2019 at 4:43:25 PM UTC-7, h3adache wrote:
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"
I'm not actually sure about the semantic difference between setTimestamp and setDateTime  
It seems a similar discussion is still open on mssql-jdbc github https://github.com/microsoft/mssql-jdbc/issues/443
Are you able to change the schema from datetime to datetime2 as suggested in the linked mssql-jdbc issue?

Thanks, unfortunately I don't have access to change the schema.

I tried creating a custom type handler for dates but BaseTypeHandler expects PreparedStatement not SQLServerPreparedStatement, and simply casting a PreparedStatement to SQLServerPreparedStatement did not work. 


On Mon, Nov 18, 2019 at 12:31 PM Crater Void <[hidden email]> 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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/77983ff5-f62b-4043-b92e-ba7d03276cd0%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 <a href="javascript:" target="_blank" rel="nofollow" gdf-obfuscated-mailto="F86lV2d1BQAJ">mybati...@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.

--
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 <a href="javascript:" target="_blank" rel="nofollow" gdf-obfuscated-mailto="F86lV2d1BQAJ">mybati...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAPqCCxNbvm1w513n%2BJGmFRzVjQ4sOf3TDHMvr5tMwS7NjSSGrA%40mail.gmail.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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/e09eef70-54eb-493b-b7c5-acf32b7a771c%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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/1e7d6636-9eef-41ed-b01d-adce09779a2c%40Spark.