I made a small application which reads in an Excel File. I connected it to my MySQL Database so that I can put the file into the database, which contains multiple tables. However, when implementing the values into the 'Orders' table, there is an SQLException and the values do not get into the database. That's the exception in the CustomerMC class. The values of the 'Customers' table do get into the database.
The code I have contains a main where I read the file and make instances of Customer and Order, two container classes where the values get into, a connection class, which is not relevant for this, and a Model Class for the Customer where the query is located.
Main:
public static void main(String[] args){
CustomerMC cmc = new CustomerMC();
ArrayList<Customer> customers = new ArrayList<>();
ArrayList<Order> orders = new ArrayList<>();
try {
try (FileInputStream file = new FileInputStream(new File("C:/Users/Wout/Desktop/ProjectTest.xlsx"))) {
XSSFWorkbook wb = new XSSFWorkbook(file);
XSSFSheet sh = wb.getSheetAt(0);
Iterator<Row> rowIterator = sh.iterator();
while(rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
int counter = 0;
int cid = 0;
int OrderID = 0;
String name = "";
String purchase = "";
String age = "";
String product = "";
while (cellIterator.hasNext() && row.getRowNum() != 0) {
Cell cell = cellIterator.next();
counter++;
switch (counter) {
case 1:
cid = (int)cell.getNumericCellValue();
break;
case 2:
name = cell.getStringCellValue();
break;
case 3:
purchase = cell.getStringCellValue();
break;
case 4:
age = "" + cell.getNumericCellValue();
break;
case 5:
OrderID = (int)cell.getNumericCellValue();
break;
case 6:
product = "" + cell.getStringCellValue();
break;
case 7:
cid = (int)cell.getNumericCellValue();
break;
}
}
if(row.getRowNum() != 0) {
Customer customer = new Customer(cid,name,purchase,age);
Order order = new Order(OrderID, product, cid);
customers.add(customer);
orders.add(order);
}
}
}
} catch (FileNotFoundException ex) {
System.out.println("File has not been found");
} catch (IOException ex) {
System.out.println("IOException");
}
for(Order order : orders) {
System.out.println(order.toString());
cmc.insertOrder(""+order.getOrderid(), order.getProduct(), ""+order.getCid());
}
for(Customer customer : customers){
System.out.println(customer.toString());
cmc.insertCustomer(""+customer.getCid(), customer.getName(), customer.getPurchase(), customer.getAge());
}
}
Container Classes:
public class Customer {
private int cid;
private String name;
private String purchase;
private String age;
public Customer(int cid, String name, String purchase, String age) {
this.cid = cid;
this.name = name;
this.purchase = purchase;
this.age = age;
}
public int getCid() {
return cid;
}
public void setCid(int cid) {
this.cid = cid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPurchase() {
return purchase;
}
public void setPurchase(String purchase) {
this.purchase = purchase;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
@Override
public String toString() {
return "" + cid + " ; "+ name + " ; " + purchase + " ; " + age;
}
public class Order {
private int orderid;
private String product;
private int cid;
public Order(int orderid, String product, int cid) {
this.orderid = orderid;
this.product = product;
this.cid = cid;
}
public int getOrderid() {
return orderid;
}
public void setOrderid(int orderid) {
this.orderid = orderid;
}
public String getProduct() {
return product;
}
public void setProduct(String product) {
this.product = product;
}
public int getCid() {
return cid;
}
public void setCid(int cid) {
this.cid = cid;
}
@Override
public String toString() {
return "" + orderid + " ; " + product + " ; " + cid;
}
Customer Model Class:
public class CustomerMC {
private Connection connection;
private PreparedStatement pst;
String query;
ResultSet rs;
public CustomerMC(){
try{
connection = SimpleDataSourceV2.getConnection();
}
catch(SQLException e){
System.out.println("Connection failure");
e.printStackTrace();
}
}
public String insertCustomer(String cid, String name, String purchase, String age) {
String returning = null;
try {
query = "insert into CustomerService values(?,?,?,?);";
pst = connection.prepareStatement(query);
pst.setInt(1, Integer.parseInt(cid));
pst.setString(2, name);
pst.setString(3, purchase);
pst.setString(4, age);
int response = pst.executeUpdate();
returning = response +" Records has/have been edited";
} catch (SQLException e) {
returning = "An error has occured";
System.out.println(returning);
}
return returning;
}
public String insertOrder(String orderid, String product, String id) {
String returning = null;
try {
query = "insert into CustomerService values(?,?,?,?);";
pst = connection.prepareStatement(query);
pst.setInt(1, Integer.parseInt(orderid));
pst.setString(2, product);
pst.setInt(3, Integer.parseInt(id));
int response = pst.executeUpdate();
returning = response +" Records has/have been edited";
} catch (SQLException e) {
returning = "xx";
System.out.println(returning);
}
return returning;
}
Obviously there is one more class with the connection to the database. That's not relevant for this question so I decided to leave it out.
Then my Excel Sheet is a simple sheet, where cid, name, purchaser and age are the 'Customer' table and OrderID, Product and cid are the 'Orders' table. Cid in Orders is the Foreign Key to Customers. Excel automatically generates that field as the value it gets in the customers table. I re-implement the cid value to make it easier for myself.
Table: https://prnt.sc/ff1yd0
The output I am getting after running is as following:
1 ; Monitor ; 1
java.sql.SQLException: Column count doesn't match value count at row 1
2 ; Couch ; 2
3 ; Appartment ; 3
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
at p4project.CustomerMC.insertOrder(CustomerMC.java:66)
at p4project.Main.main(Main.java:103)
java.sql.SQLException: Column count doesn't match value count at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
I don't know why the second table doesn't implement and I hope someone can help! Any help is appreciated.
