oracle MyBatis插入列表值

pinkon5k 于 2023-06-29 发布在 Oracle

关注(0)|答案(5)|浏览(159)

Mapper.xml（Mapper xml文件）

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="TestDAO">
<insert id="insertEmployeeList" parameterType="java.util.List">
INSERT INTO EMPLOYEE (id, name) VALUES
<foreach collection="list" item="element" index="index" open="(" separator=","  close=")">
#{element.id}, #{element.name}
</foreach>
</insert>
</mapper>

Employee.java

public class Employee {
  private List<Emp> list = new ArrayList<Emp>();
  public List<Emp> getList() {
    return list;
  }
  public void setList(List<Emp> list) {
    this.list = list;
  }
}

Emp.java

public class Emp {
  public Emp(int id, String name) {
    this.id = id;
    this.name = name;
  }
  private int id;
  private String name;
  public int getId() {
    return id;
  }
  public void setId(int id) {
    this.id = id;
  }
  public String getName() {
    return name;
  }
  public void setName(String name) {
    this.name = name;
  }
}

TestDAO.java

public interface TestDAO {
public Integer insertEmployeeList(List<Emp> empList) throws SQLException;
}

Main.java

public class Main {
   public static void main (String args[]) {
       TestDAO tm = session.getMapper(TestDAO.class);
       Employee e = new Employee();
       Emp e11 = new Emp(123,"abc");
       Emp e12 = new Emp(456,"def");
       e.getList().add(e11);
       e.getList().add(e12);
       tm.insertEmployeeList(e.getList());
   }
}

我得到的异常是：

Error updating database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00913: too many values
The error may involve com.XXXX.sample.test.dao.TestDAO.insertEmployeeList-Inline
The error occurred while setting parameters
Cause: java.sql.SQLSyntaxErrorException: ORA-00913: too many values

oracle

来源：https://stackoverflow.com/questions/17563463/mybatis-insert-list-values

5条答案

按热度按时间

bd1hkmkf1#

当我使用MySQL作为数据库时，经过几次尝试，这就是它对我的工作方式。

<insert id="insert" parameterType="java.util.List">
    INSERT INTO games (
    id,
    game_id,
    game_vendor,
    game_code,
    game_name,
    game_type)
    VALUES
    <foreach collection="list" item="element" index="index" open="(" separator="),("  close=")">
        #{element.id},
        #{element.gameId},
        #{element.gameVendor},
        #{element.gameCode},
        #{element.gameName},
        #{element.gameType},
    </foreach>
</insert>

赞(0）回复(0）举报 2023-06-29

kiayqfof2#

Mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.xxxx.sample.test.dao.TestDAO">
  <insert id="insertEmployeeList" parameterType="java.util.List">
   INSERT ALL  
    <foreach collection="list" item="element" index="index" >
     INTO EMPLOYEE (id,name) values (#{element.id},#{element.name})
    </foreach>
   SELECT * FROM dual
 </insert>
</mapper>

这就是Mapper xml中的查询方式

赞(0）回复(0）举报 2023-06-29

f3temu5u3#

您可以使用annotations（@org.apache.ibatis.annotations.Insert）为整个列表执行单个插入。

记住： 您不需要任何SQL提供程序类。

public interface SomethingMapper {

    @Insert({
        "<script>",
        "INSERT INTO your_database_name.your_table_name",
            "(column1_int, column2_str, column3_date, column4_time)",
        "VALUES" +  
            "<foreach item='each_item_name' collection='theCollection' open='' separator=',' close=''>" +
                "(" +
                    "#{each_item_name.column1,jdbcType=INTEGER},",
                    "#{each_item_name.column2,jdbcType=VARCHAR},",
                    "(SELECT SOME_DB_FUNCTION(#{each_item_name.column3,jdbcType=DATE})),",
                    "#{each_item_name.period.start,jdbcType=TIME}" +
                ")" +
            "</foreach>",
    "</script>"})
    void insertBatchSomething(@Param("theCollection") List<Something> theCollection);

}

如果有2项，则输出SQL语句：

SQL: INSERT INTO your_database_name.your_table_name (column1_int, column2_str, column3_date, column4_time) VALUES (?, ?, (SELECT SOME_DB_FUNCTION(?)), ?), (?, ?, (SELECT SOME_DB_FUNCTION(?)), ?)

附注
@Insert接收一个String[]，因此对于每个值，它将在String之间添加一个空格。

赞(0）回复(0）举报 2023-06-29

wnvonmuf4#

配置您的日志记录系统以打印出生成的查询，然后尝试直接在数据库中执行它们。
在您的例子中，预期的查询应该是

INSERT INTO EMPLOYEE (id, name) VALUES
(123, "abc")(456, "def")

就我记忆所及，这是不正确的。INSERT INTO只需要一组VALUES。我查了一下documentation，它看起来也是这样的。
参见this answer了解如何在oracle中INSERT多行。

赞(0）回复(0）举报 2023-06-29

5f0d552i5#

[tistory] http://woniperstory.tistory.com/194：此链接显示正确答案！！！

public  someClass {

    public void someMethod() {

        Map<String,Object> map =  new HashMap<String,Object>() ;
        List<Emp> list =  new ArrayList<Emp>() ;

        ::::  add Emp Objects to list

        map.put("list", list)

        public Integer insertEmployeeList(map) ;
    }
}

public interface TestDAO {
    public Integer insertEmployeeList(Map<String,Object> map) throws SQLException;
}

<?xml version="1.0" encoding="UTF-8"?>  
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">  
<mapper namespace="TestDAO">  
<insert id="insertEmployeeList" parameterType="java.util.Map">  
INSERT INTO EMPLOYEE (id, name) VALUES  
<foreach collection="list" item="element" index="index" separator=",">  
    ( #{element.id}, #{element.name} )  
</foreach>  
</insert>  
</mapper>

赞(0）回复(0）举报 2023-06-29

我来回答

oracle MyBatis插入列表值

5条答案

相关问题

热门标签

最新问答