mysql - 将 csv 加载到 mysql 表中

Question

我正在处理的 java-csv-mysql gui 应用程序遇到了问题。我将按以下功能分割应用程序:1. 使用 JFileChooser 选择一个 CSv，2.读取csv3.导入csv到Mysql表4. 导入表格后显示csv内容。

我已经设法让它完成以下功能。1.选择一个csv文件2. 通读csv文件...-只读一行3.显示阅读记录

我遇到以下问题1.读取csv中的“所有”记录2. 上传到csv。

我得到的错误是 ArrayIndexOutofBoundsException:3这是由于读取 csv。csv 格式如下:

    2018/01/25,58,294616/0
    2018/01/27,102,298970/0

csv 在读取 csv 时需要执行以下操作1.阅读csv，2. 用'/'分隔最后一列。

这将导致有 4 列而不是 3 列。

这是我目前拥有的代码。

  public class Payment_import_v4 extends JFrame{
private JTable table;

/**
 * @param args the command line arguments
 */
public static void main(String[] args) {
    EventQueue.invokeLater(new Runnable(){
        public void run()
        {
            createAndshowGUI();
        }
    });
}


private static void createAndshowGUI(){
            Payment_import_v4 form = new Payment_import_v4();
            form.setVisible(true);
} 

public Payment_import_v4(){
    //form frame
    super("Payment Import");
    setSize(900,600);
    setLocation(500,280);
    getContentPane().setLayout(null);

    //Label Result
    final JLabel lblResult = new JLabel("Result",JLabel.CENTER);
    lblResult.setBounds(150,22,370,14);
    getContentPane().add(lblResult);

    //Table
    table = new JTable();
    getContentPane().add(table);

    //Table Model
    final DefaultTableModel model = (DefaultTableModel)table.getModel();
    model.addColumn("PayDate");
    model.addColumn("Ammount");
    model.addColumn("LinkId");
    model.addColumn("BranchNo");

    //ScrollPane
    JScrollPane scroll = new JScrollPane(table);
    scroll.setBounds(84,98,506,79);
    getContentPane().add(scroll);

    //Button Open
    JButton btnOpen = new JButton("Select File");
    btnOpen.setBounds(268,47,135,23);
    btnOpen.addActionListener(new ActionListener(){
        public void actionPerformed(ActionEvent ae){
            JFileChooser fileOpen = new JFileChooser();
            FileFilter filter = new FileNameExtensionFilter("CSV file","csv");
            fileOpen.addChoosableFileFilter(filter);

            int ret = fileOpen.showDialog(null,"Choose file");

            if(ret == JFileChooser.APPROVE_OPTION){

                File file = fileOpen.getSelectedFile();//gets selectedFile.

                try {
                    BufferedReader br = new BufferedReader(new FileReader(file));
                    int row = 0;

                    //if (br.readLine() != null) {line = br.readLine();

                        while ((br.readLine()) != null) {
                            String line = br.readLine();// br string variable
                            String[] rawRow = line.split(",");
                            String lastEntry = rawRow[rawRow.length - 1];//this contains the LinkId/branchNo
                            String[] properLastEntry = lastEntry.split("/");//this contains the LinkId/branchNo split into two columnms
                            String[] oneRow = new String[rawRow.length + 1];
                            System.arraycopy(rawRow, 0, oneRow, 0, rawRow.length - 1);
                            System.arraycopy(properLastEntry, 0, oneRow, oneRow.length - properLastEntry.length, properLastEntry.length);

                            model.addRow(new Object[0]);
                            model.setValueAt(rawRow[0], row, 0);
                            model.setValueAt(rawRow[1], row, 1);
                            model.setValueAt(rawRow[2], row, 2);
                            model.setValueAt(rawRow[3], row, 3);
                            row++;
                        }
                        br.close();
                    //}
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
                lblResult.setText(fileOpen.getSelectedFile().toString());
            }
        }
    });
    getContentPane().add(btnOpen);

    //btn Save
    JButton btnSave = new JButton("Save");
    btnSave.addActionListener(new ActionListener(){
       public void actionPerformed(ActionEvent ea){
           SaveData();
       } 
    });
    btnSave.setBounds(292,228,89,23);
    getContentPane().add(btnSave);

    }

private void SaveData(){
    Connection connect = null;
    Statement stmt = null;

    try{

        //DriverManager Loader
        Class.forName("com.mysql.jdbc.Driver");

        //connection string url.. the port//schema name//username//password
                                                //this is the test Server ;oginDetails
        connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/riskfin", "root", "riskfin");//-------------> this is for the localhost server
        stmt = connect.createStatement();

        for(int i = 0;i<table.getRowCount();i++)
        {
            String PayDate = table.getValueAt(i,0).toString();
            String Ammount = table.getValueAt(i,1).toString();
            String LinkID = table.getValueAt(i,2).toString();
            String BranchNo = table.getValueAt(i,3).toString();

            String  sql = "Insert into temp_payment_import "
                    +"VALUES('"+LinkID+"','"
                    +Ammount+"','"
                    +PayDate+"','"
                    +BranchNo+"')";

            stmt.execute(sql);
       }

        JOptionPane.showMessageDialog(null,"Data imported Successfully");

    }catch(Exception ex){
        JOptionPane.showMessageDialog(null,ex.getMessage());
        ex.printStackTrace();
    }
    try{
        if(stmt!= null){
            stmt.close();
            connect.close();
        }
    }catch(SQLException e){
        System.out.println(e.getMessage());
        e.printStackTrace();
    }
}

   }

这是我得到的异常。

Exception in thread "AWT-EventQueue-0" java.lang.ArrayIndexOutOfBoundsException: 3
    at payment_import_v4.Payment_import_v4$2.actionPerformed(Payment_import_v4.java:120)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6533)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
    at java.awt.Component.processEvent(Component.java:6298)
    at java.awt.Container.processEvent(Container.java:2236)
    at java.awt.Component.dispatchEventImpl(Component.java:4889)
    at java.awt.Container.dispatchEventImpl(Container.java:2294)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4525)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
    at java.awt.Container.dispatchEventImpl(Container.java:2280)
    at java.awt.Window.dispatchEventImpl(Window.java:2746)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
    at java.awt.EventQueue.access$500(EventQueue.java:97)
    at java.awt.EventQueue$3.run(EventQueue.java:709)
    at java.awt.EventQueue$3.run(EventQueue.java:703)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:90)
    at java.awt.EventQueue$4.run(EventQueue.java:731)
    at java.awt.EventQueue$4.run(EventQueue.java:729)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

Answer 1

您竭尽全力编写非常准确的数组操作代码来处理 CSV 数据中的两个分隔符。但你从未真正使用过 oneRow大批。改变这个:

model.addRow(new Object[0]);
model.setValueAt(rawRow[0], row, 0);
model.setValueAt(rawRow[1], row, 1);
model.setValueAt(rawRow[2], row, 2);
model.setValueAt(rawRow[3], row, 3);   // ArrayIndexOutOfBoundsException

为此:

model.addRow(new Object[0]);
model.setValueAt(oneRow[0], row, 0);
model.setValueAt(oneRow[1], row, 1);
model.setValueAt(oneRow[2], row, 2);
model.setValueAt(oneRow[3], row, 3);

根据定义，rawRow将只有 3 个元素，因为最后 2 个术语仍将显示为单个术语(该术语尚未在 / 上再次拆分)。