如何在Spring Boot REST Web Service中为数据库表字段绑定参数

Your current code has two critical issues:

1. Syntax Error: There are no spaces between SQL clauses. Concatenating "SELECT exColumn" + "FROM exTable" results in invalid SQL (SELECT exColumnFROM exTable), which will throw a database syntax exception.
2. Security & Maintainability Risks: Even if you fix the spacing, hardcoding values (or concatenating user input directly) exposes your application to SQL injection attacks. This approach also makes it hard to modify queries or bind dynamic request parameters safely.

Better Implementation Options #

1. JDBC Prepared Statements (Most Basic & Secure) #

Prepared statements use parameter placeholders (?) to separate SQL logic from data, eliminating injection risks and improving query performance via database statement caching. Example:

// Correct SQL with proper spacing and placeholder
String sql = "SELECT exColumn FROM exTable WHERE exColumn2 = ?";

try (Connection conn = getDatabaseConnection();
     PreparedStatement stmt = conn.prepareStatement(sql)) {
    // Bind request parameter to the placeholder
    stmt.setString(1, request.getParameter("exParamValue"));
    ResultSet rs = stmt.executeQuery();
    
    // Process results...
} catch (SQLException e) {
    // Handle exceptions
}

2. ORM Frameworks (Simpler for Object Mapping) #

ORM tools like Hibernate/JPA or MyBatis automatically handle parameter binding and map database records to Java objects, reducing boilerplate code.

MyBatis Example (Mapper XML)

<select id="fetchExColumn" resultType="java.lang.String">
    SELECT exColumn FROM exTable WHERE exColumn2 = #{requestParam}
</select>

The #{requestParam} syntax safely binds the input parameter without injection risks.

Hibernate/JPA Example

@Repository
public interface ExTableRepository extends JpaRepository<ExTable, Long> {
    @Query("SELECT e.exColumn FROM ExTable e WHERE e.exColumn2 = :param")
    String findExColumnByExColumn2(@Param("param") String requestParam);
}

3. Template-Based Query Builders (Alternative) #

Libraries like Querydsl or JOOQ let you build type-safe queries programmatically, which also handle parameter binding internally:

// JOOQ example
String result = dsl.select(EX_TABLE.EX_COLUMN)
                   .from(EX_TABLE)
                   .where(EX_TABLE.EX_COLUMN2.eq(requestParamValue))
                   .fetchOneInto(String.class);

Summary #

Your original approach is not correct due to syntax and security flaws. Always prioritize prepared statements or ORM frameworks for parameter binding—these methods are secure, maintainable, and avoid common pitfalls like SQL injection.

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