[JAVA] Utiliser PostgreSQL dans 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 -->

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)")

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")

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) ")

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, ")

Recommended Posts

Utiliser PostgreSQL dans Scala
Utilisez java.time avec Jackson
Parse Json dans Scala
Utilisez Interceptor au printemps
Utiliser OpenCV avec Java
Utiliser MouseListener avec le traitement
Utiliser PreparedStatement en Java
[Rails] Comment utiliser PostgreSQL dans l'environnement Vagrant
Comment utiliser la bibliothèque Z3 dans Scala avec Eclipse
Comment utiliser la bibliothèque JDD dans Scala avec Eclipse
Utilisez des variables ruby en javascript.
Utilisez plusieurs cases à cocher dans Rails6!
Comment utiliser Lombok au printemps
Faites Option.ou Null de Scala en Java
Utiliser le constructeur avec des arguments dans cucumber-picocontainer
Utiliser le type inet PostgreSQL avec DbUnit
Utilisons Twilio en Java! (Introduction)
[Rails] Utiliser des cookies en mode API
Utilisez JDBC avec Java et Scala.
[Java] N'utilisez pas "+" dans append!
Utilisez des clés composites dans Java Maps.
Utiliser le plugin Findbugs dans Eclipse
Comment utiliser InjectorHolder dans OpenAM
Comment utiliser les classes en Java?
Utiliser la complétion dans Eclipse sur Mac
Solution de contournement d'adresse déjà utilisée (Windows)
Utilisez-vous Stream en Java?
[Cloud9] Adresse déjà utilisée [Solution]
Prise en charge multilingue de Java Comment utiliser les paramètres régionaux
Utilisez OpenCV_Contrib (ArUco) avec Java! (Partie 2-Programmation)
[Docker] Utiliser des variables d'environnement dans Nginx conf
Utiliser le type de données PostgreSQL (jsonb) à partir de Java
Comment utiliser le volume nommé dans docker-compose.yml
Utiliser des opérateurs de comparaison d'inégalités dans MyBatis SQL
Utiliser docker dans un environnement proxy sur ubuntu 20.04.1
[Java] Utiliser la technologie cryptographique avec les bibliothèques standard
Comment utiliser Docker dans VSCode DevContainer
Comment utiliser MySQL dans le didacticiel Rails
Utilisez "Rhino" qui exécute JavaScript en Java
Utiliser la méthode de requête DynamoDB avec Spring Boot
Comment utiliser les variables d'environnement dans RubyOnRails
Je veux utiliser @Autowired dans Servlet
Comprendre en 5 minutes !! Comment utiliser Docker
Comment utiliser credentials.yml.enc introduit à partir de Rails 5.2
Procédure d'utilisation de JConsole avec Cognos Analytics
Comment utiliser ExpandableListView dans Android Studio