I finished scala course provided by
Martin Odersky. And just want to test them in some real case scenario. I had quite easy task: there is csv file, with headers, which need to be imported to database. Normal times when I used java I prepared myself for few days of coding. Typical steps are:
- csv: find/list java csv processing libraries
- csv: read / investigate few of them
- csv: start playing, when one do not work as expected - check another one
- db: fight with proper driver
- db: find how to connect
- db: prepare statements/sqls/ etc..
- finally connect csv+db into one solution, and make it work smoothly
And after few days I would have working solution with probably few hundred lines of code...(not to mention tests/documentation).
In scala (and I am novice):
1. created my single object "importer.scala"
2. whole csv solution finished with adding to my build.sbt file single line:
libraryDependencies += "com.github.tototoshi" %% "scala-csv" % "1.1.2"
as my csv file using ";" as separator I put :
implicit object MyFormat extends DefaultCSVFormat {
override val delimiter: Char = ';' }
then to main function I added 2 lines of code:
val reader = CSVReader.open("myfile.csv")(MyFormat)
val full = reader.allWithHeaders
and ... that is it - whole csv solution done in less than 15 mins! (and yes in java I also used sbt like solution - i.e.:maven)
3. DB part:
Connection - again to build.sbt, added just one line (I'm using MySQL):
libraryDependencies += "mysql" % "mysql-connector-java" % "5.1.34"
.. and again to main function just added database connection
val dbc = "jdbc:mysql://localhost:3306/mydb?user=me&password=123"
Class.forName("com.mysql.jdbc.Driver")
val conn = DriverManager.getConnection(dbc)
and coding ... lets go ;-) :
try {
full.foreach(fields => processFields(fields, conn))
} finally {
conn.close
}
I know that I could do everything inside "full.foreach" but I had some old java habits to create methods for different actions... so I just added processFields function.
def processFields(in: Map[String, String], conn: java.sql.Connection) {
val l: MutableList[String] = new MutableList[String]()
fieldTypes.foreach(f =>
l += (in.get(f).get).replace("\"", "\\\""))
val sql = "INSERT INTO temp_products (" + myTypes.mkString(",") + ") VALUES ( " + "\"" + l.mkString("\",\"") + "\")"
val prep = conn.prepareStatement(sql)
prep.executeUpdate
}
As you see I had issue with ["] sign - which sometimes appeared inside csv fields.
This is plain/native sql - and whole solution takes me less than 2 hours.
Must say: I was little disappointed with ... so little effort to make things work.
And all I had is 60 lines of code - I really don't see reason to write comments/documentation as this is so obvious (if you know scala) that basically anything more will be overhead.
In next post I will show you about slick - proper DB/ORM mapping in scala and .. how easy is to do reverse engineering - generate objects from DB tables and do some crazy sqls without sql ;-)
