表结构
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, '北京');
依赖
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<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("==================")
参考
A Scala JDBC connection and SQL SELECT example
Scala JDBC 查询和更新MySQL
scala操作mysql数据库