Best Tutorial: Many-many relationship with extra columns in JPA

1. https://huongdanjava.com/many-many-relationship-extra-columns-jpa.html
2. http://www.javamakeuse.com/2015/01/hibernate-many-to-many-mapping-with.html
3. https://www.thoughts-on-java.org/many-relationships-additional-properties/
4. https://en.wikibooks.org/wiki/Java_Persistence/ManyToMany
5. https://giannigar.wordpress.com/2009/09/04/mapping-a-many-to-many-join-table-with-extra-column-using-jpa/
6. https://stackoverflow.com/questions/5127129/mapping-many-to-many-association-table-with-extra-columns
7. https://vladmihalcea.com/the-best-way-to-map-a-many-to-many-association-with-extra-columns-when-using-jpa-and-hibernate/

How to resolve [A collection with cascade=“all-delete-orphan” was no longer referenced by the owning entity instance]

1. http://codippa.com/how-to-resolve-a-collection-with-cascadeall-delete-orphan-was-no-longer-referenced-by-the-owning-entity-instance/
2. http://cristian.sulea.net/blog.php?p=2014-06-28-hibernate-exception-a-collection-with-cascade-all-delete-orphan-was-no-longer-referenced-by-the-owning-entity-instance

Best Spring Data JPA tutorial

Blog:

1. https://medium.com/@joeclever/using-multiple-datasources-with-spring-boot-and-spring-data-6430b00c02e7
2. https://www.petrikainulainen.net/programming/spring-framework/spring-data-jpa-tutorial-three-custom-queries-with-query-methods/
3. https://stackoverflow.com/questions/9314078/setmaxresults-for-spring-data-jpa-annotation
4. https://www.petrikainulainen.net/programming/spring-framework/spring-data-jpa-tutorial-part-two-crud/
5. https://scattercode.co.uk/2016/01/05/multiple-databases-with-spring-boot-and-spring-data-jpa/

Github:

• https://github.com/jahe/spring-boot-multiple-datasources/blob/master/src/main/java/com/foobar/BarDbConfig.java

@Entity vs @Table for hibernate

@Entity(name = "foo") => this name will be used to name Entity
@Table(name = "bar")  => this name will be used to name a table in DB

OpenJpa query caching is not refreshing in case of null value - How to resolve this issue?

Solution:

So far it is not fixed yet. But they have given some bypassing solution.

1. Disable query cache

To disable the query cache (default), set the openjpa.QueryCache property to false:

<property name="openjpa.QueryCache" value="false"/>

2. By configuring sql query cache to false
   • To specify a custom cache class:
     <property name="openjpa.jdbc.QuerySQLCache" value="com.mycompany.MyCustomCache"/>
   • To use an unmanaged cache:
     <property name="openjpa.jdbc.QuerySQLCache" value="false"/>
   OR
   • To use an unmanaged cache:
     <property name="openjpa.jdbc.QuerySQLCache" value="all"/>

---

3. Open JPA - L2 Cache Issue and Workaround

This tutorial depicts your problem same to same. Here you can get the clear conception of occuring this error.

It gives a solution that you must have to keep related data. So that NullPointerException will not arise. Data must be consistent until OpenJPA not solve the issue. :D

---

4. Several mechanisms are available to the application to bypass SQL caching for a JPQL query.

A user application can disable Prepared SQL Cache for entire lifetime of a persistence context by invoking the following method on OpenJPA's EntityManager SPI interface:

OpenJPAEntityManagerSPI.setQuerySQLCache(boolean) 

5. Plug-in property openjpa.jdbc.QuerySQLCache can be configured to exclude certain JPQL queries as shown below.
   <property name="openjpa.jdbc.QuerySQLCache" value="true(excludes='select c from Company c;select d from Department d')"/>

will never cache JPQL queries select c from Company c and select d from Department d.

Root Cause Analysis:

The query cache stores the object IDs that are returned by query executions. When you run a query, JPA assembles a key that is based on the query properties and the parameters that are used at launch time and checks for a cached query result. If one is found, the object IDs in the cached result are looked up, and the resulting persistence-capable objects are returned. Otherwise, the query is launched against the database and the object IDs that are loaded by the query are placed into the cache. The object ID list is not cached until the list that is returned at query launch time is fully traversed.

IBM Recommendation:

L2 caching increases the memory consumption of the application, therefore, it is important to limit the size of the L2 cache. There is also a possibility of stale data for updated objects in a clustered environment. Configure L2 caching for read-mostly, infrequently modified entities. L2 caches are not recommended for frequently and concurrently updated entities.

