search

ebean-orm / ebean

Ebean ORM
https://ebean.io
Apache License 2.0
1.46k stars 260 forks source link

Using RawSql is not working well if property name of entity is different than the one in @Column(name = "ColName") #1780

Closed almothafar closed 4 years ago

almothafar commented 5 years ago

I got a strange issue that I'm pretty sure is a bug; let's say I got a column called LocID in the database, which is mapped to property locationId of an entity mapped using @Column like @Column(name = "LocID") private Long locationId;

The problem is when I try to make a query using RawSqlBuilder.parse(sql).create() I get an issue of exception related to a property name (Property [locId] not found on) or column name (Invalid column name 'locationId'.). if I tried to select either of LocID or loactionId, other properties are fine, which make me believe it is a bug, that I renamed locationId to locId and worked!

I tried to rename another field like zipCode to postalCode with @Column(name = "ZipCode") got the same issue on this column too, in short, no issues at all if the property name MATCH the column name.

I tried columnMapping approach got the same issues, same exceptions.

Expected behavior

Selecting a property name in a query should be working fine.

Actual behavior

The property name MUST match column name, and can't be selected in any way if they are different.

Steps to reproduce

@Entity
@Sql
public class LocationExtractedData extends Model {

    @Id
    @Column(name = "Id")
    private Integer id;

    @Column(name = "ZipCode")
    private String zipCode;

    @Column(name = "LocID")
    private Long locationId;

    @Column(name = "CompanyName")
    private String companyName;

    @Column(name = "Latitude")
    private String latitude;

    @Column(name = "Longitude")
    private String longitude;

}

And query part for RawSql

public CompletionStage<Stream<LocationExtractedData>> list(String[] keys) {
        final String sql = "SELECT id, zipCode, locationId, companyName, latitude, longitude" +
                " FROM fnGetLocationsSummaryForCompanies(:keys)";

        return supplyAsync(() -> {
                    final RawSql rawSql = RawSqlBuilder.parse(sql).create();
                    return Ebean.find(LocationExtractedData.class)
                            .setRawSql(rawSql)
                            .setParameter("keys", String.join(",", keys))
                            .findList()
                            .stream();
                }
                , ec);

    }

Using SQL Server as you may note in exceptions.

First attempt (it does not make sense):

 java.util.concurrent.CompletionException: javax.persistence.PersistenceException: Query threw SQLException:Invalid column name 'locationId'. Bind values:[....] Query was:select id, zipCode, locationId, companyName, latitude, longitude FROM fnGetLocationsSummaryForCompanies(?)
    at java.base/java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:314)
    at java.base/java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:319)
    at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run$$$capture(CompletableFuture.java:1773)
    at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java)
    at akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:41)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:835)
Caused by: javax.persistence.PersistenceException: Query threw SQLException:Invalid column name 'locationId'. Bind values:[....] Query was:select id, zipCode, locationId, companyName, latitude, longitude FROM fnGetLocationsSummaryForCompanies(?)
    at io.ebean.config.dbplatform.SqlCodeTranslator.translate(SqlCodeTranslator.java:52)
    at io.ebean.config.dbplatform.DatabasePlatform.translate(DatabasePlatform.java:223)
    at io.ebeaninternal.server.query.CQueryEngine.translate(CQueryEngine.java:149)
    at io.ebeaninternal.server.query.DefaultOrmQueryEngine.translate(DefaultOrmQueryEngine.java:43)
    at io.ebeaninternal.server.core.OrmQueryRequest.translate(OrmQueryRequest.java:103)
    at io.ebeaninternal.server.query.CQuery.createPersistenceException(CQuery.java:701)
    at io.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:411)
    at io.ebeaninternal.server.query.DefaultOrmQueryEngine.findMany(DefaultOrmQueryEngine.java:133)
    at io.ebeaninternal.server.core.OrmQueryRequest.findList(OrmQueryRequest.java:451)
    at io.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1552)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'locationId'.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:592)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2935)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:444)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
