java - 在 Java 中更新 mysql 数据库

Question

有人能指出我的代码有什么问题吗？我正在尝试更新属性，但出现错误

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

是不是SQL命令有问题？谢谢!

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

这是我的数据库架构

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

Answer 1

首先，您缺少文本周围的结束引号...

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

你可能想成为什么样的人

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

但话虽如此，我还是强烈建议您改用 PreparedStatements。

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

参见 Using Prepared Statements更多详情...