花费了两天从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)
}
}
}