scala之使用scalikejdbc操作数据库

ゞ浴缸里的玫瑰 2021-11-11 00:20 307阅读 0赞

scalikejdbc的简介：

ScalikeJDBC是一款给Scala开发者使用的简介访问类库，它是基于SQL的，使用者只需要关注SQL逻辑的编写，所有的数据库操作都交给ScalikeJDBC。这个类库内置包含了JDBCAPI，并且给用户提供了简单易用并且非常灵活的API。并且，QueryDSl（通用查询查询框架）使你的代码类型安全，半年过去可重复使用。我们可以在生产环境大胆地使用这款DB访问类库。

笔者用的是MySQL数据库，所以此处以MySQL为例，以下是通过ScalikeJDBC来操作数据库的具体过程

【操作大纲】

1.拿到connection

2.拿到statement

3.拿到rs

4.结果获取出来

5.关掉rs，stmt和connection释放资源

【pom文件的配置】

<dependency>
    <groupId>org.scala-lang</groupId>
    <artifactId>scala-library</artifactId>
    <version>${scala.version}</version>
    
    </dependency>
    
    
    <dependency>
    <groupId>org.scalikejdbc</groupId>
    <artifactId>scalikejdbc_2.11</artifactId>
    <version>${scalikejdbc.version}</version>
    </dependency>
    
    <dependency>
    <groupId>org.scalikejdbc</groupId>
    <artifactId>scalikejdbc-config_2.11</artifactId>
    <version>${scalikejdbc.version}</version>
    </dependency>
    
    <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>${mysql.jdbc.version}</version>
    </dependency>

<scala.version>2.11.8</scala.version>
    <scalikejdbc.version>3.3.2</scalikejdbc.version>
    <mysql.jdbc.version>5.1.38</mysql.jdbc.version>

</properties>

【配置连接数据库的信息文件】

1.在src的main目录下配置一个resource文件夹，文件夹下再创建一个application.conf

![1326937-20190501091444810-417836710.png][]

【配置使文件能够被读取】

File=》Project Structure \#\#\#\#若想使配置文件能够被加载，需将config设置为resource

![1326937-20190501092115085-1964784445.png][]

【application.conf文件配置信息如下】

db.default.driver="com.mysql.jdbc.Driver"
    db.default.url="jdbc:mysql://hadoop001/ruoze_d6?characterEncoding=utf-8"
    db.default.user="root"
    db.default.password="123456"
    
    # Connection Pool settings
    db.default.poolInitialSize=10
    db.default.poolMaxSize=20
    db.default.connectionTimeoutMillis=1000

【在ruoze\_d6创建Employer表格】

create table Employer(
    name varchar(10),
    age varchar(4),
    salary  varchar(10)
    );

【scala编程实现增删改查操作】

package com.wsk.bigdata.scala.scalikejdbc
    
    import scalikejdbc._
    import scalikejdbc.config._
    
    case class Employer(name: String, age: Int, salary: Long)
    
    object JdbcTest {
      def main(args: Array[String]): Unit = {
        DBs.setupAll()
    
        val config = DBs.config
    
        val employers = List(Employer("zhangsan", 20, 18000), Employer("zhangliu", 50, 300000), Employer("lisi", 22, 22000))
        //批量插入
        insert(employers)
        println("----------------insert执行完毕---------------")
        //查询出结果
        val results = select()
        for (employer <- results) {
          println(employer.name, employer.age, employer.salary)
        }
        println("----------------select执行完毕---------------")
        //修改
        update(1000, "zhangsan")
        println("----------------update执行完毕---------------")
        //根据姓名删除
        deleteByname("zhangliu")
        println("----------------deleteByname执行完毕---------------")
        //删除所有记录
        deleteAll()
        println("----------------deleteAll执行完毕---------------")
    
        DBs.closeAll()
      }
    
    
      def insert(employers: List[Employer]): Unit = {
        DB.localTx { implicit session =>
          for (employer <- employers) {
            SQL("insert into wsktest(name,age,salary) values(?,?,?)")
              .bind(employer.name, employer.age, employer.salary)
              .update().apply()
          }
    
        }
      }
    
      def select(): List[Employer] = {
        DB.readOnly { implicit session =>
          SQL("select * from wsktest")
            .map(rs => Employer(rs.string("name"), rs.int("age"), rs.long("salary")))
            .list().apply()
        }
      }
    
      def update(age: Int, name: String) {
        DB.autoCommit { implicit session =>
          SQL("update wsktest set age = ? where name = ?").bind(age, name).update().apply()
        }
      }
    
    
      def deleteByname(name: String): Unit = {
        DB.autoCommit { implicit session =>
          SQL("delete from wsktest where name = ?").bind(name).update().apply()
        }
      }
    
      def deleteAll(): Unit ={
        DB.autoCommit { implicit session =>
          SQL("delete from wsktest ").update().apply()
        }
      }
    }

转载于:https://www.cnblogs.com/xuziyu/p/10799202.html

[1326937-20190501091444810-417836710.png]: /images/20211109/651e72b07975465bb9be0eb8bdb4f224.png
[1326937-20190501092115085-1964784445.png]: /images/20211109/24af6cadd6ec47fb999c62c31ae5ae70.png