com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:“字段列表"中的未知列"RECEPTORS.r_name" [英] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'RECEPTORS.r_name' in 'field list'

问题描述

我正在使用play 2.3.8框架来创建API并访问mariaDB.当我在mariaDB控制台上运行查询时，它运行正常，但是当我从游戏中运行该查询时，出现错误，表明RECEPTORS.r_name字段不可用，这是不正确的.

I am using play 2.3.8 framework to create an API and access mariaDB. When I ran the query on mariaDB console, it works OK but when I run it from play I get error that the field RECEPTORS.r_name is not available which is not true.

我的代码是

package models.dao

import anorm._
import models.Profile
import play.api.db.DB
import play.api.Play.current

object ProfileDAO {

def index(r_name: String): List[Profile] = {
  DB.withConnection { implicit c =>
    val results = SQL(
      """
        | SELECT `RECEPTORS.r_name`,`RECEPTORS.pdbCode`, `LIGANDS.l_id`, `LIGANDS.l_score` 
        | FROM `RECEPTORS`
        | INNER JOIN `LIGANDS`
        | WHERE `RECEPTORS.r_name`={r_name};
      """.stripMargin).on(
        "r_name" -> r_name
      ).apply()

    results.map { row =>
      Profile(row[String]("r_name"), row[String]("pdbCode"),row[String]("l_id"),row[Double]("l_score"))
    }.force.toList
  }
 }

}

我在mariaDB控制台上运行的查询是

SELECT RECEPTORS.r_name, pdbCode, l_id, l_score FROM RECEPTORS INNER JOIN LIGANDS WHERE RECEPTORS.r_name="receptor";

与Play 2.3.8一起运行的错误如下

laeeq @ optiplex:〜/Desktop/Backup/Project5/cpvsAPI $ sbt -jvm-debug 9999 在地址9999上侦听传输dt_socket [信息]正在加载 来自的项目定义 /home/laeeq/Desktop/Backup/Project5/cpvsAPI/project [info]设置当前 项目到cpvsAPI(在构建中 文件:/home/laeeq/Desktop/Backup/Project5/cpvsAPI/)[信息]正在更新 {file:/home/laeeq/Desktop/Backup/Project5/cpvsAPI/} root ... [info] 解决jline#jline; 2.11 ... [info]更新完成.

laeeq@optiplex:~/Desktop/Backup/Project5/cpvsAPI$ sbt -jvm-debug 9999 run Listening for transport dt_socket at address: 9999 [info] Loading project definition from /home/laeeq/Desktop/Backup/Project5/cpvsAPI/project [info] Set current project to cpvsAPI (in build file:/home/laeeq/Desktop/Backup/Project5/cpvsAPI/) [info] Updating {file:/home/laeeq/Desktop/Backup/Project5/cpvsAPI/}root... [info] Resolving jline#jline;2.11 ... [info] Done updating.

---(正在运行该应用程序，启用了自动重新加载)---

--- (Running the application, auto-reloading is enabled) ---

[info]播放-监听/0:0:0:0:0:0:0:0:0:9000上的HTTP

[info] play - Listening for HTTP on /0:0:0:0:0:0:0:0:9000

(服务器已启动，请使用Ctrl + D停止并返回控制台...)

(Server started, use Ctrl+D to stop and go back to the console...)

SLF4J:可能已经访问了以下替代记录器集 SLF4J:在初始化阶段.在此期间记录通话 SLF4J:阶段不兑现.但是，随后的日志记录调用 这些SLF4J:记录器将正常运行. SLF4J:另请参见 http://www.slf4j.org/codes.html#substituteLogger SLF4J: org.webjars.WebJarExtractor [info]将1个Scala源代码编译为 /home/laeeq/Desktop/Backup/Project5/cpvsAPI/target/scala-2.11/classes ... [信息]播放-数据库[默认]连接在 jdbc:mysql://localhost:3306/db_profile [信息]播放-应用 已启动(Dev)[错误]应用程序-

