Anyone interested in a simple no dependency ORM for Java?

A while ago I wrote a simple ORM for a game I was working on where I didn’t like JDBC for all the SQLExceptions and the persistence/hibernate solutions seemed like overkill with all the extra libraries required. I wanted a simple drop in useful way to read/write data out of a database so I wrote one.

I’ve been using it for years and recently I decided to polish it and merge my new changes over the years into the open sourced version and bring it up to a 1.0 release.

The old version is here http://persism.sourceforge.net/main.php - though I changed the API over the years.

Is there any interest in this here? Over the years I’ve added support for MSSQL (with jtds or mssql), MariaDB, MySQL, Postgresql, Firebird, Oracle, H2, Derby and SQLite. And I have almost 100% code coverage.

My plan is to move it over to github and add it to maven.

Hello

I advise you to use your Subversion repository correctly because it contains a Microsoft Visual Studio project with two classes written in C# which doesn’t match with the content of the most recent zip archive.

What are the pros and the cons of your API compared to JPA, JDO, Hibernate, Spring, Struts, iBATIS, MyBatis, JOOQ and Apache Cayenne?

Yeah Originally I wanted to also make it in C# as well but didn’t have a use for that. As I said I plan to move it clean to github.

JPA, JDO, Hibernate - Require a lot of extra dependencies. Spring, Struts do people make games with Spring and Struts? iBATIS is abandoned. MyBatis is a 5 meg download with about 2 megs of dependencies. JOOQ is ok for database first style development and you don’t mind lots of code generation. Cayenne is fine too if you like 3 megs of dependencies. In any case I’m not trying to compete with these solutions.

Persism is 50k with zero dependencies. It doesn’t require xml or other special configuration outside the normal Connection object. Annotations are optional - you only need one to specify an unusual table or column name or to indicate primary, autoinc or default columns (which I Persism detects anyway in most cases).

It handles plural/singular class/table names without doing anything special. Example: Class Category will map fine with CATEGORIES in the DB. It will match column to property names ignoring spaces, underscores or case.

Basically I designed it to be small, simple, unobtrusive and functional. I think it could be a great fit for game development.

2 Likes

I’m currently grappling with learning Hibernate/JPA 2.2. From what I’ve just read about the ‘history’ of Object-Relationship mapping, at first glance the mapping looks like a natural fit (e.g., a table maps to a class, a row maps to an instance) but that the mapping gets super complicated and difficult to implement rather quickly as one strives to handle more complicated use cases (e.g., table links/queries/indices?).

That seems to me to suggest that if the use cases are kept to very limited parameters (I don’t know how limited), that some of the mapping should be possible to handle without too much complication. Perhaps the persistence needs for a (simple) game can be kept within whatever those bounds are.

I assume that for professional/commercial game making, one would probably want to go with an existing framework. And, professional coders would likely have the needed expertise. So for them, this would not be particularly appealing.

But at the same time, we have a reasonable number of beginners and hobbyists here, writing their first games and having persistence needs. (Or at least, I’d like to see jvm-gaming be welcoming and useful at that level.) For such, a small jar with a simple API could be a very welcome asset. Making the transition to a framework is a big ask for someone who, for example, has only just had a taste of Java from a High School or College “Introduction to Programming” course (I’m thinking also of the infusion of young enthusiasts we got from Minecrafters), but has the itch to try making a game.

I’m currently grappling with learning Hibernate/JPA 2.2.

I hear you. I followed the same path. And it is grappling. :slight_smile:

but that the mapping gets super complicated and difficult to implement rather quickly as one strives to handle more complicated use cases

The problem is that these frameworks tend to favor modeling rather focusing on how databases actually work. This leads to the problem of “it worked great in development but now the app is slow!”. When you have a few hundred rows its fine but once you get into the thousands things bog down because often these ORMs are reading a lot more data than you actually need.

Image you model a Customer which contains Invoices which contain LineItems which contain Products. Now you want to display a list to a user of customer name last invoice, totals etc… How does this model work? It queries into each Customer and then queries into each Invoice into each LineItem into each Product. Ouch!

How should you really do this? Model a InvoiceSearchResult and write a query in SQL to get this data with joins etc… which would be near instantaneous. Unfortunately many developers only find this out later on. Anyway rant over. :wink:

I hope you have a look once I get it finished! I’m on code coverage and documentation now.

2 Likes

Seriously?

Most of what I know about databases comes from being self-taught from working with MS Access (am still maintaining a long-term,part-time relationship/contract with one client). Learning concepts like “normalization” and making efficient use of indices and joins is kind of basic to the craft, isn’t it?

If this is typical of coders launching into database frameworks, I could see the scenario you give being an actual, real-world phenomena. This would explain some of the puzzling stuff I saw in the Udemy course I just completed: using keys that contain business info, creating a junction table which on the surface seems to be handling a one-to-many relationship. It does seem plausible that there would be coders opting to do the inefficient, multiple search scenario you describe because “joins are hard.” It’s just surprising. And since I am newly getting into JPA, I have to be cautious about my assumptions. It’s easiest to assume others don’t know what they are doing when one doesn’t know what they are doing.

