Scala throw exception for reading null bigdecimal from query result

Question

I have a method with a body like this:

try {
  val query = "SELECT decimalValue as decimal_value FROM table";

  val con = new DbConnection
  val statement = con.preparedStatement(query)

  val rset = statement.executeQuery()

  var myDecimalValue = 0
  if (rset.next()) {
     myDecimalValue = rset.getBigDecimal("decimal_value")
  }
}
catch {
  case e: Exception => println(e.getMessage)
}

If the query result is null, I get an exception: null value for BigDecimal

but, from getBigDecimal method of ResultSet.java I found this definition:

* @return the column value (full precision);
* if the value is SQL <code>NULL</code>, the value returned is
* <code>null</code> in the Java programming language.

How can avoid this? I really need to declare my parameter as Option like this (below), if I know the result can contains null values?!:

var myDecimalValue: Option[BigDecimal] = Option(BigDecimal(0))

and inside if:

val rsetValue = rset.getBigDecimal("decimal_value")
if (rsetValue != null) myDecimalValue = Option(rsetValue)

update: stracktrace

java.lang.IllegalArgumentException: null value for BigDecimal
        at scala.math.BigDecimal.<init>(BigDecimal.scala:405)
        at scala.math.BigDecimal$.apply(BigDecimal.scala:333)
        at scala.math.BigDecimal$.apply(BigDecimal.scala:330)
        at scala.math.BigDecimal$.javaBigDecimal2bigDecimal(BigDecimal.scala:345)
        at ...
        at ...
        at ...
        at java.lang.Thread.run(Thread.java:748)

Answer 1

You really don't want to have null leaking into Scala code. When interfacing with Java code that can return null the best way is to wrap the result into Option(x). That will take care of turning null into None.

Also avoid var, prefer val.

So the immediate fix to your program would be

val myDecimalValue = if (rs.next) Option(rs.getBigDecimal("decimal_value")) else None

---

But what I would also do beyond that is find a reasonable database access library. The full-on Scala solutions like Slick or Doobie are probably a bit challenging for beginners, but raw JDBC is just too painful. Maybe something like Apache DbUtils? No one should need to manually iterate over result sets and pull out columns into variables. You should be getting back Lists of model classes from that library.