java mysql分页查询大量数据，批量插入

今天药忘吃喽~ 2022-06-16 04:46 421阅读 0赞

//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();
    	}