java mysql分页查询大量数据，批量插入
来源：互联网 发布：%在c语言是什么意思 编辑：程序博客网 时间：2024/05/17 16:46
//mysql连接 rewriteBatchedStatements=true打开批量写入 public static Connection getConn() throws SQLException { String userName="root"; String password="123"; String url="jdbc:mysql://127.0.0.1/content?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&rewriteBatchedStatements=true"; try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } Connection conn= conn = DriverManager.getConnection(url, userName, password) ; return conn; }//查询大量数据分页获取，获取总数public static int getCount() {Connection dbConn = null;String query = "SELECT count(*) as abc from user";int i = 0;try {dbConn = MysqlDbHelp.getConn();Statement stmt = dbConn.createStatement();ResultSet rs = stmt.executeQuery(query);if (rs.next()) {i = rs.getInt("abc");}} catch (SQLException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();}return i;}//分页获取大量数据public static List<String>getUserName()throws Exception{ int count = Test2.getMsgCount(); Connection dbConn = MysqlDbHelp.getConn(); //每次查询个数 int PAGE_SIZE = 10000;//计算页数 int page = count % 5 == 0 ? count / PAGE_SIZE : count / PAGE_SIZE + 1; System.out.println(page + "总数");List<String> result = new ArrayList<>(); for (int i = 1; i <= page; i++) { String sql = "SELECT name from user limit ?,?"; PreparedStatement ps = dbConn.prepareStatement(sql); ps.setInt(1, (i - 1) * PAGE_SIZE); ps.setInt(2, PAGE_SIZE); ResultSet rs = ps.executeQuery(); while (rs.next()) { String name = rs.getString("name"); result.add(name); } }return result; }//批量插入public static void bathInsert(List<String> results){Connection dbConn = MysqlDbHelp.getConn(); dbConn.setAutoCommit(false); String sql12 = insert into user(name) values(?)"; PreparedStatement preStmt1 = dbConn.prepareStatement(sql12); for (int i1 = 0; i1 < result.size(); i1++) { preStmt1.setString(1,results.get(i)); //增加批处理 preStmt1.addBatch(); } preStmt1.executeBatch(); dbConn.commit();}
0 0
