How to use database to perform add, delete, check and modify operations in Java and Scala?

Add jar package

The Scala here is not a maven project, so you need to find the project structure (shortcut key: hold down Ctrl shift Alt s at the same time) and add the MySQL jar package in the module. If it is a Maven project, you can Just add it directly in the pom

Two classes are needed to operate the database, one is Demo for testing, and the other is Util method for adding, deleting, checking and modifying

In MysqlDemo, companion classes and companion objects need to be used. The companion class is mainly used to connect to the database.

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
  }
}

The companion object is mainly used to operate the database. It needs to implement the apply method first and create it in the main function. The object calls the apply method, calls the connection method and releases the resources

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 needs to initialize the database connection, register the driver, get the database connection, and release the resources

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()
      }
    }
}

The preparation work has been completed and the following needs to be implemented Add, delete, check and modify the database

Add data

Method one

//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("添加失败")
    }

Method two

//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("添加失败")
    }

Delete data

//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("删除失败")
        }

Query data

//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

Modify data

//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("修改失败")
        }

Complete code

MysqlUtil code

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 code

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("删除失败")
    }
  }
}

The above is the detailed content of How to use database to perform add, delete, check and modify operations in Java and Scala?. For more information, please follow other related articles on the PHP Chinese website!