[error] h.ExceptionHandlingUtil - @null: Query threw SQLException:Invalid column name 'locationId'. Bind values:[....] Query was:select id, zipCode, locationId, companyName, latitude, longitude FROM fnGetLocationsSummaryForCompanies(?)
    at actions.RemoteDBAction.lambda$doCall$2(RemoteDBAction.java:81)
    at java.base/java.util.concurrent.CompletableFuture.uniHandle(CompletableFuture.java:930)
    at java.base/java.util.concurrent.CompletableFuture$UniHandle.tryFire(CompletableFuture.java:907)
    at java.base/java.util.concurrent.CompletableFuture$Completion.run(CompletableFuture.java:478)
    at play.core.j.HttpExecutionContext$$anon$2.run(HttpExecutionContext.scala:79)
    at akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:41)
    at akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(ForkJoinExecutorConfigurator.scala:49)
    at akka.dispatch.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
    at akka.dispatch.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
    at akka.dispatch.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
    at akka.dispatch.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)
Caused by: javax.persistence.PersistenceException: Query threw SQLException:Invalid column name 'locationId'. Bind values:[....] Query was:select id, zipCode, locationId, companyName, latitude, longitude FROM fnGetLocationsSummaryForCompanies(?)
    at io.ebean.config.dbplatform.SqlCodeTranslator.translate(SqlCodeTranslator.java:52)
    at io.ebean.config.dbplatform.DatabasePlatform.translate(DatabasePlatform.java:223)
    at io.ebeaninternal.server.query.CQueryEngine.translate(CQueryEngine.java:149)
    at io.ebeaninternal.server.query.DefaultOrmQueryEngine.translate(DefaultOrmQueryEngine.java:43)
    at io.ebeaninternal.server.core.OrmQueryRequest.translate(OrmQueryRequest.java:103)
    at io.ebeaninternal.server.query.CQuery.createPersistenceException(CQuery.java:701)
    at io.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:411)
    at io.ebeaninternal.server.query.DefaultOrmQueryEngine.findMany(DefaultOrmQueryEngine.java:133)
    at io.ebeaninternal.server.core.OrmQueryRequest.findList(OrmQueryRequest.java:451)
    at io.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1552)
    at io.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1528)
    at io.ebeaninternal.server.querydefn.DefaultOrmQuery.findList(DefaultOrmQuery.java:1526)
...
java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:835)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'locationId'.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:592)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2935)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:444)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at io.ebeaninternal.server.query.CQuery.prepareResultSet(CQuery.java:377)
    at io.ebeaninternal.server.query.CQuery.prepareBindExecuteQueryWithOption(CQuery.java:325)
    at io.ebeaninternal.server.query.CQuery.prepareBindExecuteQuery(CQuery.java:320)
    at io.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:384)
    ... 12 more

A second attempt (it does make sense):

[error] a.PostAction - Direct exception javax.persistence.PersistenceException: Property [locId] not found on models.LocationExtractedData
java.util.concurrent.CompletionException: javax.persistence.PersistenceException: Property [locId] not found on models.LocationExtractedData
    at java.base/java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:314)
    at java.base/java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:319)
    at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run$$$capture(CompletableFuture.java:1773)
    at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java)
    at akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:41)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:835)
Caused by: javax.persistence.PersistenceException: Property [locId] not found on models.LocationExtractedData
    at io.ebeaninternal.server.query.CQueryBuilder.createRawSqlSqlTree(CQueryBuilder.java:488)
    at io.ebeaninternal.server.query.CQueryBuilder.createSqlTree(CQueryBuilder.java:408)
    at io.ebeaninternal.server.query.CQueryBuilder.buildQuery(CQueryBuilder.java:361)
    at io.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:377)
    at io.ebeaninternal.server.query.DefaultOrmQueryEngine.findMany(DefaultOrmQueryEngine.java:133)
    at io.ebeaninternal.server.core.OrmQueryRequest.findList(OrmQueryRequest.java:451)
    at io.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1552)
    at io.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1528)
    at io.ebeaninternal.server.querydefn.DefaultOrmQuery.findList(DefaultOrmQuery.java:1526)
    at ....LocationExtractedRepository.lambda$list$0(LocationExtractedRepository.java:41)
