0

If I have a JQL query in hibernate select c from Customer c where c.col=:col

Is there a way to get hibernate to send the query prepopulated to JDBC preparedstatement?

ie. Can I get hibernate to send this

select c.col, c.name, c.id, etc from Customer c where c.col=value (or NULL)

instead of

select c.col, c.name, c.id, etc from Customer c where c.col=?

Ideally hibernate would also tweak the Strings correctly so SQL injection is prevented as well.

postgres has a setting so null = null and people have been making mistakes thinking null = null without that setting. I actually think null = nulll should work and makes more sense myself. However, in SQL, "WHERE c.col = :col" are not equal if both sides are null. We realize this is a deviation from SQL spec but it's better to not be introducing all these bugs going forward as we hire more junior engineers. ie. better to have a stable product and business then follow some standard in some cases so we can move fast without worry of those bugs.

There are a ton of questions on SO also about null != null in SQL. I am also not very sure why "c.col is null" gets to be correct while "c.col = null" is wrong and returns 0 rows always. I think some people desire to never have null and want c.col = null to 'fail' but of course, it really fails silently which is more annoying since we don't catch the bug. If it failed hard core, at least our developers would then stop and ask, what in the world is going on.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Dean Hiller
  • 19,235
  • 25
  • 129
  • 212
  • Could you explain why do you need that? Usually it's considered as a bad practice that can lead to performance degradation and sql injection. – SternK Jul 15 '20 at 12:21
  • @SternK edited the post. lmk if you need more detail. – Dean Hiller Jul 15 '20 at 12:30
  • "*why `c.col is null` gets to be correct*" - because this is how SQL works. You can't compare `null` with anything. https://www.postgresql.org/docs/current/functions-comparison.html#FUNCTIONS-COMPARISON-PRED-TABLE –  Jul 15 '20 at 12:33
  • If you can use native SQL, you can rewrite your condition to `where c.col is not distinct from ?` which is the "SQL standard" way to deal with comparisons that might include `NULL` values. –  Jul 15 '20 at 12:36
  • @a_horse_with_no_name I actually can compare null with null (null = null) with the postgres setting but only if prepared statements with ? are not used. This has eliminated a ton of confusion on our team. 'You can't compare null with anything'...That really depends on the semantic 'chosen'. In java, they chose you can and it made things very easy. In SQL, they chose you can't and that made things hard for newbs who keep hitting this. (I used to be on the other side sticking to specs theory but I keep seeing the same mistakes leading me to believe the lang design choice was wrong. – Dean Hiller Jul 15 '20 at 12:38
  • @a_horse_with_no_name another point of confusion around null is uniqueness as well(just fyi). In fact, we had 10 John null Smith in our database because the constraint on just first, middle, last name was not defined with coalesce(very typical mistake). I mean who wants John null Smith 10 times in a DB. Anyways, we think defining nulll = null changes it so all these mistakes go away for us. – Dean Hiller Jul 15 '20 at 12:40
  • If you don't want to deal with `NULL` values, declare your columns as `not null` and never worry about it. –  Jul 15 '20 at 12:45
  • "*I actually can compare null with null (null = null) with the postgres setting*" [Quote from the manual](https://www.postgresql.org/docs/current/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-CLIENTS) "this option is not a general fix for bad programming" –  Jul 15 '20 at 12:46
  • A Java "null pointer" is something completely different than a SQL `null` _value_. –  Jul 15 '20 at 12:47
  • @a_horse_with_no_name "declare your columns as not null and never worry " I think that it's a decent idea except as my DB evolves, adding a NotNulll column is not trivial while adding a Null one is. though I must say, I am considering it!!! – Dean Hiller Jul 15 '20 at 12:57
  • Adding a not null column is as "trivial" as adding a nullable column. What exactly do you think is complicated about that? –  Jul 15 '20 at 12:58
  • In hibernate, developers just add private String newColumn and they are done. (ie. that's trivial). Once outside, our new developers pop up once in while going "How in the world do we do this" and we have to take them over to the SQL scripts. We try to stay all in java JPL such that there is less learning and our org is faster because of not having 18 languages to learn and change between forgetting more and more things. – Dean Hiller Jul 15 '20 at 15:03
  • @a_horse_with_no_name hmm, we are going down the path of nullable=false and using "" but just realized we lose ALL the integrity aroud fields that can never be empty string now :(. Is there a way to solve that then? (on top of that json parsing which has "" and null support is now needing fixing so we marshal all null to "" and all our null checks broke!!! meaning we no longer send BadClientRequest when required fields are missing in place of the ""). This is a nightmare :(. Not sure which direction is best now. Any more opinions appreciated!(and I appreciate the previous comments too). – Dean Hiller Jul 15 '20 at 15:12
  • I have no idea what you are talking about, you lost me at "lose all integrity around fields". If you don't want to allow `null` values and empty strings `''` then create a check constraint to prevent. –  Jul 15 '20 at 15:23
  • @a_horse_with_no_name I didn't realize we could create an "no empty string"' allowed constraint. Ok, I think this may work out then!!! thanks! Just have to figure out how to prevent jackson from passing in nulls. ie. this post https://stackoverflow.com/questions/62919775/jackson-reading-in-non-existent-and-null-values-to-and-marshalling-out-to Hopefully, can figure this last little bit out. thanks!!! – Dean Hiller Jul 15 '20 at 16:57

0 Answers0