com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:未知列'USER_NAME'在'where子句' - 容器管理认证 [英] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'USER_NAME' in 'where clause' - container managed authentication
问题描述
我问的问题后 - <一个href=\"http://stackoverflow.com/questions/23087056/authorization-and-authentication-on-the-web-application-with-jsf-hibernate-and\">Authorization并与JSF,Hibernate和Tomcat的 Web应用程序的认证,我开发了一些code,我相信我是非常接近的解决方案。
首先,我通过使用Eclipse和Apache Tomcat编程使用Java EE,JSF,休眠,MySQL中的Web应用程序。我不使用Spring,EJB或者等我实施容器管理认证。我从项目共享code的某些部分。 的他们有STANDART SIMPLE codeS开发SERVLET LOGIN。当你看着他们,你就会明白都很容易。的
POJO类;
公共类用户{ // 首要的关键
私人诠释USER_ID; 私人字符串USER_NAME;
私人字符串密码;
私人字符串FIRST_NAME;
私人字符串LAST_NAME;
... 公共用户(USER_NAME字符串,字符串密码字符串FIRST_NAME,
串姓氏,...){
超();
USER_NAME = USER_NAME;
PASSWORD =密码;
FIRST_NAME = FIRST_NAME;
...
} / **
需要休眠实体*默认空的构造
* /
公共用户(){
超();
// TODO自动生成构造函数存根
} / **
* getter和setter方法
* /
...
...
}公共类用户组{// 首要的关键
私人诠释USER_GROUP_ID;//参考用户表 - 外键
私人设置&lt;使用者&GT;用户;私人字符串USER_GROUP_NAME;私人布尔ADMINISTRATOR_SCR;
私人布尔USER_SCR;
私人布尔PLANNING_SCR;
...公众用户组(SET&lt;使用者&GT;的用户,串uSER_GROUP_NAME,
布尔aDMINISTRATOR_SCR,布尔uSER_SCR,布尔pLANNING_SCR,
...){
超();
USERS =用户;
USER_GROUP_NAME = uSER_GROUP_NAME;
ADMINISTRATOR_SCR = aDMINISTRATOR_SCR;
...
}/ **
需要休眠实体*默认空的构造
* /
公众用户组(){
// TODO自动生成构造函数存根
}
/ **
* getter和setter方法
* /
...
...
}
Hibernate映射文件;
&LT;休眠映射&GT;
&LT;类名=folder.User表=USER&GT;
&LT; ID名称=USER_ID类型=整数&GT;
&LT;列名=USER_ID/&GT;
&LT;生成器类=本地/&GT;
&LT; / ID&GT;
&LT;属性名=USER_NAMETYPE =java.lang.String中&GT;
&LT;列名=USER_NAME/&GT;
&LT; /性&gt;
&LT;属性名=密码TYPE =java.lang.String中&GT;
&LT;列名=密码/&GT;
&LT; /性&gt;
&LT;属性名=FIRST_NAMETYPE =java.lang.String中&GT;
&LT;列名=FIRST_NAME/&GT;
&LT; /性&gt;
&LT;属性名=LAST_NAMETYPE =java.lang.String中&GT;
&LT;列名=LAST_NAME/&GT;
...
&LT; /班&GT;
&LT; /休眠映射&GT;&LT;休眠映射&GT;
&LT;类名=folder.UserGroup表=用户组&GT;
&LT; ID名称=USER_GROUP_ID类型=整数&GT;
&LT;列名=USER_GROUP_ID/&GT;
&LT;生成器类=本地/&GT;
&LT; / ID&GT;
&LT;集名称=用户表=USER逆=假为lazy =真正的级联=所有&GT;
&LT;密钥GT;
&LT;列名=USER_GROUP_ID/&GT;
&LT; /键&GT;
&下;一对许多类=folder.User/&GT;
&LT; /集&gt;
&LT;属性名=USER_GROUP_NAMETYPE =java.lang.String中&GT;
&LT;列名=USER_GROUP_NAME/&GT;
&LT; /性&gt;
&LT;属性名=ADMINISTRATOR_SCR类型=布尔&GT;
&LT;列名=ADMINISTRATOR_SCR/&GT;
&LT; /性&gt;
&LT;属性名=USER_SCR类型=布尔&GT;
&LT;列名=USER_SCR/&GT;
&LT; /性&gt;
...
&LT; /班&GT;
&LT; /休眠映射&GT;
web.xml配置;
&LT;安全约束&GT;
&lt;显示-名称&gt;&限制LT; /显示-名称&gt;
&LT;网上资源收集和GT;
&LT;网上资源名称&gt;限制区&LT; /网络资源名称&gt;
&LT; URL模式&GT; /授权/ *&LT; / URL模式&GT;
&LT; HTTP的方法&gt; GET&LT; / HTTP的方法&gt;
&LT; HTTP的方法&gt;中邮LT; / HTTP的方法&gt;
&LT; /网上资源收集和GT;
&LT; AUTH约束&GT;
&LT;角色名称&gt;用户LT; /角色名称&gt;
&LT; / AUTH约束&GT;
&LT; /安全约束&GT;
&LT;登录名,配置&GT;
&LT; AUTH-方法&gt;表并LT; / AUTH-方法&gt;
&LT;形式登录,配置&GT;
&LT;形式登录页&GT; /login.xhtml< /表单登录页&GT;
&LT;形式的错误页面&GT; /login.xhtml< /表单错误页&GT;
&LT; /表单登录,配置&GT;
&LT; /登录,配置&GT;
&LT;安全角色&GT;
&LT;角色名称&gt;用户LT; /角色名称&gt;
&LT; /安全角色&GT;
的server.xml Tomcat配置;
&LT;境界的className =org.apache.catalina.realm.JDBCRealm
DRIVERNAME =com.mysql.jdbc.Driver
的ConnectionURL =的jdbc:mysql的://本地主机:3306 / authentication_db
则connectionName =...connectionPassword =...
为UserTable =用户userNameCol =USER_NAMEuserCredCol =密码
userRoleTable =用户组roleNameCol =USER_GROUP_NAME/&GT;
最后,登录功能;
公共字符串登录(){ HttpServletRequest的请求=(HttpServletRequest的)FacesContext.getCurrentInstance()getExternalContext()调用getRequest()。
尝试{
//通过servlet上下文登录
request.login(getLoginName(),getLoginPass());
返回成功;
}赶上(ServletException异常五){
。FacesContext.getCurrentInstance()方法addMessage(空,新的FacesMessage(FacesMessage.SEVERITY_ERROR,无效登录,NULL));
e.printStackTrace();
}
返回失败;
}
我把用户表和用户组记录后,我运行应用程序,它看起来像正常工作。不过,我得到这个错误;
16尼什,2014年下午4时38分48秒org.apache.catalina.realm.JDBCRealm getRoles
重度:异常进行认证
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:未知
在列'USER_NAME'where子句
在sun.reflect.NativeConstructorAccessorImpl.newInstance0(本机方法)
在sun.reflect.NativeConstructorAccessorImpl.newInstance(未知
来源)
在sun.reflect.DelegatingConstructorAccessorImpl.newInstance(未知
资源)
......
块引用>请帮帮我!
编辑:
的 MySQL表定义:的
的表:的用户
的列:的
USER_ID INT(11)AI PK
USER_NAME VARCHAR(255)
密码为varchar(255)
FIRST_NAME VARCHAR(255)
LAST_NAME VARCHAR(255)
...
EMAIL VARCHAR(255)
USER_GROUP_ID INT(11)FK(用户组表 - > USER_GROUP_ID)
的表:的用户组
的列:的
USER_GROUP_ID INT(11)AI PK
USER_GROUP_NAME VARCHAR(255)
ADMINISTRATOR_SCR位(1)
USER_SCR位(1)
PLANNING_SCR位(1)...
块引用>解决方案我解决了这个问题。现在的问题是关于JDBCRealm数据格式。 JDBCRealm是Tomcat的6境界界面中查找用户通过JDBC驱动程序访问关系数据库的实现。我在使用JDBCRealm特殊格式创建用户和用户组表。要查看该配置,请访问的Apache Tomcat网站的页面 - 的https:/ /tomcat.apache.org/tomcat-6.0-doc/realm-howto.html 。
After I asked the question - Authorization and authentication on the web application with JSF, Hibernate and Tomcat, I developed some code and I believe that I am very close to the solution.
First of all, I am programming a Web-Application with Java EE, JSF, Hibernate, MySQL by using Eclipse and Apache Tomcat. I do not use Spring, EJB or etc. I implement "Container Managed Authentication". I share some parts of code from the project. THEY HAVE STANDART SIMPLE CODES TO DEVELOP SERVLET LOGIN. WHEN YOU LOOK AT THEM, YOU WILL UNDERSTAND ALL EASILY.
POJO classes;
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 mapping files;
<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 configuration;
<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 configuration;
<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" />
Lastly, login function;
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"; }
After I put records on User table and UserGroup and I run the app, it looks like working properly. However, I get this error;
Nis 16, 2014 4:38:48 PM org.apache.catalina.realm.JDBCRealm getRoles
SEVERE: Exception performing authentication
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'USER_NAME' in 'where clause'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) ... ...
Please help me!
EDIT:
MySQL Table Definition:
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) ...
解决方案I solved the problem. The problem is about JDBCRealm data format. JDBCRealm is an implementation of the Tomcat 6 Realm interface that looks up users in a relational database accessed via a JDBC driver. I have to create user and user group tables in a special format to use JDBCRealm. To view this configuration, visit the page of Apache Tomcat web site - https://tomcat.apache.org/tomcat-6.0-doc/realm-howto.html.
这篇关于com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:未知列'USER_NAME'在'where子句' - 容器管理认证的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!