Why use JPA instead of writing the SQL query using JDBC?

I've been reading up on several articles what is JPA (Java Persistent API) and which vendor supporting it (DataNucleus, JBoss Hibernate etc)

I don't have experience with ORM (object relational mapping).

What I have done so far is to write my own Database classes using DTO and DAO. So far I'm happy about what I have but would like to know why people use JPA over Java file which contains SQL.

To me I feel like writing DAO class would be ok something like below.

public class DAOUsers {
     public void insertNewUser(DTO DtoUser) {
           String query = "INSERT INTO users(username, address) " +
                          "VALUES(DtoUser.username , DtoUser.address)";
           Executor.run(query);
     }

}

I've learned JPA uses JPQL, Java persistent query language and it operates against entity object rather than directly with db tables.

My understanding (correct me if Im wrong) is that entity object here is same as my DTO object (kind of like bean?)

But anyhow.. what really benefit JPA gives over writing pure SQL in my file? Seems like using annotations required by JPA and make SQL not readable feels not really attractive to me..

please let me know if you need more clarification, I'm new to this topic and would like to hear some opinion.

> Why use JPA instead of directly > writing SQL query on Java File (i.e. > directly to JDBC) ?

Certain projects require engineers to focus more on the object model rather than on the actual SQL queries used to access data stores. The question can actually be interpreted as

> Why should one use an ORM framework ?

which can have different answers in different contexts.

Most projects can benefit from having a domain model, with persistence being a second concern. With JPA (implementations) or most other ORM frameworks, it is possible to have all entities i.e. tables in your database, modelled as classes in Java. Additionally, it also possible to embed behavior into these classes and therefore achieve a behaviorally rich domain model. The entities in this model can have multiple purposes, including the purpose of replacing DTOs for transporting data across tiers.

That said, there are places where ORM frameworks may not be a direct fit to the problem, especially when the data model is already established, or when one is working with legacy systems where mapping database tables to Java classes is a non-trivial exercise. And in certain cases, if one needs to absolutely tune the heck out of the SQL generated by the ORM framework, then ORM frameworks are usually a bad fit.

Related Questions

1. Java EE Architecture - Are DAO's still recommended when using an ORM like JPA 2?
2. Using an ORM or plain SQL?
3. ORM vs Handcoded Data Access Layer

Although this is an old question, I feel like it deserves a new answer. I'm a late adopter of JPA, I've been using it on and off for a couple of years, and while I've had my moments of being impressed by the simplicity of standing up a new application, I have become decidedly unimpressed with the performance, complexity and learning curve required to do JPA correctly. The answers in this thread actually reinforce my position.

Firstly, @vineet suggests "the entities can have multiple purposes"... which I've seen in production and I'd say is encouraged by ORM. So much for cohesion and the single responsibility principal. In my experience, adding behaviours to database entities is asking for trouble. I know this because I've done it, and lived to regret it.

Second, there are simple alternatives to the complexity of JPA that provide the ability to use classes with a RDBMS without all the heaviness (and performance issues) caused by the mismatch that ORM tries (unsuccessfully) to solve. We've been using non-JPA relational-class mapping tools for a decade in an application with over 1,000 tables and we simply do not see how JPA is an improvement over more direct access to the database. JPA obscures the power of the database while adding overhead (in the form of annotations and JQL) to the class model... shouldn't it work the other way?

@water suggests numerous things which are true in theory but impractical in reality. For example, having switched backend databases three times I can assure readers that there is no such thing as a few config tweaks and you're done. I'd suggest, if you're spending a lot of time maintaining your persistence layer, your database model is evolving, and you'd be doing the same or more work in JPA. Particularly when non trivial queries in JPA require the use of JQL!

Almost everyone pretends that JPA developers don't need to know SQL. What I've seen in practice is that now we have to learn SQL and JQL. Apparently we don't have to do DDL - but in any non trivial application of course you need to know DDL. Hibernate doesn't even recommend using automatic DDL generation. Apparently we don't have to do DML, except when we call out to a Native Query, which of course is non-portable, smashes the cache, and has all the same problems as JDBC...

Ultimately, in a properly structured application where the domain model is independent from the business logic, JPA provides little in the way of functionality for what I've found to be a very high learning curve - because the domain model is actually very easy to build. I wouldn't use JDBC directly, but something like Apache DBUtils provides a simple layer above JDBC that maps rows to objects, and with a bit of effort can provide most of the advantages of JPA with none of the hiding and none of the overhead.

