@cropredy, yeah, you're right, this book is awesome. Yet event in it there are some client classed that use selector like MyObjectsSelector.newInstance.selectById(recIds);, and others that skip the app class, ie. New MyObjectsSelector.selectById(recIds). This is frustrating and ambiguous. And my question is actually about how would I enforce security checks. Without the app class it's quite simple - new MyObjectsSelector(true, true, true).selectByd(recIds);, but I don't know how to enforce security here MyObjectsSelector.newInstance.selectById(recIds).–Jun 18 at 20:28.
JPQLQueryFactory, QueryFactory public class JPAQueryFactory extends Object implements JPQLQueryFactory Factory class for query and DML clause creation. JPAQueryFactory public JPAQueryFactory(javax.persistence.EntityManager entityManager) JPAQueryFactory public JPAQueryFactory(JPQLTemplates templates, javax.persistence.EntityManager entityManager) JPAQueryFactory public JPAQueryFactory(javax.inject.Provider entityManager).
In most cases, simply creating an Oracle SEQUENCE with all defaults is good enough:CREATE SEQUENCE mysequence;This when inserting new records in a table:CREATE OR REPLACE TRIGGER mytriggerBEFORE INSERTON mytableFOR EACH ROW- Optionally restrict this trigger to- fire only when really neededWHEN (new.id is null)BEGINSELECT mysequence.nextvalINTO:new.idFROM DUAL;END mytrigger;But if your table has heavy throughput with millions of insertions per day (e.g. A log table), you better configure the sequence cache correctly.Note: Oracle recommends using the CACHE setting to enhance performance if you are using sequences in an Oracle Real Application Clusters environment.We say: Consider using it also in other situations. Sequence values are generated in an autonomous transaction.
By default, Oracle caches 20 values before generating new ones in a new transaction. When you have a lot of inserts and thus generate a lot of sequence values, that will result in a lot of I/O on the sequence.
Your best technique would be to run benchmarks to find a good value for your sequence cache in high-throughput scenarios. Loved the type safety of today. OpenJPA is the workhorse and is the artist:) #80/20— Alessio Harri (@alessioh)Anyway, today, we’d like to congratulate Timo to his new job, and to QueryDSL’s feature completeness.jOOQ, on the other hand, is far from feature complete. JOOQ is what SQLJ should have been from the beginning.We’re only at the beginning. Java and SQL are the two platforms that are used by most of the developers on this planet.We strongly believe that all of these developers are in dire need for better SQL integration into the Java language. While ORMs and JPA are very well integrated, SQL is not, and that is what we are working on. JOOQ will be feature complete when the Java compiler can natively compile actual SQL code and SQL code fragments into jOOQ, which will serve as its backing AST model for further SQL transformation.Until we reach that goal, we’ll be adding support for more SQL goodness.
Stack Exchange
A small selection of things that we already support, beyond QueryDSL’s “feature completeness”:. Table-valued functions. PIVOT tables. DDL (with jOOQ 3.4). MERGE statement. Derived tables and derived column lists. Row value expressions.
Flashback query. Window functions. Ordered aggregate functions. Common table expressions (with jOOQ 3.4).
Object-oriented PL/SQL. User-defined types. Hierarchical SQL.
Custom SQL transformation. 16 supported RDBMS (even MS Access!). you name itOur roadmap is full of great ideas. There’s plenty of work, so let’s get going! Join us, your partner for.
What if developing an application just took 1-2 days?What if I can create it myself with only 10 clicks?What if I don’t need you developers anymore?Said every manager since the beginning of history. This is what all managers dream of. Click click click, next next next, and you’re done! Time-to-market: Zero.Of course, Data transformation and navigationLet’s have a look at some tech stuff.As a personal passion, I have always loved the idea of non-procedural approaches to manipulating data (e.g. SQL or XSLT). One of the best pieces of software I’ve ever seen to manipulate data was used by, a previous employer of mine and a customer of who has created a tool called JTT – Java Table Tool, a dinosaur written around 15 years ago.
It was essentially a live RDBMS schema and data navigation tool written as a. With only little metadata, this application was then capable of providing overviews of:. All the tables that you as a user had access to.
When clicking on a table, you got an editable list of all the records in that table with standard filtering and grouping options. When double-clicking on a record, you got an editable popup with details. When clicking on a record, you got a “children” view with tabs for all foreign keys that link to this table. Obviuosly, the tabs were again filled with records, which could be navigated through the same way as the “parent” records.
Foreign key values were not displayed using technical IDs, but using relevant data from the linked record and much much more. All business logic and complex update rules were implemented using triggers and grants and just a little meta data to decide what information is primary and what information is secondary (or hidden). Most of the views obviously also were exportable to CSV, XLS, or PDF.Ergon used this wonderful JTT for internal purposes only, e.g.
For accounting, invoice management, as a CRM, as an HR tool. It pretty much ran the company and it did its job very very well. It was one of the technically most awesome products that I’ve ever seen. So lean, so simple, and so powerful (albeit, the UI Oh well, Swing).I pressed the product manager and the sales managers to consider revitalising this gem and to make a webapp from it that can be sold to other software companies as a product. At the time, something like might have been a good choice to allow for a hybrid desktop and web UI.Unfortunately, they never wanted to make a proper product from this tool.
So, I have always thought that at some point, I’ll create my own JTT, I’ll sell it and I’ll get rich. A browser-based database schema and data navigation tool that allows you to set up a basic data management software product in virtually 2-3 days, even when running on large schemas.
Too late, it already exists!So these were our plans. (and later on, also this, the same author).“Unfortunately,” for me and for Data Geekery, I have come to discover, which does exactly that. Ironically, I have already, when I had spotted their pretty cool reactive REST API (where here, reactive means that with a simple rule engine, you could model all sorts of Excel-spreadsheet-like data updates).But this Live Browser indeed tops what I had in mind from my JTT experience. It is actually built on top of the aforementioned reactive REST API, so it inherits all the nice features, such as the “role-based, row/column level read and update permissions”, the reactive programming features, etc. Here’s an example view from their product announcement website:Another example from the:As you can see, pretty much all of the JTT features that I’ve mentioned before are available out of the box:. Table selection.
Filtering. Detail views. Foreign key navigation.
Child navigation. Data manipulation(Ergon, if you’re reading this: You see? I told you:-) )Consider having this as a general-purpose database inspection tool in your company. As a developer, you can quickly navigate the schema (and the data!) in a way that you will never find in. Obviuosly, the tools don’t compete, as SQL Developer is a database development tool, whereas Live Browser is more of an actual well, a live data browser.This browser could also be used as a prototyping engine to assess whether your database schema really models the business case of your customer – a quick display to verify the requirements.
Well, as I said, it is a general purpose data browser, that can be used for virtually any simple use-case.Now, I have again signed up for a free trial at Espresso Logic, to try Live Browser myself. I could log into. Do note that there is no single-sign-on in place between the Logic Designer and the Live Browser, so I needed to reuse my credentials to log in also to the browser.Once I’ve logged in, I could really play around with the data in an easy and straightforward way.
All the server-side rules that calculate totals are working as well. I tried changing the price for a product inside a PurchaseOrder (i.e. Inside a LineItem), and it updated the PurchaseOrder’s “Amount Total” value automatically.I wish I had created this product three years ago when SaaS started getting big. Now, I guess, it’s too late Congrats,! More informationFor more info, read. (Sorry for that click-bait heading. Couldn’t resist;-) )We’re on a mission.
To teach you SQL. But mostly, we want to teach you how to appreciate SQL.
You’ll love it!Getting SQL right or wrong shouldn’t be about that You’re-Doing-It-Wrong™ attitude that can be encountered often when evangelists promote their object of evangelism. Getting SQL right should be about the fun you’ll have once you do get it right. The things you start appreciating when you notice that you can easily replace 2000 lines of slow, hard-to-maintain, and ugly imperative (or object-oriented) code with 300 lines of lean functional code (e.g. Using ), or even better, with 50 lines of SQL.We’re glad to see that our blogging friends have started appreciating SQL, and most specifically, window functions after reading our posts. For instance, take. that lead to him starting his (among other reasons).So, after our previous, very popular posts:. we’ll bring you: Yet Another 10 Common Mistakes Java Developer Make When Writing SQLAnd of course, this doesn’t apply to Java developers alone, but it’s written from the perspective of a Java (and SQL) developer.
So here we go (again): 1. Not Using Window FunctionsAfter all that we’ve been preaching, this must be our number 1 mistake in this series.
They’re so incredibly useful, they should be the number one reason for anyone to switch to a better database, e.g. Mind bending talk by about at tonight's.
My new resolution: Install PostgreSQL and study SQL standard at once.— Peter Kofler (@codecopkofler)If free and/or Open Source is important to you, you have absolutely no better choice than using (and you’ll even get to use the free, if you’re a Java developer).And if you’re lucky enough to work in an environment with Oracle or SQL Server (or DB2, Sybase) licenses, you get even more out of your new favourite tool.We won’t repeat all the window function goodness in this section, we’ve blogged about them often enough:.The Cure:Start playing with window functions. You’ll never go back, guaranteed. Not declaring NOT NULL constraintsThis one was already part of a previous list where we claimed that you should add as much metadata as possible to your schema, because your database will be able to leverage that metadata for optimisations. For instance, if your database knows that a foreign key value in BOOK.AUTHORID must also be contained exactly once in AUTHOR.ID, then a whole set of optimisations can be achieved in complex queries.Now let’s have another look at NOT NULL constraints. If you’re using Oracle, NULL values will not be part of your index. This doesn’t matter if you’re expressing an IN constraint, for instance:SELECT.
FROM tableWHERE value IN (SELECT nullablecolumn FROM.)But what happens with a NOT IN constraint?SELECT. FROM tableWHERE value NOT IN (SELECT nullablecolumn FROM.)Due to, there is a slight risk of the second query unexpectedly not returning any results at all, namely if there is at least one NULL value as a result from the subquery.
This is true for all databases that get SQL right.But because the index on nullablecolumn doesn’t contain any NULL values, Oracle has to look up the complete content in the table, resulting in a FULL TABLE SCAN. Now that is unexpected! Loved the type safety of today. OpenJPA is the workhorse and is the artist:) #80/20— Alessio Harri (@alessioh)The Takeaway:While the above will certainly help you work around some real world issues that you may have with your favourite ORM, you could also take it one step further and think about it this way.
After all these years of pain and suffering from the, the JPA 2.1 expert group is now trying to tweak their way out of this annotation madness by adding more declarative, annotation-based fetch graph hints to JPQL queries, that no one can debug, let alone maintain.The alternative is simple and straight-forward SQL. And with Java 8, we’ll add functional transformation through the Streams API.But obviuosly, your views and experiences on that subject may differ from ours, so let’s head on to a more objective discussion about 6.
Not using Common Table ExpressionsWhile common table expressions obviously offer readability improvements, they may also offer performance improvements. History of NoSQL according to— Edd Wilder-James (@edd)The Disclaimer:This article has been quite strongly against MySQL.
We don’t mean to talk badly about a database that perfectly fulfils its purpose, as this isn’t a black and white world. Heck, you can get happy with SQLite in some situations.
MySQL, being the cheap and easy to use, easy to install commodity database. We just wanted to make you aware of the fact, that you’re expressly choosing the cheap, not-so-good database, rather than the cheap, awesome one.
Forgetting about UNDO / REDO logsWe have claimed that MERGE statements or bulk / batch updates are good. That’s correct, but nonetheless, you should be wary when updating huge data sets in transactional contexts. If your transaction “takes too long”, i.e. If you’re updating 10 million records at a time, you will run into two problems:. You increase the risk of race conditions, if another process is also writing to the same table.
This may cause a rollback on their or on your transaction, possibly making you roll out the huge update again. You cause a lot of concurrency on your system, because every other transaction / session, that wants to see the data that you’re about to update, will have to temporarily roll back all of your updates first, before they reach the state on disk that was there before your huge update. That’s the price of ACID.One way to work around this issue is to allow for other sessions to read uncommitted data.Another way to work around this issue is to frequently commit your own work, e.g. After 1000 inserts / updates.In any case, due to the, you will have to make a compromise.
Frequent commits will produce the risk of an inconsistent database in the event of the multi-million update going wrong after 5 million (committed) records. A rollback would then mean to revert all database changes towards a backup.The Cure:There is no definitive cure to this issue. But beware that you are very very rarely in a situation where it is OK to simply update 10 million records of a live and online table outside of an actual scheduled maintenance window. The simplest acceptable workaround is indeed to commit your work after N inserts / updates.The Takeaway:By this time, NoSQL aficionados will claim (again due to excessive marketing by aforementioned companies) that NoSQL has solved this by dropping schemas and typesafety.
“Don’t update, just add another property!” – they said.!First off, I can add columns to my database without any issue at all. An ALTER TABLE ADD statement is executed instantly on live databases. Filling the column with data doesn’t bother anyone either, because no one reads the column yet (remember, don’t SELECT.!). So adding columns in RDBMS is as cheap as adding JSON properties to a MongoDB document.But what about altering columns?
Removing them? Merging them?It is simply not true that denormalisation takes you anywhere far. Denormalisation is always a short-term win for the developer.
Hardly a long-term win for the operations teams. Having redundant data in your database for the sake of speeding up an ALTER TABLE statement is like sweeping dirt under the carpet.Don’t believe the marketers. And while you’re at it, perform some and forget that we’re vendors ourselves;-) Here’s again the “correct” message:10. Not using the BOOLEAN type correctlyThis is not really a mistake per se. It’s just again something that hardly anyone knows. When the standard introduced the new BOOLEAN data type, they really did it right.
Because before, we already had something like booleans in SQL. We’ve had, which are essentially predicates for use in WHERE, ON, and HAVING clauses, as well as in CASE expressions.SQL:1999, however, simply defined the new as a regular, and redefined the as such:::=Done! Now, for most of us Java / Scala / etc. Developers, this doesn’t seem like such an innovation. Heck it’s a boolean.
Obviuosly it can be interchangeably used as predicate and as variable.But in the mind-set of the keyword-heavy SQL folks who have taken inspiration from COBOL when designing the language, this was quite a step forward.Now, what does this mean? This means that you can use any predicate also as a column! For instance:SELECT a, b, cFROM (SELECT EXISTS (SELECT.) a,MYCOL IN (1, 2, 3) b,3 BETWEEN 4 AND 5 cFROM MYTABLE) tWHERE a AND b AND NOT(c)This is a bit of a dummy query, agreed, but are you aware of how powerful this is?Luckily, again, PostgreSQL fully supports this (unlike Oracle, which still doesn’t have any BOOLEAN data type in SQL).The Cure:Every now and then, using BOOLEAN types feels very right, so do it! You can transform boolean value expressions into predicates and predicates into boolean value expressions. They’re the same. This makes SQL all so powerful. ConclusionSQL has evolved steadily over the past years through great standards like, and now.
It is the only surviving mainstream declarative language, now that XQuery can be considered pretty dead for the mainstream. It can be easily mixed with procedural languages, as PL/SQL and T-SQL (and other procedural dialects) have shown. It can be easily mixed with object-oriented or functional languages, as has shown.At, we believe that SQL is the best way to query data. You don’t agree with any of the above? That’s fine, you don’t have to.
Sometimes, even we agree with Winston Churchill who is known to have said:SQL is the worst form of database querying, except for all the other forms.But as Yakov Fain has recently put it:So, let’s better get back to work and learn this beast! Thanks for reading.