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.
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());
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