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
  1. 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")
      }

Recommended Posts

Use PostgreSQL in Scala
Use java.time in Jackson
Parse Json in Scala
Use Interceptor in Spring
Use OpenCV in Java
Use MouseListener in Processing
Use PreparedStatement in Java
[Rails] How to use PostgreSQL in Vagrant environment
How to use Z3 library in Scala with Eclipse
How to use JDD library in Scala with Eclipse
Use ruby variables in javascript.
Use Redis Stream in Java
Use multiple checkboxes in Rails6!
How to use Lombok in Spring
Use voice recognition function in Unity
Do Scala Option.or Null in Java
Use constructor with arguments in cucumber-picocontainer
Use PostgreSQL inet type with DbUnit
Let's use Twilio in Java! (Introduction)
[Rails] Use cookies in API mode
Use JDBC with Java and Scala.
[Java] Do not use "+" in append!
Use composite keys in Java Map.
Use the Findbugs plugin in Eclipse
How to use InjectorHolder in OpenAM
How to use classes in Java?
Use completion in Eclipse on mac
Address already in use workaround (Windows)
Do you use Stream in Java?
[Cloud9] Address already in use [Solution]
Multilingual Locale in Java How to use Locale
Use OpenCV_Contrib (ArUco) in Java! (Part 2-Programming)
How to use custom helpers in rails
Do not use instance variables in partial
[Docker] Use environment variables in Nginx conf
Use PostgreSQL data type (jsonb) from Java
How to use named volume in docker-compose.yml
Use inequality comparison operators in MyBatis SQL
Use docker in proxy environment on ubuntu 20.04.1
[Java] Use cryptography in the standard library
How to use Docker in VSCode DevContainer
How to use MySQL in Rails tutorial
Use "Rhino" which runs JavaScript in Java
Use selenium (Firefox) in Ruby in WSL environment
Use DynamoDB query method in Spring Boot
Use selenium (Chrome) in Ruby in WSL environment
How to use environment variables in RubyOnRails
I want to use @Autowired in Servlet
Postgresql gem doesn't install properly in Ruby 3.0
Understand in 5 minutes !! How to use Docker
How to use credentials.yml.enc introduced in Rails 5.2
Steps to Use JConsole in Cognos Analytics
How to use ExpandableListView in Android Studio