java.sql.SQLException : Column 'countryid' not found

Question

这是我的java方法，我用它来更新用户
在后端我使用 jdbc 而不是 jdbc 模板来更新用户我无法插入国家/地区 ID，因为错误显示未找到国家/地区 ID 列名称。错误显示 countyid 丢失，但在我的 SQL 编辑器中已看到查询的级别正确

 public User updateUsers(int id) throws SQLException {
    User user = null;
    Connection con = null;
    PreparedStatement ps = null;
    int active = 0;
    try {
        con = createConnection();
        ps = con.prepareStatement(
                " select UserMaster1.usermasterid,UserMaster1.FName,UserMaster1.LName,UserMaster1.Gender,UserMaster1.address,UserLogin.Email,UserLogin.Password,UserMaster1.MobileNo,country.countryname,s.state,UserMaster1.hobby "
                        + " from UserMaster1 " 
                        + " INNER JOIN country  ON UserMaster1.country=country.countryid "
                        + " INNER JOIN state as s ON UserMaster1.state=s.stateid "
                        + " INNER join hobbyMaster ON UserMaster1.hobby=hobbyMaster.HobbyId "
                        + " INNER join UserLogin ON UserMaster1.usermasterid=UserLogin.usermasterid "
                        + " where UserMaster1.usermasterid=? ");
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            user = new User();
            user.setId(rs.getInt("usermasterid"));
            user.setFname(rs.getString("FName"));
            user.setLname(rs.getString("LName"));
            user.setGender(rs.getString("Gender"));
            user.setEmail(rs.getString("Email"));
            user.setPassword(rs.getString("Password"));
            user.setAddress(rs.getString("address"));
            user.setMobile_no(rs.getInt("MobileNo"));
            user.setCountry(rs.getString("countryid"));
            user.setState(rs.getString("stateid"));
            user.setHobby(rs.getInt("hobby"));
        }
    } finally {
        if (ps != null) {
            ps.close();
        }
        if (con != null) {
            con.close();
        }
    }
    return user;
}

它从技术方面的方法返回用户，我正在使用 spring-mvc 我想这不是问题

这是代码在执行时向我显示的错误。

java.sql.SQLException: Column 'countryid' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1073)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5212)
at in.co.xomic.dao.HomeDataAcessOperation.updateUsers(HomeDataAcessOperation.java:237)
at in.co.xomic.service.HomeService.updateUserById(HomeService.java:60)
at in.co.xomic.controller.HomeController.updateUser(HomeController.java:102)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:218)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:506)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:962)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1087)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)

Answer 1

原因是您通过 rs.getString("countryid") 获得值(value)但你没有在你的sql中选择它

要解决这个问题，您需要添加 country.country.id在您的选择中:

select UserMaster1.usermasterid,UserMaster1.FName,UserMaster1.LName,
 UserMaster1.Gender,UserMaster1.address,UserLogin.Email,
UserLogin.Password,UserMaster1.MobileNo,country.countryname,
s.state,UserMaster1.hobby,country.countryid,s.stateid