How to send an Option None data to a database in Scala 2

My aim is to send data to a table in postgre database from Scala. The problem is the null cases. I mean there are strict data types of each columns in database and I am reading data from Kafka that means column can be null. But I want to write that data to database table at the end.

My trial is as follws:

import java.sql.{Connection, DriverManager, PreparedStatement}

case class Person(name: Option[String], value: Option[Double])

val value1 = Person(Some("brak"), Some(26.5))
println(value1)

val value2 = Person(Some("mrk"),None)
println(value2)

These are two rows that I want to send to postgre table.

Postgre table definition:

create table person(
name varchar(256) nullable,
value numeric(3,1) nullable)

And I sent data like that:

// Define the connection parameters for the Postgresql database
val url = "jdbc:postgresql://url/db"
val username = "username"
val password = "pass"

val insertStatement = "INSERT INTO person(name, value) VALUES (?,?)"

Class.forName("org.postgresql.Driver")
val connection: Connection = DriverManager.getConnection(url, username, password)
val preparedStatement: PreparedStatement = connection.prepareStatement(insertStatement)
value2.name.foreach(preparedStatement.setString(1, _)
)
value2.value.foreach {
  case Some(value) => preparedStatement.setDouble(2, value)
  case None => preparedStatement.setNull(2, java.sql.Types.DOUBLE)
}
preparedStatement.executeUpdate()

// Close the resources
preparedStatement.close()
connection.close()

With that method I could send value1 without any problem. But I took the error below for value2 which has a null value in second column.

Error is as follows:

<console>:49: error: constructor cannot be instantiated to expected type;
 found   : Some[A]
 required: Double
         case Some(value) => preparedStatement.setDouble(2, value)
              ^
<console>:50: error: pattern type is incompatible with expected type;
 found   : None.type
 required: Double
         case None => preparedStatement.setNull(2, java.sql.Types.DOUBLE)
              ^

How can i solve that problem? Or how can i send nullable data without violating scala and postgres data types in different methods?

Remark: I shouldn't use Spark because of the architectural things.

Solution

I can't comment so I will address the problem that you're having right now.

value2.value.foreach {
  case Some(value) => preparedStatement.setDouble(2, value)
  case None => preparedStatement.setNull(2, java.sql.Types.DOUBLE)
}

so here you're using foreach for the value2.value which as stated with the error is expecting Double. Your pattern matching however says it is expecting an Option. Therefore compiler fails.

val _ = value2.value match {
  case Some(value) => preparedStatement.setDouble(2, value)
  case None => preparedStatement.setNull(2, java.sql.Types.DOUBLE)
}

should be a fix for your case. I added val _ = because setDouble and setNull functions are Unit types.