RawSql with different than entity table

[Aleksander Lados]

Hi,

I have just recently started working with Ebeans on our project. I have table called "invoice_details" with @Entity class InvoiceDetails. Data in that table is populated using external datasource (different database) by SQL statement. Currently I am traversing through ResultSet and assigning selected columns to InvoiceDetails objects in a loop. When I was reading through Ebean docs I thought that using RawSql would simplify this if it could just populate InvoiceDetails directly but I couldn't make it work with most common SQLException: The column index is out of range 0>1.

So that let me to believe I am trying to use it wrongly, that it is not possible to directly bind to entity using RawSql querying different, programatically unrelated table, am I correct? Does table that is being queried using RawSql need to have @Id related to that @Entity?

Thanks in advance for all your help.

[Rob Bygrave]

> not possible to directly bind to entity using RawSql querying different, programatically unrelated table, am I correct?

No, the table name does not matter.  You can even have RawSql based on a ResultSet (so no sql at in in that case).  

> need to have @Id related to that @Entity?

I am not sure but I suspect this isn't your problem.   That is, if there is further queries fired (further lazy loading etc) then it is hard to see that working without valid @Id values but I don't think you are doing that.

> column index is out of range 0>1.

To me this sounds like a problem with the column mapping.

Are you able to include your code?

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Aleksander Lados]

Hi Rob,

It is most likely related to column mapping as when I play around with query and it returns an empty list there is no error. But I thought that's related to where data is coming from. 

I have also tried to bind it to @Entity like below, and to @Sql annotated aggregate class like in the example on Ebean webpage but with exactly same result.

Below I am including code that is related to the issue, it's trimmed down a lot as I was trying to get even basics working, I have also removed one @ManyToOne relation to avoid Lazy loading potential issues you have mentioned.

/* This exists in one database */

@Entity

@Table(name = "invoice_details")

public class InvoiceDetails extends Model {

    @Column(name = "details_id")

    @Id

    @GeneratedValue(strategy = GenerationType.TABLE)

    public Integer detailsId;

    @Column(name = "invoice_no")

    public String invoiceNo; // normally that is a foreign key

    @Column(name = "ticket_no", length = 14)

    public String ticketNo;

    @Column(name = "coupon_no", length = 1)

    public String couponNo;

}

/* query below is trying to get data from different tables in external DB and bind them to details */

String sqlTest = "SELECT " +

                "tt.ticket_number, " +

                "tc.coupon_information_number " +

                "FROM tkt_coupon tc " +

                "INNER JOIN tkt_ticket tt " +

                "ON tc.tkt_ticket_id = tt.tkt_ticket_id ";

        RawSql testRaw = RawSqlBuilder.parse(sqlTest)

                .columnMapping("tt.ticket_number", "ticketNo")

                .columnMapping("tc.coupon_information_number", "couponNo")

                .create();

        Query<InvoiceDetails> queryDetails = server.find(InvoiceDetails.class);

        queryDetails.setRawSql(testRaw)

                .where()

                .eq("MONTH(tc.travel_product_date)", 3)

                .eq("YEAR(tc.travel_product_date)", 2015);

        return queryDetails.findList(); // This is where it fails with error

[PersistenceException: Query threw SQLException:Column Index out of range, 0 < 1. Bind values:[3,2015] Query was: select tt.ticket_number, tc.coupon_information_number FROM tkt_coupon tc INNER JOIN tkt_ticket tt ON tc.tkt_ticket_id = tt.tkt_ticket_id INNER JOIN tkt_ticket_passenger tp ON tp.tkt_ticket_passenger_id = tt.tkt_ticket_passenger_id where MONTH(tc.travel_product_date) = ? and YEAR(tc.travel_product_date) = ? ]

[Rob Bygrave]

Ok, I'll see if I can reproduce.

Just some side notes based on the example code above:

- All the @Column(name = "...") can actually be removed in that the column names match the default NamingConvention (underscore based)

- The properties are public fields so I'm thinking you are using Play framework (Ebean expects getters/setters etc)

