anrom SQL 开发

1)mysql

DROP TABLE IF EXISTS `alihc_ad_spider_db`.`ad_spider_info`;
CREATE TABLE  `alihc_ad_spider_db`.`ad_spider_info` (
  `infoid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `urlPath` varchar(255) NOT NULL,
  `addTime` datetime DEFAULT NULL,
  `visitSalerId` bigint(20) unsigned DEFAULT NULL,
  `visitSaler` varchar(45) DEFAULT NULL,
  `comForm` varchar(45) DEFAULT NULL,
  `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`infoid`)
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8;

2)application.conf

db.default.driver=com.mysql.jdbc.Driver  
db.default.url="jdbc:mysql://localhost:3306/db?characterEncoding=UTF-8"  
db.default.user="root"  
db.default.password="123"  

3)routes

GET     /adview_add_test                         controllers.AdViewTest.testadd

4)/modoule

package models


import java.util.Date
import scala.collection.immutable.HashSet
import scala.collection.Set
/*
CREATE TABLE `alibaba_ad_db`.`ad_spider_info` (
  `infoid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `urlPath` VARCHAR(255) NOT NULL,
  `addTime` DATETIME NOT NULL,
  `visitSalerId` BIGINT UNSIGNED,
  `visitSaler` VARCHAR(45),
  `comForm` VARCHAR(45),
  `updateTime` TIMESTAMP NOT NULL,
  PRIMARY KEY (`infoid`)
)
ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_general_ci;

 */

case  class  AdSpiderInfo()
 {

   var  infoid :Long=0

   var  urlPath:String=""

   var  visitSalerId:Long=0


   var visitSaler:String=""

   var  comForm:String=""

   var updateTime:Date=new Date(System.currentTimeMillis())

   var addTime:Date=new Date(System.currentTimeMillis())

   def this(  infoid:Long,urlPath:String,visitSalerId:Long, visitSaler:String,comForm:String, addTime:Date,updateTime:Date){
     this()
      this.infoid=infoid
     this.urlPath=urlPath
     this.visitSalerId=visitSalerId
     this.visitSaler=visitSaler
     this.comForm=comForm
     this.addTime=addTime
     this.updateTime=updateTime

   }

    object AdSpiderInfo{

        var adlist = new HashSet( )

        def findAll{
            println("get adlist,")

          adlist
        }
    }

}

5)dao

package models

import anorm._
import models.AdSpiderInfo
import play.api.db.DB

import scala.collection.JavaConverters._
import scala.collection.immutable.List
import java.util.Date


import play.api.Play.current
/*

db lib: anorm


 */


object AdSpiderInfoDao{

    def save(entry: AdSpiderInfo): Unit ={


    }

  def show= {

    val conn = DB.getConnection()

    println("dbName:" + conn.getCatalog)
    //val result: Boolean = SQL("Select count(*) from  ad_spider_info").execute()


  }

  def count= {
    var countvl:Long =0
    DB.withConnection { implicit c  =>


      val firstRow = SQL("Select count(*) as c from ad_spider_info").apply().head
      val countryCount = firstRow[Long]("c")
      // println("count:" + countryCount)
      countvl=countryCount
    }
    countvl
  }

    //add data
  //test : ok
    def add(e:AdSpiderInfo):Boolean = {

      DB.withConnection {implicit c  =>

   
  val rs= SQL(
      """insert into
     ad_spider_info(urlPath, comForm,addTime,updateTime)
     values ({urlPath}, {comForm} ,{addTime},{updateTime})
      """)
     .on(
       "urlPath"->e.urlPath,
       "comForm"->e.comForm,
      //
       "addTime"->e.addTime,
      //
       //
       "updateTime"->None
     )
  .executeInsert()
        rs ==1
    }

    }

  def update(e:AdSpiderInfo):Boolean = {

    DB.withConnection {implicit c  =>


      val rs= SQL(
        """update
     ad_spider_info set urlPath={urlPath}, comFrome={comFrome},addTime={addTime},updateTime={updateTime} where infoid={infoid}
        """)
        .on(
          "urlPath"->e.urlPath,
          "comFrome"->e.comForm,
          "addTime"->e.addTime,
          "updateTime"->e.updateTime,
          "id"->e.infoid
        )
      .executeUpdate()
      rs == 1
    }

  }



