如何使用@InsertProvider在postgresql数组（整数[]）类型列中插入java ArrayList

Question

我有一个mapper方法，定义如下

@InsertProvider(type = ActivityMapperSQLBuilder.class, method = "insertIntoActivityComment")
    public int insertIntoActivityComment(@Param("activityComment")ActivityComment activityComment);

相应的SQLBuilder方法定义为

public String insertIntoActivityComment(Map<String, Object> params) {
        ActivityComment activityComment = (ActivityComment) params
                .get("activityComment");

        params.put("fileIds",  activityComment.getFileIds());
        params.put("commentType",activityComment.getCommentType());
        params.put("commentText",activityComment.getCommentText());
        params.put("commentingUserId",activityComment.getCommentingUser().getId());
        params.put("attachments",activityComment.getAttachments());
        params.put("activityId",activityComment.getActivity().getId());

        StringBuilder builder = new StringBuilder(
                "insert into activityComment (commenttype, commenttext, commentdate, commentinguser_id, attachments, activity_id, fileids) VALUES "
                        + " (#{commentType}, #{commentText}, now(), #{commentingUserId}, #{attachments}, #{activityId}, #{fileIds} )");

        return builder.toString();
    }

每当我调用我的mappermethod时，如下所示

getActivityMapper().insertIntoActivityComment(activityComment);

我遇到了以下错误。

error updating database.  Cause: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.

错误可能涉及ActivityMapper。insertIntoActivityComment Inline设置参数时出错\n####SQL:insert into activityComment（commenttype、commenttext、commentdate、commentinguser_id、attachments、activity_id、FileID）值（？，now（），，，，，？）\原因：组织。postgresql。util。PSQLException：无法推断用于java实例的SQL类型。util。ArrayList。使用带有显式类型值的setObject（）指定要使用的类型。"

我的ActivityComment对象的结构是

public class ActivityComment implements Serializable, IsSerializable {
    private static final long serialVersionUID = 1L;
    private int id;
    private ActivityCommentType commentType;
    private String commentText;
    private Timestamp commentDate;
    private Assignment activity;
    private User commentingUser;
    private String attachments;
    private List<Integer> fileIds = new ArrayList<Integer>();

/*getters and setters*
}

有人能帮我把ArrayList插入postgresql数据库吗？

Answer 1

你需要做的是，获取要插入的数据。然后必须将它们添加到arrayList中。然后我假设您将把arrayList传递给一个函数，该函数负责将数据插入数据库。

要插入arraylist，需要动态地构建查询框架。

我将为您编写一个示例代码，如果需要，可以做一些更改。

//将数据添加到ArrayList中

ArrayList<String> data=new ArrayList<String>();
data.add(value1);
data.add(value2);
data.add(value3);
data.add(value4);
insertIntoMyDatabase(data); // function that will take this arraylist form a query and do the insertion.

//InsertInToMy数据库：

public void insertIntoMyDatabase(ArrayList<String> data) {
PreparedStatement p;
String newdata="";
  for(i=0;i<data.size();i++){ // This loop will take care of framing the query dynamically

                if(i==data.size()-1){
                    newdata+="'"+data.toArray()[i]+"'";
                }else{
                    newdata+="'"+data.toArray()[i]+"',";
                }
String query="insert into table values("+newdata+")"; // This is the framed query for inserting data
try{
p=con.prepareStatement(query);
ResultSet r=pr.executeQuery();
}catch(SQLExecption e){
e.printStacktrace();
}

}