Rectangle 27 77

The use of named parameters is not defined for native queries. From the JPA specification (section 3.6.3 Named Parameters):

Named parameters follow the rules for identifiers defined in Section 4.4.1. The use of named parameters applies to the Java Persistence query language, and is not defined for native queries. Only positional parameter binding may be portably used for native queries.

So try the following instead:

String queryString = "select * from Cell c where ST_DWithin(c.shape, SetSRID(ST_GeomFromEWKT('POINT(?1 ?2)'),4326), 0.1)";
Query query = Cell.em().createNativeQuery(queryString, Cell.class);
query.setParameter(1, longitude);
query.setParameter(2, latitude);

Note that in JPA >= 2.0 you can use named parameters in native queries.

+1 for citing the spec and reminding me that this is not portable. I have actually used named parameters in native queries in a hibernate project,which is working fine. In another older project, based on toplink essentials, all native queries use positional parameters, so I must have known this before and forgotten :)

Hi, thanks for your answer. However, it still seems to crash on setParameter(1, longitude) org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1 any idea?

@samokk: I wonder if the single quotes around POINT are not the problem here.

for JPA-position parameters like "?1 " you have to call query.setParameter("1", longitude); etc/

This answer should be accepted as the best one. The problem with setParameter(position, value) is sometimes we don't have enough parameter as theory. Using (?1) and (?2) help the people like to build the query on the fly.

java - JPA/Hibernate Native Queries do not recognize Parameters - Stac...

java hibernate orm jpa postgis
Rectangle 27 8

I'll leave this question and answer out here in case anyone has the same issue in the future.

The issue is in the way the JTDS drivers send the parameter strings to MSSQL. Apparently Java will attempt to send the parameters Unicode by default, and MSSQL will translate it to Ascii. Why that takes 9 seconds, I do not know.

Lot's of references to this out there, but nothing that helped my till I was able to isolate that it was an issue with the driver to MSSQL connection.

This is the string using the Microsoft driver.

jdbc:sqlserver://localhost\SQLEXPRESS;
  DatabaseName=TESTDB;
  sendStringParametersAsUnicode=false

You just need to get the sendStringParametersAsUnicode=false passed to your driver URL setup and you are good.

It takes 9 seconds because it doesn't translate your parameter to ascii (as it could lose data that way). It changes each column value to unicode before comparing against your parameter. This means it can't take full advantage of any index on the field "stringId" leading to much slower performance (I believe I also had an issue like this in the past).

thanks for this information, i thought it was hibernate causing the issue

java - JPA (Hibernate) Native Query for Prepared Statement SLOW - Stac...

java sql hibernate jpa prepared-statement
Rectangle 27 3

Check the query plans that SQL server is producing. Prepared statements can be especially problematic.

SELECT field1, field2, field3 FROM entity left join entity2 on... left join entity3 on
WHERE stringId like 'ABC123%';

and you have an index on "stringId" SQL server knows it can use it.

SELECT field1, field2, field3 FROM entity left join entity2 on... left join entity3 on
WHERE stringId like ?;

SQL server doesn't know it can use the index when it creates the prepared statement (as you could fill in the parameter with '%ABC123' instead of 'ABC123%') and thus may choose a completely different query plan.

Thanks Gareth. What you say is 100% true. In this case the primary issue is with the driver-MSSQL connection. I think MSSQL may cache the query plans, and there are actually only a few possible iterations of the SP, with different sub queries for one-to-many relationships. After fixing that issue, 100 queries return in 47 seconds, a huge improvement over 9 seconds each!

java - JPA (Hibernate) Native Query for Prepared Statement SLOW - Stac...

java sql hibernate jpa prepared-statement
Rectangle 27 18

The following solution should work:

sql = "SELECT SUM(TOTAL_BYTES_DELIVERED) / SUM(TOTAL_TIME_TAKEN_IN_DELIVERY) 
         FROM MV_MFT_TRANSFER 
        WHERE TRANSFER_INITIATION_TIME > ?
          AND TRANSFER_INITIATION_TIME < ?"

Query query = em.createNativeQuery(sql);
query.setParameter(1, startDate, TemporalType.DATE);
query.setParameter(2, endDate, TemporalType.DATE);
query.getResultList();

It seems that if you use positional parameters it will work. You cannot combine named parameters and native query. Here are some links:

