Versions Compared

Old Version 1

changes.mady.by.user Copy Page Tree

Saved on

compared with

New Version 2

changes.mady.by.user RapidClipse Translator Services

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Datenbankzugriffe können direkt in Database access can be passed directly as SQL (natives native SQL Statementstatement) in Form von SQL-Strings übergeben werden. Auf Grund der zahlreichen Nachteile von SQL-Strings sollten diese nur in Ausnahmefällen verwendet werden. Mit JPA-SQL bietet RapidClipse eine elegante Alternative zur Verwendung von SQL-Stringsthe 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:

  • Abfrage einer Tabelle - Liefert alle Datensätze zurückQuery a table - Returns all records.

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

  • Abfrage mit ParameterQuery with parameters

    Code Block
    languagejava
    themeConfluence
    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;
}

  • Datensatz über die ID suchenSearch a record with the ID

    Code Block
    languagejava
    themeConfluence
    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:


  • Vorteile von SQL-Strings
  • Absetzen beliebiger, auch proprietärer SQL-Statements möglich
    • Anfangs einfacher als Java Query APIs, z.B. Benefits of SQL strings
    • Possible to query any SQL statements, even proprietary statements.
    • Initially simpler than Java query APIs, including JPA Criteria API
  • Nachteile von SQL-Strings 
  • Unübersichtlicher Code, insbesondere bei umfangreichen Queries.
  • Nicht typsicher
    • Keine IDE-Unterstützung
  • Code Completion - Erkennung von Schlüsselwörter, Operatoren und Entities 
  • Syntax-Highlighting
  • Code-Folding

  • Formatter

    • Inline Refactoring and Refactoring Participants for JDT Member Renames and Moves
  • 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 Viewview

      • Error/Warning-Markers with Quick Fixeswarning markers with quick fixes

      • Code Templatestemplates

      • Integration in Eclipse build process

    • Gefahr von SQL-Injection
    • Debuggen des SQL-Codes nicht möglich 
    • Fehler können erst zur Laufzeit bemerkt werden
    • DatenbankspezifischRisk of SQL injection
    • Impossible to debug a SQL query 
    • Errors can only be noticed at runtime
    • Database-specific