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