- @GeneratedValue(strategy = GenerationType.TABLE) ... is not supported with Ebean as it is deemed to be not a good idea (Identity or sequence are much better)

I'll see if I can find a similar test or reproduce ...

Cheers, Rob.

 

[Aleksander Lados]

Thanks Rob,

Yes, it is in Play Framework recently moved from version 2.2.1 to 2.4.2. It is a bit of a side project at the moment that I decided to move to Ebean myself so your tips are much appreciated and I will modify @Entities accordingly. 

[Rob Bygrave]

Can you check the version of Ebean ORM are you using?  Are you able to test using 4.x?  (prefer the latest which is 4.7.3)

I am unable to reproduce.  I've added another test which looks similar but note that there are quite a lot of what look like similar RawSql queries in the Ebean tests - they use RawSqlBuilder.parse() and then extend the SQL with additionally predicated added via where() (and having() etc).

> SQLException:Column Index out of range, 0 < 1.

I am unable to find this error text in the latest version of Ebean.  PreparedStatement bind starts at position 1 (not 0 based) so that error almost looks like there is an attempt to bind position 1.

Can you provide the full stack trace? We need to identify if this error is coming out of the jdbc driver etc.

Thanks, Rob.

 

[Aleksander Lados]

Hi Rob,

Here is the stack trace:

play.api.http.HttpErrorHandlerExceptions$$anon$1: Execution exception[[PersistenceException: Query threw SQLException:Column Index out of range, 0 < 1. 

Bind values:[3,2015] 

Query was:

select tt.ticket_number, tc.coupon_information_number FROM tkt_coupon tc INNER JOIN tkt_ticket tt ON tc.tkt_ticket_id = tt.tkt_ticket_id INNER JOIN tkt_ticket_passenger tp ON tp.tkt_ticket_passenger_id = tt.tkt_ticket_passenger_id  where MONTH(tc.travel_product_date) = ?  and YEAR(tc.travel_product_date) = ? 

]]

at play.api.http.HttpErrorHandlerExceptions$.throwableToUsefulException(HttpErrorHandler.scala:265) ~[play_2.11-2.4.2.jar:2.4.2]

at play.api.http.DefaultHttpErrorHandler.onServerError(HttpErrorHandler.scala:191) ~[play_2.11-2.4.2.jar:2.4.2]

at play.api.GlobalSettings$class.onError(GlobalSettings.scala:179) [play_2.11-2.4.2.jar:2.4.2]

at play.api.DefaultGlobal$.onError(GlobalSettings.scala:212) [play_2.11-2.4.2.jar:2.4.2]

at play.api.http.GlobalSettingsHttpErrorHandler.onServerError(HttpErrorHandler.scala:94) [play_2.11-2.4.2.jar:2.4.2]

Caused by: javax.persistence.PersistenceException: Query threw SQLException:Column Index out of range, 0 < 1. 

Bind values:[3,2015] 

Query was:

select tt.ticket_number, tc.coupon_information_number FROM tkt_coupon tc INNER JOIN tkt_ticket tt ON tc.tkt_ticket_id = tt.tkt_ticket_id INNER JOIN tkt_ticket_passenger tp ON tp.tkt_ticket_passenger_id = tt.tkt_ticket_passenger_id  where MONTH(tc.travel_product_date) = ?  and YEAR(tc.travel_product_date) = ? 

at com.avaje.ebeaninternal.server.query.CQuery.createPersistenceException(CQuery.java:627) ~[avaje-ebeanorm-4.7.3.jar:na]

at com.avaje.ebeaninternal.server.query.CQuery.createPersistenceException(CQuery.java:606) ~[avaje-ebeanorm-4.7.3.jar:na]

at com.avaje.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:193) ~[avaje-ebeanorm-4.7.3.jar:na]

at com.avaje.ebeaninternal.server.query.DefaultOrmQueryEngine.findMany(DefaultOrmQueryEngine.java:79) ~[avaje-ebeanorm-4.7.3.jar:na]

at com.avaje.ebeaninternal.server.core.OrmQueryRequest.findList(OrmQueryRequest.java:284) ~[avaje-ebeanorm-4.7.3.jar:na]