java persistence native sql not accepting parameters - Stack Overflow

java sql oracle persistence
Rectangle 27 2

Pascal's answer is correct, but... How is your solution SQL injection prone? If you're using String.format and parmater type %f in your example then anything else than number throws java.util.IllegalFormatConversionException. There is no possibililty pass value like "xxx' OR 1=1 --".

Be careful, using %s in String.format is SQL injection ready.

java - JPA/Hibernate Native Queries do not recognize Parameters - Stac...

java hibernate orm jpa postgis
Rectangle 27 24

JPA is a standard for accessing relational databases through an object oriented API. Hibernate is an implementation of this API. When you want to use JPA you need some vendor to implement it, Hibernate is a good choice but there are others like EclipseLink.

Hibernate exists longer than JPA. The native, older API (which was a model for JPA) still exists, and sometimes it offers more possibilities than are exposed through JPA (e.g. orphan removal). If you need those you need to use the native API. Using JPA has other merits, most important (in my opinion) more developers that know it. And you still can use some Hibernate specifics through configuration.

In general IMO it pays to use the standard API, here JPA, and do an experienced decision on the implementation to use.

Most tutorials that use Hibernate natively are quite old - as is Hibernate 3, a pre-JPA release. While there are good reasons to use it, they (IMO) typically don't apply to the general audience. So if you are just beginning to learn in this field I would suggest to start with JPA.

As for recommendations on offsite resources: For good reasons they are not on topic here. But the current official Hibernate documentation would be a good start, as would be to look for toturial for at least Hibernate 4.

java - Hibernate Native vs Hibernate JPA - Stack Overflow

java hibernate jpa persistence
Rectangle 27 4

I had a similar problem and found that parameters can be set with question marks in native queries. Try this:

String queryString = "select * from Cell c where ST_DWithin(c.shape, SetSRID(ST_GeomFromEWKT('POINT(? ?)'),4326), 0.1)";

Query query = Cell.em().createNativeQuery(queryString, Cell.class);
query.setParameter(1, longitude);
query.setParameter(2, latitude);

java - JPA/Hibernate Native Queries do not recognize Parameters - Stac...

java hibernate orm jpa postgis
Rectangle 27 2

This question may be old, but answering just in case you still run into this. You could use {h-schema} for native SQL queries (verified for Hibernate v4). This is defined by hibernate.default_schema configuration property.

"SELECT * FROM {h-schema}LOCATION"

java - How to configure default database schema for native queries in ...

java oracle hibernate jpa native-sql
Rectangle 27 2

This question may be old, but answering just in case you still run into this. You could use {h-schema} for native SQL queries (verified for Hibernate v4). This is defined by hibernate.default_schema configuration property.

"SELECT * FROM {h-schema}LOCATION"

java - How to configure default database schema for native queries in ...

java oracle hibernate jpa native-sql
Rectangle 27 1

And another answer for people potentially using Oracle with a similar Unicode problem...

Check to make sure someone hasn't set the property oracle.jdbc.defaultNChar=true

This is sometimes done to resolve unicode problems but it means all columns are treated as nvarchars. If you have an index on a varchar column, it won't be used because oracle has to use a function to convert the character encoding.

java - JPA (Hibernate) Native Query for Prepared Statement SLOW - Stac...

java sql hibernate jpa prepared-statement
Rectangle 27 2

I faced similar issue. I was using native query in the repository with ?1. It resolved it by surrounding the parameter around brackets like the following.

SELECT * FROM XYZ WHERE ABC = (?1)

java - JPA/Hibernate Native Queries do not recognize Parameters - Stac...

java hibernate orm jpa postgis
Rectangle 27 17

'POINT(:lon :lat)'

with

'POINT(' || :lon || ' ' || :lat || ')'

This way the parameters are outside of constant strings and should be recognized by the query parser.

+1 Interesting trick which actually confirms the problem comes from the quotes, not from named parameters (that one should avoid with JPA though).

Works in Spring Data with NamedNativeQuery annotation, so doesn't require creation of Repository implementation.

java - JPA/Hibernate Native Queries do not recognize Parameters - Stac...

java hibernate orm jpa postgis
Rectangle 27 3

REGEXP has operator syntax in MySQL (SELECT 'a' REGEXP 'A') so it cannot be used with CriteriaBuilder.function() API. I'm afraid the best is to run native SQL query.

