How to create a empty Array using Mybatis and PostgreSQL?

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

How to create a empty Array using Mybatis and PostgreSQL?

Ukonn Ra
Hey guys, when I writing my project using Mybatis and Postgres, something disgusting happened.

You know that Postgres has a wonderful type `ARRAY`, but the only way creating a `java.sql.Array` is `java.sql.Connection:createOf(typeName, arrayContent)`, here is the background.

@MappedTypes(List.class)
@MappedJdbcTypes({JdbcType.ARRAY})
public class ListTypeHandler extends BaseTypeHandler<List> {
 
private static final String TYPE_NAME_UUID = "uuid";

 
@Override
 
public void setNonNullParameter(PreparedStatement ps, int i, List list, JdbcType jdbcType) throws SQLException {
   
Connection conn = ps.getConnection();

   
if(list.size() != 0) {
     
Object[] parameter = list.toArray(new Object[0]);
     
String typeName = null;
     
// using a switch to determine how to map the Java type to the sql type
     
if (list.get(0) instanceof UUID) {
       typeName
= TYPE_NAME_UUID;
     
}

     
if (typeName == null) {
       
throw new TypeException("ArrayTypeHandler parameter typeName error, your type is " + parameter.getClass().getName());
     
}
     
Array array = conn.createArrayOf(typeName, parameter);
     ps
.setArray(i, array);
   
} else {
     
// here is the hard point
     ps
.setArray(i, conn.createArrayOf("uuid", new Object[0]));
   
}
 
}

 
private List getArray(Array array) {
   
try {
     
return Arrays.asList((Object[])array.getArray());
   
} catch (Exception e) {
     
return null;
   
}

 
}
}

Because there is no type in `java.sql.Array` neither in List (because of the fxcking erasing), and there are lots of arrays with different types in my database, so I have to make a unified `TypeHandler` to deal with `Array` and `List` relationship.

My solution is to take the first object of the List and see what the type is, then map it to the database-favored type. Evertthing seems fine.

But when a empty List goes in, all the things mess up. Firstly, I cannot determine the type based on the first element; secondly, I cannot get the type infomation with List object (curse type erasure again); thirdly, I cannot get the sql type with sql.Array Object; forthly, I cannot create a empty sql.Array independent of the type (in my opinion, if a pass a empty array to database, the database should create it based on what the type it catually is...). So in a word, I am stucked...

I wonder maybody can help me out? i am SOOOOOOOO DADDDDDDDD.....

PLEASE Orz.................... 


 

--
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].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: How to create a empty Array using Mybatis and PostgreSQL?

Iwao AVE!
Have you tried retrieving the type name from ParameterMetaData?

ParameterMetaData paramMetaData = ps.getParameterMetaData();
String typeName = paramMetaData.getParameterTypeName(i);
Array array = conn.createArrayOf(typeName, parameter);

Regards,
Iwao

2018-05-07 1:20 GMT+09:00 Ukonn Ra <[hidden email]>:
Hey guys, when I writing my project using Mybatis and Postgres, something disgusting happened.

You know that Postgres has a wonderful type `ARRAY`, but the only way creating a `java.sql.Array` is `java.sql.Connection:createOf(typeName, arrayContent)`, here is the background.

@MappedTypes(List.class)
@MappedJdbcTypes({JdbcType.ARRAY})
public class ListTypeHandler extends BaseTypeHandler<List> {
 
private static final String TYPE_NAME_UUID = "uuid";

 
@Override
 
public void setNonNullParameter(PreparedStatement ps, int i, List list, JdbcType jdbcType) throws SQLException {
   
Connection conn = ps.getConnection();

   
if(list.size() != 0) {
     
Object[] parameter = list.toArray(new Object[0]);
     
String typeName = null;
     
// using a switch to determine how to map the Java type to the sql type
     
if (list.get(0) instanceof UUID) {
       typeName
= TYPE_NAME_UUID;
     
}

     
if (typeName == null) {
       
throw new TypeException("ArrayTypeHandler parameter typeName error, your type is " + parameter.getClass().getName());
     
}
     
Array array = conn.createArrayOf(typeName, parameter);
     ps
.setArray(i, array);
   
} else {
     
// here is the hard point
     ps
.setArray(i, conn.createArrayOf("uuid", new Object[0]));
   
}
 
}

 
private List getArray(Array array) {
   
try {
     
return Arrays.asList((Object[])array.getArray());
   
} catch (Exception e) {
     
return null;
   
}

 
}
}

Because there is no type in `java.sql.Array` neither in List (because of the fxcking erasing), and there are lots of arrays with different types in my database, so I have to make a unified `TypeHandler` to deal with `Array` and `List` relationship.

My solution is to take the first object of the List and see what the type is, then map it to the database-favored type. Evertthing seems fine.

But when a empty List goes in, all the things mess up. Firstly, I cannot determine the type based on the first element; secondly, I cannot get the type infomation with List object (curse type erasure again); thirdly, I cannot get the sql type with sql.Array Object; forthly, I cannot create a empty sql.Array independent of the type (in my opinion, if a pass a empty array to database, the database should create it based on what the type it catually is...). So in a word, I am stucked...

I wonder maybody can help me out? i am SOOOOOOOO DADDDDDDDD.....

PLEASE Orz.................... 


 

--
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].
For more options, visit https://groups.google.com/d/optout.

--
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].
For more options, visit https://groups.google.com/d/optout.