问题描述

jdbc批量插入，数据只有一条

我统一10条插入一次，可是最后数据库只有第十条数据，前九条都没有，插入几次都是，怎么办
List backList = new ArrayList();
Connection con = getConnection();
PreparedStatement stat = null;
String str= null;
int j = 0;
for(int i=0;i<list.size();i++){
str = list.get(i);
String[] arr = str.split("");
j++;
if(arr.length ==10){
try {

                String name = arr[0];                String pinyin = arr[1];                String age = arr[2];                String sex = arr[3];                String phone = arr[4];                String mobilephone = arr[5];                String address = arr[6];                String birthday = arr[7];                String birthPlace = arr[8];                String company = arr[9];                //根据参数动态拼一个insert语句(""+deptno+""'""+dname+""''""+loc+""')""                String sql = ""insert into user_info ""+""values ('""+name+""''""+pinyin+""''""+age+""''""+                sex+""''""+phone+""''""+mobilephone+""''""+address+""''""+birthday+""''""+birthPlace+""''""+company+""')"";                stat = con.prepareStatement(sql);                System.out.println(sql);                stat.addBatch();                System.out.println(""sql语句加入批处理中"");            } catch (SQLException e2) {                // TODO Auto-generated catch block            }        }        if(j == list.size()){            int s [] = stat.executeBatch();            for(int a:s){                System.out.println(a);            }        }    }        try {            stat.close();            con.close();        } catch (SQLException e1) {            e1.printStackTrace();        }

解决方案

PreparedStatement定义要在for外面定义具有占位符的statement，for中只负责设参数值。
示例代码：

 public int insertTCollectionBatched(List<TCollection> list) {        Connection conn = ConnectionPool.getConnection();        PreparedStatement stat = null;        ResultSet rs = null;        String sql = ""INSERT INTO table"" +         + "" (ParentID ScanTime ExtParam1 ExtParam2)"" + VALUES (????)"";        try {            conn.setAutoCommit(false);            stat = conn.prepareStatement(sql);            int[] counts = {};            for(TCollection collection:list){                setPropertyValue(collection stat);                stat.addBatch();            }            counts = stat.executeBatch();            conn.commit();            return counts.length;        }catch(Exception e){            LogUtil.error(e);        }finally{            ConnectionPool.attemptClose(rs stat conn);        }        return 0 ;    }

SQL占位参数设值代码：

 private void setPropertyValue(TCollection collection PreparedStatement stat)            throws SQLException {    //根据属性值是否传入，设置SQL INT类型参数，未传递的设置默认值，避免SQL异常    if(collection.getParentId()!=null){        stat.setInt(1 collection.getParentId());    }else{        stat.setInt(1 0);    }    if(collection.getScanTime()!=null){        stat.setString(2 collection.getScanTime());    }else{        stat.setString(2 DateTimeUtil.nowAsString());    }    //必须非空    stat.setString(3 collection.getParam1());    stat.setString(4 collection.getParam2());}

解决方案二：
那你跟踪一下代码啊，看执行了几次sql arr.length ==10这个条件满足没

解决方案三：
加点日志分析等，运行打印一下就能跟踪出具体运行时的情况了。

解决方案四：
静态的SQL批量执行

 String sql = ""insert into user_info ""+""values ('""+name+""''""+pinyin+""''""+age+""''""+                sex+""''""+phone+""''""+mobilephone+""''""+address+""''""+birthday+""''""+birthPlace+""''""+company+""')"";                //stat = con.prepareStatement(sql);//这种似乎得有占位符                System.out.println(sql);                stat.addBatch(sql);

解决方案五：
list.size()这里的list哪里来的啊？