Resource Link:

Open JPA 2.4.0 Caching Reference Guide

Original Link: OpenJpa query caching is not refreshing in case of null value

Spring boot study

https://github.com/arpitmandliya/SpringBootHibernateExample
https://github.com/lakshmandev/SpringBoot/blob/master/src/main/java/com/example/controller/web/GreetingsController.java
https://github.com/farrukhmpk/html-pdf-service/tree/master/src/main/java/org/farrukh/mirza/pdf/spi
https://www.mkyong.com/spring/maven-spring-hibernate-mysql-example/
http://viralpatel.net/blogs/spring-4-mvc-rest-example-json/
https://avaldes.com/spring-restful-web-service-example-with-json-and-jackson-using-spring-tool-suite/
http://www.beingjavaguys.com/2014/08/spring-restful-web-services.html
https://github.com/Spidertracks/spring-rest-api-demo
http://www.concretepage.com/spring-4/spring-4-rest-web-service-json-example-tomcat
https://www.java2blog.com/spring-boot-hibernate-example/
http://websystique.com/springmvc/spring-4-mvc-rest-service-example-using-restcontroller/
http://www.journaldev.com/2552/spring-rest-example-tutorial-spring-restful-web-services

www.hibernatespatial.org is dead? What will be the solution?

Exception:
-------------
11:24:27.580 [INFO] [org.apache.http.impl.execchain.RetryExec] I/O exception (java.net.SocketException) caught when processing request to {}->http://www.hibernatespatial.org:80: Connection reset
11:24:27.580 [DEBUG] [org.apache.http.impl.execchain.RetryExec] Connection reset
java.net.SocketException: Connection reset
        at java.net.SocketInputStream.read(SocketInputStream.java:196)
        at java.net.SocketInputStream.read(SocketInputStream.java:122)
        at org.apache.http.impl.io.SessionInputBufferImpl.streamRead(SessionInputBufferImpl.java:139)
        at org.apache.http.impl.io.SessionInputBufferImpl.fillBuffer(SessionInputBufferImpl.java:155)

Root Cause Analysis:
--------------------------
The javadoc for SocketException states that it is

    Thrown to indicate that there is an error in the underlying protocol such as a TCP error

So, here the actual case is,
When the command "gradle bootRun" is trying to access this maven repo "http://www.hibernatespatial.org:80", it fails. Because this repo is dead.

Resource Link:
1. http://stackoverflow.com/a/585643
2. http://stackoverflow.com/a/4300803

Solution Procedure#1:
----------------------------
Hibernate Spatial has been merged into Hibernate ORM 5.0. The hibernatespatial.org site will be discontinued, along with the Maven Repository.

The ideal solution would be to upgrade to a 5.0.x version that is on maven central https://mvnrepository.com/artifact/org.hibernate/hibernate-spatial.

Solution Procedure#2:
----------------------------
As the repo is dead, so it is required to search for another repo which can give access for related file.

The versions 4.0 and 4.3 can be found on this repo: http://nexus.e-is.pro/nexus/content/groups/public/org/hibernate/hibernate-spatial/

Resource Link:
1. http://stackoverflow.com/a/38480566

You can choose 2nd solution procedure.

Now the build.gradle file looks like below:

    repositories {
        mavenCentral()
        mavenLocal()
        maven { url "http://repo.spring.io/snapshot" }
        maven { url "http://repo.spring.io/milestone" }
        maven { url "http://download.osgeo.org/webdav/geotools" }
        // maven { url "http://www.hibernatespatial.org/repository" } //dead link
        maven { url "http://nexus.e-is.pro/nexus/content/groups/public/" }
    }

How does EJB and JPA relate?

How does EJB and JPA relate?

JPA has been designed to replace EJB2 entity beans, and has started as a part of the EJB3 specification.

Since it makes sense to also use JPA outside of an EJB container, it has now its own specification, but it's still related to EJB3, since a compliant EJB3 container has to provide a JPA implementation, which integrates into the transaction handling of the container.

EJB2 had "entity beans" which were a third type of component. EJB3 has JPA, which has "entities". But I don't think they're considered as "EJB components" anymore. They're just called JPA entities.

2. Up until version 2.1 of the EJB specifications, an entity bean class had to implement the javax.ejb.EntityBean interface and provide an implementation for boilerplate methods such as ejbLoad, ejbStore, ejbActivate, and ejbPassivate.

