Native SQL Strings

Database access can be passed directly as SQL (native SQL statement) in the form of SQL strings. Due to the numerous disadvantages associated with the use of SQL strings, they should only be used in exceptional cases. With JPA-SQL, RapidClipse offers an elegant alternative to 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 parameters

  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 a record with the 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; }

Note:

• Benefits of SQL strings

  • Possible to query any SQL statements, even proprietary statements.

  • Initially simpler than Java query APIs, including JPA Criteria API

• Disadvantages of SQL strings