Scala---操作数据库

Scala操作数据库之增删改查

在已创建Maven项目前提下

---

---

添加mysqljar包

打开ProjectStructure
这里mysql jar包版本为8.0.29

连接数据库

表结构

表数据

增删改查

新增

    //新增语句
    val str: String = "2021-12-12"
    val date1 = new sql.Date((2021-1900), 10, 9)
    val i: Int = demo.insertEmp("销售部", "1000", "小红", date1, "8000")
    if (i ==0){
      println("新增失败")
    }else{
      println("新增成功")
    }
//隐式类
    def insertEmp(dname:String, eid:String, ename:String, hiredate:Date, salary:String):Int={
      val insertSql ="insert into employee(dname,eid,ename,hiredate,salary)" +
        "values(?,?,?,?,?)"
      val preSmt: PreparedStatement = conn().prepareStatement(insertSql)
      preSmt.setString(1,dname)
      preSmt.setString(2,eid)
      preSmt.setString(3,ename)
      preSmt.setDate(4,hiredate)
      preSmt.setString(5,salary)
      val i: Int = preSmt.executeUpdate()
      i
    }

更新

    //更新
    val i: Int = demo.updateEmp("市场部", "1000")
    if (i==0){
      println("更新失败")
    }else{
      println("更新成功")
    }
//隐式类
    //更新
    def updateEmp(dname:String,eid:String):Int={
      val updateSql="update employee set dname=? where eid=?"
      val preSmt: PreparedStatement = conn().prepareStatement(updateSql)
      preSmt.setString(1,dname)
      preSmt.setString(2,eid)
      val i: Int = preSmt.executeUpdate()
      i
    }

查询

    //查询
    demo.selectEmp()
//隐式类
    //查询
    def selectEmp():Unit={
      val selectStr ="select dname,eid,ename,hiredate,salary from employee"
      val rs: ResultSet = conn().createStatement().executeQuery(selectStr)
      println("部门 员工号 入职日期 薪资")
      while (rs.next()){
        val dname: String = rs.getString("dname")
        val eid: String = rs.getString("eid")
        val ename: String = rs.getString("ename")
        val hirdate: Date = rs.getDate("hiredate")
        val str: String = rs.getString("salary")
        println(s"${dname} ${eid} ${ename} ${hirdate} ${str}")
      }
    }

删除

    //删除
    val i: Int = demo.deleteEmp("1000")
    if (i==0){
      println("删除失败")
    }else{
      println("删除成功")
    }
//隐式类
    //删除
    def deleteEmp(id:String):Int={
      val deleteSql="delete from employee where eid=?"
      val preSmt: PreparedStatement = conn().prepareStatement(deleteSql)
      preSmt.setString(1,id)
      val i: Int = preSmt.executeUpdate()
      i
    }

EmpDemo

class EmpDemo {
  //数据库驱动
  var driver = "com.mysql.cj.jdbc.Driver"
  //数据库连接地址
  //协议:什么样的数据库:数据库服务器的地址:端口号/数据库名称
  var url = "jdbc:mysql://192.168.95.130:3306/test"
  //数据库用户名
  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 EmpDemo{
  def apply():EmpDemo =new EmpDemo()
  def apply(driver:String,url:String,user:String,pwd:String):EmpDemo=new EmpDemo(driver,url,user,pwd)

  def main(args: Array[String]): Unit = {
    //创建对象（实际调用了apply方法）
    val demo = EmpDemo()
    //导包
    import EmpOpUtil._
    //调用创建连接的方法
    val connection: Connection = demo.conn()
    println(connection)

    //新增语句
    val str: String = "2021-12-12"
    val date1 = new sql.Date((2021-1900), 10, 9)
    val i: Int = demo.insertEmp("销售部", "1000", "小红", date1, "8000")
    if (i ==0){
      println("新增失败")
    }else{
      println("新增成功")
    }

    //更新
    val i: Int = demo.updateEmp("市场部", "1000")
    if (i==0){
      println("更新失败")
    }else{
      println("更新成功")
    }

    //查询
    demo.selectEmp()

    //删除
    val i: Int = demo.deleteEmp("1000")
    if (i==0){
      println("删除失败")
    }else{
      println("删除成功")
    }

    //关闭
    demo.close(connection)


  }
}

EmpopUtil

/**
 * 用隐式类的方式写一个工具类
 */
object EmpOpUtil {
  implicit class EmpOp(obj:EmpDemo){

    private var connection:Connection = _

    //连接数据库
    def conn():Connection ={
      //加载驱动
      Class.forName(obj.driver)
      //DriverManager获取Connection连接
      val conn: Connection = DriverManager.getConnection(obj.url, obj.user, obj.password)
      conn
    }

    //释放资源
    def close(connection: Connection):Unit={
      connection.close()
    }
    //新增
    def insertEmp(dname:String, eid:String, ename:String, hiredate:Date, salary:String):Int={
      val insertSql ="insert into employee(dname,eid,ename,hiredate,salary)" +
        "values(?,?,?,?,?)"
      val preSmt: PreparedStatement = conn().prepareStatement(insertSql)
      preSmt.setString(1,dname)
      preSmt.setString(2,eid)
      preSmt.setString(3,ename)
      preSmt.setDate(4,hiredate)
      preSmt.setString(5,salary)
      val i: Int = preSmt.executeUpdate()
      i
    }
    //更新
    def updateEmp(dname:String,eid:String):Int={
      val updateSql="update employee set dname=? where eid=?"
      val preSmt: PreparedStatement = conn().prepareStatement(updateSql)
      preSmt.setString(1,dname)
      preSmt.setString(2,eid)
      val i: Int = preSmt.executeUpdate()
      i
    }

    //查询
    def selectEmp():Unit={
      val selectStr ="select dname,eid,ename,hiredate,salary from employee"
      val rs: ResultSet = conn().createStatement().executeQuery(selectStr)
      println("部门 员工号 入职日期 薪资")
      while (rs.next()){
        val dname: String = rs.getString("dname")
        val eid: String = rs.getString("eid")
        val ename: String = rs.getString("ename")
        val hirdate: Date = rs.getDate("hiredate")
        val str: String = rs.getString("salary")
        println(s"${dname} ${eid} ${ename} ${hirdate} ${str}")
      }
    }

    //删除
    def deleteEmp(id:String):Int={
      val deleteSql="delete from employee where eid=?"
      val preSmt: PreparedStatement = conn().prepareStatement(deleteSql)
      preSmt.setString(1,id)
      val i: Int = preSmt.executeUpdate()
      i
    }
  }
}