EJB 3.0 adopted the JPA specification. The very notion of an entity bean was superceded by the simpler notion of a JPA entity. To create such entity, no interface implementation or boiler plate methods are required. The entity is a POJO that has the @Entity annotation.

Thus, in practice the use of "entity bean" EJBs in Java EE applications is dead (buried under JPA) as of EJB 3.

You are right. JPA has more to do than only supporting EJB. Thats the reason why JPA became a separate JSR or specification. EJB uses or enables the usage of JPA in its specification, simply because JPA is a good standard. You can now switch between JPA vendors without changing your code if designed properly.

EJB specification can be used independent of JPA (although JPA has been included as a part of EJB spec) and likewise JPA can be used for many more stuff outside the EJB spec. Nevertheless, EJB specification enables the injection of JPA Entitiy Manager (and its usage) into its beans very easily which makes programming easier. Ofcourse this can now be achieved easily using a new JSR on CDI :-).

All the application server that supports EJB spec, should support JPA also. You can see this threadfor more information.

Hibernate is an implementation of the JPA spec.

Pros of Agile methods

• Working software is delivered much more quickly and successive iterations can be delivered frequently, at a consistent pace.
• There is closer collaboration between developers and the business.
• Changes to requirements can be incorporated at any point of the process – even late in development.
• It gives the opportunity for continuous improvement for live systems
• It is highly transparent

Pros of the waterfall method

·        Potential issues that would have been found during development can be researched and bottomed out during the design phase. If appropriate meaning an alternate solution is selected before any code is written.

·        The development process tends to be better documented since this methodology places greater emphasis on documentation like requirements and design docs. Many organisations find this reassuring.

·        Because the waterfall process is a linear one it is perhaps easier to understand, especially for non-developers or those new to software development. Often teams feel more comfortable with this approach.

Cons of the waterfall method

·        Often the people we’re building software for (the client) don’t know exactly what they need up front and don’t know what’s possible with the technology available. This way of working doesn’t handle this well.

·        Solution designers often aren’t able to foresee problems that will arise out of the implementation of their designs.

·        Changes to requirements (e.g. like those resulting from new technologies, changes in a market or changes to business goals) can’t easily be incorporated with the waterfall method and there are often laborious change control procedures to go through when this happens

·        The process doesn’t have its own momentum

JPA golden touch

1. https://github.com/alanhay/jpa
2. https://github.com/somaiah

Hibernate's hbm2ddl Tool

The Hibernate hbm2ddl is a tool allows us to create, update, and validate a database schema using Hibernate mappings configuration. The .hbm files are Hibernate mapping files, but since the schema export/validation is done using the the internal configuration Hibernate creates for the entities mapping, we can use still use hbm2ddl when working with JPA. As usual in here I write about the JPA environment, just remember that the hbm2ddl can also be invoked from command line or using Ant task.

Setup
To invoke Hibernates hbm2ddl during the creation of the entity manager factory set the 'hibernate.hbm2ddl.auto' property to one of 

·         create

·         create-drop

·         update

·         validate

Here is an example:

<persistence>
  <persistence-unit name="sampleContext" transaction-type="RESOURCE_LOCAL">
  <provider>org.hibernate.ejb.HibernatePersistence</provider>
  <properties>
    <property name="hibernate.connection.driver_class" value="org.apache.derby.jdbc.ClientDriver">
     <property name="hibernate.connection.username" value="app">
     <property name="hibernate.connection.password" value="app">
     <property name="hibernate.connection.url" value="jdbc:derby://localhost:1527/HibernateTestDB;create=true">
     <property name="hibernate.hbm2ddl.auto" value="validate">
   </properties>
   </persistence-unit>
</persistence>

The Options and Their Meanings

create

