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](https://stackoverflow.com/questions/7695962/postgresql-password-authentication-failed-for-user-postgres), 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")
      }
```