scalikejdbc框架操作MySQL

一、构建一个Maven的Scala项目，然后在项目中添加以下依赖

  <properties>
        <scala.version>2.11.8</scala.version>
        <scalikejdbc.version>3.3.2</scalikejdbc.version>
    </properties>

        <!-- 添加scalikejdbc依赖 -->
        <!-- https://mvnrepository.com/artifact/org.scalikejdbc/scalikejdbc -->
        <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>

然后在src下一级目录创建resource文件夹，并指定为Resources文件

image.png

在resource文件下创建application.conf文件,并配置以下内容

db.default.driver="com.mysql.jdbc.Driver"
db.default.url="jdbc:mysql://hadoop000:3306/hadoop_train?characterEncoding=utf-8"
db.default.user="root"
db.default.password="root"
dataSourceClassName=com.mysql.jdbc.jdbc2.optional.MysqlDataSource

二、代码编写

package com.soul.bigdata.scalikejdbc

import scalikejdbc.{ConnectionPool, DB, SQL}
import scalikejdbc.config.DBs


case class User(id: Int, name: String, age: Int)


object ScalaLikeJdbc {
  def main(args: Array[String]): Unit = {


    // 加载驱动
    classOf[com.mysql.jdbc.Driver]
    //    Class.forName("com.mysql.jdbc.Driver")

    //解析application.conf的文件。
    DBs.setup()

    //    createTable()
    //    println("User2表创建完毕")

    //    val userLists = List(User(1, "zhangsan", 18), User(2, "lisi", 20), User(3, "wangwu", 35))
    //    insert(userLists)
    //    println("批量插入完毕")

    //    println(selectAll())
    //    println(selectByID(2))

    //    updateByID(2,60)
    //    println(selectByID(2))

    deleteByID(2)
    println(selectAll())

    DBs.close()

  }


  def createTable(): Unit = {
    DB.autoCommit { implicit session =>
      SQL("create table user2(\nid int not null auto_increment,\nname varchar(100) not null,\nage int,\nprimary key ( id )\n)engine=innodb default charset=utf8; ")
        .execute.apply()
    }
  }


  def insert(users: List[User]): Unit = {
    DB.localTx { implicit session =>
      for (user <- users) {
        SQL("insert into user2(id,name,age) values(?,?,?)")
          .bind(user.id, user.name, user.age)
          .update().apply()
      }

    }
  }


  //3、查询所有
  def selectAll(): List[User] = {
    val list: List[User] = DB.readOnly {
      implicit session =>
        SQL("SELECT * from user2").map(rs => User(rs.int(1), rs.string(2), rs.int(3))).list().apply()
    }
    list
  }


  def selectByID(id: Int): Option[User] = {
    val list: Option[User] = DB.readOnly {
      implicit session =>
        SQL(s"select id,name,age from user2 where id = ${id}").map(rs => User(rs.int(1), rs.string(2), rs.int(3))).single.apply()
    }
    list
  }


  def updateByID(id: Int, age: Int): Unit = {
    DB.localTx {
      implicit session =>
        SQL(s"update user2 set age = ?  where id = ${id}").bind(age).update().apply()
    }

  }

  def deleteByID(id: Int): Unit = {
    DB.localTx {
      implicit session =>
        SQL(s"delete from user2 where id = ${id}").update().apply()
    }

  }


}