Scala—连接mysql

前置

打开装有mysql的机器（虚拟机）

新建一个scala项目，新建一个类

添加mysql依赖包

找到架包位置，添加

编写程序

连接

package com.cp.mysqlstu

import java.sql.Connection


class MysqlDemo {

  var driver="com.mysql.cj.jdbc.Driver"
  var url = "jdbc:mysql://192.168.153.141:3306"
  var user = "root"
  var password = "root"

  def this(driver: String,url:String,user:String,pwd:String){
    this()
    this.driver = driver
    this.url = url
    this.user = user
    this.password = pwd
  }
}

object MysqlDemo{
  def apply(): MysqlDemo = new MysqlDemo()

  def apply(driver: String,url:String,user:String,pwd:String): MysqlDemo = new MysqlDemo(driver, url, user, pwd)

  def main(args: Array[String]): Unit = {
    val demo = MysqlDemo()
    import MysqlUtil._
    val connection: Connection = demo.conn()
//打印connection
    println(connection)

    demo.close(connection)
  }
}

新建对象，创建隐式类

package com.cp.mysqlstu

import java.sql.{Connection,DriverManager}

object MysqlUtil {
  implicit class MysqlOp(obj:MysqlDemo){

    //获取connection
    private var connection:Connection = _

    def conn():Connection={
      Class.forName(obj.driver)
      val connection:Connection = DriverManager.getConnection(obj.url,obj.user,obj.password)
      connection
    }

    def close(conn:Connection):Unit={
      if(conn!=null)
        conn.close()
    }
  }
}

结果

连接表

在隐式类添加方法

增加

  def insertStudent():Int={
      val insertSqlStr="insert into student(name, age, gender, telephone, email, classid) values ('cp',25,'男',11111111111,'123@123.com',11)"
      val i:Int = conn().createStatement().executeUpdate(insertSqlStr)// 返回影响的行数
      i
    }

val i:Int = demo.insertStudent()
println(i)

def insertStudent(name:String,age:Int,gender:String,telephone:String,email:String,classId:Int):Int={
      val insertSqlStr = "insert into student(name, age, gender, telephone, email, classid) values(?,?,?,?,?,?)"

      val preStmt: PreparedStatement = conn().prepareStatement(insertSqlStr)
      preStmt.setString(1,name)
      preStmt.setInt(2,age)
      preStmt.setString(3,gender)
      preStmt.setString(4,telephone)
      preStmt.setString(5,email)
      preStmt.setInt(6,classId)
      val i:Int = preStmt.executeUpdate()
      i
    }

val i:Int = demo.insertStudent("sanQ",1,"女","13131313131","sq@ad.com",22)
    println(i)
    demo.close(connection)

查

def selectStudent():Unit={
  val selectStr = "select id, name, age, gender, telephone, email, classid from student"
  val rs: ResultSet = conn().createStatement().executeQuery(selectStr)
  println("学号\t姓名\t年龄\t性别\t手机\t邮箱\t班级编号")
  while (rs.next()){
    val id: Int = rs.getInt("id")
    val name: String = rs.getString("name")
    val age: Int = rs.getInt("age")
    val gender: String = rs.getString("gender")
    val telephone: String = rs.getString("telephone")
    val email: String = rs.getString("email")
    val classid: Int = rs.getInt("id")
    println(id + "\t" + name + "\t" + age + "\t" + gender + "\t" + telephone + "\t" + email + "\t" + classid )
  }
}

demo.selectStudent()
demo.close(connection)

改

def updateStudent():Int={
  val updateSqlStr = "update student set name = '三清' where id = 7"
  val i: Int = conn().createStatement().executeUpdate(updateSqlStr)
  i
}

demo.updateStudent()
demo.close(connection)