CODEWITHSUNDEEP
postgresqlscalajdbcscalikejdbc
Eric
Eric

Reputation: 24954

scalike-jdbc - How to specify column alias in query?

I defined an object to help to convert result set into POJO.

  // an object, that help to map from query result to POJO,
  object Dummy extends SQLSyntaxSupport[Dummy] {
    override val tableName = "dummy"
    def apply(rs: WrappedResultSet) = new Dummy(
      rs.long("id"), rs.stringOpt("name"), rs.intOpt("size"), rs.jodaDateTime("create_date"))
  }

Then following code try to query from a table,

  /*** query - with condition - start ***/
  // use paste mode (:paste) on the Scala REPL
  val d = Dummy.syntax("d")
  val name = "hello"
  val helloDummyOpt: Option[Dummy] = withSQL {
    select.from(Dummy as d).where.eq(d.name, name).limit(1)
  }.map(rs => Dummy(rs)).single.apply()

  printf("hello dummy:\t%s\n", if(helloDummyOpt.isDefined) helloDummyOpt.get else "")
  /*** query - with condition - end ***/

From console, I can see the generated sql is:

select d.id as i_on_d, d.name as n_on_d, d.size as s_on_d, d.create_date as cd_on_d
from dummy d where d.name = 'hello' limit 1;

In order to make the convert work, need to change column alias in generated sql to remain as the original name in table, e.g name instead of n_on_d.

So, how to do that, thanks.

@Update

The complete code is here:

package eric.scalikejdbc

import scalikejdbc._
import org.postgresql.Driver._
import org.joda.time._

/**
 * scalikejdbc hello.
 * @author eric
 * @date Jul 6, 2016 23:14:07 PM
 */
object ScalikeJdbcHello extends App {
  Class.forName("org.postgresql.Driver")
  ConnectionPool.singleton("jdbc:postgresql://localhost:5432/sportslight", "postgres", "123456")

  implicit val session = AutoSession

  /*** query - parse as List of Map - start ***/
  // find rows, with limit 10000,
  val entities: List[Map[String, Any]] = sql"select * from dummy limit 10000".map(_.toMap).list.apply()
  printf("dummy count: %d\n", entities.size)
  /*** query - parse as List of Map - end ***/

  /*** query - parse as POJO - start ***/
  // define a POJO class,
  case class Dummy(id: Long, name: Option[String], size: Option[Int], createDate: DateTime)

  // an object, that help to map from query result to POJO,
  object Dummy extends SQLSyntaxSupport[Dummy] {
    override val tableName = "dummy"
    def apply(rs: WrappedResultSet) = new Dummy(
      rs.long("id"), rs.stringOpt("name"), rs.intOpt("size"), rs.jodaDateTime("create_date"))
  }

  // find top 10 rows,
  val dummyList: List[Dummy] = sql"select * from dummy limit 10".map(rs => Dummy(rs)).list.apply()

  var i = 0;
  printf("dummy top rows:\n")
  for (d <- dummyList) {
    printf("\t[%d] %s\n", i, d)
    i = i + 1
  }
  /*** query - parse as POJO - end ***/

  /*** query - with condition - start ***/
  // TODO ... specify column name in result,
  val d = Dummy.syntax("d")
  val name = "hello"
  val helloDummyOpt: Option[Dummy] = withSQL {
    select.from(Dummy as d).where.eq(d.name, name).limit(1)
  }.map(rs => Dummy(rs)).single.apply()

  printf("hello dummy:\t%s\n", if(helloDummyOpt.isDefined) helloDummyOpt.get else "")
  /*** query - with condition - end ***/
}

And here is the sql to create table & initialize data for postgresql:

/* table - dummy */
-- drop table
drop table if exists dummy;

-- create table
create table dummy (
    id serial8,
    name varchar(50) not null,
    size int not null,
    create_date timestamptz,
    primary key (id)
);

-- init data - dummy
insert into dummy(name, size, create_date) values
('test', 1, now()),
('hello', 1, now());

Upvotes: 1

Views: 1489

Answers (2)

Kazuhiro Sera
Kazuhiro Sera

Reputation: 1877

select.from calls alias.result.*. Calling alias.* simply returns column names as-is.

scala> select(a.*).from(Article as a).toSQL.statement
res7: String = select a.id, a.title, a.body, a.created_at, a.updated_at from articles a

scala> select(a.result.*).from(Article as a).toSQL.statement
res8: String = select a.id as i_on_a, a.title as t_on_a, a.body as b_on_a, a.created_at as ca_on_a, a.updated_at as ua_on_a from articles a

scala> select.from(Article as a).toSQL.statement
res9: String = select a.id as i_on_a, a.title as t_on_a, a.body as b_on_a, a.created_at as ca_on_a, a.updated_at as ua_on_a from articles a

Upvotes: 3

vvg
vvg

Reputation: 6385

You're using half type safe DSL, half default API. You should choose one and proceed with it.

In case of TypeSafe DSL you should not use custom SqlSyntaxSupport implementations.

Instead of .map(rs => Dummy(rs)) you should be able to operate with d Dummy object, defined in sql query.

  val d = Dummy.syntax("d")

  val helloDummyOpt: Option[Dummy] = withSQL {
    select.from(Dummy as d).where.eq(d.name, name).limit(1)
  }.single.apply()

Check TypeSafe DSL section for more examples: http://scalikejdbc.org

If you want to receive those auto-generated names, you should refer to them through object d:

 val d = Dummy.syntax("d")
d.id // = i_on_d
d.name // = n_on_d
d.create_date // = cd_on_d

Upvotes: 1

Related Questions