4

I'm writing a data structure that converts the results of a database query. The raw structure is a java ResultSet and it would be converted to a map or class which permits accessing different fields on that data structure by either a named method call or passing a string into apply(). Clearly different values may have different types. In order to reduce burden on the clients of this data structure, my preference is that one not need to cast the values of the data structure but the value fetched still has the correct type.

For example, suppose I'm doing a query that fetches two column values, one an Int, the other a String. The result then names of the columns are "a" and "b" respectively. Some ideal syntax might be the following:

val javaResultSet = dbQuery("select a, b from table limit 1")

// with ResultSet, particular values can be accessed like this:
val a = javaResultSet.getInt("a")
val b = javaResultSet.getString("b")
// but this syntax is undesirable. 

// since I want to convert this to a single data structure, 
// the preferred syntax might look something like this:
val newStructure = toDataStructure[Int, String](javaResultSet)("a", "b")

// that is, I'm willing to state the types during the instantiation
// of such a data structure.

// then,
val a: Int = newStructure("a") // OR
val a: Int = newStructure.a

// in both cases, "val a" does not require asInstanceOf[Int].

I've been trying to determine what sort of data structure might allow this and I could not figure out a way around the casting.

The other requirement is obviously that I would like to define a single data structure used for all db queries. I realize I could easily define a case class or similar per call and that solves the typing issue, but such a solution does not scale well when many db queries are being written. I suspect some people are going to propose using some sort of ORM, but let us assume for my case that it is preferred to maintain the query in the form of a string.

Anyone have any suggestions? Thanks!

Improve this question

6 Answers 6

Reset to default
2

To do this without casting, one needs more information about the query and one needs that information at compiole time.

I suspect some people are going to propose using some sort of ORM, but let us assume for my case that it is preferred to maintain the query in the form of a string.

Your suspicion is right and you will not get around this. If current ORMs or DSLs like squeryl don't suit your fancy, you can create your own one. But I doubt you will be able to use query strings.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

to clarify, i'm willing to do casting, but just when the data structure is constructed as opposed to when getting a value. does that affect your suggestion?
1

The basic problem is that you don't know how many columns there will be in any given query, and so you don't know how many type parameters the data structure should have and it's not possible to abstract over the number of type parameters.

There is however, a data structure that exists in different variants for different numbers of type parameters: the tuple. (E.g. Tuple2, Tuple3 etc.) You could define parameterized mapping functions for different numbers of parameters that returns tuples like this:

def toDataStructure2[T1, T2](rs: ResultSet)(c1: String, c2: String) =
  (rs.getObject(c1).asInstanceOf[T1],
  rs.getObject(c2).asInstanceOf[T2])


def toDataStructure3[T1, T2, T3](rs: ResultSet)(c1: String, c2: String, c3: String) =
  (rs.getObject(c1).asInstanceOf[T1],
  rs.getObject(c2).asInstanceOf[T2],
  rs.getObject(c3).asInstanceOf[T3])

You would have to define these for as many columns you expect to have in your tables (max 22).

This depends of course on that using getObject and casting it to a given type is safe.

In your example you could use the resulting tuple as follows:

val (a, b) = toDataStructure2[Int, String](javaResultSet)("a", "b")
Improve this answer

2 Comments

That seems possible, but how would I extract the column value by name?
You can't do that with this solution.
1

if you decide to go the route of heterogeneous collections, there are some very interesting posts on heterogeneous typed lists:

one for instance is

http://jnordenberg.blogspot.com/2008/08/hlist-in-scala.html

http://jnordenberg.blogspot.com/2008/09/hlist-in-scala-revisited-or-scala.html

with an implementation at http://www.assembla.com/wiki/show/metascala

a second great series of posts starts with

http://apocalisp.wordpress.com/2010/07/06/type-level-programming-in-scala-part-6a-heterogeneous-list%C2%A0basics/

the series continues with parts "b,c,d" linked from part a

finally, there is a talk by Daniel Spiewak which touches on HOMaps

http://vimeo.com/13518456