REGEXP
SQLFunctionTemplate
CriteriaBuilder.function()

java - Use Regular Expressions in JPA CriteriaBuilder - Stack Overflow

java mysql regex hibernate jpa
Rectangle 27 1

You declared field Double Sales_Rs as transient, therefore it is not handled by Hibernate.

a) Use a Hibernate specific mapping to calculate Sales_RS by a formula which calls your database function:

@Formula("NetValue(billType, Netvalue1)")
private Double Sales_Rs;

b) use the JPA @Postload annotation to calculate Sales_RS after the object has been loaded:

@Transient
private Double Sales_Rs;

@PostLoad
private void onLoad() {
     Sales_Rs = ... //  same logic as in your db function
}

In both cases you can drop the native query and use simple hql:

@NamedQueries({
    @NamedQuery(
       name  = "getTestData",
       query = "from Test"
)})

java - Trying to use SQL Function in hibernate - Stack Overflow

java hibernate sql-function nativequery
Rectangle 27 3

Using both an ORM and JDBC in the same application is a reasonable thing to do. Hibernate can run native SQL queries so you might consider that as an alternative depending on what you have in mind.

JDBC doesn't use a session factory or entity manager factory comparable to Hibernate. Caching for the JDBC results would have to be handled separately from Hibernate.

The Hibernate/JPA annotations will be irrelevant to JDBC queries, the RowMapper passed into the query controls how pojos get populated. You could re-use your Hibernate entities to be populated by the rowmappers but in practice the reason you use JDBC is to get more focused results with flatter objects and you end up with a separate set of DTOs.

I'm not clear on what you think is going to hurt performance here. JDBC should be an improvement over Hibernate performance-wise, though at the cost of having business-logic spread out in multiple places. The tricky part will probably be setting up the transaction manager so that JDBC and Hibernate use the same transaction, see this question.

Hey Nathan, It is really understandable but I got a query regarding what for mapping used in hibernate's POJO? If I fetch one object from hibernate and other one with jdbc template can I set that object in each other and can use it? Here hibernate object is persistent and jdbctemplate is not so will it make changes to data if I set jdbctemplate object into hibernate object while doing some CRUD operations?

@commit: yes, you could possibly change data inadvertently doing that kind of thing. one way around would be another layer of dtos, or copying the other way around from the hibernate entity to the jdbc-created one. Setting up a readonly transaction would probably help too.

java - Spring Hibernate and JDBC Template in a Single Project - Stack ...

java spring hibernate jdbctemplate
Rectangle 27 77

The use of named parameters is not defined for native queries. From the JPA specification (section 3.6.3 Named Parameters):

Named parameters follow the rules for identifiers defined in Section 4.4.1. The use of named parameters applies to the Java Persistence query language, and is not defined for native queries. Only positional parameter binding may be portably used for native queries.

So try the following instead:

String queryString = "select * from Cell c where ST_DWithin(c.shape, SetSRID(ST_GeomFromEWKT('POINT(?1 ?2)'),4326), 0.1)";
Query query = Cell.em().createNativeQuery(queryString, Cell.class);
query.setParameter(1, longitude);
query.setParameter(2, latitude);

Note that in JPA >= 2.0 you can use named parameters in native queries.

+1 for citing the spec and reminding me that this is not portable. I have actually used named parameters in native queries in a hibernate project,which is working fine. In another older project, based on toplink essentials, all native queries use positional parameters, so I must have known this before and forgotten :)

Hi, thanks for your answer. However, it still seems to crash on setParameter(1, longitude) org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1 any idea?

@samokk: I wonder if the single quotes around POINT are not the problem here.

for JPA-position parameters like "?1 " you have to call query.setParameter("1", longitude); etc/

This answer should be accepted as the best one. The problem with setParameter(position, value) is sometimes we don't have enough parameter as theory. Using (?1) and (?2) help the people like to build the query on the fly.

java - JPA/Hibernate Native Queries do not recognize Parameters - Stac...

java hibernate orm jpa postgis
Rectangle 27 111

Ibatis is about SQL. If you know SQL the learning curve for ibatis is trivial. Ibatis does some things on top of SQL such as:

  • discriminated types; and

that you'll still need to learn but the biggest hurdle is SQL.

