Scala Introduction to various database access frameworks

There are quite a few of the Scala libraries and frameworks

There are some non-ORM database libraries, as well as ORM, some of which are listed below:

Scalaquery

The first one is scalaquery. It's the most mature one, and it tries to make queries use the same for-comprehension as Scala collections do. As an example of syntax style (which might is slightly out of date):
After a trial, I found that the support for Ms SERVER was not very good

Java code   import java.lang.integer   import com.novocode.squery._   import  com.novocode.squery.implicit._   import com.novocode.squery.session._   import  com.novocode.squery.session.sessionfactory._     // define table:   object users extends table[(integer, string, string)] ("Users")  {     def id = intcolumn ("id",  o.autoinc, o.notnull)      def  first = stringcolumn ("First")      def last = stringcolumn (" Last ")      def * = id ~ first ~ last  }      // basic usage   val sf = new  Drivermanagersessionfactory ("Org.h2.Driver",  "Jdbc:h2:mem:test1")    sf withsession {    &NBSP;&NBSP;//&NBsp prepare a simple query     val q1 = for (u <-  Users)  yield u        // print sql statement to  be executed:     println (q1.selectstatement)   // displays  select t1.id,t1.first,t1.last from users t1        //  print query result:     for (T&NBSP;&LT;-&NBSP;Q1)  println ("User tuple:   "+t"         // Query statements can also be  used with updates:     val q = for (u <- Users if  u.id is 42)  yield u.first ~ u.last     q.update ("foo",   "Bar")   }  


Querulous

The second one is querulous, which are a open source project from Twitter. This one gives the dealing with a bunch of JDBC annoyances when you direct access to SQL. Here's a simple example:

Java code   import com.twitter.querulous.evaluator.queryevaluator   Val queryevaluator  = queryevaluator ("host",  "username",  "password")    val users =  Queryevaluator.select ("select * from users where id in  (?)  or name = ? ",  list (, )" Jacques ")  { row =>     new user (Row.getint ("id"),  row.getstring ("name"))   }   Queryevaluator.execute ("insert into users values  (?,  ?)", 1,  "Jacques")    queryevaluator.transaction { transaction =>      Transaction.select ("Select ... for update",   ...)      transaction.execute ("insert into users values  (?,  ?)",  1 ,  "Jacques")      transaction.execute ("Insert into users values  (?,  ?) ", 2, " Luc ")   }  


Squeryl

The third one is Squeryl. Style-wise, it sits midway between Scalaquery--which hides SQL behind Scala comprehensions as much as possible--and Qu Erulous--which uses SQL strings directly.

Squeryl provides a sql-like DSL, which gives you type safety and give you a strong likelyhood that the statements won ' t FA Il at run-time if they compile @ all. Again, a simple example:

Java code  // defining tables and a schema:   import  org.squeryl.primitivetypemode._      Class author (var id: long,                  var firstName:  string,                 var  lastname: string)       Class book (var id: long,               var title: String,               @Column ("author_id")  // the  default  ' Exact match '  policy can be overriden               var authorId: Long,            &nbsP;  var coauthorid: option[long])  {     def this ()  =  this (0, "", 0,some (0L))   }      Object library extends schema  {     //when the table name doesn ' t match the  class name, it is specified here :     val authors  = table[author] ("AUTHORS")      val books = table[Book]  }      // basic usage   class.forname ("Org.postgresql.Driver");     val session = session.create (       Java.sql.DriverManager.getConnection ("Jdbc:postgresql://localhost:5432/squeryl",  "Squeryl",  "Squeryl" "),      new PostgreSqlAdapter   )       // Squeryl database interaction must be done with a using block :     Import  Library._   using (session)  {      books.insert (New author (1,   "Michel", "Folco")                  val a = from (authors) (A=> where (a.lastname ===  "Folco")   Select (a))    }  

O/R Broker
http://code.google.com/p/orbroker/

The fourth is O/R Broker, which, despite the name, is not an ORM. Classes can designed in any desired. No interfaces/traits to implement, no conventions to uphold, no annotations needed.
Can be developed quickly with high flexibility

