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.
我做了一个小应用程序,它读入Excel文件。我将它连接到我的MySQL数据库,以便我可以将文件放入包含多个表的数据库中。但是,在“Orders”表中实现值时,会出现SQLException,并且值不会进入数据库。这是CustomerMC类中的例外。 'Customers'表的值确实进入数据库。
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,我读取文件并生成Customer和Order的实例,两个容器类,其中的值进入,一个连接类,与此无关,以及一个Model类,用于查询的Customer位于。
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.
然后我的Excel工作表是一个简单的工作表,其中cid,name,buyer和age是'Customer'表,OrderID,Product和cid是'Orders'表。订单中的Cid是客户的外键。 Excel会自动生成该字段,作为在customers表中获取的值。我重新实现了cid值,使自己更容易。
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.
我不知道为什么第二张表没有实现,我希望有人可以提供帮助!任何帮助表示赞赏。
1 个解决方案
#1
1
Your exception is thrown at: CustomerMC.insertOrder(CustomerMC.java:66) and says java.sql.SQLException: Column count doesn't match value count.
您抛出异常:CustomerMC.insertOrder(CustomerMC.java:66)并说java.sql.SQLException:列计数与值计数不匹配。
Your CustomerService table seems to have 7 columns but your want to insert a row with only 4 values. This is not possible. You have to provide a value for each column (if you do not specifiy the names of the columns where you want to insert your values). You can also insert null values, if your schema allows it, but you have to specifiy it. The statement does not know which columns you want to leave blank.
您的CustomerService表似乎有7列,但您想插入只有4个值的行。这是不可能的。您必须为每列提供一个值(如果您没有指定要插入值的列的名称)。如果您的架构允许,您也可以插入空值,但您必须指定它。该语句不知道您要将哪些列留空。
#1
1
Your exception is thrown at: CustomerMC.insertOrder(CustomerMC.java:66) and says java.sql.SQLException: Column count doesn't match value count.
您抛出异常:CustomerMC.insertOrder(CustomerMC.java:66)并说java.sql.SQLException:列计数与值计数不匹配。
Your CustomerService table seems to have 7 columns but your want to insert a row with only 4 values. This is not possible. You have to provide a value for each column (if you do not specifiy the names of the columns where you want to insert your values). You can also insert null values, if your schema allows it, but you have to specifiy it. The statement does not know which columns you want to leave blank.
您的CustomerService表似乎有7列,但您想插入只有4个值的行。这是不可能的。您必须为每列提供一个值(如果您没有指定要插入值的列的名称)。如果您的架构允许,您也可以插入空值,但您必须指定它。该语句不知道您要将哪些列留空。