[error] h.ExceptionHandlingUtil - @null: Property [locId] not found on models.LocationExtractedData
    at actions.RemoteDBAction.lambda$doCall$2(RemoteDBAction.java:81)
    at java.base/java.util.concurrent.CompletableFuture.uniHandle(CompletableFuture.java:930)
    at java.base/java.util.concurrent.CompletableFuture$UniHandle.tryFire(CompletableFuture.java:907)
    at java.base/java.util.concurrent.CompletableFuture$Completion.run(CompletableFuture.java:478)
    at play.core.j.HttpExecutionContext$$anon$2.run(HttpExecutionContext.scala:79)
    at akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:41)
    at akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(ForkJoinExecutorConfigurator.scala:49)
    at akka.dispatch.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
    at akka.dispatch.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
    at akka.dispatch.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
    at akka.dispatch.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)
Caused by: javax.persistence.PersistenceException: Property [locId] not found on models.LocationExtractedData
    at io.ebeaninternal.server.query.CQueryBuilder.createRawSqlSqlTree(CQueryBuilder.java:488)
    at io.ebeaninternal.server.query.CQueryBuilder.createSqlTree(CQueryBuilder.java:408)
    at io.ebeaninternal.server.query.CQueryBuilder.buildQuery(CQueryBuilder.java:361)
    at io.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:377)
    at io.ebeaninternal.server.query.DefaultOrmQueryEngine.findMany(DefaultOrmQueryEngine.java:133)
    at io.ebeaninternal.server.core.OrmQueryRequest.findList(OrmQueryRequest.java:451)
    at io.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1552)
    at io.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1528)
    at io.ebeaninternal.server.querydefn.DefaultOrmQuery.findList(DefaultOrmQuery.java:1526)
    at ...LocationExtractedRepository.lambda$list$0(LocationExtractedRepository.java:41)
    at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run$$$capture(CompletableFuture.java:1771)
    at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java)
    at akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:41)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:835)
almothafar commented 5 years ago

Ops! I just noticed it might be SQL driver issue com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'locationId'., isn't? or an issue with generating script to SQL server, not checked this while debugging. I'm not really sure who is responsible...😅

Is it starting from here?:

at io.ebeaninternal.server.query.CQuery.prepareResultSet(CQuery.java:377)
    at io.ebeaninternal.server.query.CQuery.prepareBindExecuteQueryWithOption(CQuery.java:325)
    at io.ebeaninternal.server.query.CQuery.prepareBindExecuteQuery(CQuery.java:320)
    at io.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:384)

Sorry for lack of knowledge around this part, I'm learning :)

rbygrave commented 5 years ago

Do you know if this SQL Server instance has case sensitive collation for the dictionary?
So column name LocationId works but locationId doesn't?

There are a lot of people that tend to use quoted identifiers everywhere with sql server. So instead the column would be [LocationId].

With most other databases people tend to use underscore naming convention (and that is the default for Ebean) so location_id ... and the database dictionary is case insensitive so LOCATION_ID is also fine to use.

Are these column names and table names already defined in the application? Are people using quoted identifiers everywhere (for all table names and column names) with this database?

almothafar commented 5 years ago

For SQL Server, it is not case sensitive, if I got what you mean correctly, so if I select LocationId, or LOCATIONID, or LoCaAtIoNiD all works fine.

And for the column name, I understand that Ebean taking underscore as default convention, but I can override that by specifying the name with @Column, that works fine and map columns fine.

My problem is as described in the original post, I'm not really sure if I'm getting what you mean, but another example here:

@Entity
@Sql
public class FilePathExtractedData extends Model {
    @Id
    @Column(name = "ID")
    private Integer id;

    @Column(name = "WOID")
    private int woid;

    @Column(name = "ReportPath")
    private String reportPath;
}
final String sql = "SELECT id, WOID, reportPath FROM fnGetReportDocsForOrder(:order)";

