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

最新推荐文章于 2024-07-20 02:58:28 发布

java守门员

最新推荐文章于 2024-07-20 02:58:28 发布

阅读量2.5k

点赞数

分类专栏： java文件

本文链接：https://blog.csdn.net/baidu_16217779/article/details/71216172

版权

java文件专栏收录该内容

17 篇文章 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();
	}