Scala---JDBC

【前提：将mysql jar包导入到工程目录下，并且在Mysql中创建好对应的库和表】

表结构如下：

其实和之前的JDBC一样的步骤：

1. 创建驱动
2. 建立连接
3. 编写sql
4. 预处理
5. 执行

一、原版

import java.sql.{Connection, DriverManager}

class ScalaJdbc{
  private var driver:String = "com.mysql.jdbc.Driver"
  private var url:String = "jdbc:mysql://192.168.XXX.100:3306/scala_test"
  private var username:String = "root"
  private var password:String = "root"
  private var conn:Connection = _

  def this(driver:String,url:String,username:String,password:String){
    this()
    this.driver=driver
    this.url=url
    this.username=username
    this.password=password
  }
  //获取连接
  def getConn(): Connection ={
    Class.forName(driver)
    val conn = DriverManager.getConnection(url,username,password)
    conn
  }
  //插入
  def insert(id:Int,name:String,age:Int)={
    var sql = "insert into student values(?,?,?)"
    var prep = getConn().prepareStatement(sql)
    prep.setInt(1,id)
    prep.setString(2,name)
    prep.setInt(3,age)
    prep.executeUpdate()
  }
  //查询
  def select()={
    var sql = "select * from student"
    val prep = getConn().prepareStatement(sql)
    var rs = prep.executeQuery()
    while (rs.next()){
      var id = rs.getInt(1)
      var name = rs.getString(2)
      var age = rs.getInt(3)
      println("编号：%d\t姓名：%s\t年龄：%d".format(id,name,age))
    }
  }

  //更新
  def update(name:String,id:Int): Unit ={
    var sql = "update student set name = ? where id = ?"
    var prep = getConn().prepareStatement(sql)
    prep.setString(1,name)
    prep.setInt(2,id)
    prep.executeUpdate()
  }

  //删除
  def delete(id:Int)={
    var sql = "delete from student where id = ?"
    var prep = getConn().prepareStatement(sql)
    prep.setInt(1,id)
    prep.executeUpdate()
  }

}

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

  def main(args: Array[String]): Unit = {
    val s = new ScalaJdbc()
    s.select()
    s.insert(4,"John",33)
    s.select()
    s.update("Bob",4)
    s.select()
    s.delete(3)
    s.select()
  }
}

二、隐式类封装

隐式类
【直接将原来的代码复制进来，把构造方法去掉就行】

import java.sql.{Connection, DriverManager}

object ScalaImplicit {
  implicit class scalaJdbcOp(scalaJdbc: ScalaJdbc){
    private var driver:String = "com.mysql.jdbc.Driver"
    private var url:String = "jdbc:mysql://192.168.XXX.100:3306/scala_test"
    private var username:String = "root"
    private var password:String = "root"
    private var conn:Connection = _

    //获取连接
    def getConn(): Connection ={
      Class.forName(driver)
      val conn = DriverManager.getConnection(url,username,password)
      conn
    }
    //插入
    def insert(id:Int,name:String,age:Int)={
      var sql = "insert into student values(?,?,?)"
      var prep = getConn().prepareStatement(sql)
      prep.setInt(1,id)
      prep.setString(2,name)
      prep.setInt(3,age)
      prep.executeUpdate()
    }
    //查询
    def select()={
      var sql = "select * from student"
      val prep = getConn().prepareStatement(sql)
      var rs = prep.executeQuery()
      while (rs.next()){
        var id = rs.getInt(1)
        var name = rs.getString(2)
        var age = rs.getInt(3)
        println("编号：%d\t姓名：%s\t年龄：%d".format(id,name,age))
      }
    }

    //更新
    def update(name:String,id:Int): Unit ={
      var sql = "update student set name = ? where id = ?"
      var prep = getConn().prepareStatement(sql)
      prep.setString(1,name)
      prep.setInt(2,id)
      prep.executeUpdate()
    }

    //删除
    def delete(id:Int)={
      var sql = "delete from student where id = ?"
      var prep = getConn().prepareStatement(sql)
      prep.setInt(1,id)
      prep.executeUpdate()
    }
  }

}

原来的类
【删掉原来的，导入隐式类的包就OK】

import  ScalaImplicit._
class ScalaJdbc{}

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

  def main(args: Array[String]): Unit = {
    val s = new ScalaJdbc()
    s.select()
    s.insert(4,"John",33)
    s.select()
    s.update("Bob",4)
    s.select()
    s.delete(3)
    s.select()
  }
}