return supplyAsync(() -> {
            final RawSql rawSql = RawSqlBuilder.parse(sql).create();
            return Ebean.find(FilePathExtractedData.class)
                    .setRawSql(rawSql)
                    .setParameter("order", order)
                    .findList()
                    .stream();
        }
        , ec);

The code above is working fine, the fun part that if I changed WOID to woid in String sql the code still works, 

final String sql = "SELECT id, woid, reportPath FROM fnGetReportDocsForOrder(:order)";

if I changed it to:

final String sql = "SELECT id, wOID, reportPath FROM fnGetReportDocsForOrder(:order)";

I get exception Property [wOID] not found on models.FilePathExtractedData which is fine.

BUT if I changed property name to:

    @Column(name = "WOID")
    private int WOID;

There is no way this code going to work, whatever I provide in String sql WOID or woid, both are not going to work with error: Property [woid] not found on models.FilePathExtractedData

Something messed up here, but I'm not sure what SQL Server is going to do here, as I have a full project working well in production, but I have a few areas that I need to do a workaround for them, as LocID column should have locId as property otherwise it is not going to work.

almothafar commented 5 years ago

What I understand is, String sql should be providing the property name and @Column should be respected to deal with mapping with the real physical column in SQL server.

BTW, The statement in native sql tool is working fine, I test them first to see my results, then translate them to java or ebean version of sql, also, everything is working fine with hibernate, and working fine using Ebean.createSqlQuery(sql) instead.

rbygrave commented 5 years ago

Hmmm. 

    @Id
    @Column(name = "Id")
    private Integer id;

    @Column(name = "ZipCode")
    private String zipCode;

    @Column(name = "LocID")
    private Long locationId;

    @Column(name = "CompanyName")
    private String companyName;

The problem with the above with the database case insensitive dictionary (most common) is that the above column names don't follow the the default UnderscoreNamingConvention .. and strictly speaking does not follow the MatchingNamingConvention ... it doesn't follow any Ebean provided naming convention.

The goal is actually to have a naming convention and follow it such that we don't need to use @Column at all (and similarly we don't need to use @JoinColumn etc). This should be the goal / approach for any new database schema.

We ought to only use @Column and @JoinColumn when we are required to support and existing database schema.

I'm not sure where you are at with this. If this is a new application / database schema I recommend that you remove all @Column and @JoinColumn annotations (which then means everything will follow the naming convention chosen).

Where are you at with this issue?

almothafar commented 5 years ago

@rbygrave I'm with a new application with very old Database system like a few years ago, running on MS Server 2012, what I understand the naming convention of ebean, and I follow it when I own the database and the application.

But I started with a new project using JPA starter that was using Hibernate, I decided then to see Ebean and found that it is better for performance and even a lot of less code, actually I'm Ebean fan from 3 years ago, but this is the first time I work with old exist SQL server, and almost the first time I work with database that I don't own, I just got a user to read/write but for dev I got my own database (SQL Server 2017) that I try to make it like prod one.

This is the story, about the issue, what I understand is ebean respecting @Columnon everything except this case, for some reason, if I try to query using: Ebean.find(LocationData.class).where().eq("locationId", something).findList() as for example, the code is working fine, and yes with @Column(name = "LocID") .

Sadly, I got a lot of areas that I need to provide @JoinColumn and @Column, as I said, I don't own the DB, and it got a lot of real data, like millions of rows, actually my application and DB is already live in production.

rbygrave commented 5 years ago

Ok. But when we see @Column(name = "LocID") ... this can also be stated as @Column(name = "LOCID") ... as the DB dictionary isn't case sensitive right. (So LocID is somewhat misleading and that can be problematic for resultSet meta data based features (findNative and findDto).

Can you re-state the minimum failing test again?
I think we need to start again fresh. Thanks.

rbygrave commented 5 years ago

Ok, I'll close this for now. You can re-open re-stating the issue again. Thanks.

almothafar commented 5 years ago

@rbygrave

The problem does not relate to letter case, as for example, I got the same issue in different property:

    @Column(name = "Received")
    private LocalDateTime receivedDate;

