Spring中JdbcTemplate查询返回空列表但控制台SQL可正常获取数据

Hey there, I totally get how frustrating this is—when your SQL runs perfectly in the database console but returns nothing in your JdbcTemplate code. Let’s break down the most likely culprits and how to fix them:

1. Verify You’re Connecting to the Right Database #

First things first: double-check that your application’s JdbcTemplate is pointing to the same database you’re querying in the console. It’s super common to accidentally connect to a test/staging environment instead of production (or vice versa).

To confirm, add a quick debug line to print your database URL:

try {
    String dbUrl = jdbcTemplate.getDataSource().getConnection().getMetaData().getURL();
    System.out.println("Connected to: " + dbUrl);
} catch (SQLException e) {
    e.printStackTrace();
}

Compare this URL to the one your console uses—if they don’t match, that’s your problem!

2. Check Parameter Type Mismatch #

Even if you’re passing a Long id, there might be a type mismatch with your database’s id column. For example:

• If your database uses INT (32-bit) instead of BIGINT (64-bit), passing a Long could cause silent conversion issues.
• Some databases are strict about numeric types, so try wrapping the parameter in a type cast in your SQL:

  SELECT * FROM users WHERE id = CAST(? AS UNSIGNED) -- For MySQL
  

Or, test with an Integer parameter instead of Long to see if that resolves the issue.

3. Enable Debug Logging to See the Actual SQL #

JdbcTemplate might be executing a slightly different query than you expect (or binding the wrong parameter value). Enable debug logging for Spring JDBC to see exactly what’s being sent to the database:

If you’re using Spring Boot, add this to your application.properties:

logging.level.org.springframework.jdbc=DEBUG

This will log the full SQL statement and the parameter values being used. You can then copy that exact SQL into your console to confirm it returns results—if it doesn’t, you’ll know the parameter binding is the issue.

4. Rule Out Transaction Context Issues #

If your get() method is running inside a transaction, there might be uncommitted changes blocking your query. For example, if you recently updated the user with id=1 in the same transaction, the changes might not be visible to your query yet (depending on your transaction isolation level).

Try running the query outside of a transaction, or explicitly commit any pending changes before executing the select.

5. Check for Case Sensitivity #

Some databases (like PostgreSQL) are case-sensitive with table/column names. If your database has the table named Users (capital U) but your SQL uses users (lowercase), the console might auto-correct it, but JdbcTemplate will strictly use the string you provided.

Double-check that your SQL’s table and column names match exactly what’s in your database.

Bonus: Add Safety to Your Code #

To avoid the IndexOutOfBoundsException when the list is empty, add a check before accessing the first element:

@Override
public UserInfo get(Long id) {
    String sql = "SELECT * FROM users WHERE id = ? ";
    List<UserInfo> list = jdbcTemplate.query(sql, new UserInfoMapper(), id);
    if (list.isEmpty()) {
        // Either throw a custom exception or return null (whichever fits your app's logic)
        throw new IllegalArgumentException("No user found with id: " + id);
    }
    return list.get(0);
}

内容的提问来源于stack exchange，提问作者Jesper