scala操作jdbc

发表于 | 分类于

题目

使用scala操作jdbc (使用 scalikejdbc 来操作)

环境

IDEA 版本： IntelliJ IDEA 2018.2.5 x64

实现

添加依赖

修改pom文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>github.fenian7788.io</groupId>
    <artifactId>scala-training</artifactId>
    <version>1.0.0</version>
    <inceptionYear>2008</inceptionYear>
    <properties>
        <scala.version>2.12.5</scala.version>
        <scalikejdbc.version>3.3.1</scalikejdbc.version>
        <mysql.version>5.1.47</mysql.version>
        <logback.version>1.2.3</logback.version>
    </properties>

    <!-- 添加一个仓库地址 -->
    <repositories>
        <repository>
            <id>maven-center</id>
            <name>Maven Central Repository</name>
            <url>http://central.maven.org/maven2/</url>
        </repository>
    </repositories>

    <dependencies>
        <!-- scala依赖 -->
        <dependency>
            <groupId>org.scala-lang</groupId>
            <artifactId>scala-library</artifactId>
            <version>${scala.version}</version>
        </dependency>

        <!-- scalikejdbc依赖 -->
        <dependency>
            <groupId>org.scalikejdbc</groupId>
            <artifactId>scalikejdbc_2.12</artifactId>
            <version>${scalikejdbc.version}</version>
        </dependency>

        <!-- mysql jdbc -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>

        <!-- 日志 -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>${logback.version}</version>
        </dependency>
    </dependencies>
</project>

注 ：在阿里云目前好像找不到scalikejdbc的3.3.1版本的

代码

初始化

1
2
3
4
5
6
7
8
import scalikejdbc._

val driverClass = "com.mysql.jdbc.Driver"
val jdbcUrl = "jdbc:mysql://192.168.137.189:3306/hive";
val username = "root";
val pwd = "123456";
Class.forName(driverClass)
ConnectionPool.singleton(jdbcUrl, username, pwd)

建表

1
2
3
4
5
6
7
8
9
10
def doBefore = {
    implicit val session = AutoSession
    sql"""
       CREATE TABLE IF NOT EXISTS Person(
         id int PRIMARY KEY NOT NULL auto_increment,
         name varchar(64),
         created_time timestamp not null DEFAULT current_timestamp
      )ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
      """.execute.apply()
}

插入数据

1
2
3
4
5
//提交一条
def doInsert(name: String) = {
    implicit val session = AutoSession
    sql"insert into Person(name) values (${name})".update().apply()
}

更新

1
2
3
4
5
//更新
def doUpdate(name: String, id: Int) = {
    implicit val session = AutoSession
    sql"update Person set name = ${name} where id = ${id}".update().apply()
}

删除

1
2
3
4
5
//删除
def doDelete(id: Int) = {
    implicit val session = AutoSession
    sql"delete from Person where id = ${id}".execute().apply()
}

获取单条记录

1
2
3
4
5
//根据ID获取一条
def doGet(id: Int) = {
    implicit val session = AutoSession
    sql"select * from person where id = ${id}".map((Person(_))).single().apply().get
}

获取列表

1
2
3
4
5
//获取列表
def doList() = {
    implicit val session = AutoSession
    sql"select * from person".map((Person(_))).list().apply()
}

删表

1
2
3
4
def doAfter = {
    implicit val session = AutoSession
    sql"DROP TABLE IF EXISTS Person".execute.apply()
}

关闭连接

1
ConnectionPool.close()

整体流程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
def main(args: Array[String]): Unit = {
    Class.forName(driverClass)
    ConnectionPool.singleton(jdbcUrl, username, pwd)

    //建表
    doBefore

    //提交一条数据
    doInsert("Peter")
    //提交多条数据
    doInsert("Mike","Tom","Ken")

    //查询列表
    val list = doList()
    list.foreach(println) //Person(1,Peter,2018-12-09T15:20:27+08:00[Asia/Shanghai])....

    //根据ID获取一条记录
    val person1 = doGet(1)
    println(person1) //Person(1,Peter,2018-12-09T15:20:27+08:00[Asia/Shanghai])

    //更新一条记录
    val status = doUpdate("Peter Wu",person1.id)
    println(status) //1


    val statusD = doDelete(4)
    println(statusD) //false -->已经删除了

    doAfter

    ConnectionPool.close()

}

我感觉这些API是比较高级的API，等我找到比较底层实现在弄一个

源文件

ScalaJdbcApp.scala