utility scala-sql

中文版本帮助

scala-sql 2.0 is the simple scala sql api on top of JDBC.

Little new concepts, just a light enclosing api on top of JDBC. Learning scala-sql is 1-2 hours if you familar with JDBC.
scala friend api, designed for stronger typed, immutable and functional programming
extensible for new data types.
simple typed mapping, via macro.
compile time SQL grammer check.
deattached Row for common use without mapping.

Planning Features

utils to support REPL.
Batch API
Client Side Sharding(Sharding-JDBC) support

Basic usgae

scala-sql enhance java.sql.Connection & java.sql.DataSource with more methods:

executeUpdate
```
  dataSource executeUpdate sql"""update table set name = "$name" and age = ${age} where id = ${id}"""
```
you may compare the code with the Java Version that do the same thing:
```
  DataSource dataSource = ...;
  Connection conn = null;
  PreparedStatement ps = null;
  try {
    conn = dataSource.getConnection();
    ps = conn.prepareStatement("update table set name = ? and age = ? where id = ?")
    ps.setString(1, name);
    ps.setInt(2, age);
    ps.setInt(3, id);
  
    ps.executeUpdate
  }
  finally {
    try {
        if(ps != null) ps.close();
    }
    catch(SQLException ex){}
    try {
        if(conn != null) conn.close();
    }
    catch(SQLException ex) {}
  }
}
```
That is scala-sql, which has the same sematics as the JDBC framework, but with better encapsulation and more scala style.

scala-sql provide a powerful sql/SQL string interpolation sql"sql-statement" and SQL"sql-statement" It has features:
- ${expr} is not injected as string concat but SQL parameter, or the ? style in prepared statement. so by using scala-sql, you can avoid SQL-Inject problems automated
- also ${expr} is type checked at compile time, you can't pass a wrong type such as a java.sql.Connection or java.swing.JFrame value, since which can't saved to table columns.
- scala-sql support all baisc jdbc primitive SQL types such as boolean, byte, short, int, float, double, string, bigdecimal, date, time, timestamp etc.
- scala-sql also support scala types such as scala.BigDecimal, scala.Option[T] where T is valid sql type
- you can define your pass-in Type T by define a implict value JdbcValueAccessor[T]
- MOST INTERESTING FEATURE, the SQL"sql-statement" can even check the statement grammar at compile time, include the sql grammar, wrong table name, wrong fieldname, and etc. to using this feature, please see usage on the Compile-Time grammar check section.
rows
```
  case class User(name: String, age: Int)

  val users: List[User] = dataSource.rows[User](sql"select * from users where name like ${name}")
```
scala-sql provide a Simple ORM mechanism, any object of type T which has a implict ResultSetMapper[T] can be used in rows, row, foreach method.

rows[T](sql) where T can be
- Case Class. Currently, the scala-sql provide a macro which support automated generate the ResultSetMapper of a case class, so you need not writing the mapping code by hand, the macro will automate generate it.
- Row exists when you don't provide a mapping type, you can think Row is a deattached ResultSet row. so a simple rows[Row](sql"statement") can used to recieve data from database.
- primitive types. if your statment only select 1 column such as select count(*) from table, you can even using the primitive sql types such as rows[Int](sql"statement""), this include
  - Byte, Short, Int, Long, java.math.BigDecimal, scala.BigDecimal
  - String
  - java.sql.Date, java.sql.Time, java.sql.DateTime, java.sql.Timestamp

foreach

dataSource.foreach(sql"select * from users where name like ${name}" { u: User =>
  ...
}

also, you can use Case Class or Row or primitive sql types for recieve the mapping.

generateKey

withStatement

dataSource.withStatement { stmt: Statement => ...
}

withPreparedStatement

withConnection

dataSource.withConnection { conn: Connection => ...
}

withTransaction

dataSource.withTransaction { conn: Conntion => ...
}

Batch API provide a scala style API for batch operation, special support for MySQL Grammar

Compile-Time grammar check

write a scala-sql.properties in current directory
provide a default.url, default.user, default.password, default.driver for the default configuration
write sql statement using SQL"select * from table"
If you need to access muti-database, you can define a @db(name="some") in the enclosing class, and define some.url, some.user, some.password, some.driver

JdbcValue[T]， JdbcValueAccessor[T]

scala-sql defines type class JdbcValueAccessor[T], any type which has an implicit context bound of JdbcValueAccessor can be passed into query, and passed out from ResultSet. This include:

primary SQL types, such as byte, short, int, string, date, time, timestamp, BigDecimal
scala types: such as scala.BigDecimal
optional types. Now you can pass a Option[BigDecimal] into statement which will auto support the null
customize your type via define a implicit value JdbcValueAccessor[T]

ResultSetMapper[T]

scala-sql define type class ResultSetMapper[T], any type which has an implicit context of ResultSetMapper can be mapped to a ResulSet, thus, can be used in the rows[T], row[T], foreach[T] operations.

instead of writing the ResultSetMapper yourself, scala-sql provide a Macro which automate generate the mapper for Case Class.

So, does it support all Case Class ? of couse not, eg. you Case class case class User(name: String, url: URL) is not supported because the url field is not compatible with SQL. the scala-sql Macro provide a stronger type check mechanism for ensure the Case Class is able to mapping from ResultSet.

sbt usage:

libraryDependencies +=  "com.github.wangzaixiang" %% "scala-sql" % "2.0.7"