本节主要内容

  1. Scala Mavenproject的创建
  2. Scala JDBC方式訪问MySQL
  3. Slick简单介绍
  4. Slick数据库编程实战
  5. SQL与Slick相互转换

本课程在多数内容是在官方教程上改动而来的，官方给的样例是H2数据库上的。经过本人改造，用在MySQL数据库上，官方教程地址：http://slick.typesafe.com/doc/2.1.0/sql-to-slick.html

1. Scala Mavenproject的创建

本节的project项目採用的是Maven Project，在POM.xml文件里加入以下两个依赖就能够使用scala进行JDBC方式及Slick框架操作MySQL数据库：

 <dependency>

        <groupId>mysql</groupId>

        <artifactId>mysql-connector-java</artifactId>

        <version>5.1.18</version>

     </dependency>

     <dependency>

    <groupId>com.typesafe.slick</groupId>

    <artifactId>slick_2.11</artifactId>

    <version>2.1.0</version>

   </dependency>

scala IDE for eclipse 中创建scala Maven项目的方式例如以下：

在Eclispe 中点击” File->new->other”。例如以下图



输入Maven能够看到Maven Project:



直接next。得到



再点击next，在filter中输入scala得到：



选中，然后next输入相应的groupId等。直接finish就可以。创建完项目将上述依赖加入到pom.xml文件其中。这样就完毕了scala maven Project的创建。

2. Scala JDBC方式訪问MySQL

以下给出的是scala採用JDBC訪问MySQL的代码演示样例

package cn.scala.xtwy.jdbc

import java.sql.{ Connection, DriverManager }

object ScalaJdbcConnectSelect extends App {

  // 訪问本地MySQLserver，通过3306端口訪问mysql数据库

  val url = "jdbc:mysql://localhost:3306/mysql"

  //驱动名称

  val driver = "com.mysql.jdbc.Driver"

  //用户名

  val username = "root"

  //密码

  val password = "123"

  //初始化数据连接

  var connection: Connection = _

  try {

   //注冊Driver

    Class.forName(driver)

    //得到连接

    connection = DriverManager.getConnection(url, username, password)

    val statement = connection.createStatement

    //运行查询语句，并返回结果

    val rs = statement.executeQuery("SELECT host, user FROM user")

    //打印返回结果

    while (rs.next) {

      val host = rs.getString("host")

      val user = rs.getString("user")

      println("host = %s, user = %s".format(host, user))

    }

  } catch {

    case e: Exception => e.printStackTrace

  }

  //关闭连接。释放资源

  connection.close

}

3. Slick简单介绍

在前一小节中我们演示了怎样通过JDBC进行数据库訪问。相同在Scala中也能够利用JAVA中的ORM框架如Hibernate、IBatis等进行数据库的操纵，但它们都是Java风格的数据库操纵方式，Scala语言中也有着自己的ORM框架。眼下比較流行的框架包括:

1、Slick （typesafe公司开发）

2、Squeryl

3、Anorm

4、ScalaActiveRecord （基于Squeryl之上）

5、circumflex-orm

6、activate-framework（Scala版的Hibernate）

本节课程要讲的便是Slick框架，它是Scala语言创建者所成立的公司TypeSafe所开发的一个Scala风格的开源数据库操纵框架，它眼下支持以下几种主流的数据：

DB2 (via slick-extensions)

Derby/JavaDB

H2

HSQLDB/HyperSQL

Microsoft Access

Microsoft SQL Server (via slick-extensions)

MySQL

Oracle (via slick-extensions)

PostgreSQL

SQLite

当然它也支持其他数据，仅仅只是功能可能还不完好。在Slick中。能够像訪问Scala自身的集合一样对数据库进行操作。它具有例如以下几个特点：

1 数据库的訪问採用Scala风格：

//以下给出的是数据查询操作

class Coffees(tag: Tag) extends Table[(String, Double)](tag, "COFFEES") {

  def name = column[String]("COF_NAME", O.PrimaryKey)

  def price = column[Double]("PRICE")

  def * = (name, price)

}

val coffees = TableQuery[Coffees]

//以下给出的数据訪问API

