• Spring利用JDBCTemplate实现批量插入和返回id


    1、先介绍一下java.sql.Connection接口提供的三个在执行插入语句后可取的自动生成的主键的方法:
    //第一个是 
    PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException;   
    其中autoGenerateKeys 有两个可选值:Statement.RETURN_GENERATED_KEYS、Statement.NO_GENERATED_KEYS        
    //第二个是
    PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException;
    //第三个是
    PreparedStatement prepareStatement(String sql, String[] columnNames)throws SQLEception;
    
    
    

    //
    批量插入Person实例,返回每条插入记录的主键值 public int[] insert(List<Person> persons) throws SQLException{ String sql = "insert into test_table(name) values(?)" ; int i = 0 ; int rowCount = persons.size() ; int[] keys = new int[rowCount] ; DataSource ds = SimpleDBSource.getDB() ; Connection conn = ds.getConnection() ; //根据主键列名取得自动生成主键值 String[] columnNames= {"id"} ; PreparedStatement pstmt = conn.prepareStatement(sql, columnNames) ; Person p = null ; for (i = 0 ; i < rowCount ; i++){ p = persons.get(i) ; pstmt.setString(1, p.getName()) ; pstmt.addBatch(); } pstmt.executeBatch() ; //取得自动生成的主键值的结果集 ResultSet rs = pstmt.getGeneratedKeys() ; while(rs.next() && i < rowCount){ keys[i] = rs.getInt(1) ; i++ ; } return keys ; }

    2、下面是Spring的JDBCTemplate实例

    插入一条记录返回刚插入记录的id

    Java代码

    
    public int addBean(final Bean b){   
               
            final String strSql = "insert into buy(id,c,s,remark,line,cdatetime," +   
                    "c_id,a_id,count,type) values(null,?,?,?,?,?,?,?,?,?)";   
            KeyHolder keyHolder = new GeneratedKeyHolder();   
               
            this.getJdbcTemplate().update(   
                    new PreparedStatementCreator(){   
                        public java.sql.PreparedStatement createPreparedStatement(Connection conn) throws SQLException{   
                            int i = 0;   
                            java.sql.PreparedStatement ps = conn.prepareStatement(strSql);    
                            ps = conn.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);   
                            ps.setString(++i, b.getC());   
                            ps.setInt(++i,b.getS() );   
                            ps.setString(++i,b.getR() );   
                            ps.setString(++i,b.getline() );   
                            ps.setString(++i,b.getCDatetime() );   
                            ps.setInt(++i,b.getCId() );   
                            ps.setInt(++i,b.getAId());   
                            ps.setInt(++i,b.getCount());   
                            ps.setInt(++i,b.getType());   
                            return ps;   
                        }   
                    },   
                    keyHolder);   
           
            return keyHolder.getKey().intValue();   
        }  
    
    public int addBean(final Bean b){
            
            final String strSql = "insert into buy(id,c,s,remark,line,cdatetime," +
                    "c_id,a_id,count,type) values(null,?,?,?,?,?,?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            
            this.getJdbcTemplate().update(
                    new PreparedStatementCreator(){
                        public java.sql.PreparedStatement createPreparedStatement(Connection conn) throws SQLException{
                            int i = 0;
                            java.sql.PreparedStatement ps = conn.prepareStatement(strSql); 
                            ps = conn.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);
                            ps.setString(++i, b.getC());
                            ps.setInt(++i,b.getS() );
                            ps.setString(++i,b.getR() );
                            ps.setString(++i,b.getline() );
                            ps.setString(++i,b.getCDatetime() );
                            ps.setInt(++i,b.getCId() );
                            ps.setInt(++i,b.getAId());
                            ps.setInt(++i,b.getCount());
                            ps.setInt(++i,b.getType());
                            return ps;
                        }
                    },
                    keyHolder);
        
            return keyHolder.getKey().intValue();
        }
    
     2.批量插入数据
    
    Java代码
    public void addBuyBean(List<BuyBean> list)    
        {    
           final List<BuyBean> tempBpplist = list;    
           String sql="insert into buy_bean(id,bid,pid,s,datetime,mark,count)" +   
                " values(null,?,?,?,?,?,?)";    
           this.getJdbcTemplate().batchUpdate(sql,new BatchPreparedStatementSetter() {   
      
                @Override  
                public int getBatchSize() {   
                     return tempBpplist.size();    
                }   
                @Override  
                public void setValues(PreparedStatement ps, int i)   
                        throws SQLException {   
                      ps.setInt(1, tempBpplist.get(i).getBId());    
                      ps.setInt(2, tempBpplist.get(i).getPId());    
                      ps.setInt(3, tempBpplist.get(i).getS());    
                      ps.setString(4, tempBpplist.get(i).getDatetime());    
                      ps.setString(5, tempBpplist.get(i).getMark());                    
                      ps.setInt(6, tempBpplist.get(i).getCount());   
                }    
          });    
        }  
    
    public void addBuyBean(List<BuyBean> list) 
        { 
           final List<BuyBean> tempBpplist = list; 
           String sql="insert into buy_bean(id,bid,pid,s,datetime,mark,count)" +
                   " values(null,?,?,?,?,?,?)"; 
           this.getJdbcTemplate().batchUpdate(sql,new BatchPreparedStatementSetter() {
    
                @Override
                public int getBatchSize() {
                     return tempBpplist.size(); 
                }
                @Override
                public void setValues(PreparedStatement ps, int i)
                        throws SQLException {
                      ps.setInt(1, tempBpplist.get(i).getBId()); 
                      ps.setInt(2, tempBpplist.get(i).getPId()); 
                      ps.setInt(3, tempBpplist.get(i).getS()); 
                      ps.setString(4, tempBpplist.get(i).getDatetime()); 
                      ps.setString(5, tempBpplist.get(i).getMark());                  
                      ps.setInt(6, tempBpplist.get(i).getCount());
                } 
          }); 
        }
    
     3.批量插入并返回批量id
    
    注:由于JDBCTemplate不支持批量插入后返回批量id,所以此处使用jdbc原生的方法实现此功能
    
    Java代码
    public List<Integer> addProduct(List<ProductBean> expList) throws SQLException {   
           final List<ProductBean> tempexpList = expList;   
             
           String sql="insert into product(id,s_id,status,datetime,"  
                + " count,o_id,reasons"  
                + " values(null,?,?,?,?,?,?)";   
              
           DbOperation dbOp = new DbOperation();   
           dbOp.init();   
           Connection con = dbOp.getConn();   
           con.setAutoCommit(false);   
           PreparedStatement pstmt = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);   
           for (ProductBean n : tempexpList) {   
               pstmt.setInt(1,n.getSId());      
               pstmt.setInt(2,n.getStatus());    
               pstmt.setString(3,n.getDatetime());    
               pstmt.setInt(4,n.getCount());   
               pstmt.setInt(5,n.getOId());   
               pstmt.setInt(6,n.getReasons());   
               pstmt.addBatch();   
           }   
           pstmt.executeBatch();    
           con.commit();      
           ResultSet rs = pstmt.getGeneratedKeys(); //获取结果   
           List<Integer> list = new ArrayList<Integer>();    
           while(rs.next()) {   
               list.add(rs.getInt(1));//取得ID   
           }   
           con.close();   
           pstmt.close();   
           rs.close();   
              
           return list;   
              
    }
  • 相关阅读:
    力扣238.除自身以外数组的乘积 & 剑指offer 51.构建乘积数组
    网易的Airtest
    ZOOKEEPER
    Apache和Nginx负载均衡集群及测试分析
    mysql——创建索引、修改索引、删除索引的命令语句
    sql-索引的作用
    ADB连接手机的两种方式(usb数据线连接和wifi连接)
    adb shell dumpsys 命令
    count(*) 和 count(1)和count(列名)区别
    博客园页面设置
  • 原文地址:https://www.cnblogs.com/langtianya/p/4919866.html
Copyright © 2020-2023  润新知