I've been developing Java database applications since JDBC 1.0, using a variety of libraries (and iODBC and ESQL before JDBC), and for performance reasons alone, I'm done with JPA. But even if performance was better, the learning curve and incomplete abstractions give me serious pause. JPA is complex and tries to hide details which, in my opinion, developers actually need to care about. As an example, we recently saw hibernate issue 250 delete commands to the database when one would suffice. JPA, by its nature, makes this kind of error easy.

I'm not advocating for JDBC, I'm simply advocating against JPA. Developers who don't or can't work in SQL probably shouldn't be writing relational applications - any more than Developers like me, who couldn't do matrix algebra to save my life, should be writing 3D games. Developers who do use SQL for a living should be horrified by the contorted SQL that hibernate, for one, sends to the server, in order to avoid round trips that shouldn't be necessary in the first place.

> What really benefit JPA gives over writing pure SQL in my file?

Here are some of the benefits:

• JPA allows you to avoid writing DDL in a database specific dialect of SQL. Instead you write "mappings" in XML, or using Java annotations.

• JPA allows you to avoid writing DML in the database specific dialect of SQL.

• JPA allows you to load and save Java objects and graphs without any DML language at all.

• When you do need to perform queries JPQL allows you to express the queries in terms of the Java entities rather than the (native) SQL tables and columns.

Generally speaking, JPA is simpler, cleaner and less labour intensive than JDBC + SQL + hand-written mappings. The more complicated your data model, the more beneficial it is.

However, if performance is an overriding concern, JPA does tend to get in the way by adding layers between your application and the database. If your application requires you to extensively hand-optimize the native database queries and schemas to maximize performance, JPA is probably not a good fit.

JPA is probably also not for you if you are much more comfortable juggling Java, JDBC and SQL in the same application, than with letting the ORM deal with the messy details. (But if you are, you are probably in the minority ...)

JDBC is verbose

For instance, this is the most common way of inserting some records:

int postCount = 100;

try (PreparedStatement postStatement = connection.prepareStatement("""
    INSERT INTO post (
        id,
        title 
    ) 
    VALUES (
        ?, 
        ?
    )
    """
)) {
    for (int i = 1; i <= postCount; i++) {        
        int index = 0;
        
        postStatement.setLong(
            ++index, 
            i
        );
        postStatement.setString(
            ++index, 
            String.format(
                "High-Performance Java Persistence, review no. %1$d", 
                i
            )
        );
        
        postStatement.executeUpdate();
    }
} catch (SQLException e) {
    fail(e.getMessage());
}

JDBC batching requires changing your data access code

And, the moment you realized that this does not perform well, because you forgot to use batching, you have to change the previous implementation, like this:

int postCount = 100;
int batchSize = 50;

try (PreparedStatement postStatement = connection.prepareStatement("""
    INSERT INTO post (
        id,
        title 
    ) 
    VALUES (
        ?, 
        ?
    )
    """
)) {
    for (int i = 1; i <= postCount; i++) {
        if (i % batchSize == 0) {
            postStatement.executeBatch();
        }
        
        int index = 0;
        
        postStatement.setLong(
            ++index, 
            i
        );
        postStatement.setString(
            ++index, 
            String.format(
                "High-Performance Java Persistence, review no. %1$d", 
                i
            )
        );
        
        postStatement.addBatch();
    }
    postStatement.executeBatch();
} catch (SQLException e) {
    fail(e.getMessage());
}

The JPA and Hibernate alternative

With JPA, once you mapped your entity:

@Entity
@Table(name = "post")
public class Post {

    @Id
    private Long id;

    private String title;

    public Long getId() {
        return id;
    }

    public Post setId(Long id) {
        this.id = id;
        return this;
    }

    public String getTitle() {
        return title;
    }

    public Post setTitle(String title) {
        this.title = title;
        return this;
    }
}

And, you set the following Hibernate configuration property:

<property name="hibernate.jdbc.batch_size" value="50"/>

This is how you can insert those post table records:

for (long i = 1; i <= postCount; i++) {
    entityManager.persist(
        new Post()
            .setId(i)
            .setTitle(
                String.format(
                    "High-Performance Java Persistence, review no. %1$d", 
                    i
                )
            )
    );
}

Much simpler, right?

Fetching data with JDBC

With JDBC, this is how you execute an SQL projection:

int maxResults = 10;

List<Post> posts = new ArrayList<>();

