Group items tagged

JPA 2.0, still contains only a subset of the features supported by many database vendors

features not supported in JP QL.

performance required by an application is to replace the JP QL query with a hand-optimized SQL version. This may be a simple restructuring of the query that the persistence provider was generating, or it may be a vendor-specific version that leverages query hints and features specific to a particular database.

recommend avoiding SQL initially if possible and then introducing it only when necessary

benefits of SQL query support is that it uses the same Query interface used for JP QL queries. With some small exceptions that will be described later, all the Query interface operations discussed in previous chapters apply equally to both JP QL and SQL queries.

keep application code consistent because it needs to concern itself only with the EntityManager and Query interfaces.

An unfortunate result of adding the TypedQuery interface in JPA 2.0 is that the createNativeQuery() method was already defined in JPA 1.0 to accept a SQL string and a result class and return an untyped Query interface

consequence is that when the createNativeQuery() method is called with a result class argument one might mistakenly think it will produce a TypedQuery, like createQuery() and createNamedQuery() do when a result class is passed in.

@NamedNativeQuery

resultClass=Employee.class

The fact that the named query was defined using SQL instead of JP QL is not important to the caller

SQL Result Set Mapping

JPA provides SQL result set mappings to handle these scenarios

A SQL result set mapping is defined using the @SqlResultSetMapping annotation. It may be placed on an entity class and consists of a name (unique within the persistence unit) and one or more entity and column mappings.

entities=@EntityResult(entityClass=Employee.class)

@SqlResultSetMapping

Multiple Result Mappings

A query may return more than one entity at a time

The SQL result set mapping to return both the Employee and Address entities out of this query

emp_id, name, salary, manager_id, dept_id

address_id, id, street, city, state, zip

order in which the entities are listed is not important

ntities={@EntityResult(entityClass=Employee.class), @EntityResult(entityClass=Address.class)}

To prevent SQL injection:

All queries should be

should be

String concatenation

to create dynamic SQL

Parameterized Queries

Prepared Statements

automatically be escaped by the JDBC driver

userId = ?

PreparedStatement

setString

Dynamic Queries via String Concatenation

never construct SQL statements using string concatenation of unchecked input values

dynamic queries via the java.sql.Statement class leads to SQL Injection

very voluminous output

Setting the SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time

STRICT_TRANS_TABLES

Strict mode does not affect whether foreign key constraints are checked

a) stop writing dynamic queries

b) prevent user supplied input which contains malicious SQL from affecting the logic of the executed query

Primary Defenses:

Option #1: Use of Prepared Statements (Parameterized Queries)

Option #3: Escaping all User Supplied Input

Additional Defenses:

Perform: White List Input Validation

Primary Defenses

Defense Option 1: Prepared Statements (Parameterized Queries)

attacker is not able to

of a query, even if SQL commands are inserted by an attacker

allows the database to

distinguish

between

Defense Option 3: Escaping All User Supplied Input

SQL injection errors occur when:

Data enters a program from an

The data used to

consequences are:

Confidentiality:

sensitive data

Authentication

user names and passwords

Authorization

change this information

Integrity

read sensitive information

changes or even delete this information

for the application

Parameterized Queries with Bound Parameters

keep the

separate through the use of placeholders known as "bound" parameters

how to Review Code for SQL Injection Vulnerabilities.

Guide to SQL Injection

"injection" of a SQL query via the input data from the client to the application

after a restart

resultClass=Employee.class

class Employee

createNamedQuery("findAllEmployeesInCity")

List<Employee>

@NamedNativeQuery

resultSetMapping="employee-address"

@SqlResultSetMapping(name="employee-address", entities={ @EntityResult(entityClass=Employee.class), @EntityResult(entityClass=Address.class)} )

Changes for Hibernate 3.5 applications

if your application uses Hibernate 3 classes that are not available in Hibernate 4, for example, some of the validator or search classes, you may see ClassNotFoundExceptions when you deploy your application. If you encounter this problem, you can try one of two approaches: You may be able to resolve the issue by copying the specific Hibernate 3 JARs containing those classes into the application "/lib" directory or by adding them to the classpath using some other method. In some cases this may result in ClassCastExceptions or other class loading issues due to the mixed use of the Hibernate versions, so you will need to use the second approach. You need to tell the server to use only the Hibernate 3 libraries and you will need to add exclusions for the Hibernate 4 libraries. Details on how to do this are described here: JPA Reference Guide.

In previous versions of the application server, the JCA data source configuration was defined in a file with a suffix of *-ds.xml. This file was then deployed in the server's deploy directory. The JDBC driver was copied to the server lib/ directory or packaged in the application's WEB-INF/lib/ directory. In AS7, this has all changed. You will no longer package the JDBC driver with the application or in the server/lib directory. The *-ds.xml file is now obsolete and the datasource configuration information is now defined in the standalone/configuration/standalone.xml or in the domain/configuration/domain.xml file. A JDBC 4-compliant driver can be installed as a deployment or as a core module. A driver that is JDBC 4-compliant contains a META-INF/services/java.sql.Driver file that specifies the driver class name. A driver that is not JDBC 4-compliant requires additional steps, as noted below.

DataSource Configuration

domain mode, the configuration file is the domain/configuration/domain.xml

standalone mode, you will configure the datasource in the standalone/configuration/standalone.xml

MySQL datasource element:

        <connection-url>jdbc:mysql://localhost:3306/YourApplicationURL</connection-url>        <driver-class> com.mysql.jdbc.Driver </driver-class>        <driver> mysql-connector-java-5.1.15.jar </driver>

       <security>            <user-name> USERID </user-name>            <password> PASSWORD</password>        </security>

example of the driver element for driver that is not JDBC 4-compliant. The driver-class must be specified since it there is no META-INF/services/java.sql.Driver file that specifies the driver class name.

 <driver-class>com.mysql.jdbc.Driver</driver-class>

JDBC driver can be installed into the container in one of two ways: either as a deployment or as a core module

Install the JDBC driver

Install the JDBC driver as a deployment

In AS7 standalone mode, you simply copy the JDBC 4-compliant JAR into the AS7_HOME/standalone/deployments directory

example of a MySQL JDBC driver installed as a deployment:     AS7_HOME/standalone/deployments/mysql-connector-java-5.1.15.jar

There is a drawback: not all views are updatable. Whether a view is updatable or not highly depends on the complexity and particular database

fully specify table names using the database name (that is, SELECT dbname.tablename.colname FROM dbname.tablename...) in your SQL

work with multiple databases