将csv加载到mysql表中 [英] loading csv into mysql table
问题描述
我正在处理我的java-csv-mysql gui应用程序问题. 我将按以下功能细分应用程序: 1.使用JFileChooser选择一个CSv， 2.读取csv 3.将csv导入Mysql表 4.将csv内容导入表格后，立即显示.
I am faced with a problem with my java-csv-mysql gui application that i am working on. i will breakdown the application in the following functions: 1. select a CSv using a JFileChooser, 2. reading the csv 3. importing the csv to Mysql table 4. displaying the csv contents once they are imported into the Table.
我设法使其具有以下功能. 1.选择一个csv文件 2.读取csv文件...-仅读取一行 3.显示已读记录
I have managed to get it to do the following functions. 1. select a csv file 2. read through the csv file...-reads only one row 3. display read records
在以下方面，我遇到了问题 1.读取全部" csv中的记录 2.上传到csv.
I have problems when it come to the following 1. reading 'all' the records in the csv 2. uploading to the csv.
我得到的错误是ArrayIndexOutofBoundsException:3 这是由于csv的读取. csv具有以下格式:
the Error I get is an ArrayIndexOutofBoundsException:3 which is due to the reading of the csv. the csv has the following format:
2018/01/25,58,294616/0
2018/01/27,102,298970/0
csv在读取csv时需要执行以下操作 1.阅读csv， 2.分隔将由a'/'分隔的最后一列.
the csv needs to do the following while it reads the csv 1. read the csv, 2. separate the last column which is to be seprated by a'/'.
这将导致有4列而不是3列.
this will result in there being 4 columns instead of 3.
这是我到目前为止的代码.
here is the Code that I have so far.
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)
推荐答案
您竭尽全力编写了非常准确的数组操作代码，以处理CSV数据中包含两个分隔符的情况.但是您实际上从未使用过oneRow数组.更改此:
You went to great lengths to write very accurate array manipulation code to deal with having two separators in your CSV data. But you never actually used the oneRow array. Change this:
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个术语仍将作为单个术语出现(该术语尚未在/上再次拆分).
By definition, rawRow will only have 3 elements in it, because the final 2 terms will still appear as a single term (the term not yet having been split again on /).
这篇关于将csv加载到mysql表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