When my String sql = "SELECT Received from someFunction()" I get error: Property [Received] not found on models.SomeModal and when my String sql = "SELECT receivedDate from someFunction()" i get error: Invalid column name 'receivedDate'.

I tried: 

    @Column(name = "RECEIVED")
    private LocalDateTime receivedDate;

Still same errors.

almothafar commented 5 years ago

Also,

If I get something like:

    @Column(name = "STID")
    private int STID;

And selecting STID with String sql = "SELECT STID from someFunction()", I get error: Property [stid] not found

For LocID I tried:

    @Column(name = "LOCID")
    private int locationId;

Still having the same issues, Invalid column name 'locationId'. and Property [locid] not found

rbygrave commented 5 years ago

Can you look to provide a project with a failing test. Thanks.

On Fri, 13 Sep 2019 at 02:46, Al-Mothafar Al-Hasan notifications@github.com wrote:

Also,

If I get something like:

@Column(name = "STID")
private int STID;

And selecting STID with String sql = "SELECT STID from someFunction()", I get error: Property [stid] not found

For LocID I tried:

@Column(name = "LOCID")
private int locationId;

Still having the same issues, Invalid column name 'locationId'. and Property [locid] not found

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/ebean-orm/ebean/issues/1780?email_source=notifications&email_token=AABTATMULKCPW7NFBC6QLS3QJJI4JA5CNFSM4IIOJ472YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD6SER4Q#issuecomment-530860274, or mute the thread https://github.com/notifications/unsubscribe-auth/AABTATMQYG2DZN3V2GMECYLQJJI4JANCNFSM4IIOJ47Q .

almothafar commented 4 years ago

Hey @rbygrave sorry for the delay as I was really busy in a project here, I have created a new project that can reproduce the problem:

https://github.com/almothafar/play-java-ebean-sql-server

I created a silly example but I hope that enough, As you can test that when you start play application, hit http://localhost:9000/location/1?locName=true will reproduce the issue, http://localhost:9000/location/1?locName=false is not, because when you do locName=false the normal ebean query run:

    public CompletionStage<List<Company>> companiesByLocation(long locationId) {
        return supplyAsync(() -> ebeanServer.find(Company.class).where()
                .eq("locationId", locationId)
                .orderBy("name").findList(), executionContext);
    }

But with locName=true, RawSql workign here:

public CompletionStage<List<CompanyExtracted>> companiesByLocationFn(long locationId) {
        final String sql = "SELECT id, name, locationName, locationId FROM fnGetCompanyWithLocation (:id) result";
        return supplyAsync(() -> {
            final RawSql rawSql = RawSqlBuilder.parse(sql).create();
            return Ebean.find(CompanyExtracted.class)
                    .setRawSql(rawSql)
                    .setParameter("id", locationId)
                    .findList();
        }, executionContext);
    }

If you change locationId to locId in CompanyExtracted.java, the API here will work! As you can see here, the only problem is RawSql while the normal ebean method is OK whatever the property name is.

almothafar commented 4 years ago

@rbygrave are you able to test that?

May you reopen this issue to keep track of it?

Thanks.

rbygrave commented 4 years ago

I haven't looked at it yet - will reopen though.

rbygrave commented 4 years ago

Right so this isn't a bug with how RawSql is currently designed.

Raw sql is parsed without ANY knowledge of the type of entity being used and can be treated as a thread safe constant (static final).

So in the test case the mapping below is solely based on the naming convention.

final RawSql rawSql = RawSqlBuilder.parse(sql).create();

When things don't map by naming convention then explicit column mapping needs to be used. In this case that would be via:

final RawSql rawSql = RawSqlBuilder.parse(sql)
  .columnMapping("LocId","locationId")
  .create();

Changing the design to dynamically map based on @Column means we can lose that thread safe constant nature (static final).

almothafar commented 4 years ago

I'll try to do mapping, I think I did that before but didn't work maybe, I don't know, I need to try that again on Sunday.

almothafar commented 4 years ago

OK with Mapping it seems working fine now, but I have to specify column name in DB, also, it is case sensitive, which is fine I think, but just a note.

rbygrave commented 4 years ago

Cool - closing.