Hibernate will create the database when the entity manager factory is created (actually when Hibernate's SessionFactory is created by the entity manager factory). If a file named import.sql exists in the root of the class path ('/import.sql') Hibernate will execute the SQL statements read from the file after the creation of the database schema. It is important to remember that before Hibernate creates the schema it empties it (delete all tables, constraints, or any other database object that is going to be created in the process of building the schema).

create-drop

Same as 'create' but when the entity manager factory (which holds the SessionFactory) is explicitly closed the schema will be dropped.

update

Hibernate creates an update script trying to update the database structure to the current mapping. Does not read and invoke the SQL statements from import.sql. Useful, but we have to be careful, not all of the updates can be done performed ? for example adding a not null column to a table with existing data.

validate

Validates the existing schema with the current entities configuration. When using this mode Hibernate will not do any changes to the schema and will not use the import.sql file.

Mode	Reads import.sql	Alters Database Structure	Comments
update	No	Yes
create	Yes	Yes	Empties the database before creating it
create-drop	Yes	Yes	Drops the database when the SessionFactory is closed
validate	No	No

What is dialect class in Hibernate?

1. Dialect class is java class, which contains code to map between java language data type database data type.
2. All Dialect classes extend the Dialect abstract class.
3. Dialect is used to convert HQL statements to data base specific statements.

To connect to any database with hibernate, we need to specify the SQL dialect class in hibernate.cfg.xml

     Ex: To connect to oracle database we need to specify oracle dialect class in configuration xml as below.
<property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>

Hibernate Master Vlad Mihalcea

1. https://vladmihalcea.com/tutorials/hibernate/
2. https://github.com/vladmihalcea
3. http://stackoverflow.com/users/1025118/vlad-mihalcea
4. http://what-when-how.com/hibernate/advanced-query-options-hibernate/

Hibernate Merge

1. http://stackoverflow.com/questions/1069992/jpa-entitymanager-why-use-persist-over-merge?rq=1
2. http://techblog.bozho.net/how-does-merge-work-in-jpa-and-hibernate/
3. https://hibernate.atlassian.net/browse/HHH-5855
4. https://github.com/hibernate/hibernate-orm/pull/1212
5. http://www.programmingforfuture.com/2011/02/hibernate-merge-may-insert-new-record.html
6. https://gopinathb4u.wordpress.com/2011/07/26/duplicate-child-inserts-during-merge-operation-in-hibernate/

Hibernate Tutorial

https://vladmihalcea.com/tutorials/hibernate/

OpenJpa second level caching

1.  http://openjpa.apache.org/builds/1.2.3/apache-openjpa/docs/ref_guide_caching.html
2. http://openjpa.apache.org/builds/1.2.3/apache-openjpa/docs/ref_guide_caching.html#ref_guide_cache_pmevict
3. https://www.ibm.com/support/knowledgecenter/was_beta/com.ibm.websphere.base.doc/ae/tejb_datcacheconfig.html
4. https://www.ibm.com/support/knowledgecenter/SSAW57_8.0.0/com.ibm.websphere.nd.doc/info/ae/ae/rdyn_openjpa.html
5. http://www.developer.com/java/using-second-level-caching-in-a-jpa-application.html
6. http://docs.oracle.com/javaee/6/tutorial/doc/gkjjj.html
7. http://blog.jhades.org/setup-and-gotchas-of-the-hibernate-second-level-and-query-caches/
8. http://www.javalobby.org/java/forums/t48846.html
9. http://docs.jboss.org/hibernate/orm/3.5/reference/en/html/performance.html
10. http://www.coderpanda.com/jpa-caching/
11. http://tech.puredanger.com/2009/07/10/hibernate-query-cache/

 When Query cache is bypassed?

 The evictAll method with no arguments clears the cache.

 Most caches are of limited size. Pinning an identity to the cache ensures that the cache will not kick the data for the corresponding instance out of the cache, unless you manually evict it.

 StoreCache Usage

import org.apache.openjpa.persistence.*;

...

OpenJPAEntityManagerFactory oemf = OpenJPAPersistence.cast(emf);

StoreCache cache = oemf.getStoreCache();

cache.pin(Magazine.class, popularMag.getId());

cache.evict(Magazine.class, changedMag.getId());

Hibernate Performance Tuning

1. http://stackoverflow.com/questions/35323950/hibernate-associations-using-too-much-memory
2. http://www.mkyong.com/hibernate/hibernate-fetching-strategies-examples/
3. https://developer.jboss.org/wiki/AShortPrimerOnFetchingStrategies
4. https://dzone.com/articles/lazyeager-loading-using
5. http://www.laliluna.com/articles/java-persistence-hibernate/performance-tips-hibernate-java-persistence.html
6. http://blog.jhades.org/performance-tuning-of-spring-hibernate-applications/
7. http://www.thoughts-on-java.org/tips-to-boost-your-hibernate-performance/
8. http://arnosoftwaredev.blogspot.com/2011/01/hibernate-performance-tips.html
9. http://arnosoftwaredev.blogspot.com/2011/01/fetching-strategies-in-hibernate.html
10. http://vladmihalcea.com/2013/10/17/hibernate-facts-the-importance-of-fetch-strategy/
11. http://what-when-how.com/hibernate/optimizing-fetching-and-caching-hibernate/
12. http://www.laliluna.com/articles/java-persistence-hibernate/performance-tips-hibernate-java-persistence.html
13. https://dzone.com/articles/hibernate-performance-tuning

hibernate improve database performance

hibernate improve database performance

1. In the query string, you should use jdbc placeholder? Or use named parameters: Do not use the query string value instead of the very values.
2. Flush affect performance, frequently refreshed affect performance, minimize unnecessary refresh. 
3. Cascade strategy, a few to several relationships set correctly cascade strategy, to think clearly in the operation target A cascade operation, while the need for an object B, such as one to many relationship between father and son, the father removed one, required level United delete child many, this time can be set to one end of this cascade = "delete", so when you delete one, the child is automatically deleted, but the pair does not affect the parent. Cascade has other property values, as long as the settings are correct, you can improve performance. 
4. Lazy policy set correctly delay loading strategy will also enhance the performance, one to many or many to many, the total delay should normally be loaded into the memory of many of the party. Set lazy = "true", first send sql statements to load itself into memory only when needed to load Cascading objects; lazy = "false", is simultaneously loaded into memory objects themselves and cascade. 
5. In addition to the performance of the collection (set, list, map, array ), should be set up correctly. 
6.  The proper use of third-party cache, read frequently writes small operating conditions, the use of third-party cache can significantly improve performance, such as ehcache caching strategies have: read-only, read-write and notstrict-read-write.

SQL Injection and how to prevent it? Hibernet/JPA/SQL

SQL Injection

1.      Prepared Statement and Callable Statement:

A PreparedStatement represents a precompiled SQL statement that can be executed multiple times without having to recompile for every execution.

Secure Code:

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM users WHERE userid=? AND password=?");

stmt.setString(1, userid);

stmt.setString(2, password);

ResultSet rs = stmt.executeQuery();

Why this code is secure?

Ans: This code is not vulnerable to SQL Injection because it correctly uses parameterized queries. By utilizing Java's PreparedStatement class, bind variables (i.e. the question marks) and the corresponding setString methods, SQL Injection can be easily prevented.

Vulnerable Code 1:

// Example #1

String query = "SELECT * FROM users WHERE userid ='"+ userid + "'" + " AND password='" + password + "'";

Statement stmt = connection.createStatement();

ResultSet rs = stmt.executeQuery(query);

Why this code is vulnerable?

Ans: This code is vulnerable to SQL Injection because it uses dynamic queries to concatenate malicious data to the query itself. Notice that it uses the Statement class instead of the PreparedStatement class.

Vulnerable Code 2:

// Example #2

String query = "SELECT * FROM users WHERE userid ='"+ userid + "'" + " AND password='" + password + "'";

PreparedStatement stmt = connection.prepareStatement(query);

ResultSet rs = stmt.executeQuery();

Why this code is vulnerable?

Ans: This code is also vulnerable to SQL Injection. Even though it uses the PreparedStatement class it is still creating the query dynamically via string concatenation.

http://software-security.sans.org/developer-how-to/fix-sql-injection-in-java-using-prepared-callable-statement

2.      Hibernate:

How to Fix SQL Injection using Hibernate?

Hibernate facilitates the storage and retrieval of Java domain objects via Object/Relational Mapping (ORM). It is a very common misconception that ORM solutions, like hibernate, are SQL Injection proof. Hibernate allows the use of "native SQL" and defines a proprietary query language, named, HQL (Hibernate Query Language); the former is prone to SQL Injection and the later is prone to HQL (or ORM) injection.

This article is intended to illustrate how certain syntax offered by hibernate to define SQL & HQL, is better over the other, in terms of defense against SQL and/or HQL injection attacks.

Secure Usage:

Code-1:

/* Positional parameter in HQL */

Query hqlQuery = session.createQuery("from Orders as orders where orders.id = ?");

List results = hqlQuery.setString(0, "123-ADB-567-QTWYTFDL").list();

Code-2:

/* named parameter in HQL */

Query hqlQuery = session.createQuery("from Employees as emp where emp.incentive > :incentive");

List results = hqlQuery.setLong("incentive", new Long(10000)).list();

Code-3:

/* named parameter list in HQL */

List items = new ArrayList();

items.add("book"); items.add("clock"); items.add("ink");

List results = session.createQuery("from Cart as cart where cart.item in (:itemList)").setParameterList("itemList", items).list();

Code-4:

/* JavaBean in HQL */

Query hqlQuery = session.createQuery("from Books as books where book.name = :name and book.author = :author");

List results = hqlQuery.setProperties(javaBean).list();

//assumes javaBean has getName() & getAuthor() methods.

Code-5:

/* Native-SQL */

Query sqlQuery = session.createSQLQuery("Select * from Books where author = ?");

List results = sqlQuery.setString(0, "Charles Dickens").list();

Why above 5 codes are secure ?

Ans:

The above code snippets use parameter binding to set data. The JDBC driver will escape this data appropriately before the query is executed, making sure that data is used just as data.

Assuming data used in the above code snippets is user input, that has not been validated or escaped and it contains malicious database code (payload), the payload will be escaped appropriately by the JDBC driver (since parameterized queries are used), such that it would be used as data and not as code.

Vulnerable Code:

List results = session.createQuery("from Orders as orders where orders.id = " + currentOrder.getId()).list();

List results = session.createSQLQuery("Select * from Books where author = " + book.getAuthor()).list();

Why this code is vulnerable ?

Ans:

Assuming orderId and author are user input that have not been validated or escaped, it leaves the above queries vulnerable to SQL and HQL(ORM) injection attacks.

http://software-security.sans.org/developer-how-to/fix-sql-injection-in-java-hibernate

3.      Java Persistence API(JPA):

How to Fix SQL Injection using the Java Persistence API (JPA) ?

Java Persistence API (JPA), is an ORM solution that is a part of the Java EE framework. It helps manage relational data in applications that use Java SE and Java EE. It is a common misconception that ORM solutions like JPA (Java Persistence API) are SQL Injection proof. JPA allows the use of native SQL and defines its own query language, named, JPQL (Java Persistence Query Language). The former is prone to traditional SQL injection attacks and the later is prone to JPQL (or ORM) injection attacks.

This article is intended to illustrate how certain syntax offered by JPA to define SQL & HQL, is better over the other, in terms of defense against SQL and/or HQL injection attacks.

Secure usage:

Code-1:

/* positional parameter in JPQL */

Query jpqlQuery = entityManager.createQuery("Select order from Orders order where order.id = ?1");

List results = jpqlQuery.setParameter(1,"123-ADB-567-QTWYTFDL").getResultList();

Code-2:

/* named parameter in JPQL */

Query jpqlQuery = entityManager.createQuery("Select emp from Employees emp where emp.incentive > :incentive");

List results = jpqlQuery.setParameter("incentive",

new Long(10000)).getResultList();

Code-3:

/* named query in JPQL - Query named "myCart" being "Select c from Cart c where c.itemId = :itemId" */

Query jpqlQuery = entityManager.createNamedQuery("myCart");

List results = jpqlQuery.setParameter("itemId", "item-id-0001").getResultList();

Code-4:

/* Native SQL */

Query sqlQuery = entityManager.createNativeQuery("Select * from Books where author = ?", Book.class);

List results = sqlQuery.setParameter(1, "Charles Dickens").getResultList();

Why above 4 codes are secure ?

Ans:

The above code snippets use parameter binding to set data. The JDBC driver will escape this data appropriately before the query is executed; making sure that data is used just as data.

Assuming data used in the above code snippets is user input, that has not been validated or escaped and it contains malicious database code (payload), the payload will be escaped appropriately by the JDBC driver (since parameterized queries are used), such that it would be used as data and not as code.

Vulnerable Code:

List results = entityManager.createQuery("Select order from Orders order where order.id = " + orderId).getResultList();

List results = entityManager.createNativeQuery("Select * from Books where author = " + author).getResultList();

int resultCode = entityManager.createNativeQuery("Delete from Cart where itemId = " + itemId).executeUpdate();

Why this code is vulnerable ?

Ans:

Assuming orderId, author & itemId are user input that have not been validated or escaped as required, it leaves the above queries vulnerable to SQL and JPQL (ORM) injection attacks.

http://software-security.sans.org/developer-how-to/fix-sql-injection-in-java-persistence-api-jpa

You can use Prepared Statements wrong like this:

Code:

String strUserName = request.getParameter("Txt_UserName");
 PreparedStatement prepStmt = con.prepareStatement("SELECT * FROM user WHERE userId = '+strUserName+'");

So be sure to use Prepared Statements WITH ALL Bind Variables.

Code:

String selectStatement = "SELECT * FROM User WHERE userId = ? ";
PreparedStatement prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, userId);
ResultSet rs = prepStmt.executeQuery();