Update mysql database in Java

Question

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
);

Check your size of colom. I think it is less than the data you want to insert — Rahul
– Rahul, Commented Apr 16, 2015 at 8:05
We need to see the DB schema: its telling you the column type might be too small. — sandos
– sandos, Commented Apr 16, 2015 at 8:05
You are also missing the enclosing quotation mark ('), in both cases. Also please consider using prepared statements. — npinti
– npinti, Commented Apr 16, 2015 at 8:06
Just set it to varchar and increase the size to what you want to have. — Michał Szydłowski
– Michał Szydłowski, Commented Apr 16, 2015 at 8:07

MadProgrammer · Accepted Answer · 2015-04-16 08:17:06Z

3

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...

edited Apr 16, 2015 at 8:17

answered Apr 16, 2015 at 8:06

MadProgrammer

348k22 gold badges243 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MadProgrammer Over a year ago

That makes a nice change

roeygol Over a year ago

Like @MadProgrammer said, PreparedStatements is the best practice for these kind of queries

Davide Lorenzo MARINO · Accepted Answer · 2015-04-16 08:29:06Z

0

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

answered Apr 16, 2015 at 8:29

Davide Lorenzo MARINO

27k4 gold badges45 silver badges58 bronze badges

Collectives™ on Stack Overflow

Update mysql database in Java

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related