Category : Scala | Sub Category : Scala Programs | By Prasad Bonam Last updated: 2020-10-10 04:33:49 Viewed : 525
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>
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 connection: Connection = null
try {
// make the connection
Class.forName(driver)
connection = DriverManager.getConnection(url, username, password)
// 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:
scalalibraryDependencies += "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:
scalaimport 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:
source_table
.target_table
.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:
scalalibraryDependencies += "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:
scalaimport 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
"source_table"
and "target_table"
with the names of your HBase source and target tables.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.