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.
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
while ( resultSet.next() ) {
for (c <- all_columns_list) {
var value = resultSet.getString(c)
printf(s"$value, ")
}
printf("%n")
}
Recommended Posts