CODEWITHSUNDEEP
javamysql
John
John

Reputation: 53

Update mysql database in Java

Can anybody point out what's wrong with my code? I am trying to update an attribute but I got an error

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'phone' at row 1

Is there anything wrong with the SQL command? Thank you!

String queryString = "";
String selectedItem = (String) searchTypeCmb.getSelectedItem();
String searchTerm = searchTermField.getText();
String id = theId.getText();
textArea.setText("");

if ("Phone".equals(selectedItem)) {
    queryString = "UPDATE person SET phone = '" + (searchTerm) + 
        " WHERE driverID = " + (id) + "'";
}
else if ("Address".equals(selectedItem)) {
    queryString = "UPDATE person SET address = '" + (searchTerm) + 
        " WHERE driverID = " + (id) + "'";
}

try {
    connection = DriverManager.getConnection("jdbc:mysql://localhost/dealer", "root", "admin");
    statement = connection.createStatement();
    statement.executeUpdate(queryString);

Here is my DB schema

create table person
( driverID int unsigned not null primary key,
  firstName char(20) not null,
  lastName char(20) not null,
  address char(30) not null,
  phone char(20)
);

create table cars
( license char(10) not null,
  brand char(20) not null,
  model char(20) not null,
  year char(10),
  status char(10) not null,
  carID int unsigned not null primary key
);

Upvotes: 0

Views: 94

Answers (2)

Davide Lorenzo MARINO
Davide Lorenzo MARINO

Reputation: 26926

If you have the message

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'phone'

The data that you are writing in the column "phone" is longer than the maximum length of that field on the database. So you can:

  • Truncate the phone value before inserting it in the database
  • Alter your column length in the database

Upvotes: 0

MadProgrammer
MadProgrammer

Reputation: 347184

To start with, your missing closing quotes around your text...

"UPDATE person SET phone = '" + (searchTerm) + " WHERE driverID = " + (id) + "'"
                                                ^-----------------------------^

Which you probably want to be

"UPDATE person SET phone = '" + (searchTerm) + "' WHERE driverID = " + (id)

But having said that, I'd strongly encourage you to use PreparedStatements instead. 

String queryString = "";
String selectedItem = (String) searchTypeCmb.getSelectedItem();
String searchTerm = searchTermField.getText();
String id = theId.getText();
textArea.setText("");
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/dealer", "root", "admin")) {

    PreparedStatement stmt = null;
    if ("Phone".equals(selectedItem)) {
        stmt = connection.prepareStatement("UPDATE person SET phone = ? WHERE driverID = ?");
    } else if ("Address".equals(selectedItem)) {
        stmt = connection.prepareStatement("UPDATE person SET address = ? WHERE driverID = ?");
    }
    if (stmt != null) {
        stmt.setString(1, searchTerm);
        stmt.setString(2, id);
        stmt.executeUpdate();
    }

See Using Prepared Statements for more details...

Upvotes: 3

Related Questions