try (PreparedStatement preparedStatement = connection.prepareStatement("""
        SELECT 
            p.id AS id, 
            p.title AS title
        FROM post p 
        ORDER BY p.id
        LIMIT ?
        """
)) {
    preparedStatement.setInt(1, maxResults);

    try (ResultSet resultSet = preparedStatement.executeQuery()) {
        while (resultSet.next()) {
            int index = 0;
			
            posts.add(
                new Post()
                    .setId(resultSet.getLong(++index))
                    .setTitle(resultSet.getString(++index))
            );
        }
    }

} catch (SQLException e) {
    fail(e.getMessage());
}

That's rather verbose because you have to transform the ResultSet to the data structure your application is using (e.g., DTOs, JSON web response).

Fetching data with JPA

With JPA, you can fetch the List of Post records like this:

int maxResults = 10;

List<Post> posts = entityManager.createQuery("""
    select p
    from post p 
    order by p.id
    """, Post.class)
.setMaxResults(maxResults)
.getResultList();

And, not only that it's simpler to write it, but it works on every database supported by Hibernate since the pagination syntax is adapted based on the underlying database dialect.

Other advantages JPA has over JDBC

• You can fetch entities or DTOs. You can even fetch hierarchical parent-child DTO projection.
• You can enable JDBC batching without changing the data access code.
• You have support for optimistic locking.
• You have a pessimistic locking abstraction that's independent of the underlying database-specific syntax so that you can acquire a READ and WRITE LOCK or even a SKIP LOCK.
• You have a database-independent pagination API.
• hibernate.query.in_clause_parameter_padding.
• You can use a strongly consistent caching solution that allows you to offload the Primary node, which, for read-write transactions, can only be called vertically.
• You have built-in support for audit logging via Hibernate Envers.
• You have built-in support for multitenancy.
• You can generate an initial schema script from the entity mappings using the Hibernate hbm2ddl tool, which you can supply to an automatic schema migration tool, like Flyway.
• Not only that you have the freedom of executing any native SQL query, but you can use the SqlResultSetMapping to transform the JDBC ResultSet to JPA entities or DTOs.

JPA disadvantages

The disadvantages of using JPA and Hibernate are the following:

• While getting started with JPA is very easy, become an expert requires a significant time investment because, besides reading its manual, you still have to learn how database systems work, the SQL standard as well as the specific SQL flavor used by your project relation database.
• There are some less-intuitive behaviors that might surprise beginners, like the flush operation order.
• The Criteria API is rather verbose, so you need to use a tool like Codota to write dynamic queries more easily.

Conclusion

One of the greatest things about the Java ecosystem is the abundance of high-quality frameworks. If JPA and Hibernate are not a good fit for your use case, you can use any of the following frameworks:

• MyBatis, which is a very lightweight SQL query mapper framework.
• QueryDSL, which allows you to build SQL, JPA, Lucene, and MongoDB queries dynamically.
• jOOQ, which provides a Java metamodel for the underlying tables, stored procedures, and functions and allows you to build an SQL query dynamically using a very intuitive DSL and in a type-safe manner.

While JPA brings many advantages, you have many other high-quality alternatives to use if JPA and Hibernate don't work best for your current application requirements. So, nowadays, you don't really need to use plain JDBC unless you are developing a data access framework.

If done correctly, you can map SQL queries directly to java objects with JPA implementations such as hibernate. I recently did a project where I had a POJO and 3 or 4 annotations and little bit of setup code to take a stored procedure and map it directly to a list of objects (of the POJO class type). This to me is part of the power of JPA.

If you're using it like you would straight up SQL+JDBC then I don't know of any advantages.

There is a decent article here on the advantages of JPA.

Hope this helps.

As we all know that object is one of most import thing of our life and in programming object is very easy way to simplify any problem.... and if object is available there then why we use whole thing instead of that small part of that thing means object....

• if you're hand-coding SQL statements in your enterprise application, you are spending a significant amount of your development time updating and maintaining your persistence layer.

In persistance,==> no more need of JDBC APIs for result set or data handling. ==>It helps reduce the lines of code, &&&&&& ==>It abstracts our application away from the underlying SQL database and sql dialect. Switching to other SQL database requires few changes in Hibernate configuration file (Write once / run-anywhere).

• JPA is a perfect fit for non performance oriented complex applications.
• JDBC is a best fit where performance is a key performer.

In addition, as you know Java's slogan: "Write once, run everywhere"

Also with JPQL you can execute your JPQL queries on every database (DB2, Oracle, etc..)

When using JDBC we have one mapping layer to map DB joined tables to business object. But JPA one way or other forces to have data objects maintain separately, which adds additional work of maintenance and refactoring of DB tables make life difficult.

Also agree with https://stackoverflow.com/a/57766861/3343801