■ はじめに

SparkからSnowflakeへの接続について考える
https://dk521123.hatenablog.com/entry/2023/03/19/013833

で、JDBCでもSnowflakeにアクセスすることに触れたが
今回は、ScalaでJDBCドライバからDBにアクセスすることについて触れる

【１】JDBCドライバ取得

１）PostgreSQL

libraryDependencies ++= Seq(
  "org.postgresql" % "postgresql" % "42.6.0",

https://mvnrepository.com/artifact/org.postgresql/postgresql

２）Snowflake

libraryDependencies ++= Seq(
  "net.snowflake" % "snowflake-jdbc" % "3.13.28",

https://mvnrepository.com/artifact/net.snowflake/snowflake-jdbc
https://docs.snowflake.com/ja/user-guide/jdbc-download#integrating-the-driver-into-a-maven-project

【２】JDBC ドライバー

１）JDBC ドライバークラス

[1] PostgreSQL

Class.forName("org.postgresql.Driver")

[2] Snowflake

Class.forName("net.snowflake.client.jdbc.SnowflakeDriver")

https://docs.snowflake.com/ja/user-guide/jdbc-configure#jdbc-driver-class
https://docs.snowflake.com/ja/user-guide/jdbc-api#object-driver

２）JDBC ドライバー接続文字列

[1] PostgreSQL

// jdbc:postgresql://<host>:<port>/<db_name>?<connection_params>
val connectionURL ="jdbc:postgresql://localhost:5432/sample_db"

[2] Snowflake

// Syntax
// jdbc:snowflake://<account_identifier>.snowflakecomputing.com/?<connection_params>
val connectionURL = "jdbc:snowflake://myorganization-myaccount.snowflakecomputing.com/?query_tag='folder%3Dfolder1%20folder2%26'

https://docs.snowflake.com/ja/user-guide/jdbc-configure#jdbc-driver-connection-string

【３】API

１）PostgreSQL

https://www.postgresql.jp/document/7.4/html/jdbc.html

２）Snowflake

* メソッドの中には、Snowflake独自の動きをするメソッドも
　ちらほらあるので使う前に確認しておいた方がよさそう

オブジェクト: Statement
https://docs.snowflake.com/ja/user-guide/jdbc-api#object-statement
オブジェクト： PreparedStatement
https://docs.snowflake.com/ja/user-guide/jdbc-api#object-preparedstatement

【４】サンプル

１）PostgreSQL

例１：SELECT文

import java.sql.DriverManager
import java.sql.Connection

object  Hello {
  def main(args: Array[String]) {
    val dbUrl = "jdbc:postgresql://localhost:5432/sample_db"
    val dbUser = "postgres"
    val dbPassword = "password"

    var dbConnector: Connection = null
    Class.forName("org.postgresql.Driver")
    try {
      dbConnector = DriverManager.getConnection(dbUrl, dbUser, dbPassword)
      val sql = "SELECT user_id, user_name, created_at FROM users LIMIT ?"
      val limit = 5
      val sqlStatement = dbConnector.prepareStatement(sql)
      sqlStatement.setInt(1, limit)
      val resultSet = sqlStatement.executeQuery()
      var index = 0
      while (resultSet.next()) {
        println(s"*[${index}]******************")
        println("user_id: " + resultSet.getInt(1))
        println("user_name: " + resultSet.getString("user_name"))
        println("created_at: " + resultSet.getTimestamp("created_at"))
        println("*******************")
        index = index + 1
      }
      resultSet.close()
      sqlStatement.close()
    } finally {
      if (dbConnector != null) {
        dbConnector.close()
      }
    }
    println("Done...")
  }
}

サンプルデータ

CREATE TABLE IF NOT EXISTS "users" (
    "user_id" INTEGER NOT NULL,
    "user_name" VARCHAR(50) NOT NULL,
    "created_at" TIMESTAMP NULL DEFAULT 'CURRENT_TIMESTAMP',
    PRIMARY KEY ("user_id")
);

INSERT INTO "users" ("user_id", "user_name", "created_at") VALUES
    (1, 'Mike', '2019-12-19 21:50:21.3885'),
    (2, 'Tom', '2019-12-19 21:50:21.3885'),
    (3, 'Sam', '2019-12-19 21:50:21.3885'),
    (4, 'Kevin', '2019-12-19 21:50:27.483248'),
    (5, 'Smith', '2019-12-19 21:50:27.484514'),
    (6, 'Ken', '2019-12-19 21:50:27.484514'),
    (10, 'Sam', '2020-08-26 22:39:55.175736'),
    (12, 'Naomi', '2020-08-26 22:39:55.175736'),
    (20, 'Tim', '2021-08-02 16:39:55.175736'),
    (30, 'kevin', '2021-08-02 16:40:55.175736');

例２：複数SQL文実行 / addBatch

import java.sql.DriverManager
import java.sql.Connection

object  Hello {
  def main(args: Array[String]) {
    val dbUrl = "jdbc:postgresql://localhost:5432/sample_db"
    val dbUser = "postgres"
    val dbPassword = "password"

    var dbConnector: Connection = null
    Class.forName("org.postgresql.Driver")
    try {
      dbConnector = DriverManager.getConnection(dbUrl, dbUser, dbPassword)
      val statement = dbConnector.createStatement()
      statement.addBatch("/*ex1*/ DROP TABLE IF EXISTS test;")
      statement.addBatch("/*ex2*/ CREATE TABLE test(id integer, name varchar);")
      statement.addBatch("/*ex3*/ INSERT INTO test VALUES(1, 'Mike');")
      statement.addBatch("/*ex4*/ INSERT INTO test VALUES(2, 'Smith');")
      val results = statement.executeBatch()
      println(s"Result ${results.length}")
      statement.close()
    } finally {
      if (dbConnector != null) {
        dbConnector.close()
      }
    }
    println("Done...")
  }
}

例３：複数SQL文実行 / execute

import java.sql.DriverManager
import java.sql.Connection

object  Hello {
  def main(args: Array[String]) {
    val dbUrl = "jdbc:postgresql://localhost:5432/sample_db"
    val dbUser = "postgres"
    val dbPassword = "password"

    var dbConnector: Connection = null
    Class.forName("org.postgresql.Driver")
    try {
      dbConnector = DriverManager.getConnection(dbUrl, dbUser, dbPassword)
      val statement = dbConnector.createStatement()
      val param11 = 1
      val param12 = "Mike"
      val param21 = 2
      val param22 = "Kevin"
      val sql = s"""
      -- ex1
      DROP TABLE IF EXISTS test2;
      -- ex2
      CREATE TABLE test2 (
        id integer,
        name varchar
      );
      -- ex3
      INSERT INTO test2 VALUES(${param11}, '${param12}');
      INSERT INTO test2 VALUES(${param21}, '${param22}');
      INSERT INTO test2 VALUES(3, 'Tommy');
      """
      val isOk = statement.execute(sql)
      println(s"Result ${isOk}")
      statement.close()
    } finally {
      if (dbConnector != null) {
        dbConnector.close()
      }
    }
    println("Done...")
  }
}

プログラムの超個人的なメモ

Memo for Programming.

【Scala】Scala ～ JDBC / DB接続～

■ はじめに

目次

【１】JDBCドライバ取得

１）PostgreSQL

２）Snowflake

【２】JDBC ドライバー

１）JDBC ドライバークラス

２）JDBC ドライバー接続文字列

【３】API

１）PostgreSQL

２）Snowflake

【４】サンプル

１）PostgreSQL

関連記事