  def delete(infoid:Long):Boolean = {

    DB.withConnection {implicit c  =>


      val rs= SQL(
        """delete from  ad_spider_info where infoid={infoid}
        """)
        .on(

          "id"->infoid
        )
       .executeUpdate()
      rs == 0
    }

  }

  /*
*/
  //query ad
  def getAdById(infoid: Long): AdSpiderInfoView = {

    var adSpiderInfo:AdSpiderInfoView=null

      DB.withConnection { implicit conn =>


      val sql: SqlQuery = SQL("Select * from  ad_spider_info where infoid={infoid} order by infoid desc")
//var infoid :Long, var urlPath:String,
//var addTime:Date,var visitSalerId:Long, var visitSaler:String,  var comForm:String, var updateTime:Date
     val list:List[AdSpiderInfoView]= sql().map(row =>
  AdSpiderInfoView(row[Long]("infoid"), row[String]("urlPath")
          ,row[Long]("visitSalerId"),
            row[String]("visitSaler"),row[String]("comForm"),row[Date]("updateTime"), row[Date]("addTime"))
        ).toList

      adSpiderInfo=   list.head
    }

    adSpiderInfo
  }

  //ad list page index

    def getListByPageIndex(index:Int, pageCount:Int,comForm:String): List[AdSpiderInfoView] ={

      val start:Int=(index-1)*pageCount

      var adSpiderInfolist: List[AdSpiderInfoView]=null

      DB.withConnection { implicit c =>


        val sql: SimpleSql[Row] = SQL("Select  * from  ad_spider_info  limit {start}, {pageCount} where comForm={comForm}")
        .on(
          "start"->start,
          "pageCount"->pageCount,
         "comForm"->comForm

        )
        //var infoid :Long, var urlPath:String,
        //var addTime:Date,var visitSalerId:Long, var visitSaler:String,  var comForm:String, var updateTime:Date
        val list:List[AdSpiderInfoView]= sql().map(row =>
          AdSpiderInfoView(row[Long]("infoid"), row[String]("urlPath")
            ,row[Long]("visitSalerId"),
            row[String]("visitSaler"),row[String]("comForm"), row[Date]("addTime"),row[Date]("updateTime"))
        ).toList

        adSpiderInfolist=   list
      }

      adSpiderInfolist
    }


  def getListAll(): List[AdSpiderInfoView]= {

    var adSpiderInfolist: List[AdSpiderInfoView]=null

    DB.withConnection { implicit c =>

      val sql: SqlQuery = SQL("Select  * from  ad_spider_info  order by infoid desc")

      //var infoid :Long, var urlPath:String,
      //var addTime:Date,var visitSalerId:Long, var visitSaler:String,  var comForm:String, var updateTime:Date
      val list: List[AdSpiderInfoView] = sql().map(row =>
        AdSpiderInfoView(row[Long]("infoid"), row[String]("urlPath")
          , row[Long]("visitSalerId"),
          row[String]("visitSaler"), row[String]("comForm"), row[Date]("addTime"), row[Date]("updateTime"))
      ).toList

      adSpiderInfolist = list
    }
      adSpiderInfolist
  }


  def  main (argvs:Array [String]){
     var  ifo=new AdSpiderInfo()
    ifo.urlPath="http://aabbcc.com/test.html"

    ifo.comForm="ali"

    this.add(ifo)

    println("aabbcc")
  }
}

6)controller

/**
 * Created by Administrator on 15-11-18.
 */
package  controllers

import java.util.Date

import controllers.AdView._
import models.{AdSpiderInfo, AdSpiderInfoDao}
import play.api.mvc.Action
import play.api.mvc.Controller
object AdViewTest extends  {

  def testadd=Action{

    var dao= AdSpiderInfoDao

    var  ifo=new AdSpiderInfo()
    ifo.urlPath="http://aabbcc.com/test.html"

    ifo.comForm="ali"

    var date=new Date(System.currentTimeMillis())

      ifo.addTime=date

    dao.add(ifo)

    var acount:Long=10
    Ok(views.html.adlist.render("AdSpider","{ifo}",acount) )
  }
}