Scala操作数据库实现增删查改

﹏ヽ暗。殇╰゛Y 2024-03-24 09:27 113阅读 0赞

**目录**

添加jar包

添加数据

方法一

方法二

删除数据

查询数据

修改数据

完整代码

MysqlUtil代码

MysqlDemo代码

--------------------

### 添加jar包 ###

这里的Scala不是maven工程所以要找到项目结构（快捷键：同时按住Ctrl+shift+Alt+s）在模块里面添加添加MySQL的jar包，如果是Maven工程可以直接在pom里面直接加即可

![bbc5fe3fbe0d4917a93f2ef426ae906b.png][]

实现操作数据库需要两个类，一个用于测试的Demo，一个用于实现增删查改的Util方法

在MysqlDemo在这里需要用到伴生类和伴生对象，伴生类主要是实现连接数据库

class MysqlDemo {
      //  连接数据库 底层原理还是jdbc
      //驱动名称
      var driver = "com.mysql.cj.jdbc.Driver"
      //  访问MySQL服务器，通过3306端口访问mysql数据库
      var url = "jdbc:mysql://192.168.152.184:3306/exam"
      //用户名
      var user = "root"
      //密码
      var password = "123456"
    
      //辅助构造器
      def this(driver: String, url: String, user: String, pwd: String) {
        this()
        this.driver = driver
        this.url = url
        this.user = user
        this.password = pwd
      }
    }

伴生对象则主要是实现操作数据库 ，需要先实现apply方法，主函数里面创建对象调用apply方法，调用连接方法和释放资源

object MysqlDemo {
      //  实现apply方法
      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 = {
        //创建对象，调用apply方法
        val demo = MysqlDemo()
        //导入MySQLUtil包
        import MysqlUtil._
        //调用创建连接的方法
        demo.conn()
        // 调用释放资源方法
        demo.close(connection)
    
    
    /**操作数据库代码*/
    
    }

Util则需要先初始化数据库连接，注册驱动，得到数据库连接，释放资源

准备工作已做完下面要实现对数据库的增删查改

### 添加数据 ###

#### 方法一 ####

//Util代码
       
    //添加数据方法一
        def insertStudent: Int = {
          val insertSqlStr = "insert into student(name, age, gender, telephone, email, classid)" +
            "values ('周七', 6, '男', '13888888888', '346497@qq.com', 2)"
          val i: Int = conn().createStatement.executeUpdate(insertSqlStr)
          i
        }
    
    //Demo测试代码
    
     //添加数据方法一
        val i: Int = demo.insertStudent
        if (i == 1) {
          println("添加成功")
        } else if (i == 0) {
          println("添加失败")
        }

#### 方法二 ####

//Util代码  
    
       //添加数据方法二
        def insertStudent(name: String, age: Int, gender: String, telephone: String, email: String, classid: Int): Int = {
          val insertSqlStr = "insert into student(id,name, age, gender, telephone, email, classid)" +
            "values (null,?,?,?,?,?,?)"
          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
        }
    
    //Demo测试代码
    
     //添加数据方法二
        val i: Int = demo.insertStudent("aaa", 18, "男", "12345678910", "34649756@qq.com", 1)
        if (i == 1) {
          println("添加成功")
        } else if (i == 0) {
          println("添加失败")
        }

### 删除数据 ###

//Util代码
    
      def deleteStudent(id: Int): Int = {
          val deleteSql = "delete from student where id= ?"
          val preStmt: PreparedStatement = conn().prepareStatement(deleteSql)
          preStmt.setInt(1, id)
          val i: Int = preStmt.executeUpdate()
          i
        }
    
    
    //Demo测试代码
    
            val i: Int = demo.deleteStudent(21)
            if (i == 1) {
              println("删除成功")
            } else if (i == 0) {
              println("删除失败")
            }

### 查询数据 ###

//Util代码
       def selectStudent: Unit = {
          val selectSql = "select id,name, age, gender, telephone, email, classid from student"
    
          val rs: ResultSet = conn().createStatement().executeQuery(selectSql)
          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: String = rs.getString("classid")
            println(id + "\t" + name + "\t" + age + "\t" + gender + "\t" + telephone + "\t" + email + "\t" + classid)
          }
        }
    
    //Demo测试代码
            demo.selectStudent

### 修改数据 ###

