ScalaQuery

花费了两天从20多篇英文文章总结出这100多行代码,10几种情况

问题1.scala版本

问题2.sbt版本

问题3.scalaquery版本

问题4.从maven同步jar

这些都是最头疼的

import org.scalaquery.ql._
import org.scalaquery.simple._
import org.scalaquery.session._
import org.scalaquery.session.Database.threadLocalSession
import org.scalaquery._
import org.scalaquery.ql.extended.{ExtendedTable => Table}

import org.scalaquery.simple._
import org.scalaquery.simple.StaticQuery._

object MySqlConn{
    //forURL(url:String, user:String = null, password:String = null, prop: Properties = null, driver:String = null):
	val db =  Database.forURL("jdbc:mysql://localhost/playdb",user = "root",password = "111111",driver = "com.mysql.jdbc.Driver")

	val Suppliers = new Table[(Int,String,String,String,String,String)]("SUPPLIERS"){
		def id = column[Int]("SUP_ID",O.PrimaryKey)
		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")
		def * = id ~ name ~ street ~ city ~state ~ zip
		def nameConstraint = index("SUP_NAME_INDEX",id,true)
	}

	val Coffees = new Table[(Int,String,Int,Double,Int,Int)]("COFFEES"){
		def id = column[Int]("COF_ID")
		def name = column[String]("COF_NAME")
		def supID = column[Int]("SUP_ID")
		def price = column[Double]("PRICE")
		def sales = column[Int]("SALES")
		def total = column[Int]("TOTAL")
		def * = id ~ name ~ supID ~ price ~ sales ~ total 
		def suppliers = foreignKey("SUP_FK",supID,Suppliers)(_.id)
		def pk = primaryKey("COF_NAME_PK",id)
		//级联操作
		//import org.scalaquery.ql.extended.Query
		//def supplier = Suppliers.where(_.id == supID)
		//def cheaperThan(d:Double) = this.where(_.price < d)
	}
	
	def main(args: Array[String]) {
	   db withSession {
	   	  //Version scala版本和mysql版本得限制
	      import org.scalaquery.ql.extended.MySQLDriver.Implicit._
	      //版本问题无法删除表
	      //(Coffees.ddl ++ Suppliers.ddl).drop
	      //(Suppliers.ddl ++ Coffees.ddl).create
	      
	      //1.增加数据
	      /*Suppliers.insert(101, "Acme, Inc.",      "99 Market Street", "Groundsville", "CA", "95199")
	      Suppliers.insert( 49, "Superior Coffee", "1 Party Place",    "Mendocino",    "CA", "95460")
	      Suppliers.insert(150, "The High Ground", "100 Coffee Lane",  "Meadows",      "CA", "93966")*/
	      /*Coffees.insertAll(
	        (21,"Colombian",         101, 7.99, 0, 0),
	        (11,"French_Roast",       49, 8.99, 0, 0),
	        (1, "Espresso",          150, 9.99, 0, 0),
	        (33,"Colombian_Decaf",   101, 8.99, 0, 0),
	        (78,"French_Roast_Decaf", 49, 9.99, 0, 0)
	      )*/
	      //2.查询数据
	      /*val temp1 = for {
				c <- Coffees if c.price < 9.0
				s <- Suppliers if s.id === c.supID
		  } yield c.name ~ s.name*/
		  /*val temp2 = for{
		  	c <- Coffees.cheaperThan(9.0)
		  	s <- c.supplier
		  } yield c.name ~ s.name*/

		  /*for(temp <- temp2)
		     println("temp:" + "Coffees--" + temp._1 + "Suppliers--" + temp._2)*/
		  /*for(temp <- temp1)
		     println("temp:" + "Coffees--" + temp._1 + "Suppliers--" + temp._2)*/
		  //3.使用Query查询
		   /*val temp3 = for{
				c <- Coffees if c.price < 9.0
				s <- Suppliers if s.id === c.supID
				_ <- Query groupBy s.id
				_ <- Query orderBy c.name.count

		   } yield s.id ~ s.name.min.get ~ c.name.count */

		   /*for(temp <- temp3)
		     println("temp:" + "su.id--" + temp._1 + "su.name--" + temp._2 + "cof.name--" + temp._3)*/
	       //4.分页操作,在scala中分页是so easy
	       //temp3.drop(a).take(b)
	       //5.调试状态
	       //temp3.dump("q:")
		   //6.绑定参数 Bind Variables
		   /*def coffeesForSupplier(supId: Int) = for{
		   	 c <- Coffees if c.supID === supId
		   } yield c.name
		   val temp4 = coffeesForSupplier(101)
		   for(temp <- temp4) 
		   					 println(temp)*/
		   //7.Query Templates					 
		   /*def coffeesForSupplier = for{
		   	 supId <- Parameters[Int]
		   	 c <- Coffees if c.supID === supId
		   } yield c.name
		   val temp5 = coffeesForSupplier(101).list
		   for(temp <- temp5) 
		   					 println(temp)*/ 
		   	//8.删除数据
		   	//val q = Coffees.where(_.supID === 49)		
		   	//q.delete
		   	//println(q.deleteStatement)
		   	//9.更新数据	
		   	//val q2 =  q.map(_.supID)  
		   	//q2.update(101)	
		   	//println(q2.updateStatement)	
		   	//10.级联操作
		   	//Unions
		   	val q1 = Coffees.filter(_.supID == 45)
		   	val q2 = Coffees.filter(_.supID == 150)
		   	val q3 = q1 unionAll q2
		   	//11.Explicit Inner Joins
		   	for(Join(c,s) <- Coffees innerJoin Suppliers on (_.supID === _.id)) yield c.name ~ s.name
		   	//12.Left Outer Joins
		   	for(Join(c,s) <- Coffees leftJoin Suppliers on(_.supID === _.id)) yield c.name ~ s.name
		   	//13.Option Lifting
		   	for(Join(c,s) <- Coffees leftJoin Suppliers on(_.supID === _.id)) yield c.name.? ~ s.name.?
		   	//14.Right Outer Joins
		   	for(Join(c,s) <- Coffees rightJoin Suppliers on(_.supID === _.id)) yield c.name.? ~ s.name.?
	    	//15.ull Outer Joins
	    	for(Join(c,s) <- Coffees outerJoin Suppliers on(_.supID === _.id)) yield c.name.? ~ s.name.?
	    	//16.Case
	    	for{c <- Coffees} yield (Case when c.price < 8.0 then "cheaper"
	    		when c.price < 9.0 then "medium"
	    		otherwise "expense") ~ c.name
	    	//17.Static Queries
			def supplierNameForCoffee(name: String) =
			query[String, String]("""
			select s.sup_name from suppliers s, coffees c
			where c.cof_name = ? and c.sup_id = s.sup_id
			""").firstOption(name)
			//18静态查询高级
			case class Coffee(name: String, supID: Int, price: Double)
			implicit val getCoffeeResult = GetResult(r => Coffee(r<<, r<<, r<<))
			def coffeesInPriceRange(min: Double, max: Double) =
			query[(Double, Double), (String, Int, Double)]("""
			select cof_name, sup_id, price from coffees
			where price >= ? and price <= ?
			""").list(min, max)
	    }
	}
	
}

源码下载