scala 连接 MySQL 数据库案例

1 依赖准备

mysql 8添加：

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

mysql 5 添加：

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

当然为了避免下载，可以直接去你的本地仓库查看你以前下过什么版本的依赖

2 连接 mysql 数据库并进行数据操作

        这里的driver 换成对应自己版本的驱动

        mysql 8 就是 com.mysql.cj.jdbc.Driver

        mysql 5 就是 com.mysql.jdbc.Driver

        这里的 url 一样换成自己的，若是在服务器上就换成服务器 ip，若在本地就用 localhost 就可以

        这里只要能打印出连接，能添加进数据就算成功，其余的无非是 Java 连接数据库的操作了，大差不差了 ，有兴趣的可以自己往下加需求

        这里的的数据库结构是

        建表语句：

show databases ;

create database kb23test;
use kb23test;
create table student(
    id int,
    name varchar(32),
    age int
);

select * from student;

package mysqlstu

import java.sql.{Connection, DriverManager, PreparedStatement, ResultSet}

class MysqlDemo {
//  var driver : String = "com.mysql.jdbc.Driver"
  var driver : String = "com.mysql.cj.jdbc.Driver"
//  var url = "jdbc:mysql://127.0.0.1:3306"
  var url = "jdbc:mysql://39.107.230.113:3306"
  var user = "root"
  var pwd = "100860"

  var connection : Connection = null


  def this(driver : String,url : String,user : String,pwd : String){
    this()
    this.driver = driver
    this.url = url
    this.user = user
    this.pwd = pwd
  }
  def conn : Connection={
    Class.forName(driver)
    connection = DriverManager.getConnection(url,user,pwd)
    connection
  }

  def insert():Int = {
    val insertSql = "insert into kb23test.student(id,name,age) values(1,'jojo',12)"
    if (connection == null) conn
    val i : Int = connection.createStatement().executeUpdate(insertSql)
    i
  }
  def insert(id:Int,name:String,age:Int):Int = {
    val insertSql = "insert into kb23test.student(id,name,age) values(?,?,?)"
    if (connection == null) conn
    val pstm : PreparedStatement = connection.prepareStatement(insertSql)
    pstm.setInt(1,id)
    pstm.setString(2,name)
    pstm.setInt(3,age)
    val i : Int = pstm.executeUpdate()
    i
  }

  def update(id:Int,name:String,age:Int):Int = {
    val insertSql = "update kb23test.student set name = ?,age = ? where id = ?"
    if (connection == null) conn
    val pstm : PreparedStatement = connection.prepareStatement(insertSql)
    pstm.setInt(3,id)
    pstm.setString(1,name)
    pstm.setInt(2,age)
    val i : Int = pstm.executeUpdate()
    i
  }

  def select():Unit={
    val selectSql = "select id,name,age from kb23test.student"
    if (connection == null)conn
    val rs : ResultSet = connection.createStatement().executeQuery(selectSql)
    while(rs.next()){
      val id : Int = rs.getInt("id")
      val name : String = rs.getString("name")
      val age : Int = rs.getInt("age")
      println("学号：%d 姓名：%s 年龄：%d".format(id,name,age))
    }
  }
  def select(name:String):Unit={
    val selectSql = "select id,name,age from kb23test.student where name like concat('%',?,'%')"
    if (connection == null)conn
    val pstm = connection.prepareStatement(selectSql)
    pstm.setString(1,name)
    val rs : ResultSet = pstm.executeQuery()
    while(rs.next()){
      val id : Int = rs.getInt("id")
      val name : String = rs.getString("name")
      val age : Int = rs.getInt("age")
      println("学号：%d 姓名：%s 年龄：%d".format(id,name,age))
    }
  }
}


object MysqlDemo{
  def main(args: Array[String]): Unit = {
    val mysqlDemo = new MysqlDemo
    println(mysqlDemo.conn)

//    println(mysqlDemo.insert())
//    println(mysqlDemo.insert(2,"dio",142))
//    println(mysqlDemo.update(2,"dio",112))
//    mysqlDemo.select("d")
    mysqlDemo.select()
  }
}

3 隐式类形式去实现连接 mysql

package mysqlstu

import java.sql.Connection

class MysqlDemo2 {
  //  var driver : String = "com.mysql.jdbc.Driver"
  var driver: String = "com.mysql.cj.jdbc.Driver"
  //  var url = "jdbc:mysql://127.0.0.1:3306"
  var url = "jdbc:mysql://39.107.230.113:3306"
  var user = "root"
  var pwd = "100860"


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

}

object MysqlDemo2 {
  def main(args: Array[String]): Unit = {
    val demo = new MysqlDemo2()
    import mysqlstu.MysqlUtils._

    demo.insert(3,"kk",11)
  }
}

package mysqlstu

import java.sql.{Connection, DriverManager, PreparedStatement}

object MysqlUtils {
  implicit class Mysqlop(mysqlDemo2: MysqlDemo2){
    private var connection:Connection = _
    private def conn():Unit={
      Class.forName(mysqlDemo2.driver)
      connection = DriverManager.getConnection(mysqlDemo2.url,mysqlDemo2.user,mysqlDemo2.pwd)
    }

    def insert(id:Int,name:String,age:Int):Int = {
      val insertSql = "insert into kb23test.student(id,name,age) values(?,?,?)"
      if (connection == null) conn
      val pstm : PreparedStatement = connection.prepareStatement(insertSql)
      pstm.setInt(1,id)
      pstm.setString(2,name)
      pstm.setInt(3,age)
      val i : Int = pstm.executeUpdate()
      i
    }
  }
}