通过scalike,在idea上面对MySQL进行增删改查
1. 配置maven工程的pom.xml
<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>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
object 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()//删除整张表
}
}