用scala连接数据库【用隐式类封装增删改查操作，用java对比】

一、scala连接数据库

代码演示：

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

class MysqlDemo{

}

object MysqlDemo {
  private val driver = "com.mysql.jdbc.Driver"
  private val url = "jdbc:mysql://192.168.222.110:3306/test"
  private val userName = "root"
  private val userPwd = "ok"

  //连接数据库
  var connection:Connection=_
  def conn():Connection={
    Class.forName(driver)
    connection = DriverManager.getConnection(url,userName,userPwd)
    connection
  }

  //增加数据操作
  def insert():Unit={
    var insertSql = "insert into test.student(id,name,age) values(1,'zs',18)"
    val i = conn().createStatement().executeUpdate(insertSql)
    println("返回值为："+i)
  }

  //查询数据操作
  def select():Unit={
    val selectSql="select * from test.student"
    val rs:ResultSet = conn().createStatement().executeQuery(selectSql)
    println("查看查询的数据")
    while(rs.next()){
      var id=rs.getInt("id")
      var name=rs.getString("name")
      var age=rs.getInt("age")
      println("学号：%d 姓名：%s 年龄：%d".format(id,name,age))
    }
  }

  //删除数据操作
  def deleteSql():Unit={
    val deleteSql="delete from test.student where id=1"
    val rs:Int=conn().createStatement().executeUpdate(deleteSql)
    println(rs)
    if(rs>0)
      println("删除成功")
    else
      println("删除失败")
  }

  def main(args: Array[String]): Unit = {
//    insert()
//    select()
    deleteSql()
    println(conn())
  }
}

二、java连接数据库对比scala连接数据库

代码演示：

import java.sql.*;

public class DataBase {
    public static Connection conn;
    public static Statement stmt;
//    public static ResultSet rs;
    public static void getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        //使用驱动管理器，获取连接
        String url="jdbc:mysql://localhost:3306/test";
        conn= DriverManager.getConnection(url,"root","19971105");
        //任务管理器管理sql语句
        stmt=conn.createStatement();
    }

    //插入数据
    public static void insert() throws SQLException {
        String sql="insert into student values(1,'zs',22)";
        int i=stmt.executeUpdate(sql);
        if(i>0){
            System.out.println("插入成功");
        }else{
            System.out.println("插入失败");
        }
    }

    //查询数据
    public static void sqlQuery() throws SQLException {
        String sql="select * from student where id=?";
        PreparedStatement querySql = conn.prepareStatement(sql);
        querySql.setInt(1,2);
        ResultSet resultSet = querySql.executeQuery();
        while(resultSet.next()){
            int id=resultSet.getInt("id");
            String name=resultSet.getString("name");
            int age=resultSet.getInt("age");
            System.out.println("学号："+id+"姓名："+name+"年龄："+age);
        }
    }

    //删除数据
    public static void deleteSql() throws SQLException {
        String sql="delete from student where id=?";
        PreparedStatement deleteSql=conn.prepareStatement(sql);
        deleteSql.setInt(1,3);
        int result=deleteSql.executeUpdate();
        if(result>0){
            System.out.println("删除成功");
        }else{
            System.out.println("删除失败");
        }
    }

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //获取连接
        getConnection();
        //插入方法
//        insert();
        //查询方法
//        sqlQuery();
        //删除方法
        deleteSql();
    }
}

三、用隐式类封装增删改查操作，创建对象时直接拥有增删改查操作

代码演示：
创建工具类：

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


class MysqlUtil{

}
//object相当于java中的静态类，在编译执行前，就编译好了，所以隐式类放在object类中可以找到
object MysqlUtil {
  implicit class MySqlOP(obj:MysqlDemo){
    private val driver = "com.mysql.jdbc.Driver"
    private val url = "jdbc:mysql://192.168.222.110:3306/test"
    private val userName = "root"
    private val userPwd = "ok"

    var connection:Connection=_

    //防止sql注入的准备状态
    private val insertStmt: PreparedStatement
//    = conn().prepareStatement("insert into test.student(id,name,age) values(?,?,?)")
    =conn prepareStatement "insert into test.student(id,name,age) values(?,?,?)"

    //防止sql注入的准备状态
    private val selectSql:PreparedStatement
    =conn prepareStatement "select * frzom student where id=?"

    //连接数据库
    def conn():Connection={
      Class.forName(driver)
      connection = DriverManager.getConnection(url,userName,userPwd)
      connection
    }

    //增加数据
    def insert(id:Int,name:String,age:Int)={
      insertStmt.setInt(1,id)
      insertStmt.setString(2,name)
      insertStmt.setInt(3,age)
      val i:Int=insertStmt.executeUpdate()
      println(i)
    }

    //查询数据
    def selectMysql(id:Int)={
      selectSql.setInt(1,id)
      val  rs:ResultSet=selectSql.executeQuery()
      while(rs.next()){
        var id=rs.getInt("id")
        var name=rs.getString("name")
        var age=rs.getInt("age")
        println("学号：%d 姓名：%s 年龄：%d".format(id,name,age))
      }
    }
  }
}

用目标类创建实例对象，创建完之后该对象就会有增删改查方法了

class MysqlDemo{

}

object MysqlDemo {
  def main(args: Array[String]): Unit = {
  import  com.njzb728.MysqlUtil._
  var mysqlDemo =new MysqlDemo
  //增加数据
  mysqlDemo.insert(4,"hqj",30)
  //查询数据
  mysqlDemo.selectMysql(2)
  }
}