sql批量入库

时间：2022-03-15 15:13

public void saveBatch(List<Asset> entitys) throws ClassNotFoundException, SQLException {
       SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
       StringBuffer sql = new StringBuffer("INSERT IGNORE INTO 表名 (AssetId,Category,SubCategory,VideoUrl"
               + ",Anchor,AssetDesc,Keyword,Title,UploadTime,ImgUrl,PosterUrl,Duration,CreateTime,VideoStatus,ImgStatus,UpdateTime) "
               + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");

       String className = PropertyManager.getProperty("jdbc.driverClassName");
       String url = PropertyManager.getProperty("jdbc.url")+"&useServerPrepStmts=false&rewriteBatchedStatements=true";
       String userName = PropertyManager.getProperty("jdbc.username");
       String password = PropertyManager.getProperty("jdbc.password");

       Class.forName(className);
       Connection connection = DriverManager.getConnection(url,userName,password);
       //设置手动提交
       connection.setAutoCommit(false);
       PreparedStatement ps = connection.prepareStatement( sql.toString());
       for (Asset entity : entitys) {
           ps.setString(1,entity.getAssetId());
           ps.setString(2,entity.getCategory());
           ps.setString(3,entity.getSubCategory());
           ps.setString(4,entity.getVideoUrl());
           ps.setString(5,entity.getAnchor());
           ps.setString(6,entity.getAssetDesc());
           ps.setString(7,entity.getKeyword());
           ps.setString(8,entity.getTitle());
           ps.setString(9,entity.getUploadTime()+"");
           ps.setString(10,entity.getImgUrl());
           ps.setString(11,entity.getPosterUrl());
           ps.setInt(12,entity.getDuration());
           ps.setString(13,sdf.format(new Date()));
           ps.setInt(14,0);
           ps.setInt(15,0);
           ps.setString(16,sdf.format(new Date()));
           ps.addBatch();
       }
       ps.executeBatch();
       ps.clearBatch();
       //提交批处理
       connection.commit();
       //执行
       connection.close();
   }