Why MyBatis Returns No Results While MySQL Shows Data – Fix the Hidden SQL Issue

Problem Description

MyBatis query returns no results while the same SQL executed directly in the database returns a record.

Database record screenshot.

MyBatis query result shows 0 rows.

Executing the identical SQL in Navicat returns 1 row.

Solution

Place the where conditions on the same line, i.e., combine username='${username}' and and password='${password}' into a single line.

<select id="selectByUsernameAndPassword" resultMap="BaseResultMap" parameterType="string">
    SELECT
    <include refid="Base_Column_List" />
    FROM user
    where username = '${username}'
    and password = '${password}'
</select>

<select id="selectByUsernameAndPassword" resultMap="BaseResultMap" parameterType="string">
    SELECT
    <include refid="Base_Column_List" />
    FROM user
    where username = '${username}' and password = '${password}'
</select>

Now the query results are consistent.

Exception Analysis

Many developers encounter this problem; the root cause is usually that the actual SQL executed differs from the displayed statement due to formatting errors.

In this case the XML splits the two conditions onto separate lines, causing MyBatis to generate a different SQL than expected.

SELECT id, username, password FROM user where username = 'aaa' # ''
and password = 'xxx'

The console‑printed SQL omits the line break, leading to mismatched results.

Conclusion

The key is to ensure the SQL MyBatis executes matches the intended statement; check line breaks and whitespace. Also note that using ${username} is vulnerable to SQL injection – prefer #{} placeholders.