Use PostgreSQL in Scala

Upgrade PostgreSQL from 9.5 to 9.6

First of all, as I am using Ubuntu 16.04 and its default PostgreSQL version was 9.5, so I had to upgrade PostgreSQL to 9.6.
(The reason I needed to upgrade PostgreSQL was because some features are not supported in 9.5 e.g. "ALTER TABLE ADD COLUMN IF NOT EXISTS".)

This is how to upgrade PostgreSQL to 9.6 if you are using Ubuntu 16.04.
First, you need to install postgresql-9.6 package into your system.
https://askubuntu.com/questions/831292/how-to-install-postgresql-9-6-on-any-ubuntu-version

sudo add-apt-repository ""deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main""
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6

Next, (in my case), you need to switch your postgresql cluster from 9.5 to 9.6. You can follow this link: https://gist.github.com/delameko/bd3aa2a54a15c50c723f0eef8f583a44

sudo pg_dropcluster 9.6 main --stop
sudo pg_upgradecluster 9.5 main
sudo pg_dropcluster 9.5 main

Finally, you should set cluster_name = '9.6/main' in /etc/postgresql/9.6/main/postgresql.conf.

Connect to PostgreSQL using JDBC

What is JDBC?

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.jdbc_pg.doc/ids_jdbc_011.htm
Java™ database connectivity (JDBC) is the JavaSoft specification of a standard application programming interface (API) that allows Java programs to access database management systems. The JDBC API consists of a set of interfaces and classes written in the Java programming language.

To use JDBC in your scala code, you need to add dependencies to your scala project. (In my case, add dependencies to pom.xml like below.) https://mvnrepository.com/artifact/org.postgresql/postgresql/42.1.1

<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.1.1</version>
</dependency>

After loading postresql driver, you can connect to your PostgreSQL database by using "java.sql.DriverManager.getConnection". Here, "sql_url + database_name" can be like "jdbc:postgresql://localhost:5432/test_db" in string.
(I followed the below instructions. Please refer it if you need.)
https://jdbc.postgresql.org/documentation/head/load.html
http://alvinalexander.com/scala/scala-jdbc-connection-mysql-sql-select-example

    var sql_connection: Connection = null
    Class.forName(driver_name)// Load the driver     
    sql_connection = DriverManager.getConnection(sql_url + database_name, sql_user, sql_password) // Make the connection

Troubleshooting memo: Fail to connect to PostgreSQL

When I tried to connect to SQL, I got the error "java.sql.SQLException: No suitable driver found for jdbc:postgres://localhost/test_db" but the code seemed nothing wrong.
According to the post, this is because "postgres" DB user's password is not set by default. So I added password setting to "postgres" user like below. Then it worked and successfully connected to DB.

ALTER USER postgres PASSWORD 'password';

Create a new table

First, the code below create a new table if not exists. In this case, the new table has only one column for timestamp when the transaction executed. Here, by using "DEFAULT", you can set the default value which is automatically inserted into the column if no value is specified when executing a transaction. CURRENT_TIMESTAMP is a function which returns current timestamp.
To execute SQL statement, you just pass SQL statement as String format to "prepareStatement" in "java.sql" module and execute it by "executeUpdate()".

      val prepare_statement = sql_connection.prepareStatement(s" CREATE TABLE IF NOT EXISTS $table_name(transaction_time timestamp DEFAULT CURRENT_TIMESTAMP)")
      prepare_statement.executeUpdate()
      prepare_statement.close()

To add columns to the new table, just send a statement "ALTER TABLE [TABLE_NAME] ADD COLUMN [COLUMN] [TYPE]".

        val prepare_statement_add_column = sql_connection.prepareStatement(s"ALTER TABLE $table_name ADD COLUMN IF NOT EXISTS $column_to_add VARCHAR")
        prepare_statement_add_column.executeUpdate()
        prepare_statement_add_column.close()

Insert data into table

If you are already clear of what values should be added, the basic of this step is nothing different from the step of creating a new table. Just change the string of SQL statement for inserting data like below.

      val prepare_statement_add_column = sql_connection.prepareStatement(s"INSERT INTO $table_name ($column_label_list_string) VALUES ($value_list_string) ")
      prepare_statement_add_column.executeUpdate()
      prepare_statement_add_column.close()

Read table

After some data is added to your table, you may want to check the table. In order to read a table in Scala, just like you did when creating a table and inserting data, you need to send a statement "select * from your_table".
According to the instruction here: http://alvinalexander.com/scala/scala-jdbc-connection-mysql-sql-select-example
You can do this like below.

      val statement = sql_connection.createStatement()
      val resultSet = statement.executeQuery("select * from test_table")
      while ( resultSet.next() ) {
        val type_column = resultSet.getString("type")
        val color_column = resultSet.getString("color")
        val install_date_column = resultSet.getString("install_date")
        println("type, color, install_date = " + type_column + ", " + color_column + ", " + install_date_column)
      }

Using this loop while ( resultSet.next() ) { }, you can read through all rows in the table. However, here is a problem. In the above case, you need to explicitly specify what column's value you want to retrieve. What if you have a table with dozens columns or you don't know what columns your table have?

There may be other good workaroud, but in my case, I wrote the following code.

1. First of all, extract all columns in your table

You can get "resultSet" from statement.executeQuery(s"select * from $table_name") like the above example. Next, you can take out metadata of it resultSet.getMetaData(). Using the number of columns resultSet_metadata.getColumnCount(), you can run the for loop and retrive all column_name in the table and put it into "all_columns" List. (Note that, "all_columns" is acutually not a list as a list is immutable in Scala so you can't add a new value to it. I use ListBuffer instead. The real type of "all_columns" is ListBuffer.)

        // create the statement, and run the select query
      val statement = sql_connection.createStatement()
      val resultSet = statement.executeQuery(s"select * from $table_name")
        //Get metadata of resultSet
      val resultSet_metadata = resultSet.getMetaData()
        //Get the number of columns
      val column_count = resultSet_metadata.getColumnCount()
      for (i <- 1 to column_count ) {
        val column_name = resultSet_metadata.getColumnName(i)
        all_columns += column_name
      }

Troubleshooting memo: Update a list in Scala

In Scala, a list is an immutable data structure so that you can't add elements to a Scala List. So if you want to add values into a list in scala using for loop, you need to use ListBuffer instead of Scala List Type. In this case, you can define all_columns as "ListBuffer[String]" type.

import scala.collection.mutable.ListBuffer
      var all_columns= new ListBuffer[String]()

Once you put all date you want into the list, then you can change its type to List.

    val all_columns_list = all_columns.toList

2. Show all data in table

Now, you know what columns the table has. So what you need to do is to return data of all columns in "resultSet.next()" using for loop.

      while ( resultSet.next() ) {
        for (c <- all_columns_list) {
          var value = resultSet.getString(c)
          printf(s"$value, ")
        }
        printf("%n")
      }