Caused by: java.sql.SQLException: Column Index out of range, 0 < 1.

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998) ~[mysql-connector-java-5.1.36.jar:5.1.36]

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937) ~[mysql-connector-java-5.1.36.jar:5.1.36]

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926) ~[mysql-connector-java-5.1.36.jar:5.1.36]

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872) ~[mysql-connector-java-5.1.36.jar:5.1.36]

at com.mysql.jdbc.ResultSetImpl.checkColumnBounds(ResultSetImpl.java:759) ~[mysql-connector-java-5.1.36.jar:5.1.36]

[Rob Bygrave]

Can I have more stack trace?

I want to see past:

 at com.mysql.jdbc.ResultSetImpl.checkColumnBounds(ResultSetImpl.java:759)

[Rob Bygrave]

Ah, no that will be the entire stack trace right.

[Aleksander Lados]

Yes, this is all I got.

[Rob Bygrave]

Ok, I think I have reproduced ....

Caused by: java.sql.SQLException: Column Index out of range, 0 < 1.

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)

at com.mysql.jdbc.ResultSetImpl.checkColumnBounds(ResultSetImpl.java:759)

at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2471)

at com.avaje.ebeaninternal.server.type.RsetDataReader.getInt(RsetDataReader.java:118)

at com.avaje.ebeaninternal.server.type.ScalarTypeInteger.read(ScalarTypeInteger.java:35)

at com.avaje.ebeaninternal.server.type.ScalarTypeInteger.read(ScalarTypeInteger.java:18)

at com.avaje.ebeaninternal.server.deploy.BeanProperty.read(BeanProperty.java:545)

at com.avaje.ebeaninternal.server.deploy.id.IdBinderSimple.readSet(IdBinderSimple.java:166)

at com.avaje.ebeaninternal.server.query.SqlTreeNodeBean.load(SqlTreeNodeBean.java:200)

at com.avaje.ebeaninternal.server.query.CQuery.readNextBean(CQuery.java:401)

at com.avaje.ebeaninternal.server.query.CQuery.hasNext(CQuery.java:478)

at com.avaje.ebeaninternal.server.query.CQuery.readCollection(CQuery.java:484)

at com.avaje.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:175)

... 39 more

Looking at it ...

[Rob Bygrave]

> RawSql need to have @Id related to that @Entity?

Right, I'm pretty sure we have identified the issue.  If you had the same stack trace as mine above we'd be 100% confident - I'm not sure why your stack trace seems cut down and doesn't show use what calls ResultSetImpl.checkColumnBounds(). 

Anyway, I think you were onto the correct issue right at the beginning ... in that the problem with your RawSql is indeed that the @Id column is not included and the error occurs when it tries to read that @Id property from the resultSet when that column is not included in the RawSql statement.  

I got a bit confused there because I thought the problem was going to be binding the where predicate parameters but no the problem I believe is in the reading of the ResultSet. 

Internally there is actually support for not having the @Id property but in the current code for RawSql this is not supported. The fix for this issue is to add that check in for RawSql.

I have logged this as: https://github.com/ebean-orm/avaje-ebeanorm/issues/317

Side notes:

When a bean is loaded without an @Id property that means that:

- The PersistenceContext is not used (it has no @Id value so we can't use the PC - Checks against PC and loads into PC are bypassed)

- There is no further lazy loading support (lazy loading is disabled on the beans loaded in this manor, these beans don't register with the "LoadContext")

[Aleksander Lados]

Thank you Rob for your support and prompt answers. I cannot tell why my stack trace was shorter than yours, Play fault maybe?

Regarding your side notes, if PersistentContext is not used does it mean Beans loaded this way would not be able to be persisted (inserted as new) into database?

[Rob Bygrave]

> if PersistentContext is not used does it mean Beans loaded this way would not be able to be persisted (inserted as new) into database?

With Ebean the PersistenceContext is not involved in persisting ... it is only involved on the query side (object graph building / populating).

So yes you can insert these beans into a database and for this you'd want to use EbeanServer.insert() rather than EbeanServer.save(). That is, save() does an insert or update based on the bean state and in this case you'd want to use the explicit insert().  This is because the beans are in 'loaded state' even though they don't have an Id value.

And here is a little test. It uses Ebean.insert() but can use EbeanServer.insert() or Model.insert() just the same way ...

  @Test

  public void testWithNoIdPropertyWithInsert() {

    EBasic basic = new EBasic();

    basic.setName("RawSql-NoIdTest");

    basic.setStatus(EBasic.Status.ACTIVE);

    Ebean.save(basic);

    String rs = "select b.status, b.name from e_basic b ";

    RawSql rawSql = RawSqlBuilder.parse(rs).create();

    List<EBasic> list = Ebean.find(EBasic.class)

        .setRawSql(rawSql)

        .where().eq("name", "RawSql-NoIdTest")

        .findList();

    assertEquals(1, list.size());

    EBasic basic1 = list.get(0);

    basic1.setDescription("insertAfterRawFetch");

    Ebean.insert(basic1);

  }

// transaction log from the above code ...

00:01:36.303 [main] TRACE org.avaje.ebean.SQL - txn[1003] insert into e_basic (id, status, name, description, some_date) values (?,?,?,?,?); --bind(1,ACTIVE,RawSql-NoIdTest,null,null,)

00:01:36.304 [main] DEBUG org.avaje.ebean.SUM - txn[1003] Inserted [EBasic] [1]

00:01:36.304 [main] DEBUG org.avaje.ebean.TXN - txn[1003] Commit

// the RawSql fetch ...

00:01:36.327 [main] TRACE org.avaje.ebean.SQL - txn[1004] select b.status, b.name from e_basic b  where b.name = ?; --bind(RawSql-NoIdTest)

00:01:36.329 [main] DEBUG org.avaje.ebean.SUM - txn[1004] FindMany type[EBasic] origin[S6yM2.CVisCT.BvQ020] exeMicros[2309] rows[1] name[] predicates[b.name = ? ] bind[RawSql-NoIdTest]

00:01:36.330 [main] TRACE org.avaje.ebean.TXN - txn[1004] Commit - query only

// The explicit insert 

00:01:36.331 [main] TRACE org.avaje.ebean.SQL - txn[1005] insert into e_basic (id, status, name, description, some_date) values (?,?,?,?,?); --bind(2,ACTIVE,RawSql-NoIdTest,insertAfterRawFetch,null,)

00:01:36.331 [main] DEBUG org.avaje.ebean.SUM - txn[1005] Inserted [EBasic] [2]

00:01:36.332 [main] DEBUG org.avaje.ebean.TXN - txn[1005] Commit

Hmmm, so yes using save() does fail as it treats the bean as 'loaded' and hence save() turns it into an update statement that fails.  Now it could be that beans like this (with no Id value) should really be returned in 'NEW' state rather than 'LOADED' state so I'll ponder that - if that was the case then save() would result in an insert statement and work and perhaps that is the more expected behavior?

[Aleksander Lados]

> Hmmm, so yes using save() does fail as it treats the bean as 'loaded' and hence save() turns it into an update statement that fails.  Now it could be that beans like this (with no Id value) should really be returned in 'NEW' state rather than 'LOADED' state so I'll ponder that - if that was the case then save() would result in an insert statement and work and perhaps that is the more expected behavior?

It does seem to be more intuitive this way, as you can't really expect to update correctly bean without having its Id imho.

[Rob Bygrave]

So the fix for #317 is in HEAD.

> It does seem to be more intuitive this way, as you can't really expect to update correctly bean without having its Id imho.

Quite right. I have also logged #319 to address this issue.  

#319 - Beans loaded without an @Id value ... return in NEW state rather than LOADED state (also refer to #317)

https://github.com/ebean-orm/avaje-ebeanorm/issues/319

This fix/enhancement is also in HEAD ... and means that beans loaded via a query without an @Id value are now returned in NEW state and that means that if you save() them that will result in a SQL insert statement (rather than a SQL update and error).

Cheers, Rob.