sproket/persism

A zero ceremony ORM for Java

persism's Issues

Maybe add an onReady onInitialized type of event

Maybe the Persitable (or other) interface could also have some call back to tell the code when the object was read and initialized. This is because this happens after the constructor call.

With the new Join way, can I sort by specifiying a field?

 @Join(to = LineItem.class, onProperties = "id", toProperties = "invoiceId")
    private List<LineItem> lineItems = new ArrayList<>();

Is it possible to sort this lineItems ArrayList?. I mean: If i have a LineItem with itemName and ItemDescription, is there any way to specify the field by which I want to sort the list? for instance: itemName or itemDescription. I know I can do it after retrieving the list, but I use sqlite and this db has a rowId that I use as a primary key and is 'hidden'. Of course, this is the field I want to sort by.

Session-creation not possible with container-managed DataSource

I'm trying to use Persism with a DataSource managed by Payara (Glassfish):
Session session = new Session(dataSource.getConnection());
I'm getting a NPE in this scenario :-(:

java.lang.NullPointerException
	at java.util.concurrent.ConcurrentHashMap.get(ConcurrentHashMap.java:936)
	at net.sf.persism.MetaData.getInstance(MetaData.java:79)
	at net.sf.persism.Session.init(Session.java:61)
	at net.sf.persism.Session.<init>(Session.java:40)
        at ....

The reason for this is in the MetaData-class, that wants to use the URL as a key in the HashMap of Persism-MetaData:

   static synchronized MetaData getInstance(Connection con) throws SQLException {

        String url = con.getMetaData().getURL();
        if (metaData.get(url) == null) {
            metaData.put(url, new MetaData(con));
        }
        log.debug("MetaData getting instance " + url);
        return metaData.get(url);
    }

The use of getURL() is unfortunate, as the JDBC-Doc in DatabaseMetaData states that this value might be null -- and for my DataSource it seems to be the case :-( ...

    /**
     * Retrieves the URL for this DBMS.
     *
     * @return the URL for this DBMS or <code>null</code> if it cannot be
     *          generated
     * @exception SQLException if a database access error occurs
     */
    String getURL() throws SQLException;

Even there's another place where the URL will bite back: it's the ConnectionTypes.get()-method which will return null, and lead to funny NPEs in other places later on.

I browsed through DatabaseMetaData and Connection, but I couldn't find a suitable alternative as an identifier for a connection. I've use Connection.getCatalog() in other cases if I need some identifier for a database, but only for informative user display, and it's local to a single database and might be null, too. So this will not work as a general solution to fix this problem.

I propose two things:

• provide a new Session-constructor with an extra argument used as connection-id. The current constructor can chain to this constructor and use DatabaseMetaData.getURL() as default value.
• change the get-factory-method in ConnectionTypes to use DatabaseMetaData.getDatabaseProductName() (or getDriverName()?), maybe keeping the current implementation as a fallback until the new way is stable. [Actually I would prefer having the database-product be flexibly configurable somewhere -- we had the Informix question before, and currently there's no way to add a new database product without modifying the library itself.]

If you say that looks like a reasonable way to go, I could see if I manage to fork the project and prepare a pull request.

Failure in TestMetaData

Running TestMetaData results in a failure:

-------------------------------------------------------------------------------
Test set: net.sf.persism.TestMetaData
-------------------------------------------------------------------------------
Tests run: 4, Failures: 1, Errors: 0, Skipped: 0, Time elapsed: 0.02 s <<< FAILURE! - in net.sf.persism.TestMetaData
net.sf.persism.TestMetaData.testGuessing  Time elapsed: 0.009 s  <<< FAILURE!
junit.framework.ComparisonFailure: Message s/b 'Could not determine a table for type: net.sf.persism.TestDerby Guesses were: [TestDerby, TestDerbies, Test Derby, Test_Derby, Test Derbies, Test_Derbies] and we found multiple matching tables: [TEST_DERBY, TESTDERBY]' expected:<...rbies, Test_Derbies][ and we found multiple matching tables: [TEST_DERBY, TESTDERBY]]> but was:<...rbies, Test_Derbies][]>
	at net.sf.persism.TestMetaData.testGuessing(TestMetaData.java:70)

The test seems to assume the existence of two tables in the Derby-database: TEST_DERBY and TESTDERBY.
Maybe you somehow created them a long time ago manually.

        try {
            session.insert(new TestDerby());
        } catch (PersismException e) {
            failed = true;
            assertEquals("Message s/b 'Could not determine a table for type: net.sf.persism.TestDerby Guesses were: [TestDerby, TestDerbies, Test Derby, Test_Derby, Test Derbies, Test_Derbies] and we found multiple matching tables: [TEST_DERBY, TESTDERBY]'",
                    "Could not determine a table for type: net.sf.persism.TestDerby Guesses were: [TestDerby, TestDerbies, Test Derby, Test_Derby, Test Derbies, Test_Derbies] and we found multiple matching tables: [TEST_DERBY, TESTDERBY]",
                    e.getMessage());
        }

Easiest solution seems to be to create the required tables in the test:

diff --git a/test/net/sf/persism/TestMetaData.java b/test/net/sf/persism/TestMetaData.java
index 105fc69..cbb9fe7 100644
--- a/test/net/sf/persism/TestMetaData.java
+++ b/test/net/sf/persism/TestMetaData.java
@@ -41,0 +42 @@
+        createTables();
@@ -43,0 +45,8 @@
+    private void createTables() {
+        if (!UtilsForTests.isTableInDatabase("TEST_DERBY", con)) {
+            session.execute("CREATE TABLE TEST_DERBY (X INTEGER)");
+        }
+        if (!UtilsForTests.isTableInDatabase("TESTDERBY", con)) {
+            session.execute("CREATE TABLE TESTDERBY (X INTEGER)");
+        }
+    }

This makes the test pass for me.

UUID for PostreSQL fails

Describe the bug
Use a UUID on PostgreSQL - fails with unknown type 1111

From here: https://www.reddit.com/r/java/comments/l9gv6d/announcing_persism_100_a_no_nonsense_orm_for_java/glkn2cb/

Maybe the method Types.isCountable is wrong?

Describe the bug
Maybe the method Types.isCountable is wrong?

The code is :

    public boolean isCountable() {
        return this == IntegerType || this == integerType || this == LongType || this == longType || this == byteType || this == ByteType
                || this == ShortType || this == shortType || this == DoubleType || this == doubleType || this == BigDecimalType;
    }

DoubleType, doubleType and BigDecimalType seem to be included by mistake , and BigInteger seems to be missing.

Of course, maybe you have your reasons, but in that case please write them.

Thanks for this project! Great idea and compact implementation!

Finish support for Informix

I need to get a Informix VM up to do full testing.

Add support for transient keyword

Should work the same as @NotColumn annotation

Removal of JTDS 2.1.0

JTDS is not well supported by anyone anymore and it's buggy. Developers should use the MSSQL driver which is open source now anyway.

We'll remove it in Persism 2.1.0

Improve signature of insert-Method

The signature of the insert-Method currently reads:

public <T> Result<T> insert(Object object) throws PersismException

The type parameter T is rather useless this way. It should read:

public <T> Result<T> insert(T object) throws PersismException

You can get a module error because persismOriginalValue is serializable to json or xml

If you try serializing to xml you can't extend from PersistableObject since it has a field which is not accessible to the modules.

[GameManager] Failed making field 'net.sf.persism.PersistableObject#persismOriginalValue' accessible; either increase its visibility or write a custom TypeAdapter for its declaring type.
com.google.gson.JsonIOException: Failed making field 'net.sf.persism.PersistableObject#persismOriginalValue' accessible; either increase its visibility or write a custom TypeAdapter for its declaring type.
	at [email protected]/com.google.gson.internal.reflect.ReflectionHelper.makeAccessible(ReflectionHelper.java:38)
	at [email protected]/com.google.gson.internal.bind.ReflectiveTypeAdapterFactory.getBoundFields(ReflectiveTypeAdapterFactory.java:287)
	at [email protected]/com.google.gson.internal.bind.ReflectiveTypeAdapterFactory.create(ReflectiveTypeAdapterFactory.java:130)
	at [email protected]/com.google.gson.Gson.getAdapter(Gson.java:546)
	at [email protected]/com.google.gson.internal.bind.CollectionTypeAdapterFactory.create(CollectionTypeAdapterFactory.java:53)
	at [email protected]/com.google.gson.Gson.getAdapter(Gson.java:546)
	at [email protected]/com.google.gson.internal.bind.ReflectiveTypeAdapterFactory.createBoundField(ReflectiveTypeAdapterFactory.java:160)
	at [email protected]/com.google.gson.internal.bind.ReflectiveTypeAdapterFactory.getBoundFields(ReflectiveTypeAdapterFactory.java:295)
	at [email protected]/com.google.gson.internal.bind.ReflectiveTypeAdapterFactory.create(ReflectiveTypeAdapterFactory.java:130)
	at [email protected]/com.google.gson.Gson.getAdapter(Gson.java:546)
	at [email protected]/com.google.gson.Gson.toJson(Gson.java:817)
	at [email protected]/com.google.gson.Gson.toJson(Gson.java:795)
	at [email protected]/com.google.gson.Gson.toJson(Gson.java:742)
	at [email protected]/com.google.gson.Gson.toJson(Gson.java:719)
	at game.goldchest/game.ui.GameManager.saveGame(GameManager.java:457)
	at game.goldchest/game.ui.menu.CampMenu.lambda$new$0(CampMenu.java:46)
	at game.goldchest/game.ui.MenuPanel.doSelect(MenuPanel.java:359)
	at game.goldchest/game.ui.MenuPanel.menuKeyEvent(MenuPanel.java:273)
	at game.goldchest/game.ui.MenuPanel.keyPressed(MenuPanel.java:202)
	at game.goldchest/game.ui.KeyDispatcher.dispatchKeyEvent(KeyDispatcher.java:64)
	at java.desktop/java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(DefaultKeyboardFocusManager.java:1144)
	at java.desktop/java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(DefaultKeyboardFocusManager.java:1020)
	at java.desktop/java.awt.DefaultKeyboardFocusManager.dispatchEvent(DefaultKeyboardFocusManager.java:848)
	at java.desktop/java.awt.Component.dispatchEventImpl(Component.java:4882)
	at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2324)
	at java.desktop/java.awt.Window.dispatchEventImpl(Window.java:2780)
	at java.desktop/java.awt.Component.dispatchEvent(Component.java:4833)
	at java.desktop/java.awt.EventQueue.dispatchEventImpl(EventQueue.java:773)
	at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:722)
	at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:716)
	at java.base/java.security.AccessController.doPrivileged(AccessController.java:399)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:97)
	at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:746)
	at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:744)
	at java.base/java.security.AccessController.doPrivileged(AccessController.java:399)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
	at java.desktop/java.awt.EventQueue.dispatchEvent(EventQueue.java:743)
	at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:203)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:124)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:113)
	at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:109)
	at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
	at java.desktop/java.awt.EventDispatchThread.run(EventDispatchThread.java:90)
Caused by: java.lang.reflect.InaccessibleObjectException: Unable to make field private java.lang.Object net.sf.persism.PersistableObject.persismOriginalValue accessible: module sproket.github.io.persism does not "opens net.sf.persism" to module com.google.gson
	at java.base/java.lang.reflect.AccessibleObject.checkCanSetAccessible(AccessibleObject.java:354)
	at java.base/java.lang.reflect.AccessibleObject.checkCanSetAccessible(AccessibleObject.java:297)
	at java.base/java.lang.reflect.Field.checkCanSetAccessible(Field.java:178)
	at java.base/java.lang.reflect.Field.setAccessible(Field.java:172)
	at [email protected]/com.google.gson.internal.reflect.ReflectionHelper.makeAccessible(ReflectionHelper.java:35)
	... 43 common frames omitted

query() forces classname to match tablename

Yesterday I ran into an - at first sight - obscure problem with the query()-method. Digging into it revealed what's going an.

The following query:

List<UnknownTable> l = session.query(UnknownTable.class,
"SELECT LIMIT 10 personalnr, bewerbernr, nlcode FROM mitarbeiter WHERE austrittsdatum < MDY(1,1,2018)");

results in an exception, unless the the database has a matching table name for "unknowntable":

net.sf.persism.PersismException: Could not determine a table for type: de.test.dali.persistence.UnknownTable Guesses were: [UnknownTable, UnknownTables, Unknown Table, Unknown_Table, Unknown Tables, Unknown_Tables]

If there is actually a table for "UnknownTable" the error message gets more obscure (as it was for us yesterday) - imagine you do a query with the parameter Bewerber.class and there actually is a table in the database with the same name:

net.sf.persism.PersismException: Object class de.test.dali.persistence.Bewerber was not properly initialized. Some properties not initialized by the queried columns: [bewerbernr, nlcode] Missing:[nl_code]

I didn't try it with joins, but I guess it will cause similar problems.

So what's would be expected behaviour:

• at least an error message stating something like "Class name (or @Table annotation) needs to match the queried tabled" -- especially the second case left us totally clueless yesterday ...
• but better: no error message but simply delivering the results of the query, independent of a match of the table name.

I think the meachanism to match table names to class names (or @Table annotations) is useful for CRUD of individual records -- but with a free query this should not be constraining the classes that can be used.

Add support for Views

Views have some advantages if Persism can use them directly. Namely we can cache the meta-data for better performance and do better type checking.

Add Annotation for View to work like Table.

Add support for DuckDB

see https://duckdb.org/

and https://duckdb.org/docs/api/java

Add support for System.Logger

See this thread https://www.reddit.com/r/java/comments/srnhsi/system_logger/

Insert with POJO containing @Join annotations should also insert the child records

Let's say you have a customer class containing invoices. You instantiate a new Customer and also set some child Invoice objects on the invoices property (annotated with @Join). In that case Persism should also insert the child rows using the new auto-inc parent value.

The SQL.sql method doesn't work with parameters but the SQL.where does. Why?

I'm sure there's a reason. I realized I need to use the column names when using SQL.sql method. Maybe we should be able to handle that situation.

Oracle JDBC drivers have new groupIds and artifactIds

The Oracle JDBC drivers have been updated and published to Maven Central since September 2019 starting with version 19.6.0.0. The new coordinates use com.oracle.database.jdbc as base gorupId

https://mvnrepository.com/artifact/com.oracle.database.jdbc

Is there any easier option to update an object in a "one-to-many" relationship?

Hello, how can I easily update an object belonging to a one-to-many relationship?. I mean:

Table Movies(id, title, ...)
Table Genres(id, movie_id, name)

I have, movie Blade runner and a list of genres( scifi, action...). What I'm doing is delete the genres and again insert the new ones, but I remember years ago I was using ormlite and they had something like 'movie.getGenres.update(...)'
Is there something similiar to this for persism? Am I using the best and most efficient way to update a register in a one-to-many relationship? There will be an easier feature to update 'children' records in this kind of relationships?

By the way, I'm using sqlite as DB Engine

Test problems with fixes

I tried to build the project (LocalDB only) and encountered two problems:

1. A localization issue in TestH2
2. Implicit javafx-dependency, which is actually not needed

Here come two patches:

Localization issue in TestH2

The localization issue occurs because I'm in Germany and getMessage() contains both a localized version of the error message plus the original message in English. The proposed fix is to change the test from "startsWith" to "contains". This fixes it for me, though it would probably be better to check for some well defined specific exception or error code -- yet that would require more digging into H2, so I didn't do that.

diff --git a/test/net/sf/persism/TestH2.java b/test/net/sf/persism/TestH2.java
index 9281c3c..c55b5db 100644
--- a/test/net/sf/persism/TestH2.java
+++ b/test/net/sf/persism/TestH2.java
@@ -514,2 +514,2 @@
-            assertTrue("message starts with 'Unique index or primary key violation'",
-                    e.getMessage().startsWith("Unique index or primary key violation"));
+            assertTrue("message contains 'Unique index or primary key violation' failed: "+e.getMessage(),
+                    e.getMessage().contains("Unique index or primary key violation"));

Implicit javafx-dependency, which is actually not needed

Javafx has been removed from the jdk, and even my zulu-8 doesn't bring it out of the box. And it's usage doesn't seem to be nescessary at all, at the same can be achieved with ImageIO.

diff --git a/test/net/sf/persism/dao/northwind/Category.java b/test/net/sf/persism/dao/northwind/Category.java
index 7f08af1..c1c5623 100644
--- a/test/net/sf/persism/dao/northwind/Category.java
+++ b/test/net/sf/persism/dao/northwind/Category.java
@@ -3,2 +2,0 @@
-import javafx.embed.swing.SwingFXUtils;
-import javafx.scene.image.Image;
@@ -11,0 +10 @@
+import javax.imageio.ImageIO;
@@ -85,2 +84 @@
-                Image image1 = new Image(in);
-                image = SwingFXUtils.fromFXImage(image1, null);
+                image = ImageIO.read(in);

I could also try to provide a pull request, if you like, though that would be my first try with github forking ... ;-)

On a side note: is there a special reason, why you put all the local databases into the user-home directory? I'd rather propose to have them local to the project, most likely somewhere in target.

Implement Optimistic Locking with version

see https://vladmihalcea.com/optimistic-locking-version-property-jpa-hibernate/

might be useful.

Add upsert and/or fetchByKey(Class, Object...) methods to Session

I'm just exploring this library on Informix and it's basically working though it doesn't have "official" support - so that good :-).

What I'm missing is a kind of upsert-statement: if an object is alread in the database I'd like to update it, if it's not there I'd like to insert it, e.g.:

try (Session session = new Session(dataSource.getConnection())) {
    session.upsert(customer);			
}

I tried to work around it by using fetch first and then deciding if I need to insert or update. But I couldn't find a method which I could pass in the requested class and the key, e.g.:
Customer customer = session.fetch(Customer.class, 5);
Actually -- I found exactly that method, but it's commented out, as it doesn't work together with the variant that accepts an sql as a second parameter)... so that's a method that I'm missing, too.

There is a fetch-method with a single POJO parameter, but it needs to be filled with the primary key first and when querying it, it will overwrite all contents contained in it. I could work around it by cloning the object, but that would require the POJO to be Cloneable, and it doesn't feel very right conceptually:

    Customer customerThrowAway = customer.clone();
    if (session.fetch(customerThrowAway)) {
        session.update(customer);
    } else {
        session.insert(customer);
    }

So I'm kind of stuck here. I can of course write a simple SELECT myself and use the fetch-method with the SQL as a second parameter, but that doesn't really fit into the concept of simplicity with zero ceremony ;-) ...

So I'd propose

• to "wake up" the commented out fetch-method, probably with a new name
• (an alternative to using a different name would be to change the existing fetch(Class, String, Object...) signature to fetch(String, Class, Object...) -- it should avoid the clash of signatures, but would break backwards compatibility).
• consider adding an upsert-method

So what do you think? Thanks for consdidering :-)

[Background: My usage szenario is that I'm offering a REST webservice where the consumer can request a PUT some special field value. This value is usually just some optional add-one to some other POJO. In the GET-request we simply provide a default value if there's no value available, but we need to offer the option to store something explicitly.]

Projections

I don't know whether this is implemented or not, but I haven't found the way.
I have an object whose name is Movie and also a moviesList.
I know I can fetch a list of these movies and , of course, a single movie object. But what I don't know is how to get a list of the movies ids.

Example:
Table movies( int id , String title), ......
I don't want a list of movies with ALL their fields, nor a single movie object. I want a list that ONLY consists of the ids of these movies and for the other fields, filled with their default values. Is that possible?. I can load all the objects and afterwards, map these objects to their ids, but I'd like to avoid loading everything.

I've tried the following but is not working:
List<Movie> moviesIdsList = session.query(Movie.class, sql("SELECT id FROM Movies"));
It says the object movie was not properly initialized. I though the object would be initialized with default values or something like that. Can you help me?

Follow up on UCanAccess - seems broken under Java 16

Works under Java 8-15 but breaks under Java 16 and the newer version 5.0.1 also breaks for different reasons. :(

Have some kind of Join functionality

Currently Persism does not support joins in any automatic way. Maybe it should while avoiding any SELECT N+1 problems.

Read about it here: https://sproket.github.io/Persism/n+1.html

Add support for schema names with table names

We should have support to include the schema name as well as the table name when generating or using SQL.

e,g,

SELECT [identity], [First Name], [Last Name], [ContactName] FROM [Contacts]

s.b.

SELECT [identity], [First Name], [Last Name], [ContactName] FROM [dbo].[Contacts]

It is possible to have access to a DB where you would have multiple schema names. Currently Persism fails to find them.

It is also possible that a DB has multiple schemas with tables with the same name. Need to be able to handle that situation.

Should not remove milliseconds from Time when converting from String

This originally was an issue with JTDS - since JTDS is scheduled for removal in 2.1.0 we need to revisit. See Converter.

Add support for tinylog

see https://github.com/tinylog-org/tinylog