Scala操作MySQL数据库

桃扇骨 2023-06-26 03:29 18阅读 0赞

一、工具IDEA+MAVEN

二、Pom文件添加依赖  
1、更改成自己的scala版本

2、添加驱动依赖

<dependency>  
      <groupId>mysql</groupId>  
      <artifactId>mysql-connector-java</artifactId>  
      <version>5.1.12</version>  
    </dependency>

三、代码书写  
1、DBUtils

package com.encapsulation  
import java.sql  
import java.sql.\{Connection, DriverManager\}  
/\*\*  
  \* Created by A chun on 2017/10/18.  
  \*/  
object DBUtils \{  
  val IP = "192.168.187.111"  
  val Port = "3306"  
  val DBType = "mysql"  
  val DBName = "scala"  
  val username = "root"  
  val password = "123456"  
  val url = "jdbc:" + DBType + "://" + IP + ":" + Port + "/" + DBName  
  classOf\[com.mysql.jdbc.Driver\]

def getConnection(): Connection = \{  
    DriverManager.getConnection(url, username, password)  
  \}  
  def close(conn: Connection): Unit = \{  
    try \{  
      if (!conn.isClosed() || conn != null) \{  
        conn.close()  
      \}  
    \}  
    catch \{  
      case ex: Exception => \{  
        ex.printStackTrace()  
      \}  
    \}  
  \}  
\}  
2、Operations

package com.encapsulation  
import com.encapsulation.DBUtils.close

import scala.collection.mutable  
import scala.collection.mutable.ArrayBuffer  
class Operations \{  
  case class User(id: String, name: String, age: Int)  
  //insert  
  def add(user: User): Boolean = \{  
    val conn = DBUtils.getConnection()  
    try \{  
      val sql = new StringBuilder()  
        .append("INSERT INTO user(id, name, age)")  
        .append("     VALUES(?, ?, ?)")  
      val pstm = conn.prepareStatement(sql.toString())  
      pstm.setObject(1, user.id)  
      pstm.setObject(2, user.name)  
      pstm.setObject(3, user.age)

pstm.executeUpdate() > 0  
    \}  
    finally \{  
      conn.close()  
    \}  
  \}  
  //delete  
  def delete(id: String): Boolean = \{  
    val conn = DBUtils.getConnection()  
    try \{  
      val sql = "DELETE FROM user WHERE id = ?"  
      val pstm = conn.prepareStatement(sql)  
      pstm.setObject(1, id)  
      pstm.executeUpdate() > 0  
    \}  
    finally \{  
      conn.close()  
    \}  
  \}  
  //update  
  def modify(user: User): Boolean = \{  
    val conn = DBUtils.getConnection()  
    try \{  
      val sql = "UPDATE user SET age = ? WHERE id = ?"  
      val pstm = conn.prepareStatement(sql)  
      pstm.setObject(1, user.age)  
      pstm.setObject(2, user.id)

pstm.executeUpdate() > 0  
    \}  
    finally \{  
      conn.close()  
    \}  
  \}  
  //select  
  def query(id: Int): ArrayBuffer\[mutable.HashMap\[String, Any\]\] = \{  
    val conn = DBUtils.getConnection()  
    try \{  
      val sql = new StringBuilder()  
        .append("SELECT name, age")  
        .append("  FROM user")  
        .append(" WHERE id >  ?")  
      val pstm = conn.prepareStatement(sql.toString())  
      pstm.setObject(1, id)  
      val rs = pstm.executeQuery()  
      val rsmd = rs.getMetaData()  
      val size = rsmd.getColumnCount()  
      val buffer = new ArrayBuffer\[mutable.HashMap\[String, Any\]\]()  
      while (rs.next()) \{  
        val map = mutable.HashMap\[String, Any\]()  
        for (i <- 1 to size) \{  
          map += (rsmd.getColumnLabel(i) -> rs.getString(i))  
        \}  
        buffer += map  
      \}

buffer  
    \}  
    finally \{  
      conn.close()  
    \}  
  \}  
\}  
3、MySQLOperations

package com.encapsulation

object MySQLOperations \{  
  def main(args: Array\[String\]): Unit = \{  
    val op = new Operations()  
    val user = op.User("5", "Allen", 34)  
    //Insert  
    //println(op.add(user))

//Delete  
    println(op.delete("5"))

//update  
    //println(op.update(user))  
  \}  
\}