My assumption for the persistence needs of a game is that there really isn’t that much data to handle relative to an eCommerce business with a decent annual sales. On that basis, the cost of being inefficient might not be disqualifying, just annoying (e.g., sitting waiting for a level to load). It will be interesting to see what you come up with.

You’re right about dependencies even though it depends on what we mean by “a lot” (MyBatis has at least 7 dependencies, Hibernate JPA has about 10 dependencies). You can use JOOQ without its tools of code generation. I noticed that you try to handle plurals when I read a small part of your source code, I admit that I didn’t read everything, managing some regular plurals is doable but managing all of them is nearly impossible, I tried to do that naively several years ago, I preferred switching to forcing singular names because I couldn’t support all plurals even with about 9 patterns and some exceptions.

Actually, what worries me the most when using those libraries is the speed and when a colleague of mine showed me an option to display the SQL queries generated by Hibernate JPA, I understood that it generated several smaller SQL queries instead of a single bigger SQL query to retrieve the data from several tables (and it’s not only related to the N+1 problem). In other words, I suspect that it favors safety over performance. Personally, I use plain JDBC if possible when I need to go fast. When I’m forced to use JPA, I use NetBeans build-in code generation and wsimport. Please don’t use Oracle, you need a Platinum subscription to be allowed to report bugs whereas it’s a quite buggy beast (I’m fed up with ORA-600 and its friends).

In my humble opinion, having fewer or no dependency can be a selling point in some games but not for those already bundling a JRE, many artworks and many third party libraries.

By the way, what’s the point of using Maven as you have no dependency?

When you post the new version, I’ll look at its source code for sure. There’s still some room for innovation in this subject.

Actually, what worries me the most when using those libraries is the speed and when a colleague of mine showed me an option to display the SQL queries generated by Hibernate JPA, I understood that it generated several smaller SQL queries instead of a single bigger SQL query to retrieve the data from several tables (and it’s not only related to the N+1 problem). In other words, I suspect that it favors safety over performance.

From my experience, sub-queries are usually significant performance enhancers. I’m curious what aroused your suspicions in the case you mentioned. I can’t think of a situation where I’ve ever coded a query one way rather than another because it was “safer”. Either the results are reliably and correct or they are not–no middle ground. Perhaps there was another rationale for the formulation.

@philfrei Instead of taking a connection from the pool, executing a single bigger SQL query and putting this connection back into the pool, it creates many smaller SQL queries and for each of them, it takes a connection from the pool, it executes this query and it puts this connection back into the pool.

Both Oracle and PostgreSQL compute an execution plan before executing an SQL query, they are able (in theory) to optimize the query itself (query transformations, you can disable them by using some hints, maybe you know the famous NO_QUERY_TRANSFORMATION) and its execution. Imagine that you want to get an instance of Car, you need to eagerly retrieve its instances of Wheel, its instance of SteeringWheel, etc; I succeed in writing a single query (by using plain JDBC) to retrieve the Car whereas Hibernate JPA needs multiple queries. All relational database management systems have bugs, I find some RDBMS buggier than others and more complicated queries are more exposed to these bugs. Using simpler SQL queries can be a mean of favoring safety over performance and I suspect that some implementation choices were made with some considerations I disagree with, especially using simpler SQL queries for most RDBMS not to exhibit some bugs in some specific RDBMS.

Just to be sure that we’re talking about the same things, using sub-queries and (inner, outer, …) joins is ok, using several distinct queries when you can use a single one might cause performance problems. By the way, yes, using indices to filter and sort is a lot faster :slight_smile:

Oracle’s optimizer might pick a very bad execution plan in some cases, you can either force it to use a particular execution plan, disable some optimizations, the RULE hint disables the use of the query optimizer or even write your own pre-optimizer in order to simplify the SQL queries before sending them to Oracle (it allows to work around the bugs caused by the wrong branch being kept in a condition). I reminded that you need a Platinum subscription to report bugs against Oracle SQL because to me, it means that many programmers that can’t afford such an expensive subscription must learn how to live with those bugs or they have to switch to a less buggy RDBMS, which is what I advise after more than 18 years of experience in databases.

1 Like

SQL queries instead of a single bigger SQL query to retrieve the data from several tables (and it’s not only related to the N+1 problem). In other words, I suspect that it favors safety over performance.

Exactly. Most of these libraries emphasize the modeling side without considering performance costs. It’s not that difficult to understand the basics of databases but it seems these other frameworks want to create large abstractions around it.

1 Like

By the way, what’s the point of using Maven as you have no dependency?

I don’t want to make it worse. :wink:

When you post the new version, I’ll look at its source code for sure. There’s still some room for innovation in this subject.

Thanks. I’ll post an announcement here.

I’m working on a project right now which was developed for a small company a while back. It’s C# WPF with MVVM and Entity Framework. It’s a very nice looking app and very well organized code. But the performance is terrible.

So when I looked at the code he was doing exactly like I describe. He had Entity models and was using LINQ for grid searches and even reports! When I looked at his DB it was well organized but he had NO indexes!

I added a couple of indexes for now to make it a little faster. Eventually I’ll convince the owner to let me redo it in Spring.

Life in the big city. :wink: