scala [SlickException：读取列的 NULL 值 (USERS /670412212).LOGIN_ID]

[SlickException: Read NULL value for column (USERS /670412212).LOGIN_ID]

提问by Abhishek Pande

I am using Slick 1.0.0 with play framework 2.1.0. I am getting the following error when I query my Userstable. The value of LOGIN_IDis null in DB. The query I am executing is:

我正在使用带有播放框架 2.1.0 的 Slick 1.0.0。查询Users表时出现以下错误。LOGIN_IDDB 中的值为空。我正在执行的查询是：

    val user = { for { u <- Users if u.providerId === id.id } yield u}.first

This results in the following error:

这会导致以下错误：

play.api.Application$$anon: Execution exception[[SlickException: Read NULL value for column (USERS /670412212).LOGIN_ID]]
    at play.api.Application$class.handleError(Application.scala:289) ~[play_2.10.jar:2.1.0]
    at play.api.DefaultApplication.handleError(Application.scala:383) [play_2.10.jar:2.1.0]
    at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$$anonfun$apply.apply(PlayDefaultUpstreamHandler.scala:314) [play_2.10.jar:2.1.0]
    at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$$anonfun$apply.apply(PlayDefaultUpstreamHandler.scala:312) [play_2.10.jar:2.1.0]
    at play.api.libs.concurrent.PlayPromise$$anonfun$extend1.apply(Promise.scala:113) [play_2.10.jar:2.1.0]
    at play.api.libs.concurrent.PlayPromise$$anonfun$extend1.apply(Promise.scala:113) [play_2.10.jar:2.1.0]
scala.slick.SlickException: Read NULL value for column (USERS /670412212).LOGIN_ID
    at scala.slick.lifted.Column$$anonfun$getResult.apply(ColumnBase.scala:29) ~[slick_2.10-1.0.0.jar:1.0.0]
    at scala.slick.lifted.TypeMapperDelegate$class.nextValueOrElse(TypeMapper.scala:158) ~[slick_2.10-1.0.0.jar:1.0.0]
    at scala.slick.driver.BasicTypeMapperDelegatesComponent$TypeMapperDelegates$StringTypeMapperDelegate.nextValueOrElse(BasicTypeMapperDelegatesComponent.scala:146) ~[slick_2.10-1.0.0.jar:1.0.0]
    at scala.slick.lifted.Column.getResult(ColumnBase.scala:28) ~[slick_2.10-1.0.0.jar:1.0.0]
    at scala.slick.lifted.Projection15.getResult(Projection.scala:627) ~[slick_2.10-1.0.0.jar:1.0.0]
    at scala.slick.lifted.Projection15.getResult(Projection.scala:604) ~[slick_2.10-1.0.0.jar:1.0.0]

My User table is defined as :

我的用户表定义为：

package models

import scala.slick.driver.MySQLDriver.simple._

case class User(userId:String,email:String,loginId:String,fullName:String,firstName:String,lastName:String,location:String,homeTown:String,providerId:String,provider:String,state:String,zip:String,accessKey:String,refreshKey:String,avatarUrl:String)

object Users extends Table[User]("USERS") {
  def userId = column[String]("USER_ID", O.PrimaryKey) // This is the primary key column
  def email =  column[String]("EMAIL",O.NotNull)
  def loginId = column[String]("LOGIN_ID",O.Nullable)
  def fullName = column[String]("FULL_NAME",O.NotNull)
  def firstName = column[String]("FIRST_NAME",O.Nullable)
  def lastName = column[String]("LAST_NAME",O.Nullable)
  def location = column[String]("LOCATION",O.Nullable)
  def homeTown = column[String]("HOME_TOWN",O.Nullable)
  def providerId = column[String]("PROVIDER_ID",O.Nullable)
  def provider = column[String]("PROVIDER",O.Nullable)
  def state = column[String]("STATE",O.Nullable)
  def zip = column[String]("ZIP",O.Nullable)
  def accessKey = column[String]("ACCESS_KEY",O.Nullable)
  def refreshKey = column[String]("REFRESH_KEY",O.Nullable)
  def avatarUrl = column[String]("AVATAR_URL",O.Nullable)

  // Every table needs a * projection with the same type as the table's type parameter
  def * = userId ~ email ~ loginId ~ fullName ~ firstName ~ lastName ~ location ~ homeTown ~ providerId ~ provider ~ state ~ zip ~ accessKey ~ refreshKey ~ avatarUrl <> (User,User.unapply _)
}

Please help. It looks like Slick can not handle Null values from DB?

请帮忙。看起来 Slick 无法处理来自 DB 的 Null 值？

回答by i.am.michiel

Your case class is not ok. If you use O.Nullable, all your properties have to be Option[String].

你的案例类不行。如果您使用O.Nullable，则您的所有属性都必须是 Option[String]。

回答by iwein

If you get this error, you'll have to either make the properties O.Nullable, oryou have to specify that your query returns an option.

如果出现此错误，则必须创建属性O.Nullable，或者必须指定查询返回一个选项。

For example let's say you do a rightJoinyou might not want to make the properties of the right record optional. In that case you can customize the way you yield your results using .?

例如，假设您执行了一项操作，rightJoin您可能不想将正确记录的属性设为可选。在这种情况下，您可以使用自定义产生结果的方式.?

val results = (for {
  (left, right) <- rightRecord.table rightJoin leftRecord.table on (_.xId === _.id)
  } yield (rightRecord.id, leftRecord.name.?)).list
results map (r => SomeJoinedRecord(Some(r._1), r._2.getOrElse(default)))

回答by ForeverLearner

This problem arises if a column contains a null value and at runtime it gets a null in the column response. If you see in the code below, my cust_id is nullable, but it has no null values. Since, there is a job that makes sure that is is never null. So, the below mapping works. However, it is the best practice to look at your table structure and create the class accordingly. This avoids nasty runtime exception.

如果列包含空值并且在运行时它在列响应中获得空值，则会出现此问题。如果您在下面的代码中看到，我的 cust_id 可以为空，但它没有空值。因为，有一项工作可以确保永远不会为空。因此，下面的映射有效。但是，最佳做法是查看您的表结构并相应地创建类。这避免了讨厌的运行时异常。

If the table definition on database is like:

如果数据库上的表定义是这样的：

CREATE TABLE public.perf_test (
    dwh_id serial NOT NULL,
    cust_id int4 NULL,
    cust_address varchar(30) NULL,
    partner_id int4 NULL,
    CONSTRAINT perf_test_new_dwh_id_key UNIQUE (dwh_id)
);

The corresponding class definition can be as below. But, it will be advised to have the cust_id also as Option[Int]. However, as long as it has values and no nulls, you will not encounter error.

对应的类定义如下。但是，建议将 cust_id 也设为Option[Int]. 但是，只要它有值且没有空值，您就不会遇到错误。

import slick.jdbc.PostgresProfile.api._

导入 slick.jdbc.PostgresProfile.api._

class PerfTest(tag: Tag) extends Table[(Int, Int, Option[String], Option[Int])](tag, "perf_test") {
  def dwhId = column[Int]("dwh_id")
  def custId = column[Int]("cust_id")
  def custAddress = column[Option[String]]("cust_address")
  def partnerId = column[Option[Int]]("partner_id")
  def * = (dwhId, custId, custAddress,partnerId)
}

回答by Bertie92

What happened to me was that I had anomalies in the DB and some values were accidentally nulls - those that I didn't expect to be. So do not forget to check your data too :)

发生在我身上的是我在数据库中有异常并且一些值意外为空 - 那些我没想到的。所以也不要忘记检查您的数据:)