Scala with JDBC connection and SQL INSERT

Category : Scala | Sub Category : Scala Programs | By Prasad Bonam Last updated: 2020-10-10 04:33:49 Viewed : 511


Scala with JDBC connection and SQL INSERT 

·        connecting to a MySQL database server on   local computer

·        running a SQL INSERT and  SELECT query against the employee table of the mysql database 

Dependency:

download the jar : mysql-connector-java-5.1.49 

(OR)

MavenRepository:

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->

<dependency>

    <groupId>mysql</groupId>

    <artifactId>mysql-connector-java</artifactId>

    <version>5.1.49</version>

</dependency>

 Table Name: employee 

Columns Names:

    

Example:

Following example illustrates about Scala JDBC Connection and SQL INSERT and SELECT query  

Save the file as −  ScalaJdbcInsert.scala.  

ScalaJdbcInsert.scala 

import java.sql.DriverManager

import java.sql.Connection 

object ScalaJdbcInsert {

  def main(args: Array[String]) {

    // connect to the database named "test" on the localhost

    val driver = "com.mysql.jdbc.Driver"

    val url = "jdbc:mysql://localhost/test" //DB name test

    val username = "root"

    val password = "" 

    var connectionConnection = null 

    try {

      // make the connection

      Class.forName(driver)

      connection = DriverManager.getConnection(urlusernamepassword) 

      // create the statement, and run the select query

      val statement = connection.createStatement()   

      val sql = "INSERT INTO employee " +

        "VALUES (102, `Sathya`, 4000, `Mumbai`)";

      val update = statement.executeUpdate(sql);

      println(update + " Record is  updated")

      val resultSet = statement.executeQuery("SELECT * FROM employee ")

      while (resultSet.next()) {

        val empId = resultSet.getString("empId")

        val empName = resultSet.getString("empName")

        val empSal = resultSet.getString("empSal")

        val empAddr = resultSet.getString("empAddr")

        println("empId:" + empId + " empName " + empName + " empSal:" + empSal + " empAddr: " + empAddr)

      } 

    } catch {

      case e => { println("exception "e.printStackTrace) }

    } finally {

      connection.close()

    }

  } 

}

Compile and run the above example as follows −

C:>scalac ScalaJdbcInsert.scala

C:>scala ScalaJdbcInsert 

Output:

1 Record is updated

empId:101

empName RamPrasad

empSal:20000

empAddr: Bangalore

empId:102

empName Sathya

empSal:4000

empAddr: Mumbai


To fetch data from a database and insert data in Scala, you can use a database driver and a library like Slick or JDBC. In this example, will show you how to use JDBC to connect to a database, fetch data from a table, and insert data into another table. Use H2, an in-memory database, for this example. You can replace it with your database of choice.

Step 1: Set Up Dependencies

To use JDBC in Scala, you need to include the JDBC driver for your database in your project. For H2, you can add the following dependency to your build file:

scala
libraryDependencies += "com.h2database" % "h2" % "1.4.200"

Step 2: Fetch Data from Database and Insert Data

Here is an example of fetching data from a database table and inserting data into another table using JDBC:

scala
import java.sql.{Connection, DriverManager, PreparedStatement, ResultSet} object DatabaseExample { def main(args: Array[String]): Unit = { // JDBC URL, username, and password of the database val jdbcUrl = "jdbc:h2:mem:testdb" val jdbcUsername = "sa" val jdbcPassword = "" // Create a JDBC connection var connection: Connection = null try { Class.forName("org.h2.Driver") connection = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword) // Fetch data from a source table val sourceQuery = "SELECT id, name FROM source_table" val sourceStatement = connection.prepareStatement(sourceQuery) val resultSet: ResultSet = sourceStatement.executeQuery() // Insert data into a target table val targetQuery = "INSERT INTO target_table (id, name) VALUES (?, ?)" val targetStatement: PreparedStatement = connection.prepareStatement(targetQuery) // Process each row from the source and insert into the target while (resultSet.next()) { val id = resultSet.getInt("id") val name = resultSet.getString("name") // Insert the data into the target table targetStatement.setInt(1, id) targetStatement.setString(2, name) targetStatement.executeUpdate() } println("Data inserted successfully.") } catch { case e: Exception => e.printStackTrace() } finally { // Close the resources if (connection != null) { connection.close() } } } }

Step 3: Running the Example

When you run the above code, it will:

  1. Connect to an H2 in-memory database.
  2. Fetch data from a hypothetical source_table.
  3. Insert the fetched data into a hypothetical target_table.
  4. Print "Data inserted successfully." if the process is successful.

Please replace the database URL, credentials, table names, and SQL queries with the appropriate values for your specific database and use case.

Step 4: Output

The output of this code will be "Data inserted successfully." printed to the console if the data insertion is successful. Additionally, data will be transferred from the source table to the target table in the database.


To fetch data from HBase and insert data in Scala, you can use the HBase Java API, which provides programmatic access to HBase tables. Here is an example of how to read data from an HBase table and insert data into another HBase table using the HBase Java API. Make sure you have the HBase client libraries available in your project.

Step 1: Set Up Dependencies

You need to include the HBase client libraries in your projects dependencies. Add the following dependencies to your build file (e.g., SBT or Maven):

For SBT:

scala
libraryDependencies += "org.apache.hbase" % "hbase-client" % "x.x.x"

For Maven, add the following to your pom.xml (replace x.x.x with the appropriate version):

xml
<dependency> <groupId>org.apache.hbase</groupId> <artifactId>hbase-client</artifactId> <version>x.x.x</version> </dependency>

Step 2: Fetch Data from HBase and Insert Data

Here is an example of fetching data from one HBase table and inserting data into another HBase table:

scala
import org.apache.hadoop.conf.Configuration import org.apache.hadoop.hbase.{HBaseConfiguration, HColumnDescriptor, HTableDescriptor, TableName} import org.apache.hadoop.hbase.client.{Connection, ConnectionFactory, Put, Result, Scan, Table} import org.apache.hadoop.hbase.util.Bytes object HBaseExample { def main(args: Array[String]): Unit = { // Configuration for HBase val conf: Configuration = HBaseConfiguration.create() // Create a connection to HBase val connection: Connection = ConnectionFactory.createConnection(conf) try { // Fetch data from the source table val tableName = TableName.valueOf("source_table") val sourceTable: Table = connection.getTable(tableName) val scan = new Scan() val scanner = sourceTable.getScanner(scan) // Insert data into the target table val targetTableName = TableName.valueOf("target_table") val targetTable: Table = connection.getTable(targetTableName) scanner.forEach { result: Result => val key = result.getRow // Assuming you have a row key val columnValue = result.getValue(Bytes.toBytes("cf"), Bytes.toBytes("column_name")) val put = new Put(key) put.addColumn(Bytes.toBytes("cf"), Bytes.toBytes("column_name"), columnValue) targetTable.put(put) } println("Data inserted successfully.") } catch { case e: Exception => e.printStackTrace() } finally { // Close the HBase connection if (connection != null) { connection.close() } } } }

Step 3: Running the Example

  • Replace "source_table" and "target_table" with the names of your HBase source and target tables.
  • Adjust the Scan and data extraction logic according to your specific requirements.

Step 4: Output

The output of this code will be "Data inserted successfully." printed to the console if the data insertion is successful. Additionally, data will be transferred from the source table to the target table in HBase.

Please ensure that you have the HBase cluster configuration properly set up and accessible from your Scala application.

Search
Related Articles

Leave a Comment: