drewmoore drewmoore - 7 days ago 5
Java Question

SQL Syntax Error Exception

I am trying to execute the following statement:

String sql = "INSERT INTO `ad`.`ad` (`imgURL`, `linkURL`, `client`, `idx`) VALUES (" + a.getImgURL() + " , " + a.getLinkURL() + " , " + a.getClient() + " , " + a.getIdx() + ");";


But I am getting an error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'fooUrl' in 'field list'


But
fooUrl
is one of the values - it is the result of calling
a.getImgUrl()
.

I'm new to working with databases and I'm sure that a simple syntactical error is causing this.

Answer

You will avoid these kind of problems and you will write safer code if you use a prepared statement.

String sql = "INSERT INTO `ad`.`ad` (`imgURL`, `linkURL`, `client`, `idx`) VALUES (?, ?, ?, ?)";
final PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1, a.getImgURL()) ;
statement.setString(2, a.getLinkURL()) ;
statement.setString(3, a.getClient()) ;
statement.setString(4, a.getIdx());
final ResultSet results = statement.executeQuery();