SLF4J: The following set of substitute loggers may have been accessed SLF4J: during the initialization phase. Logging calls during this SLF4J: phase were not honored. However, subsequent logging calls to these SLF4J: loggers will work as normally expected. SLF4J: See also http://www.slf4j.org/codes.html#substituteLogger SLF4J: org.webjars.WebJarExtractor [info] Compiling 1 Scala source to /home/laeeq/Desktop/Backup/Project5/cpvsAPI/target/scala-2.11/classes... [info] play - database [default] connected at jdbc:mysql://localhost:3306/db_profile [info] play - Application started (Dev) [error] application -

！ @ 766oc7b8l-内部服务器错误，用于(GET)[/profiles/receptor] ->

! @766oc7b8l - Internal server error, for (GET) [/profiles/receptor] ->

play.api.Application $$ anon $ 1:执行 例外[[MySQLSyntaxErrorException:未知列 [字段列表中的'RECEPTORS.r_name']]在 play.api.Application $ class.handleError(Application.scala:296) 〜[play_2.11-2.3.8.jar:2.3.8]在 play.api.DefaultApplication.handleError(Application.scala:402) 在[play_2.11-2.3.8.jar:2.3.8] play.core.server.netty.PlayDefaultUpstreamHandler $$ anonfun $ 14 $$ anonfun $ apply $ 1.applyOrElse(PlayDefaultUpstreamHandler.scala:205) 在[play_2.11-2.3.8.jar:2.3.8] play.core.server.netty.PlayDefaultUpstreamHandler $$ anonfun $ 14 $$ anonfun $ apply $ 1.applyOrElse(PlayDefaultUpstreamHandler.scala:202) 在[play_2.11-2.3.8.jar:2.3.8] scala.runtime.AbstractPartialFunction.apply(AbstractPartialFunction.scala:36) [scala-library-2.11.1.jar:na]由以下原因引起: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:未知 位于字段列表"中的"RECEPTORS.r_name"列 sun.reflect.NativeConstructorAccessorImpl.newInstance0(本机方法) 〜[na:1.8.0_151]在 sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) 〜[na:1.8.0_151]在 sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) 〜[na:1.8.0_151]在 java.lang.reflect.Constructor.newInstance(Constructor.java:423) 〜[na:1.8.0_151]在 com.mysql.jdbc.Util.handleNewInstance(Util.java:411) 〜[mysql-connector-java-5.1.18.jar:na]

play.api.Application$$anon$1: Execution exception[[MySQLSyntaxErrorException: Unknown column 'RECEPTORS.r_name' in 'field list']] at play.api.Application$class.handleError(Application.scala:296) ~[play_2.11-2.3.8.jar:2.3.8] at play.api.DefaultApplication.handleError(Application.scala:402) [play_2.11-2.3.8.jar:2.3.8] at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$14$$anonfun$apply$1.applyOrElse(PlayDefaultUpstreamHandler.scala:205) [play_2.11-2.3.8.jar:2.3.8] at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$14$$anonfun$apply$1.applyOrElse(PlayDefaultUpstreamHandler.scala:202) [play_2.11-2.3.8.jar:2.3.8] at scala.runtime.AbstractPartialFunction.apply(AbstractPartialFunction.scala:36) [scala-library-2.11.1.jar:na] Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'RECEPTORS.r_name' in 'field list' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_151] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_151] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_151] at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_151] at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.18.jar:na]

推荐答案

您需要单独引用表和列，因此请使用:

You need to quote the table and column individually, so use:

`RECEPTORS`.`r_name`

否则，MySQL认为您正在尝试在某些隐式表中引用名称为RECEPTORS.r_name的列.

otherwise MySQL thinks you are trying to reference a column with the name RECEPTORS.r_name in some implicit table.

您需要对所有(引用的)列引用进行此操作.特别是在这种情况下，似乎不需要引号，因此您也可以只使用RECEPTORS.r_name而不加反引号.

You need to do this for all your (quoted) column references. Specifically in this case, quoting seems to be unnecessary, so you could also just use RECEPTORS.r_name without backticks.

这篇关于com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:“字段列表"中的未知列"RECEPTORS.r_name"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！