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)