//Util代码
       def updateStudent(id: Int, name: String, age: Int, gender: String, telephone: String, email: String, classid: Int): Int = {
          val updateSql = "update student set name=?," + "age=?, " + "gender=?," +
            "telephone=?," + "email=?, " + "classid=? where id=?"
          val preStmt: PreparedStatement = conn().prepareStatement(updateSql)
          preStmt.setString(1, name)
          preStmt.setInt(2, age)
          preStmt.setString(3, gender)
          preStmt.setString(4, telephone)
          preStmt.setString(5, email)
          preStmt.setInt(6, classid)
          preStmt.setInt(7, id)
          val i: Int = preStmt.executeUpdate()
          i
        }
    
    //Demo测试代码
            val i: Int = demo.updateStudent(22, "test", 25, "女", "123456789", "369852@qq.com", 2)
            if (i == 1) {
              println("修改成功")
            } else if (i == 0) {
              println("修改失败")
            }

### 完整代码 ###

#### MysqlUtil代码 ####

import java.sql.{Connection, DriverManager, PreparedStatement, ResultSet}
    
    object MysqlUtil {
      implicit class MysqlOp(obj: MysqlDemo) {
        //初始化数据连接
        private var connection: Connection = _
        def conn(): Connection = {
          //注册driver
          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 ('bbb', 6, '男', '13852277346', '3464975236@qq.com', 2)"
          val i: Int = conn().createStatement.executeUpdate(insertSqlStr)
          i
        }
    
    
        //添加数据方法二
        def insertStudent(name: String, age: Int, gender: String, telephone: String, email: String, classid: Int): Int = {
          val insertSqlStr = "insert into student(id,name, age, gender, telephone, email, classid)" +
            "values (null,?,?,?,?,?,?)"
          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
        }
    
        //删除数据
        def deleteStudent(id: Int): Int = {
          val deleteSql = "delete from student where id= ?"
          val preStmt: PreparedStatement = conn().prepareStatement(deleteSql)
          preStmt.setInt(1, id)
          val i: Int = preStmt.executeUpdate()
          i
        }
    
        //修改数据
        def updateStudent(id: Int, name: String, age: Int, gender: String, telephone: String, email: String, classid: Int): Int = {
          val updateSql = "update student set name=?," + "age=?, " + "gender=?," +
            "telephone=?," + "email=?, " + "classid=? where id=?"
          val preStmt: PreparedStatement = conn().prepareStatement(updateSql)
          preStmt.setString(1, name)
          preStmt.setInt(2, age)
          preStmt.setString(3, gender)
          preStmt.setString(4, telephone)
          preStmt.setString(5, email)
          preStmt.setInt(6, classid)
          preStmt.setInt(7, id)
          val i: Int = preStmt.executeUpdate()
          i
        }
    
        //查询数据
        def selectStudent: Unit = {
          val selectSql = "select id,name, age, gender, telephone, email, classid from student"
          val rs: ResultSet = conn().createStatement().executeQuery(selectSql)
          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: String = rs.getString("classid")
            println(id + "\t" + name + "\t" + age + "\t" + gender + "\t" + telephone + "\t" + email + "\t" + classid)
          }
        }
      }
    }

#### MysqlDemo代码 ####

import java.sql.Connection
    
    class MysqlDemo {
      //  连接数据库 底层原理还是jdbc
      //驱动名称
      var driver = "com.mysql.cj.jdbc.Driver"
      //  访问MySQL服务器，通过3306端口访问mysql数据库
      var url = "jdbc:mysql://192.168.152.184:3306/exam"
      //用户名
      var user = "root"
      //密码
      var password = "123456"
    
      //辅助构造器    
      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 {
      //  实现apply方法
      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 = {
        //创建对象，调用apply方法
        val demo = MysqlDemo()
        //导入MySQLUtil包
        import MysqlUtil._
        //调用创建连接的方法
        val connection: Connection = demo.conn()
        // 调用释放资源方法
        demo.close(connection)
    
    
        //添加数据方法一
        val i: Int = demo.insertStudent
        if (i == 1) {
          println("添加成功")
        } else if (i == 0) {
          println("添加失败")
        }
    
        //添加数据方法二
        val i: Int = demo.insertStudent("aaa", 18, "男", "12345678910", "34649756@qq.com", 1)
        if (i == 1) {
          println("添加成功")
        } else if (i == 0) {
          println("添加失败")
        }
    
        //查询数据
        demo.selectStudent
    
        // 修改数据
        val i: Int = demo.updateStudent(22, "test", 25, "女", "123456789", "369852@qq.com", 2)
        if (i == 1) {
          println("修改成功")
        } else if (i == 0) {
          println("修改失败")
        }
    
        //删除数据
        val i: Int = demo.deleteStudent(21)
        if (i == 1) {
          println("删除成功")
        } else if (i == 0) {
          println("删除失败")
        }
      }
    }

[bbc5fe3fbe0d4917a93f2ef426ae906b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/c80e4bb48ce94bb49119ea0535d4c2dc.png