java处理较大数据量到mysql
1、 jdbd采用批处理插入大量数据,速度还是相当的慢,一个拥有一个自增字段、三个字符串字段的表,往里面插入1W条数据消耗一分多钟。代码如下: public class DBbatchdeal {/** * * @param conn jdbc链接 * @param tableName 表明 * @param lists 数据集 * @
1、 jdbd采用批处理插入大量数据,速度还是相当的慢,一个拥有一个自增字段、三个字符串字段的表,往里面插入1W条数据消耗一分多钟。代码如下:
<span>public class DBbatchdeal { /** * * @param conn jdbc链接 * @param tableName 表明 * @param lists 数据集 * @param n 每行字段个数 出去自增字段 * @param flag 第一列是否自增字段 * @return 是否成功 */ public boolean deal(Connection conn,String tableName,ArrayList<string> lists ,int n,boolean flag){ StringBuffer sql = new StringBuffer(); sql.append("insert into ").append(tableName) .append(" values("); sql=(flag==true?sql.append("null ,"):sql); for(int i=0;i<n-1 sql.append int size="lists.size();" m="(true==flag?n-1:n);" preparedstatement prestmt="null;" long a="System.currentTimeMillis();" try for j="0;j<size;j++){" string str="lists.get(j);" k="0;k<n;k++){" prestmt.setstring prestmt.addbatch if prestmt.executebatch prestmt.clearbatch catch e e.printstacktrace prestmt.close b="System.currentTimeMillis();" system.out.println return true public static void main args throws classnotfoundexception sqlexception class.forname connection conn='DriverManager.getConnection("jdbc:mysql://ip***/db",' dbbatchdeal deal="new" arraylist> lists =new ArrayList<string>(); for(int i=0;i</string></n-1></string></span>
2、因为上面的方法处理的较慢,又想了个较为麻烦点儿的方式,用mysql的load data来导入数据。具体就是写段导入数据的脚本用java来执行,测试了下1w条记录插入的时间还是相当短的。
结果如下:
Java2Sh内容如下:
<span>import java.io.BufferedWriter; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.io.OutputStreamWriter; import java.io.Writer; public class Java2Sh { /** * 对文件进行赋权 * @param infile 文件全路径 * @return runtime执行返回码 */ public int chmodrun(String infile) { int retCode = 0; try { Runtime rtime = Runtime.getRuntime(); Process child = rtime.exec("chmod 777 " +infile); retCode=child.waitFor(); System.out.println("chmod :"+retCode); } catch (Exception e) { System.out.println("chmod failed "+infile); e.printStackTrace(); } return retCode; } /** *执行脚本文件 * @param infile 文件全路径 * @return runtime执行返回码 */ public int shellFile(String infile) { int retCode = 0; try { Runtime rtime = Runtime.getRuntime(); Process child = rtime.exec("sh " +infile); retCode=child.waitFor(); System.out.println("shell file :"+retCode); } catch (Exception e) { System.out.println("shell file failed "+infile); e.printStackTrace(); } return retCode; } public void writeData() throws IOException{//生成mysql2.txt String str="13311122,passwds,20130710235959"; BufferedWriter out =new BufferedWriter(new OutputStreamWriter(new FileOutputStream(new File("D:\\mysql2.txt")))); for(int i=0;i</span>
其中shh.sh内容为:
mysql -h localhost -u root -ppwd /dblog
loaddata.sql内容为:
use dbname;
LOAD DATA INFILE '/mysql2.txt' INTO TABLE testTable FIELDS TERMINATED BY ',' (cardnum,cardpwd,times);
commit;
介于个人水平,贴出来仅供参考,欢迎告诉我更简便高效的方式,先谢过了。

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the "MySQL Native Password" plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

Java 8 introduces the Stream API, providing a powerful and expressive way to process data collections. However, a common question when using Stream is: How to break or return from a forEach operation? Traditional loops allow for early interruption or return, but Stream's forEach method does not directly support this method. This article will explain the reasons and explore alternative methods for implementing premature termination in Stream processing systems. Further reading: Java Stream API improvements Understand Stream forEach The forEach method is a terminal operation that performs one operation on each element in the Stream. Its design intention is

Java Made Simple: A Beginner's Guide to Programming Power Introduction Java is a powerful programming language used in everything from mobile applications to enterprise-level systems. For beginners, Java's syntax is simple and easy to understand, making it an ideal choice for learning programming. Basic Syntax Java uses a class-based object-oriented programming paradigm. Classes are templates that organize related data and behavior together. Here is a simple Java class example: publicclassPerson{privateStringname;privateintage;

Java is a popular programming language that can be learned by both beginners and experienced developers. This tutorial starts with basic concepts and progresses through advanced topics. After installing the Java Development Kit, you can practice programming by creating a simple "Hello, World!" program. After you understand the code, use the command prompt to compile and run the program, and "Hello, World!" will be output on the console. Learning Java starts your programming journey, and as your mastery deepens, you can create more complex applications.

Capsules are three-dimensional geometric figures, composed of a cylinder and a hemisphere at both ends. The volume of the capsule can be calculated by adding the volume of the cylinder and the volume of the hemisphere at both ends. This tutorial will discuss how to calculate the volume of a given capsule in Java using different methods. Capsule volume formula The formula for capsule volume is as follows: Capsule volume = Cylindrical volume Volume Two hemisphere volume in, r: The radius of the hemisphere. h: The height of the cylinder (excluding the hemisphere). Example 1 enter Radius = 5 units Height = 10 units Output Volume = 1570.8 cubic units explain Calculate volume using formula: Volume = π × r2 × h (4

Spring Boot simplifies the creation of robust, scalable, and production-ready Java applications, revolutionizing Java development. Its "convention over configuration" approach, inherent to the Spring ecosystem, minimizes manual setup, allo

A stack is a data structure that follows the LIFO (Last In, First Out) principle. In other words, The last element we add to a stack is the first one to be removed. When we add (or push) elements to a stack, they are placed on top; i.e. above all the

Effective monitoring of Redis databases is essential for maintaining optimal performance, identifying potential bottlenecks, and ensuring overall system reliability. Redis Exporter Service is a robust utility designed to monitor Redis databases using Prometheus. This tutorial will guide you through the complete setup and configuration of Redis Exporter Service, ensuring you establish a monitoring solution seamlessly. By following this tutorial, you’ll achieve a fully operational monitoring setup to effectively monitor the performance metrics of your Redis database.