// Query that only returns the "name" column

coffees.map(_.name)

// Query that does a "where price < 10.0"

coffees.filter(_.price < 10.0)

从上面的代码能够看到。Slick訪问数据库就跟Scala操纵自身的集合一样.

2 Slick数据操纵是类型安全的

// The result of "select PRICE from COFFEES" is a Seq of Double

// because of the type safe column definitions

val coffeeNames: Seq[Double] = coffees.map(_.price).list

// Query builders are type safe:

coffees.filter(_.price < 10.0)

// Using a string in the filter would result in a compilation error

3 支持链式操作

// Create a query for coffee names with a price less than 10, sorted by name

coffees.filter(_.price < 10.0).sortBy(_.name).map(_.name)

// The generated SQL is equivalent to:

// select name from COFFEES where PRICE < 10.0 order by NAME

4. Slick 数据库编程实战

以下的代码演示了Slick怎样创建数据库表、怎样进行数据插入操作及怎样进行数据的查询操作（以MySQL为例）：

package cn.scala.xtwy

//导入MySQL相关方法

import scala.slick.driver.MySQLDriver.simple._

object UseInvoker extends App {

  // 定义一个Test表

  //表中包括两列，各自是id,name

  class Test(tag: Tag) extends Table[(Int, String)](tag, "Test") {

    def k = column[Int]("id", O.PrimaryKey)

    def v = column[String]("name")

     // Every table needs a * projection with the same type as the table's type parameter

     //每一个Table中都应该有*方法。它的类型必须与前面定义的类型參数(Int, String)一致

    def * = (k, v)

  }

  //创建TableQuery对象（这里调用的是TableQuery的apply方法

  //没有显式地调用new

  val ts = TableQuery[Test]

  //forURL注冊MySQL驱动器，传入URL，用户名及密码

  //方法回返的是一个DatabaseDef对象，然后再调用withSession方法

  Database.forURL("jdbc:mysql://localhost:3306/slick", "root","123",

      driver = "com.mysql.jdbc.Driver") withSession { 

    //定义一个隐式值

    //implicit session: MySQLDriverbackend.Session

    //兴许方法中当做隐式參数传递

    implicit session =>

    // 创建Test表

    //create方法中带有一个隐式參数

    //def create(implicit session: JdbcBackend.SessionDef): Unit

    ts.ddl.create

    //插入数据

    //def insertAll(values: U*)(implicit session: JdbcBackend.SessionDef): MultiInsertResult

    ts.insertAll(1 -> "a", 2 -> "b", 3 -> "c", 4 -> "d", 5 -> "e")

   //数据库查询（这里返回全部数据）

   ts.foreach { x => println("k="+x._1+" v="+x._2) }

   //这里查询返回全部主鍵 <3的

   ts.filter { _.k <3 }.foreach { x => println("k="+x._1+" v="+x._2) }

  }

  //模式匹配方式

  ts.foreach { case(id,name) => println("id="+id+" name="+name) }

}

以下我们再给一个更为复杂的样例来演示Slick中是怎样进行数据的入库与查询操作的：

package cn.scala.xtwy

import scala.slick.driver.MySQLDriver.simple._

object CoffeeExample extends App {

  // Definition of the SUPPLIERS table

  //定义Suppliers表

  class Suppliers(tag: Tag) extends Table[(Int, String, String, String, String, String)](tag, "SUPPLIERS") {

    def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column

    def name = column[String]("SUP_NAME")

    def street = column[String]("STREET")

    def city = column[String]("CITY")

    def state = column[String]("STATE")

    def zip = column[String]("ZIP")

    // Every table needs a * projection with the same type as the table's type parameter

    def * = (id, name, street, city, state, zip)

  }

  val suppliers = TableQuery[Suppliers]

  // Definition of the COFFEES table

   //定义Coffees表

  class Coffees(tag: Tag) extends Table[(String, Int, Double, Int, Int)](tag, "COFFEES") {

    def name = column[String]("COF_NAME", O.PrimaryKey)

    def supID = column[Int]("SUP_ID")

    def price = column[Double]("PRICE")

    def sales = column[Int]("SALES")

