通过scalike，在idea上面对MySQL进行增删改查

1. 配置maven工程的pom.

<project  ns=\"http://maven.apache.org/POM/4.0.0\"  ns:xsi=\"http://www.w3.org/2001/ Schema-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>www.ruozedata.bigdata</groupId>
  <artifactId>g5-spark</artifactId>
  <version>1.0</version>
  <inceptionYear>2008</inceptionYear>
  <properties>
    <scala.version>2.11.8</scala.version>
    <spark.version>2.4.0</spark.version>
    <hadoop.version>2.6.0-cdh5.7.0</hadoop.version>
    <scalikejdbc.version>2.5.2</scalikejdbc.version>
    <mysql.version>5.1.38</mysql.version>
  </properties>

  <repositories>
    <repository>
      <id>scala-tools.org</id>
      <name>Scala-Tools Maven2 Repository</name>
      <url>http://scala-tools.org/repo-releases</url>
    </repository>

    <repository>
      <id>cloudera</id>
      <url>https://repository.cloudera.com/artifactory/cloudera-repos/</url>
    </repository>

  </repositories>

  <pluginRepositories>
    <pluginRepository>
      <id>scala-tools.org</id>
      <name>Scala-Tools Maven2 Repository</name>
      <url>http://scala-tools.org/repo-releases</url>
    </pluginRepository>
  </pluginRepositories>

  <dependencies>
    <!--Scala dependency-->
    <dependency>
      <groupId>org.scala-lang</groupId>
      <artifactId>scala-library</artifactId>
      <version>${scala.version}</version>
    </dependency>
    <!--Spark core dependency-->
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-core_2.11</artifactId>
      <version>${spark.version}</version>
    </dependency>
    <!--hadoop dependency-->
      <dependency>
          <groupId>org.apache.hadoop</groupId>
          <artifactId>hadoop-client</artifactId>
          <version>${hadoop.version}</version>
      </dependency>
    <!--Test dependency-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
    <!--scalikejdbc 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>
    <!--mysql dependency-->
    <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>1.2.3</version>
    </dependency>
  </dependencies>

  <build>
    <sourceDirectory>src/main/scala</sourceDirectory>
    <testSourceDirectory>src/test/scala</testSourceDirectory>
    <plugins>
      <plugin>
        <groupId>org.scala-tools</groupId>
        <artifactId>maven-scala-plugin</artifactId>
        <executions>
          <execution>
            <goals>
              <goal>compile</goal>
              <goal>testCompile</goal>
            </goals>
          </execution>
        </executions>
        <configuration>
          <scalaVersion>${scala.version}</scalaVersion>
          <args>
            <arg>-target:jvm-1.5</arg>
          </args>
        </configuration>
      </plugin>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-eclipse-plugin</artifactId>
        <configuration>
          <downloadSources>true</downloadSources>
          <buildcommands>
            <buildcommand>ch.epfl.lamp.sdt.core.scalabuilder</buildcommand>
          </buildcommands>
          <additionalProjectnatures>
            <projectnature>ch.epfl.lamp.sdt.core.scalanature</projectnature>
          </additionalProjectnatures>
          <classpathContainers>
            <classpathContainer>org.eclipse.jdt.launching.JRE_CONTAINER</classpathContainer>
            <classpathContainer>ch.epfl.lamp.sdt.launching.SCALA_CONTAINER</classpathContainer>
          </classpathContainers>
        </configuration>
      </plugin>
    </plugins>
  </build>
  <reporting>
    <plugins>
      <plugin>
        <groupId>org.scala-tools</groupId>
        <artifactId>maven-scala-plugin</artifactId>
        <configuration>
          <scalaVersion>${scala.version}</scalaVersion>
        </configuration>
      </plugin>
    </plugins>
  </reporting>
</project>

2.配置连接MySQL

根据官网介绍可以通过在src/main/resources/application.conf中配置（resources/application.conf不存在的话需要手动新建添加）配置内容如下：

# JDBC settings
db.default.driver=\"org.h2.Driver\"
db.default.url=\"jdbc:h2:file:./db/default\"
db.default.user=\"sa\"
db.default.password=\"\"
# Connection Pool settings
db.default.poolInitialSize=10
db.default.poolMaxSize=20
db.default.connectionTimeoutMillis=1000

# Connection Pool settings
db.default.poolInitialSize=5
db.default.poolMaxSize=7
db.default.poolConnectionTimeoutMillis=1000
db.default.poolValidationQuery=\"select 1 as one\"
db.default.poolFactoryName=\"commons-dbcp2\"

db.legacy.driver=\"org.h2.Driver\"
db.legacy.url=\"jdbc:h2:file:./db/db2\"
db.legacy.user=\"foo\"
db.legacy.password=\"bar\"

# MySQL example
db.default.driver=\"com.mysql.jdbc.Driver\"
db.default.url=\"jdbc:mysql://localhost/scalikejdbc\"

# PostgreSQL example
db.default.driver=\"org.postgresql.Driver\"
db.default.url=\"jdbc:postgresql://localhost:5432/scalikejdbc\"

我并没有采用这种方式，我直接在代码中进行了连接的初始化。如下：

import scalikejdbc.config._
import scalikejdbc._
import scala.collection.mutable.ListBuffer
  ScalikejdbcApp {
  Class.forName(\"com.mysql.jdbc.Driver\")
  ConnectionPool.singleton(\"jdbc:mysql://192.168.2.65:3306/ruozedb\",\"ruoze\",\"123456\")
  implicit val session = AutoSession

  def main(args: Array[String]): Unit = {
    create
    //insert(1,\"ruoruo\")
    //highlevelinsert(List(3,4),List(\"JJ\",\"星星\"))//顺序不连续没关系，但是id有重复就会报错
    //update(4,\"xingxing\")
    println(select())
    delete()
    ConnectionPool.close()//用完连接池要关闭
  }
  def create = {
    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()
    //如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
    //PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔
    //AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1
    //ENGINE 设置存储引擎，CHARSET 设置编码
  }
  //插入一条数据
    def insert(id:Int,name:String ): Unit ={
      implicit val session=AutoSession
      sql\"\"\"insert into Person(id,name)values (${id},${name})\"\"\".update.apply()
    }
  //插入两条数据。随便调用插入随机个数的数据还不会
  def highlevelinsert(id:List[Int],name:List[String])={
    sql\"\"\"insert into Person(id,name)values(${id(0)},${name(0)}),(${id(1)},${name(1)}) \"\"\".update().apply()
    println(s\"${id}(0),${name(0)}\")//List(3, 4)(0),JJ
  }
  def update(id:Int,name:String)={
    implicit val session=AutoSession
    sql\"update Person set name=${name}where id =${id}\".update().apply()
  }
  def select()={
    implicit val session=AutoSession
    //sql\"select * from Person\".map(x=>x.string(\"name\")).list().apply()//List(ruoruo, J?, xingxing)
    //sql\"select * from Person where Person.id=4\".map(x=>x.string(\"name\")).single().apply()//Some(xingxing)
   // sql\"select * from Person where Person.id=4\".map(x=>x.string(\"name\")).single().apply().get//xingxing
    sql\"select * from Person\".map(x=>(x.string(\"id\"),x.string(\"name\"))).list().apply()//List((1,ruoruo), (3,J?), (4,xingxing))
  }
def delete={
  //sql\"delete from Person where person.id=3\".update()//删除id=3,name=J总这条数据
  //sql\"delete from Person\".update()//删除Person这张表里面的所有数据，但是该表依然存在
  sql\"drop table if exists  person\".update()//删除整张表
}
}