java - 将数据传输到数据库问题，如何提高从java应用程序到数据库的数据传输速度？

Question

在我的应用程序中，我从文件中获取数据并传输到数据库。我有 400 000 条记录。首先，它可以快速传输高达 10 000 条记录的数据，然后更新速度非常缓慢。如何提高将数据传输到数据库的性能？

gc有什么问题吗？

这是我的代码:

package com.fileupload;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.swing.text.ZoneView;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

public class SendDataToDb extends HttpServlet{
PreparedStatement ps = null;
HttpSession hs;
Connection con1;
@Override
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

     hs = request.getSession(false);
    try {
        Class.forName("com.mysql.jdbc.Driver");
        con1 = DriverManager.getConnection("jdbc:mysql://localhost:3306/xlsx","root","Inf123#");
        ps = con1.prepareStatement("INSERT INTO userdetails(ID, NAME, AGE, GENDER,ADDRESS, ZONEID, LOCATION) VALUES(?, ?, ?, ?, ?, ?, ?)");

    } catch (ClassNotFoundException e1) {
        e1.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    try {
        processOneSheet("C:/Users/Penchalaiah/Desktop/New folder/"+hs.getAttribute("filename1"));
        System.out.println("clossing the connnection");
        ps.close();
        con1.close();
    } catch (Exception e) {
        e.printStackTrace();
    }   

}

public void processOneSheet(String filename) throws Exception {
    OPCPackage pkg = OPCPackage.open(filename);
    XSSFReader r = new XSSFReader( pkg );
    SharedStringsTable sst = r.getSharedStringsTable();

    XMLReader parser = fetchSheetParser(sst);

    // To look up the Sheet Name / Sheet Order / rID,
    //  you need to process the core Workbook stream.
    // Normally it's of the form rId# or rSheet#
    InputStream sheet2 = r.getSheet("rId2");
    InputSource sheetSource = new InputSource(sheet2);
    parser.parse(sheetSource);
    sheet2.close();
}

public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
    XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
    ContentHandler handler = new SheetHandler(sst);
    parser.setContentHandler(handler);
    return parser;
}

/** 
 * See org.xml.sax.helpers.DefaultHandler javadocs 
 */
private  class SheetHandler extends DefaultHandler {

    private SharedStringsTable sst;
    private String lastContents;
    private boolean nextIsString;
    String id;
    String names;
    String age;
    String gender;
    String address;

    int i = 1;

    private SheetHandler(SharedStringsTable sst) {
        this.sst = sst;

    }

    public void startElement(String uri, String localName, String name,
            Attributes attributes) throws SAXException {
        // c => cell
        if(name.equals("c")) {
            // Print the cell reference
            // Figure out if the value is an index in the SST
            String cellType = attributes.getValue("t");
            if(cellType != null && cellType.equals("s")) {
                nextIsString = true;
            } else {
                nextIsString = false;
            }
        }
        // Clear contents cache
        lastContents = "";
        //System.out.println("===>"+lastContents+"<====");
    }

    public void endElement(String uri, String localName, String name)
            throws SAXException {
        // Process the last contents as required.
        // Do now, as characters() may be called more than once
        if(nextIsString) {
            int idx = Integer.parseInt(lastContents);
            lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
            nextIsString = false;

        }

        // v => contents of a cell
        // Output after we've seen the string contents




        if(name.equals("v")) {
            System.out.print(lastContents+"\t");

            if(i == 1){
                id = lastContents;

                System.out.print(lastContents+"("+i+")");
            }
            if(i == 2){
                names = lastContents;

                System.out.print(lastContents+"("+i+")");
            }
            if(i == 3){
                age = lastContents;

                System.out.print(lastContents+"("+i+")");
            }
            if(i == 4){
                gender = lastContents;
                System.out.print(lastContents+"("+i+")");
            }
            if(i == 5){
                address = lastContents;

                System.out.print(lastContents+"("+i+")");
                insertInToDb(id, names, age, gender, address);
                i = 0;
            }

            i++;


        }

    }


    public void characters(char[] ch, int start, int length)
            throws SAXException {
        lastContents += new String(ch, start, length);
    }
}

    public void insertInToDb(String id,String names,String age, String gender,String address){

        try {

            ps.setString(1, id);
            ps.setString(2, names);
            ps.setString(3, age);
            ps.setString(4, gender);
            ps.setString(5, address);
            ps.setString(6, (String)hs.getAttribute("zoneId1"));
            ps.setString(7, (String)hs.getAttribute("location1"));
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}

Answer 1

对多条记录执行一个批处理操作比对多条记录执行每个插入查询要快得多。

您可以创建一个 10000 或任何您想要的批处理，然后执行该批处理。

 Connection con = null;
        PreparedStatement pstm = null;
        try {
            Class.forName("driver class");
            con = DriverManager.
                    getConnection("connectionUrlString","password");
            con.setAutoCommit(false);
            pstm = con.prepareStatement("your insert command );
            pstm .setInt(1, 3000); //set all parameters            
            pst.addBatch();
            int count[] = pst.executeBatch();
            for(int i=1;i<=count.length;i++){
                System.out.println("Query "+i+" has effected "+count[i]+" records");
            }
            con.commit();
            pst.close();
            con.close();