mysql asyn 樣本

標籤：

這篇文章摘自mysql asyn作者的部落格，部落格地址

 

開頭有一個簡單樣本，然後是一個在play上的應用。例子我並沒有跑過，但是仍能學到不少東西。

object BasicExample {    def main(args: Array[String]) {        // 這個Parser我發現好像用不了。。        val configuration = URLParser.parse("")        val connection: Connection = new PostgreSQLConnection(configuration)        //阻塞等待串連        Await.result(connection.connect, 5 seconds)        //sendQuery直接就執行了，不像slick那樣，還要單獨的run        val future: Future[QueryResult] = connection.sendQuery("SELECT * FROM USERS")        val mapResult: Future[Any] = future.map(queryResult => queryResult.rows match {            case Some(resultSet) => {                val row: RowDat = resultSet.head                row(0)            }            //注意，這裡的-1是Any而不是Future[Any]            case None => -1        })        val result = Await.result(mapResult, 5 seconds)        println(result)        //關閉資料庫連結        connection.disconnect    }}

The basic usage pattern is quite simple, you ask for something, you get a future[_] back. The PostgreSQLConnection is a real connection to database. it implements the Connection trait and you should try to use the trait as much as possible. 

When you create a connection handler, it‘s not connected to the db yet, you have to connect it yourself calling connect and waiting for the future to return or composing on the future to do something else.

 

下面是一個play app. 包括MVC模型。因為Controller牽扯了太多play的知識，我就暫時不抄了，只把持久化部分寫上。

這個持久化實現了ResultSet到case class的mapping，雖然是手動進行的，但是寫的非常好。如果能使用implicit寫mapping應該會更加優雅。

此外，一個play app應該選用connection pool而不是每次用到資料庫都重建立立串連。下面給出了用法。

// 需要提前建立好資料庫，id應該是自增的case class Message(id: Option[Long], content: String: LocalDate = LocalDate.now())object MessageRepository {    val Insert = "INSERT INTO messages (content, moment) VALUES(?, ?)"    val Update = "UPDATE messages SET content = ?, moment = ? WHERE id = ?"    val Select = "SELECT id, content, moment FROM messages ORDER BY id asc"    val SelectOne = "SELECT id, content, momment FROM messages WHERE id = ?"}// 這個有點dependency injection的意思class MessageRepository（pool: Content) {    import MessageRepository._    def save(m: Message): Future[Message] = {        // queryResult => m 是什麼意思        case Some(id) => pool.sendPreparedStatement(Update, Array(m.content, m.moment, id)).            map { queryResult => m }        case None => pool.sendPreparedStatement(Insert, Array(m.content, m.moment)).            map { queryResult => m }    }    def list: Future[IndexSeq[Message]] = {        pool.sendQuery(Select). map {            //rows 返回resultSet, get返回什麼呢，返回的是 Iterator 類型的東西麼            queryResult => queryResult.rows.get.map {                item => rowToMessage(item)            }        }    }    def find(id: Long): Future[Option[Message]] = {        //[Any] 非得加麼        pool.sendPreparedStatement(SelectOne, Array[Any](id)).map {            queryResult =>                queryResult.rows match {                    case Some(rows) =>                         Some(rowToMessage(rows.apply(0)))                    case None => None                }        }    }    private def rowToMessage(row: RowData): Message = {        new Message(            id = Some(row("id".asInstanceOf[Long]))            content = row("content").asInstanceOf[String]            moment = row("moment").asInstanceOf[LocalDate]        )    }}

 

對於mysql的每一張表，都應該有一個這樣的插入語句，對於多表join的情況，可能要單獨處理吧。

上面實現了DAO，下面一小段代碼可以充當設定檔來用，相當於dependency injection

object Global extends GlobalSettings {    private val databaseConfiguration = System.getenv("DATABASE_URL") match {        case url: String => URLParser.parse(url)        case _ => new Configuration(            username = "postgres"            database = Some("databasename")            port     = 5433        )    }    // factory 還有mysql專用版麼    private val factory = new PostgreSQLFactory(databaseConfiguration)    private val pool    = new ConnectionPool(factory, PoolConfiguration.Default)    val messageRepository = new MessageRepository( pool )//    play 的東西，普通的程式不曉得如何處理close問題    override def onStop(app: Application)        pool.close}

對於一般的程式，用connectionPool要好一點，但是要注意，不能直接在connectionPool上應用transacation。當需要用到transacation時，從connectionPool中擷取一個connection，還要記得還回去。

 

mysql asyn 樣本