Case Class Song (Id:option[long], title:string, Seconds:short)
Case Class Album (Id:option[long], title:string, Year:short, Songs:indexedseq[song])
Case Class Artist (Id:option[long], name:string, Albums:set[album])
Extractors is declarative, written in Scala. Can is reused in and queries that fit the expectation of the extractor.

Java code   object songextractor extends joinextractor[song] {     val  key = set ("song_id")         def extract (row: row,  Join: join)  = {       new song (              row.bigint ("song_id"),               row.string ("TITLE") .get,               row.smallint ("Duration_seconds") .get            )      }  }      object albumextractor  extends JoinExtractor[Album] {     val key = set ("album_id")         def extract (row: row, join: join)  = {        new album (              row.bigint ("album_id"),              Row.string ("TITLE") .get,             row.smallint (" Year_issued ") .get,             join.extractseq ( Songextractor, map ("TITLE", "Song_title"))             )        }  }      object artistextractor  extends joinextractor[artist] {     val key = set ("ARTIST_ID")         def extract (row: row, join: join)  = {        new artist (              row.bigint ("artist_id "),             row.string (" NAME "),    &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;JOIN.EXTRACTSEQ (AlbumExtractor)            )      }  }   one  could then use that like this:      val ds:  javax.sql.datasource = ...   Val builder = new sqlfilebuilder (ds,  New java.io.file ("sql/"))    Val broker = builder.build ()      / / print all artists with their albums  (if any)    val  Artists = broker.readonly ()  { session =>     session.selectall[ Artist] (' selectartist)  //  '  I wish they could fix the Scala  Symbol formatting  }   for  (ar <- artists)  {     println ( A.name)          if  (ar.albums.isEmpty)             println ("\t<no albums>")           else for  (al <- ar.albums)  {            println ("\ T"  + al.title)             for  (s <- al.songs)  {              println ("\t\t"  +  (al.songs.indexOf (s) +1)  +  ". "  + s.title)            }         }   }  



Anorm
From the play framework, it ditches mappings and DSL completely, giving you direct access to SQL. Instance:

Java code  // create an sql query   Val selectcountries = sql (" Select * from country ")      // transform the resulting  stream[row] as a list[(string,string)]   val countries =  Selectcountries (). Map (row =>        row[string] ("code")  ->  row[string] ("name")   ) .tolist   it also supports pattern matching  for row extraction:      val countries = sql ("select  Name,population from country ") () .collect {       case row (" France ",  _)  => france ()        case row (name:string,  Pop:int)  if (pop > 1000000)  => bigcountry (name)         case row (Name:string, _)  => smallcountry (name)         }    binding variables in queries uses this syntax:      SQL (         ""            select  * from Country c            join  CountryLanguage l on l.CountryCode = c.Code             where c.code = {countryCode};        "" "  ). On (" CountryCode " -> " FRA ")   


And it also have support for use of the parse combinators to translate queries or even table schemas into data structures. Can either define the parser yourself, or use some default conventions (like a Case class mapping field names to Colum n names) and let it does the work for you.

Circumflex ORM
The following examples are available on the website:

Java code   class category extends record[category] {     val id  = field (category.id)      val name = field (category.name)       val books = onetomany (book.category)     // allows  navigating between associations transparently  }      Object  Category extends Table[Category] with LongIdPK[Category] {      val name = stringcolumn ("name")          //  creates a column         .notNull                              // creates NOT NULL constraint          .unique                              // creates  unique constraint         .validateNotEmpty                    // adds  NotEmpty validation         .validatepattern ("^[a-za-z]{1,8} $ ")  // adds Pattern validation  }      class book  extends record[book] {     val id = field (Book.id)      val title = field (book.title)      val category =  Manytoone (book.category)   }      Object book extends table[book]  with longidpk[book] {     val title = stringcolumn ("title")           .notNull         .validateNotEmpty      val category = longcolumn ("category_id")           .references (Category)      // creates an association  with category         .onDeleteSetNull           // specifies a foreign-key action          .onUpdateCascade  }      New ddlexport (Category ,  book) .create   // creates database schema     //  find category by id   val c = category.get (2l)   // find  all books   val allbooks = book.all  // find books for  category   val cbooks = c.get.books  // find books by  title   Book.criteria.add ("title"  like  "a%") .list      Select ()           .from (category as  "C"  join  (book as  "b "), category as " C1 ")          .where (" C1.name " like