Scala JDBC 操作 Mysql

╰半夏微凉° 2022-02-27 19:20 245阅读 0赞

### 文章目录 ###

*   *  表结构
     *  依赖
     *  代码
     *   *  MysqlConnect
         *  CRUD
         *  测试
     *  参考

## 表结构 ##

CREATE TABLE `scala_jdbc_test` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `age` tinyint(1) DEFAULT NULL,
      `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

插入测试数据

INSERT INTO `demo`.`scala_jdbc_test`(`id`, `name`, `age`, `address`) VALUES (1, 'zhangsan', 1, '北京');

## 依赖 ##

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>6.0.6</version>
    </dependency>

## 代码 ##

### MysqlConnect ###

package com.yljphp.demo.learn
    
    import java.sql.{ Connection, DriverManager}
    
    object MysqlConnect { 
    
      private var connection: Connection = _
    
      private val driver = "com.mysql.cj.jdbc.Driver"
      private val url = "jdbc:mysql://ip:port/dbname?useUnicode=true&characterEncoding=utf-8&useSSL=false"
      private val username = ""
      private val password = ""
    
      /** * 创建mysql连接 * * @return */
      def conn(): Connection = { 
    
        if (connection == null) { 
          println(this.driver)
          Class.forName(this.driver)
          connection = DriverManager.getConnection(this.url, this.username, this.password)
        }
        connection
      }
    }

### CRUD ###

val statement: Statement = MysqlConnect.conn().createStatement
    
      def delete(): Unit ={ 
        val resDelete = statement.executeUpdate("DELETE FROM `scala_jdbc_test` WHERE `id` = 2")
        println("删除数据，返回值=>" + resDelete)
      }
    
      def update(): Unit = { 
        val resUpdate = statement.executeUpdate("UPDATE `scala_jdbc_test` SET `name` = '张三'WHERE `id` = 1")
        println("更新数据，返回值=>" + resUpdate)
      }
    
      def insert(): Unit = { 
        val resInsert = statement.executeUpdate("INSERT INTO `scala_jdbc_test`(`name`, `age`, `address`) VALUES ( '李四', 2, '南京')")
        println("插入数据，返回值=>" + resInsert)
      }
    
    
      def select(): Unit = { 
        val res: ResultSet = statement.executeQuery("select * from scala_jdbc_test")
    
        while (res.next) { 
    
          val id = res.getString("id")
          val name = res.getString("name")
          val age = res.getString("age")
          val address = res.getString("address")
    
          println("查询数据")
          println("id=%s,name=%s,age=%s,address=%s".format(id, name, age, address))
        }
      }

### 测试 ###

select()
    println("==================")
    insert()
    select()
    println("==================")
    update()
    select()
    println("==================")
    delete()
    select()
    println("==================")

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lsanBocA_size_16_color_FFFFFF_t_70]

## 参考 ##

[A Scala JDBC connection and SQL SELECT example][]  
[Scala JDBC 查询和更新MySQL][Scala JDBC _MySQL]  
[scala操作mysql数据库][scala_mysql]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lsanBocA_size_16_color_FFFFFF_t_70]: /images/20220227/5b6853c4dd0540939b665abd9b33f060.png
[A Scala JDBC connection and SQL SELECT example]: https://alvinalexander.com/scala/scala-jdbc-connection-mysql-sql-select-example
[Scala JDBC _MySQL]: https://www.jianshu.com/p/6c97fe5a9887
[scala_mysql]: https://blog.csdn.net/u013421629/article/details/78883759