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

• • Unclear code, particularly for large queries.
  • Not type safe
  • No IDE support
    • Code completion - detection 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

  • Risk of SQL injection
  • Impossible to debug a SQL query 
  • Errors can only be noticed at runtime
  • Database-specific