CODEWITHSUNDEEP
javaspringmavenjdbc
DrugCrazed
DrugCrazed

Reputation: 281

Spring JDBCTemplate Always Throws QueryTimeOutException

This is something that I've been scratching my head with - especially since it's infuriating to deal with.

Consider the following code:

String query = "UPDATE ORDERS SET VOLUME=?,CONTRACT_ID=?,PROJECT_ID=?,WORKSITE_ID=?,DROPZONE_ID=?,DESCRIPTION_ID=?,MANAGER_ID=?,DELIVERY_DATE=?,REVISION=REVISION+1) WHERE ID=?";
jdbcTemplate.update(query, orderEntity.getVolume(), orderEntity.getContractNo(), orderEntity.getProjectID(), orderEntity.getWorksiteID(), orderEntity.getDropzoneID(), orderEntity.getDescriptionID(), orderEntity.getManagerID(), orderEntity.getDeliveryDate(), id);

We can see that the SQL query is incorrect - and will therefore throw some SQL error but one might have missed that. Spring will (for me) throw a QueryTimeoutException in response to this. I'm sort of okay with that but it's not helpful.

Now let's try

String query = "INSERT INTO ORDERS(ID,REISION,CONTRACT_ID,PROJECT_ID,WORKSITE_ID,DROPZONE_ID,DESCRIPTION_ID,MANAGER_ID,VOLUME,DELIVERY_DATE) VALUES(?,?,?,?,?,?,?,?,?,?)";
jdbcTemplate.update(query, id, revision, etc);

Another spelling mistake that's easily missed - REVISION is misspelled as REISION) Spring throws another QueryTimeoutException again. This now means that if I get that exception I don't actually know what it is. Is it a syntax error? Is it a column spelling error? Is it the (much harder to notice) fact that the foreign key constraint not being followed?

While debugging, this is quite possibly the most infuriating thing ever - all I know is that my query failed to run. How can I get something useful? Is there something I've not added to my pom.xml file?

EDIT:

Here's a nicer example. I have a DESCRIPTIONS table, with an ID, REVISION and TEXT column. All of those are marked as not being nullable.

DescriptionEntity descriptionEntity = new DescriptionEntity("newDesc", 1, null);
String query = "INSERT INTO DESCRIPTIONS (ID,REVISION,TEXT) VALUES(?,?,?)";
jdbcTemplate.update(query, descriptionEntity.getID(), 1, descriptionEntity.getText());

That will also throw a query timeout exception, when running the query in mysql gives me ERROR 1048 (23000): Column 'TEXT' cannot be null

This is, to put it politely, a bit of a pain.

Upvotes: 2

Views: 1866

Answers (2)

DrugCrazed
DrugCrazed

Reputation: 281

This is quite possibly the most stupid error I've ever come across: the issue was in how Maven resolved all the dependencies.

The requirement for Spring Security was placed before the JDBC requirement. That made Spring Security pull down org.springframework:spring-tx:jar:3.0.7.RELEASE:compile which satisfied the dependency for JDBC. Moving the JDBC requirement up meant JDBC pulled down org.springframework:spring-tx:jar:3.2.2.RELEASE:compile.

Upvotes: 0

CodeChimp
CodeChimp

Reputation: 8154

It's not a spelling mistake in the first example, as you left out the opening paren. I would say this isn't an issue with Spring or JDBC, but rather your DB is trying to process the SQL, waiting for more input or something, and never returning.

In the second one, I am not sure what you are talking about since I don't know the table design. I have to assume what you mean is ID is not unique? Again, I wouldn't blame Spring or JDBC, maybe the drive, most likely the database server.

Keep in mind, in a lot of cases, the way SQL is handled in the user Client UI is not the same as how it gets handled through JDBC. For instance, in SQL Server the SQL is set as a string, the passed in parameters set as variables, and it uses sp_executesql() to run it. I discovered that when I had a report that ran PERFECTLY fine through SQL Studio Manager client, but blew up when I ran it live because the query plan optimizer took a different path due to the differences in how the SQL was ran.

Upvotes: 2

Related Questions