    def total = column[Int]("TOTAL")

    def * = (name, supID, price, sales, total)

    // A reified foreign key relation that

    //can be navigated to create a join

    //外鍵定义，它使得supID域中的值关联到suppliers中的id

    //从而保证表中数据的正确性

    //它定义的事实上是一个n:1的关系，

    //即一个Coffees相应仅仅有一个Suppliers,

    //而一个Suppliers能够相应多个Coffees

    def supplier = foreignKey("SUP_FK", supID, suppliers)(_.id)

  }

  val coffees = TableQuery[Coffees]

  Database.forURL("jdbc:mysql://localhost:3306/slick", "root", "123",

    driver = "com.mysql.jdbc.Driver") withSession {

      implicit session =>

        // Create the tables, including primary and foreign keys

        //按顺序创建表

        (suppliers.ddl ++ coffees.ddl).create

        // Insert some suppliers

        //插入操作，集合的方式

        suppliers += (101, "Acme, Inc.", "99 Market Street", "Groundsville", "CA", "95199")

        suppliers += (49, "Superior Coffee", "1 Party Place", "Mendocino", "CA", "95460")

        suppliers += (150, "The High Ground", "100 Coffee Lane", "Meadows", "CA", "93966")

        // Insert some coffees (using JDBC's batch insert feature, if supported by the DB)

        //批量插入操作，集合方式

        coffees ++= Seq(

          ("Colombian", 101, 7.99, 0, 0),

          ("French_Roast", 49, 8.99, 0, 0),

          ("Espresso", 150, 9.99, 0, 0),

          ("Colombian_Decaf", 101, 8.99, 0, 0),

          ("French_Roast_Decaf", 49, 9.99, 0, 0))

       //返回表中全部数据，相当于SELECT * FROM COFFEES

       coffees foreach {

          case (name, supID, price, sales, total) =>

            println("  " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" + total)

        }

       //返回表中全部数据。仅仅只是这次是直接让数据库帮我们进行转换

        val q1 = for (c <- coffees)

          yield LiteralColumn("  ") ++ c.name ++ "\t" ++ c.supID.asColumnOf[String] ++

          "\t" ++ c.price.asColumnOf[String] ++ "\t" ++ c.sales.asColumnOf[String] ++

          "\t" ++ c.total.asColumnOf[String]

        // The first string constant needs to be lifted manually to a LiteralColumn

        // so that the proper ++ operator is found

        q1 foreach println

        //联合查询

        //採用===进行比較操作，而非==操作符。用于进行值比較

        //相同的还有!=值不等比較符

        //甚至其他比較操作符与scala是一致的 <, <=, >=, >

         val q2 = for {

          c <- coffees if c.price < 9.0

          s <- suppliers if s.id === c.supID

        } yield (c.name, s.name)

}

5. SQL与Slick相互转换

package cn.scala.xtwy

import scala.slick.driver.MySQLDriver.simple._

import scala.slick.jdbc.StaticQuery.interpolation

import scala.slick.jdbc.GetResult

object SQLAndSlick extends App {

  type Person = (Int, String, Int, Int)

  class People(tag: Tag) extends Table[Person](tag, "PERSON") {

    def id = column[Int]("ID", O.PrimaryKey)

    def name = column[String]("NAME")

    def age = column[Int]("AGE")

    def addressId = column[Int]("ADDRESS_ID")

    def * = (id, name, age, addressId)

    def address = foreignKey("ADDRESS", addressId, addresses)(_.id)

  }

  lazy val people = TableQuery[People]

  type Address = (Int, String, String)

  class Addresses(tag: Tag) extends Table[Address](tag, "ADDRESS") {

    def id = column[Int]("ID", O.PrimaryKey)

    def street = column[String]("STREET")

    def city = column[String]("CITY")

    def * = (id, street, city)

  }

  lazy val addresses = TableQuery[Addresses]

  val dbUrl = "jdbc:mysql://localhost:3306/slick"

  val jdbcDriver = "com.mysql.jdbc.Driver"

  val user = "root"

  val password = "123"

  val db = Database.forURL(dbUrl, user, password, driver = jdbcDriver)

  db.withSession { implicit session =>

    (people.ddl ++ addresses.ddl).create

    //插入address数据

    addresses += (23, "文一西路", "浙江省杭州市")

    addresses += (41, "紫荆花路", "浙江省杭州市")

    //插入people数据

    people += (1, "摇摆少年梦", 27, 23)

    people += (2, "john", 28, 41)

    people += (3, "stephen", 28, 23)

    //以下两条语句是等同的（获取固定几个字段）

    val query = sql"select ID, NAME, AGE from PERSON".as[(Int, String, Int)]

    query.list.foreach(x => println("id=" + x._1 + " name=" + x._2 + " age=" + x._3))

    val query2 = people.map(p => (p.id, p.name, p.age))

    query2.list.foreach(x => println("id=" + x._1 + " name=" + x._2 + " age=" + x._3))

    //以下两条语句是等同的(Where语句）

    val query3 = sql"select * from PERSON where AGE >= 18 AND NAME = '摇摆少年梦'".as[Person]

    query3.list.foreach(x => println("id=" + x._1 + " name=" + x._2 + " age=" + x._3))

    val query4 = people.filter(p => p.age >= 18 && p.name === "摇摆少年梦")

    query4.list.foreach(x => println("id=" + x._1 + " name=" + x._2 + " age=" + x._3))

    //orderBy

    sql"select * from PERSON order by AGE asc, NAME".as[Person].list

    people.sortBy(p => (p.age.asc, p.name)).list

    //max

    sql"select max(AGE) from PERSON".as[Option[Int]].first

    people.map(_.age).max

    //隐式join

    sql"""

  select P.NAME, A.CITY

  from PERSON P, ADDRESS A

  where P.ADDRESS_ID = a.id

""".as[(String, String)].list

    people.flatMap(p =>

      addresses.filter(a => p.addressId === a.id)

        .map(a => (p.name, a.city))).run

    // or equivalent for-expression:

    (for (

      p <- people;

      a <- addresses if p.addressId === a.id

    ) yield (p.name, a.city)).run

    //join操作

    sql"""select P.NAME, A.CITY from PERSON P join ADDRESS A on P.ADDRESS_ID = a.id """.as[(String, String)].list

    (people join addresses on (_.addressId === _.id))

  .map{ case (p, a) => (p.name, a.city) }.run

  }

}

上面列出的仅仅是Slick与SQL的部分转换，还有诸如:Update、Delete等操作能够參见：http://slick.typesafe.com/doc/2.1.0/sql-to-slick.html

加入公众微信号，能够了解很多其他最新Spark、Scala相关技术资讯

最新文章

  1. Oracle学习笔记十三 触发器
  2. 基于ArcGIS API for Javascript的地图编辑工具
  3. 为你的网站或App提供免费Https支持
  4. warning MSB3391
  5. 第十八章 数据访问(In .net4.5) 之 I/O操作
  6. css3 钟表
  7. hibernate的n+1问题
  8. VO,DTO,DO,PO的划分
  9. Python自动化运维之5、内置函数
  10. iOS-UITableviewcell分割线位置
  11. echarts使用笔记二:柱子堆叠
  12. C# System.Collections.Queue
  13. Windows如何安装Android SDK
  14. TRichEdit怎样新增的内容到最后一行？
  15. Facebook广告API系列 2 - Audience Management
  16. git/github 生成密钥
  17. windows下配置nginx+php环境(转)
  18. wait(),notify(),notifyAll()用来操作线程为什么定义在Object类中？
  19. MVC视图布局页常用代码
  20. JDBC（1）简单介绍/数据库的连接

热门文章

  1. Quart 学习
  2. 由安装两块网卡的linux系统中引起网络不通想到的
  3. HDU 4513 吉哥系列故事――完美队形II
  4. shell中IF的用法介绍
  5. PHP 相关配置
  6. 51nod 01背包
  7. Swift vs C# Go OC
  8. 怎样用redis实现分布式锁
  9. jquery源码03 (3184 , 3295) support : 功能检测
  10. Anders Hejlsberg 和 Erich Gamma