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

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

}