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.
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
I have problems when it come to the following 1. reading 'all' the records in the csv 2. uploading to the 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
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'/'.
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();
}
}
}
here is the exception I get.
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)
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
to this:
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);
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 /).