First things first. In my main : The connection is successful the DROP is successful; and the creation of the table to my understanding is successful. However when I get to the insertingCustomers part, things start go awry.
private void run() throws ClassNotFoundException, SQLException, ApplicationException {
LOG.info("Running");
//Class.forName(properties.getProperty(DbConstants.DB_DRIVER_KEY));
LOG.info("Driver loaded");
connect();
Statement statement = connection.createStatement();
try {
customerDao.drop(); //Good I think.
customerDao.create(); //Good I think.
insertCustomers(); //Problems.
} catch (SQLException e) {
e.printStackTrace();
LOG.error(e.getMessage());
} finally {
connection.close();
}
}
The Table being created with customerDao.create() looks like this: Side question am I assigning the Customer's id as the primary key properly here?
@Override
public void create() throws SQLException {
String sql = String.format(
"create table %s(" // 1
+ "%s VARCHAR(9), " // 2
+ "%s VARCHAR(20), " // 3
+ "%s VARCHAR(20), " // 4
+ "%s VARCHAR(20), " // 5
+ "%s VARCHAR(20), " // 6
+ "%s VARCHAR(7), " // 7
+ "%s VARCHAR(14), " // 8
+ "%s VARCHAR(20), " // 9
+ "%s DATE," //10
+ "primary key (%s) )", // 11
tableName, // 1
Fields.ID.getName(), // 2
Fields.FIRST_NAME.getName(), // 3
Fields.LAST_NAME.getName(), // 4
Fields.STREET.getName(), // 5
Fields.CITY.getName(), // 6
Fields.POSTAL_CODE.getName(), // 7
Fields.PHONE.getName(), // 8
Fields.EMAIL.getName(), // 9
Fields.JOIN_DATE.getName(), //10
Fields.ID.getName()); // 11
LOG.debug(sql);
super.create(sql);
}
And the Fields are as follows:
public enum Fields {
ID("Id", "Integer", 9, 1), //
FIRST_NAME("firstName", "VARCHAR", 20, 2), //
LAST_NAME("lastname", "VARCHAR", 20, 3), //
STREET("Steet", "VARCHAR", 20, 4), //
CITY("City", "VARCHAR", 10, 5), //
POSTAL_CODE("postalCode", "VARCHAR", 7, 6), //
PHONE("phone", "VARCHAR", 14, 7), //
EMAIL("email", "VARCHAR", 30, 8), //
JOIN_DATE("joinDate", "DATE", -1, 9); //
private final String name;
private final String type;
private final int length;
private final int column;
Fields(String name, String type, int length, int column) {
this.name = name;
this.type = type;
this.length = length;
this.column = column;
}
public String getType() {
return type;
}
public String getName() {
return name;
}
public int getLength() {
return length;
}
public int getColumn() {
return column;
}
}
}
I'll level with you I'm somewhat new to JDBC and SQL and I'm not sure why my JOIN_DATE Field has a length of -1; some example code we got had a BIRTH_DAY length of -1. Perhaps that's why things are being truncated. More on that now.
So after I create my Table I try to use the InsertCustomers() method to add to my table. For clarity I'm reading my Customer information from a text file. It works and the List DATA is filled properly.
private void insertCustomers() throws SQLException, ApplicationException {
File customers_data_file = new File(CUSTOMER_DATA);
List<Customer> DATA = CustomerReader.read(customers_data_file);
try {
for (Customer customer : DATA) {
customerDao.add(customer);
}
} catch (SQLException e) {
LOG.error(e.getMessage());
e.printStackTrace();
}
}
This then goes to my customerDao.add() method where the exception is being thrown.
public void add(Customer customer) throws SQLException {
Statement statement = null;
try {
Connection connection = database.getConnection();
statement = connection.createStatement();
String sql = String.format(
"insert into %s values(" // 1 tableName
+ "'%s', " // 2 StudentId
+ "'%s', " // 3 FirstName
+ "'%s', " // 4 LastName
+ "'%s', " // 5 Street
+ "'%s', " // 6 City
+ "'%s', " // 7 PostalCode
+ "'%s', " // 8 Phone
+ "'%s', " // 9 Email
+ "'%s')", // 10 JoinDate
tableName,
customer.getId(),
customer.getFirstName(),
customer.getLastName(),
customer.getStreet(),
customer.getCity(),
customer.getPostalCode(),
customer.getPhone(),
customer.getEmailAddress(),
customer.getJoinedDateString());
LOG.debug(sql);
statement.executeUpdate(sql);
} finally {
close(statement);
}
}
One other thing that just occurred to me; is the method I'm using to convert my joinedDate to a string might be causing problems see below; but I'd be mystified as to how.
public String getJoinedDateString() {
if (joinedDate == null) {
return null;
}
String result = joinedDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
return result;
}
And as soon as the line: statement.executeUpdate(sql); executes I get this error:
insert into A01045801_Customer values('1', 'Fred', 'Fish', '5707 Sidley St', 'Burnaby', 'V5J 5E6', '(604) 433-5004', 'fredfish@imperial.net', '2008-03-22') String or binary data would be truncated. Shutting down com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.
Sorry it's quite a bit of code I just read a few other peoples posts to see if my question was asked & answered and I didn't seem to find answers; and many people requested more info on that person's code. I guess I figured I'd supply all the necessary material.
Thanks :)
