hibernate - com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException : Unknown column 'USER_NAME' in 'where clause' - container managed authentication

Question

在我提出问题后 - Authorization and authentication on the web application with JSF, Hibernate and Tomcat ，我开发了一些代码，我相信我已经非常接近解决方案了。

首先，我正在使用 Eclipse 和 Apache Tomcat 使用 Java EE、JSF、Hibernate、MySQL 编写 Web 应用程序。我不使用 Spring、EJB 等。我实现了“容器管理的身份验证”。我分享了项目中的部分代码。 他们有标准的简单代码来开发 Servlet 登录。当您查看它们时，您将很容易理解所有内容。

POJO 类；

public class User {

    // Primary Key
    private int USER_ID;
    
    private String USER_NAME;
    private String PASSWORD;
    private String FIRST_NAME;
    private String LAST_NAME;
    ...

    public User(String uSER_NAME, String pASSWORD, String fIRST_NAME,
            String lAST_NAME, ...) {
        super();
        USER_NAME = uSER_NAME;
        PASSWORD = pASSWORD;
        FIRST_NAME = fIRST_NAME;
        ...
    }

    /**
     * Default empty constructor needed for hibernate entity
     */
    public User() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * Getters and setters
     */
        ...
        ...
}

public class UserGroup {

// Primary Key
private int USER_GROUP_ID;

// Reference for User table - foreign key
private Set<User> USERS;

private String USER_GROUP_NAME;

private boolean ADMINISTRATOR_SCR;
private boolean USER_SCR;
private boolean PLANNING_SCR;
...

public UserGroup(Set<User> uSERS, String uSER_GROUP_NAME,
        boolean aDMINISTRATOR_SCR, boolean uSER_SCR, boolean pLANNING_SCR,
        ...) {
    super();
    USERS = uSERS;
    USER_GROUP_NAME = uSER_GROUP_NAME;
    ADMINISTRATOR_SCR = aDMINISTRATOR_SCR;
    ...
}

/**
 * Default empty constructor needed for hibernate entity
 */
public UserGroup() {
    // TODO Auto-generated constructor stub
}
/**
 * Getters and setters
 */
    ...
    ...
}

hibernate 映射文件；

<hibernate-mapping>
    <class name="folder.User" table="USER">
    <id name="USER_ID" type="int">
        <column name="USER_ID" />
        <generator class="native" />
    </id>
    <property name="USER_NAME" type="java.lang.String">
        <column name="USER_NAME" />
    </property>
        <property name="PASSWORD" type="java.lang.String">
            <column name="PASSWORD" />
        </property>
        <property name="FIRST_NAME" type="java.lang.String">
            <column name="FIRST_NAME" />
        </property>
        <property name="LAST_NAME" type="java.lang.String">
            <column name="LAST_NAME" />
       ...
    </class>
</hibernate-mapping>

<hibernate-mapping>
    <class name="folder.UserGroup" table="USERGROUP">
        <id name="USER_GROUP_ID" type="int">
            <column name="USER_GROUP_ID" />
            <generator class="native" />
        </id>
        <set name="USERS" table="USER" inverse="false" lazy="true" cascade="all">
            <key>
                <column name="USER_GROUP_ID" />
            </key>
            <one-to-many class="folder.User" />
        </set>
        <property name="USER_GROUP_NAME" type="java.lang.String">
            <column name="USER_GROUP_NAME" />
        </property>
        <property name="ADMINISTRATOR_SCR" type="boolean">
            <column name="ADMINISTRATOR_SCR" />
        </property>
        <property name="USER_SCR" type="boolean">
            <column name="USER_SCR" />
        </property>
        ...
    </class>
</hibernate-mapping>

web.xml配置；

    <security-constraint>
        <display-name>Restricted</display-name>
        <web-resource-collection>
            <web-resource-name>Restricted Area</web-resource-name>
            <url-pattern>/authorized/*</url-pattern>
            <http-method>GET</http-method>
            <http-method>POST</http-method>
        </web-resource-collection>
        <auth-constraint>
            <role-name>user</role-name>
        </auth-constraint>
    </security-constraint>
    <login-config>
        <auth-method>FORM</auth-method>
        <form-login-config>
            <form-login-page>/login.xhtml</form-login-page>
            <form-error-page>/login.xhtml</form-error-page>
        </form-login-config>
    </login-config>
    <security-role>
        <role-name>user</role-name>
    </security-role>

server.xml tomcat配置；

 <Realm className="org.apache.catalina.realm.JDBCRealm"
       driverName="com.mysql.jdbc.Driver"
       connectionURL="jdbc:mysql://localhost:3306/authentication_db"
       connectionName="..." connectionPassword="..."
       userTable="user" userNameCol="USER_NAME" userCredCol="PASSWORD" 
       userRoleTable="usergroup" roleNameCol="USER_GROUP_NAME" />

最后是登录功能；

public String login(){
        
        HttpServletRequest request = (HttpServletRequest) FacesContext.getCurrentInstance().getExternalContext().getRequest();
        try {
            //Login via the Servlet Context
            request.login(getLoginName(), getLoginPass());
       

            return "success";
        } catch (ServletException e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Invalid Login", null));
            e.printStackTrace();
        }
        return "failure";
}

在我将记录放在用户表和用户组上并运行该应用程序后，它看起来工作正常。但是，我收到此错误；

Nis 16, 2014 4:38:48 PM org.apache.catalina.realm.JDBCRealm getRoles

SEVERE: Exception performing authentication

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknowncolumn 'USER_NAME' in 'where clause'

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(UnknownSource)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(UnknownSource)... ...

请帮帮我!

编辑:

MySQL 表定义:

Table: user

Columns:

USER_ID int(11) AI PK

USER_NAME varchar(255)

PASSWORD varchar(255)

FIRST_NAME varchar(255)

LAST_NAME varchar(255)

...

EMAIL varchar(255)

USER_GROUP_ID int(11) FK (UserGroup Table -> USER_GROUP_ID)

---

Table: usergroup

Columns:

USER_GROUP_ID int(11) AI PK

USER_GROUP_NAME varchar(255)

ADMINISTRATOR_SCR bit(1)

USER_SCR bit(1)

PLANNING_SCR bit(1) ...

Answer 1

我解决了这个问题。问题是关于 JDBCRealm 数据格式。 JDBCRealm 是 Tomcat 6 Realm 接口(interface)的实现，它在通过 JDBC 驱动程序访问的关系数据库中查找用户。我必须以特殊格式创建用户和用户组表才能使用 JDBCRealm。要查看此配置，请访问 Apache Tomcat 网站的页面 - https://tomcat.apache.org/tomcat-6.0-doc/realm-howto.html .