JPA (which includes Hibernate) on the other hand tries to distance itself from SQL and present things in an object rather than a relational way. As Joel points out however, abstractions are leaky and JPA is no exception. To do JPA you'll still need to know about relational models, SQL, performance tuning of queries and so forth.

Whereas Ibatis will simply having you apply the SQL you know or are learning, JPA will require you to know something else: how to configure it (either XML or annotations). By this I mean figuring out that foreign key relationships are a relationship (one-to-one, one-to-many or many-to-many) of some kind, the type mapping, etc.

If you know SQL I would say the barrier to learning JPA is actually higher. If you don't, it's more of a mixed result with JPA allowing you to effectively defer learning SQL for a time (but it doesn't put it off indefinitely).

With JPA once you setup your entities and their relationships then other developers can simply use them and don't need to learn everything about configuring JPA. This could be an advantage but a developer will still need to know about entity managers, transaction management, managed vs unmanaged objects and so on.

It's worth noting that JPA also has its own query language (JPA-SQL), which you will need to learn whether or not you know SQL. You will find situations where JPA-SQL just can't do things that SQL can.

This is a hard one to judge. Personally I think I'm more productive in ibatis but I'm also really comfortable with SQL. Some will argue they're way more productive with Hibernate but this is possibly due--at least in part--to unfamiliarity with SQL.

Also the productivity with JPA is deceptive because you will occasionally come across a problem with your data model or queries that takes you a half a day to a day to solve as you turn up logging and watch what SQL your JPA provider is producing and then working out the combination of settings and calls to get it to produce something that's both correct and performant.

You just don't have this kind of problem with Ibatis because you've written the SQL yourself. You test it by running the SQL inside PL/SQL Developer, SQL Server Management Studio, Navicat for MySQL or whatever. After the query is right, all you're doing is mapping inputs and outputs.

Also I found JPA-QL to be more awkward than pure SQL. You need separate tools to just run a JPA-QL query to see the results and it's something more you have to learn. I actually found this whole part of JPA rather awkward and unwieldy although some people love it.

The danger with Ibatis here is proliferation meaning your dev team may just keep adding value objects and queries as they need them rather than looking for reuse whereas JPA has one entitty per table and once you have that entity, that's it. Named queries tend to go on that entity so are hard to miss. Ad-hoc queries can still be repeated but I think it's less of a potential problem.

That comes at the cost of rigidity however. Often in an application you will need bits and pieces of data from different tables. With SQL it's easy because you can write a single query (or a small number of queries) to get all that data in one hit and put it in a custom value object just for that purpose.

With JPA you are moving up that logic into your business layer. Entities are basically all or nothing. Now that's not strictly true. Various JPA providers will allow you to partially load entities and so forth but even there you're talking about the same discrete entitites. If you need data from 4 tables you either need 4 entities or you need to combine the data you want into some kind of custom value object in the business or presentation layer.

One other thing I like about ibatis is that all your SQL is external (in XML files). Some will cite this is as a disadvantage but not me. You can then find uses of a table and/or column relatively easy by searching your XML files. With SQL embedded in code (or where there is no SQL at all) it can be a lot harder to find. You can also cut and paste SQL into a database tool and run it. I can't overstate enough how many times this has been useful to me over the years.

Here I think ibatis wins hands down. It's straight SQL and low cost. By its nature JPA simply won't be able to manage the same level of latency or throughput. Now what JPA has going for it is that latency and throughput are only rarely problems. High performance systems however do exist and will tend to disfavour more heavyweight solutions like JPA.

Plus with ibatis you can write a query that returns exactly the data you want with the exact columns that you need. Fundamentally there's no way JPA can beat (or even match) that when it's returning discrete entities.

I think this one is a win for Ibatis too. Like I mentioned above, with JPA you will sometimes spend half a day getting a query or entity produce the SQL you want or diagnosing a problem where a transaction fails because the entity manager tried to persist an unmanaged object (which could be part of a batch job where you've committed a lot of work so it might be nontrivial to find).

Both of them will fail if you try to use a table or column that doesn't exist, which is good.

Now you didn't mention portability as one of your requirements (meaning moving between database vendors). It's worth noting that here JPA has the advantage. The annotations are less portable than, say, Hibernate XML (eg standard JPA annotations don't have an equivalent for Hibernate's "native" ID type) but both of them are more portable than ibatis / SQL.

Also I've seen JPA / Hibernate used as a form of portable DDL, meaning you run a small Java program that creates the database schema from JPA configuration. With ibatis you'll need a script for each supported database.

The downside of portability is that JPA is, in some ways, lowest common denominator, meaning the supported behaviour is largely the common supported behaviour across a wide range of database vendors. If you want to use Oracle Analytics in ibatis, no problem. In JPA? Well, that's a problem.

+1 from me - very nice.

I think this really over-exaggerates the uniqueness of iBATIS's simplicity and downplays the value of having business logic in the Java layer. Native queries can be loaded from a text file in JPA and/or manipulated as strings -- this essentially is iBATIS. In that sense I consider JPA to be almost a superset. You can use native queries and map those to specifically designed entities, or migrate to JPQL and try your hand at consolidating business logic.

& maybe Oracle Analytics integrates better with iBATIS (I don't know?), but JPA is supported out-of-box by most Java containers/servers now so I'd say its more "standard", not the other way around. Thus making it lightweight in the sense that at least its a widely available dependency.

@jm0 I'd consider that a drawback of JPA, since you are easily running into version conflicts, when the app server provides these libraries.

Hibernate, iBatis, Java EE or other Java ORM tool - Stack Overflow

java hibernate orm java-ee ibatis
Rectangle 27 1

Hibernate Formulas are elegant solution for this, however they may be difficult to get right if they are complex (Hibernate has limited ability to parse native SQL). Also, even if you get them to work properly, the performance may suffer because formulas may involve subqueries which are always embedded into the final SQL.

The approach I use often is to create a database view which does the necessary joins for me and then I map my entity to the view. The database is able to optimize the view much better then nested subqueries, and Java code is a bit cleaner because there is no native SQL in it.

So, you could create a db view Membership_Profile and a reusable entity mapped to it, say MembershipProfile.

@Entity
@Table(name = Membership_Profile)
class MembershipProfile {
   Long moduleMembershipId;
   String displayName;
   // Other properties if needed
}

Then, you can associate this entity with specific profiles, for example:

@Entity
class PokerProfile {
   @OneToOne(fetch=FetchType.LAZY)
   @JoinColumn(name = "membershipId")
   MembershipProfile membershipProfile;
   ...
}

The benefit of this approach is that you load the displayName (and any other MembershipProfile properties) lazily when you need them (thus avoiding the cost of executing the view or subqueries).

An alternative to creating database views are Hibernate Subselects which are a kind of Hibernate (read-only) views, but I expect real db views to perform much better.

This would have the benefit of being very low level - Hibernate wouldn't know the difference, and I could leverage full optimization on the SQL side for whatever flavor I'm running. In fact, I don't even need to put it on the membership profile - it could stay on the user profile like it is now, have a poker profile without the display name, and have a view that wraps the two.

@corsiKa True, you can wrap in the view anything that best suits your use cases and your performance requirements. The disadvantages of defining the view on PokerProfile is that it is always going to execute when you load PokerProfile instances and that you would have to define a new view for each other *Profile use cases (for example BlackjackProfile, etc).

java - Is there a way to use SecondaryTable to jump multiple tables? -...

java hibernate
Rectangle 27 1

I figure it out. Native SQL DELETE queries were causing hibernate to frequently dump the entire cache (the removeAll calls above). I did as suggested below, and everything is working now.

spring - hibernate second level cache keeps emptying itself - Stack Ov...

spring hibernate ehcache second-level-cache
Rectangle 27 5

We are currently working on a project which uses both Hibernate and ibatis. Why use hibernate ? It supports our domain model, relationships and inheritance. We have a pretty complex domain model and hiberante supports it very well. No need to worry about writing inserts, updates etc. Ibatis is used only for view. There are queries and we have view objects(similar to domain models but not domain models) which are mapped with queries. Ibatis returns the data in the view obejct you want without worrying about reading from result set , which you have to manage in Spring JDBC. Why do we that instead of using HQl or Spring JDBC ? The domain is so complex and when rendering view , we do calculations , group by and tons of native SQL functions. we do all that in query, use dynamic queries , manage conditions in ibatis and get a clean light weight object. Makes much more sense if you have to traverse down multiple layers to fetch data in hibernate So depending on your situation , you may want to use only one, both or may be none. But definitely, hibernate is not something you cannot live without.

Hibernate, iBatis, Java EE or other Java ORM tool - Stack Overflow

java hibernate orm java-ee ibatis