Native SQL Strings

Database accesses can be passed directly in SQL (native SQL statement) in the form of SQL strings. Due to the numerous disadvantages of SQL strings, they should only be used in exceptional cases. With JPA-SQL, RapidClipse offers an elegant alternative to the use of SQL strings.

Examples:

  • Query a table - Returns all records.

public List<Customer> getAllCustomerNativeSQL()
{
    String SQL = "SELECT * FROM CUSTOMER";
    Query nativeQuery = em().createNativeQuery(SQL, Customer.class);
    List<Customer> resultList = nativeQuery.getResultList();

    return resultList;
}

  • Query with parameter

public List<Customer> getCustomersByFirstAndLastname(String lastname, String firstname)
{
    String SQL = "SELECT * FROM CUSTOMER WHERE Lastname LIKE ? AND Firstname LIKE ?";

    Query nativeQuery = em().createNativeQuery(SQL, Customer.class);
    nativeQuery.setParameter(1, lastname);
    nativeQuery.setParameter(2, firstname);
    List<Customer> resultList = nativeQuery.getResultList();

    return resultList;
}

  • Search record via ID

public Customer getCustomersByID(Integer ID)
{
    String SQL = "SELECT * FROM CUSTOMER WHERE ID = ?";

    Query nativeQuery = em().createNativeQuery(SQL, Customer.class);
    nativeQuery.setParameter(1, ID);
    Customer singleResult = (Customer) nativeQuery.getSingleResult();

    return singleResult;
}

Notice:

  • Advantages of SQL strings

    • Any SQL statements, including proprietary ones, can be issued.

    • Initially simpler than Java Query APIs, e.g. JPA Criteria API

  • Disadvantages of SQL strings

    • Unclear code, especially with extensive queries.

    • Not type safe

    • No IDE support

      • Code Completion - recognition of keywords, operators and entities

      • Syntax highlighting

      • Code folding

      • Formatter

      • Inline Refactoring and Refactoring Participants for JDT Member Renames and Moves

      • Hovers

      • Linking (Ctrl+Click)

      • Outline View

      • Error/Warning Markers with Quick Fixes

      • Code Templates

      • Integration in Eclipse build process

    • Danger of SQL injection

    • Debugging of SQL code not possible

    • Errors can only be noticed at runtime

    • Database specific