so all this to say that perhaps you can build you solution from these ideas. sorry that i don't have a specific example, but i admit i haven't tried these out yet myself!

Improve this answer

Comments

0

Joschua Bloch has introduced a heterogeneous collection, which can be written in Java. I once adopted it a little. It now works as a value register. It is basically a wrapper around two maps. Here is the code and this is how you can use it. But this is just FYI, since you are interested in a Scala solution.

In Scala I would start by playing with Tuples. Tuples are kinda heterogeneous collections. The results can be, but not have to be accessed through fields like _1, _2, _3 and so on. But you don't want that, you want names. This is how you can assign names to those:

scala> val tuple = (1, "word")
tuple: ([Int], [String]) = (1, word)

scala> val (a, b) = tuple
a: Int = 1
b: String = word

So as mentioned before I would try to build a ResultSetWrapper around tuples.

Improve this answer

4 Comments

i would like to use something like the heterogeneous collection but written in scala. is there any reason why the java approach isn't possible in scala?
Well, Scala does not support raw types, which makes it impossible to transfer my example "line for line" into Scala. But Scala is so powerful...I'm sure there is some other way to express what you want. On the other hand. Why don't you just use the Java version? The code I posted is fully functional...
i'm considering that option. however it still does not appear to allow doing something like "val x = map.get(key)". you have to do something like "val x: Int = map.get(key)". does that seem right?
Yes that is correct. But this is the whole point of this collection. Although you HAVE to specify the type, the cast to that type is DONE FOR YOU. I mean at some point you have to specify the type...
0

If you want "extract the column value by name" on a plain bean instance, you can probably:

  1. use reflects and CASTs, which you(and me) don't like.
  2. use a ResultSetToJavaBeanMapper provided by most ORM libraries, which is a little heavy and coupled.
  3. write a scala compiler plugin, which is too complex to control.

so, I guess a lightweight ORM with following features may satisfy you:

  1. support raw SQL
  2. support a lightweight,declarative and adaptive ResultSetToJavaBeanMapper
  3. nothing else.

I made an experimental project on that idea, but note it's still an ORM, and I just think it may be useful to you, or can bring you some hint.

Usage:

declare the model:

//declare DB schema
trait UserDef extends TableDef {
  var name = property[String]("name", title = Some("姓名"))
  var age1 = property[Int]("age", primary = true)
}

//declare model, and it mixes in properties as {var name = ""}
@BeanInfo class User extends Model with UserDef

//declare a object.
//it mixes in properties as {var name = Property[String]("name") }
//and, object User is a Mapper[User], thus, it can translate ResultSet to a User instance.
object `package`{
  @BeanInfo implicit object User extends Table[User]("users") with UserDef
}

then call raw sql, the implicit Mapper[User] works for you:

val users = SQL("select name, age from users").all[User] 
users.foreach{user => println(user.name)}

or even build a type safe query:

val users = User.q.where(User.age > 20).where(User.name like "%liu%").all[User]

for more, see unit test:

https://github.com/liusong1111/soupy-orm/blob/master/src/test/scala/mapper/SoupyMapperSpec.scala

project home:

https://github.com/liusong1111/soupy-orm

It uses "abstract Type" and "implicit" heavily to make the magic happen, and you can check source code of TableDef, Table, Model for detail.

Improve this answer

Comments

0

Several million years ago I wrote an example showing how to use Scala's type system to push and pull values from a ResultSet. Check it out; it matches up with what you want to do fairly closely.

implicit val conn = connect("jdbc:h2:f2", "sa", "");
implicit val s: Statement = conn << setup;
val insertPerson = conn prepareStatement "insert into person(type, name) values(?, ?)";
for (val name <- names) 
            insertPerson<<rnd.nextInt(10)<<name<<!;
for (val person <- query("select * from person", rs => Person(rs,rs,rs)))
            println(person.toXML);
for (val person <- "select * from person" <<! (rs => Person(rs,rs,rs)))
            println(person.toXML);

Primitives types are used to guide the Scala compiler into selecting the right functions on the ResultSet.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.