postgresql - Pass Database values to a view in Spring MVC -
i working on spring tool suite mvc project. dealing postgresql db i've created using pg admin 4 v1. have view called veditor.jsp toolbox , canvas/container. when user drags , drops first tool/element onto container properties pane display form set element related properties in form. have created form in separate view called tableselectform.jsp , calling via ajax call , displaying within veditor.jsp's properties div.
in tableselectform, there combobox needs list tables postgres database. tables stored in table called tables , hence field values need retrieved table , populated in combo-box user can select table he/she wishes represent using dropped element toolbox.
but issue here is, have created model , dao class represent tables , retrieve tables postgresql db. when try pass values tableselectform view, view not rendered.
view - veditor.jsp
when table element dragged , dropped, properties pane needs open form shown below tables db.
homecontroller.java
package com.postgres.controllers; import java.io.ioexception; import java.text.dateformat; import java.util.date; import java.util.list; import java.util.locale; import org.slf4j.logger; import org.slf4j.loggerfactory; import org.springframework.beans.factory.annotation.autowired; import org.springframework.context.annotation.componentscan; import org.springframework.context.annotation.configuration; import org.springframework.context.annotation.import; import org.springframework.stereotype.controller; import org.springframework.ui.model; import org.springframework.web.bind.annotation.requestmapping; import org.springframework.web.bind.annotation.requestmethod; import org.springframework.web.servlet.modelandview; import com.postgres.dao.tabledao; import com.postgres.models.table; /** * handles requests application home page. */ @controller @configuration @import(com.postgres.config.mvcconfiguration.class) @componentscan public class homecontroller { @autowired private tabledao tabledao; private static final logger logger = loggerfactory.getlogger(homecontroller.class); /** * selects home view render returning name. */ @requestmapping(value = "/", method = requestmethod.get) public string home(locale locale, model model) { logger.info("welcome home! client locale {}.", locale); date date = new date(); dateformat dateformat = dateformat.getdatetimeinstance(dateformat.long, dateformat.long, locale); string formatteddate = dateformat.format(date); model.addattribute("servertime", formatteddate ); return "veditor"; } /** * @description displaytableselectform called @ moment * @param model * @return * @throws ioexception */ @requestmapping(value="displaytableselectform") public modelandview displaytableselectform(modelandview model) throws ioexception{ list<table> listtable = tabledao.list(); model.addobject("listtable", listtable); model.setviewname("tableselectform"); return model; } @requestmapping(value="displaytableclauseform") public string displaytableclauseform(model model) { return "tableselectform"; } @requestmapping(value="/displayselectclauseform") public string displayselectclauseform(model model) { return "selectclauseform"; } @requestmapping(value="/displaywhereclauseform") public string displaywhereclauseform(model model) { return "whereclauseform"; } } tableselectform.jsp
<%@ page language="java" contenttype="text/html; charset=iso-8859-1" pageencoding="iso-8859-1"%> <!doctype html public "-//w3c//dtd html 4.01 transitional//en" "http://www.w3.org/tr/html4/loose.dtd"> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <html> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1"> <title>select</title> <!-- latest compiled , minified css --> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> <!-- optional theme --> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css"> <!-- latest compiled , minified javascript --> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> <script src="https://npmcdn.com/tether@1.2.4/dist/js/tether.min.js"></script> <script src="resources/js/taro/helperscripts/jquery.min.js"></script> <script src="//code.jquery.com/jquery-1.9.1.min.js"></script> <script src="https://code.jquery.com/jquery-1.10.2.js"></script> <script src="resources/js/taro/helperscripts/jquery-ui.min.js"></script> <script src="resources/js/taro/helperscripts/jquery.jsplumb-1.4.1-all-min.js"></script> <script src="http://cdnjs.cloudflare.com/ajax/libs/jsplumb/1.4.1/jquery.jsplumb-1.4.1-all-min.js"></script> <script src="resources/js/taro/helperscripts/jquery.jsplumb-1.6.4-min.js"></script> <script src="resources/js/taro/customscripts/veditor.js" type="text/javascript"></script> <script src="resources/js/taro/helperscripts/bootstrap.min.js"></script> <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.10.3/jquery-ui.min.js"></script> <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.10.3/themes/smoothness/jquery-ui.css" rel="stylesheet" type="text/css"/> <link rel="stylesheet" type="text/css" href="resources/css/veditor.css"> <link rel="stylesheet" type="text/css" href="resources/css/style.css"> <link href="http://maxcdn.bootstrapcdn.com/font-awesome/4.2.0/css/font-awesome.min.css" rel="stylesheet"> <link rel="stylesheet" type="text/css" href="resources/css/bootstrap.min.css"> <link href='http://fonts.googleapis.com/css?family=roboto:400,700,300' rel='stylesheet' type='text/css'> <link href="resources/css/pe-icon-7-stroke.css" rel="stylesheet" /> </head> <body> <form class="form-horizontal"> <fieldset> <!-- form name --> <legend>select</legend> <!-- select basic --> <div class="form-group"> <label class="col-md-4 control-label" for="attr-combobox-style">select table</label> <div class="col-md-4"> <select id="attr-combobox-style" name="attr-combobox-style" class="form-control"> <option value="select option">select option</option> <c:foreach var="tables" items="${listtable}" varstatus="status"> <option value="${tables.table_name}">${tables.table_name}</option> </c:foreach> </select> </div> </div> <!-- button (double) --> <div class="form-group"> <label class="col-md-4 control-label" for="tableformconfirmbutton"></label> <div class="col-md-8"> <button id="tableformconfirmbutton" name="tableformconfirmbutton" class="btn btn-primary">confirm</button> <button id="tableformcancelbutton" name="tableformcancelbutton" class="btn btn-danger">cancel</button> </div> </div> </fieldset> </form> </body> </html> the following js file calls homecontroller's method when table element dropped
veditor.js
... //if dropped element table then-> if (dropelem == "stream ui-draggable") { var newagent = $('<div>').attr('id', i).addclass('streamdrop'); var elemtype = "table"; //the container , toolbox disabled prevent user dropping elements before initializing stream element $("#container").addclass("disabledbutton"); $("#toolbox").addclass("disabledbutton"); $('#container').append(newagent); $.ajax({ type: "get", cache: false, url: "displaytableselectform", data: "", success: function(response){ $('#lot').html(response); } }); ... tabledaoimpl.java
package com.postgres.dao; import java.sql.resultset; import java.sql.sqlexception; import java.util.list; import javax.sql.datasource; import com.postgres.models.table; import org.springframework.dao.dataaccessexception; import org.springframework.jdbc.core.jdbctemplate; import org.springframework.jdbc.core.resultsetextractor; import org.springframework.jdbc.core.rowmapper; /** * implementation of tabledao interface. * @author nayantara jeyaraj * */ public class tabledaoimpl implements tabledao { private jdbctemplate jdbctemplate; public tabledaoimpl(datasource datasource) { jdbctemplate = new jdbctemplate(datasource); } @override public list<table> list() { string sql = "select * tables"; list<table> listtable = jdbctemplate.query(sql, new rowmapper<table>() { @override public table maprow(resultset rs, int rownum) throws sqlexception { table atable = new table(); atable.settid(rs.getint("tid")); atable.setdid(rs.getint("did")); atable.settable_name(rs.getstring("table_name")); atable.setcreated_on(rs.getdate("created_on")); return atable; } }); return listtable; } } tabledao.java
package com.postgres.dao; import java.util.list; import com.postgres.models.table; public interface tabledao { public list<table> list(); } mvcconfiguration.java
package com.postgres.config; import javax.sql.datasource; import com.postgres.dao.tabledao; import com.postgres.dao.tabledaoimpl; import org.springframework.context.annotation.bean; import org.springframework.context.annotation.componentscan; import org.springframework.context.annotation.configuration; import org.springframework.jdbc.datasource.drivermanagerdatasource; import org.springframework.web.servlet.viewresolver; import org.springframework.web.servlet.config.annotation.enablewebmvc; import org.springframework.web.servlet.config.annotation.resourcehandlerregistry; import org.springframework.web.servlet.config.annotation.webmvcconfigureradapter; import org.springframework.web.servlet.view.internalresourceviewresolver; @configuration @componentscan(basepackages="com.postgres") @enablewebmvc public class mvcconfiguration extends webmvcconfigureradapter{ @bean public viewresolver getviewresolver(){ internalresourceviewresolver resolver = new internalresourceviewresolver(); resolver.setprefix("/web-inf/views/"); resolver.setsuffix(".jsp"); return resolver; } @override public void addresourcehandlers(resourcehandlerregistry registry) { registry.addresourcehandler("/resources/**").addresourcelocations("/resources/"); } @bean public datasource getdatasource() { drivermanagerdatasource datasource = new drivermanagerdatasource(); datasource.setdriverclassname("com.mysql.jdbc.driver"); datasource.seturl("jdbc:mysql://localhost:3306/postgreside"); datasource.setusername("root"); datasource.setpassword("123"); return datasource; } @bean public tabledao gettabledao() { return new tabledaoimpl(getdatasource()); } } postgresql db - tables table structure in pg admin
so need display properties pane combo - box following items.
- select option
- employee
- department
- projects
- acquisition
- opac
the error getting @ moment gives jdbcconnectionexception
stacktrace
http status 500 - request processing failed; nested exception org.springframework.jdbc.cannotgetjdbcconnectionexception: not jdbc connection; nested exception com.mysql.jdbc.communicationsexception: communications link failure due underlying exception: type exception report message request processing failed; nested exception org.springframework.jdbc.cannotgetjdbcconnectionexception: not jdbc connection; nested exception com.mysql.jdbc.communicationsexception: communications link failure due underlying exception: description server encountered internal error prevented fulfilling request. exception org.springframework.web.util.nestedservletexception: request processing failed; nested exception org.springframework.jdbc.cannotgetjdbcconnectionexception: not jdbc connection; nested exception com.mysql.jdbc.communicationsexception: communications link failure due underlying exception: ** begin nested exception ** java.net.connectexception message: connection refused: connect stacktrace: java.net.connectexception: connection refused: connect @ java.net.dualstackplainsocketimpl.connect0(native method) @ java.net.dualstackplainsocketimpl.socketconnect(unknown source) @ java.net.abstractplainsocketimpl.doconnect(unknown source) @ java.net.abstractplainsocketimpl.connecttoaddress(unknown source) @ java.net.abstractplainsocketimpl.connect(unknown source) @ java.net.plainsocketimpl.connect(unknown source) @ java.net.sockssocketimpl.connect(unknown source) @ java.net.socket.connect(unknown source) @ java.net.socket.connect(unknown source) @ java.net.socket.<init>(unknown source) @ java.net.socket.<init>(unknown source) @ com.mysql.jdbc.standardsocketfactory.connect(standardsocketfactory.java:256) @ com.mysql.jdbc.mysqlio.<init>(mysqlio.java:271) @ com.mysql.jdbc.connection.createnewio(connection.java:2771) @ com.mysql.jdbc.connection.<init>(connection.java:1555) @ com.mysql.jdbc.nonregisteringdriver.connect(nonregisteringdriver.java:285) @ java.sql.drivermanager.getconnection(unknown source) @ java.sql.drivermanager.getconnection(unknown source) @ org.springframework.jdbc.datasource.drivermanagerdatasource.getconnectionfromdrivermanager(drivermanagerdatasource.java:153) @ org.springframework.jdbc.datasource.drivermanagerdatasource.getconnectionfromdriver(drivermanagerdatasource.java:144) @ org.springframework.jdbc.datasource.abstractdriverbaseddatasource.getconnectionfromdriver(abstractdriverbaseddatasource.java:155) @ org.springframework.jdbc.datasource.abstractdriverbaseddatasource.getconnection(abstractdriverbaseddatasource.java:120) @ org.springframework.jdbc.datasource.datasourceutils.dogetconnection(datasourceutils.java:111) @ org.springframework.jdbc.datasource.datasourceutils.getconnection(datasourceutils.java:77) @ org.springframework.jdbc.core.jdbctemplate.execute(jdbctemplate.java:386) @ org.springframework.jdbc.core.jdbctemplate.query(jdbctemplate.java:466) @ org.springframework.jdbc.core.jdbctemplate.query(jdbctemplate.java:476) @ com.postgres.dao.tabledaoimpl.list(tabledaoimpl.java:32) @ com.postgres.controllers.homecontroller.displaytableselectform(homecontroller.java:60) @ sun.reflect.nativemethodaccessorimpl.invoke0(native method) @ sun.reflect.nativemethodaccessorimpl.invoke(unknown source) @ sun.reflect.delegatingmethodaccessorimpl.invoke(unknown source) @ java.lang.reflect.method.invoke(unknown source) @ org.springframework.web.method.support.invocablehandlermethod.doinvoke(invocablehandlermethod.java:205) @ org.springframework.web.method.support.invocablehandlermethod.invokeforrequest(invocablehandlermethod.java:133) @ org.springframework.web.servlet.mvc.method.annotation.servletinvocablehandlermethod.invokeandhandle(servletinvocablehandlermethod.java:97) @ org.springframework.web.servlet.mvc.method.annotation.requestmappinghandleradapter.invokehandlermethod(requestmappinghandleradapter.java:827) @ org.springframework.web.servlet.mvc.method.annotation.requestmappinghandleradapter.handleinternal(requestmappinghandleradapter.java:738) @ org.springframework.web.servlet.mvc.method.abstracthandlermethodadapter.handle(abstracthandlermethodadapter.java:85) @ org.springframework.web.servlet.dispatcherservlet.dodispatch(dispatcherservlet.java:967) @ org.springframework.web.servlet.dispatcherservlet.doservice(dispatcherservlet.java:901) @ org.springframework.web.servlet.frameworkservlet.processrequest(frameworkservlet.java:970) @ org.springframework.web.servlet.frameworkservlet.doget(frameworkservlet.java:861) @ javax.servlet.http.httpservlet.service(httpservlet.java:622) @ org.springframework.web.servlet.frameworkservlet.service(frameworkservlet.java:846) @ javax.servlet.http.httpservlet.service(httpservlet.java:729) @ org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:230) @ org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:165) @ org.apache.tomcat.websocket.server.wsfilter.dofilter(wsfilter.java:52) @ org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:192) @ org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:165) @ org.apache.catalina.core.standardwrappervalve.invoke(standardwrappervalve.java:198) @ org.apache.catalina.core.standardcontextvalve.invoke(standardcontextvalve.java:96) @ org.apache.catalina.authenticator.authenticatorbase.invoke(authenticatorbase.java:474) @ org.apache.catalina.core.standardhostvalve.invoke(standardhostvalve.java:140) @ org.apache.catalina.valves.errorreportvalve.invoke(errorreportvalve.java:79) @ org.apache.catalina.valves.abstractaccesslogvalve.invoke(abstractaccesslogvalve.java:624) @ org.apache.catalina.core.standardenginevalve.invoke(standardenginevalve.java:87) @ org.apache.catalina.connector.coyoteadapter.service(coyoteadapter.java:349) @ org.apache.coyote.http11.http11processor.service(http11processor.java:783) @ org.apache.coyote.abstractprocessorlight.process(abstractprocessorlight.java:66) @ org.apache.coyote.abstractprotocol$connectionhandler.process(abstractprotocol.java:789) @ org.apache.tomcat.util.net.nioendpoint$socketprocessor.dorun(nioendpoint.java:1437) @ org.apache.tomcat.util.net.socketprocessorbase.run(socketprocessorbase.java:49) @ java.util.concurrent.threadpoolexecutor.runworker(unknown source) @ java.util.concurrent.threadpoolexecutor$worker.run(unknown source) @ org.apache.tomcat.util.threads.taskthread$wrappingrunnable.run(taskthread.java:61) @ java.lang.thread.run(unknown source) ** end nested exception ** last packet sent server 1 ms ago. org.springframework.web.servlet.frameworkservlet.processrequest(frameworkservlet.java:982) org.springframework.web.servlet.frameworkservlet.doget(frameworkservlet.java:861) javax.servlet.http.httpservlet.service(httpservlet.java:622) org.springframework.web.servlet.frameworkservlet.service(frameworkservlet.java:846) javax.servlet.http.httpservlet.service(httpservlet.java:729) org.apache.tomcat.websocket.server.wsfilter.dofilter(wsfilter.java:52) root cause org.springframework.jdbc.cannotgetjdbcconnectionexception: not jdbc connection; nested exception com.mysql.jdbc.communicationsexception: communications link failure due underlying exception: ** begin nested exception ** java.net.connectexception message: connection refused: connect stacktrace: java.net.connectexception: connection refused: connect @ java.net.dualstackplainsocketimpl.connect0(native method) @ java.net.dualstackplainsocketimpl.socketconnect(unknown source) @ java.net.abstractplainsocketimpl.doconnect(unknown source) @ java.net.abstractplainsocketimpl.connecttoaddress(unknown source) @ java.net.abstractplainsocketimpl.connect(unknown source) @ java.net.plainsocketimpl.connect(unknown source) @ java.net.sockssocketimpl.connect(unknown source) @ java.net.socket.connect(unknown source) @ java.net.socket.connect(unknown source) @ java.net.socket.<init>(unknown source) @ java.net.socket.<init>(unknown source) @ com.mysql.jdbc.standardsocketfactory.connect(standardsocketfactory.java:256) @ com.mysql.jdbc.mysqlio.<init>(mysqlio.java:271) @ com.mysql.jdbc.connection.createnewio(connection.java:2771) @ com.mysql.jdbc.connection.<init>(connection.java:1555) @ com.mysql.jdbc.nonregisteringdriver.connect(nonregisteringdriver.java:285) @ java.sql.drivermanager.getconnection(unknown source) @ java.sql.drivermanager.getconnection(unknown source) @ org.springframework.jdbc.datasource.drivermanagerdatasource.getconnectionfromdrivermanager(drivermanagerdatasource.java:153) @ ... pivotal tc runtime 3.2.4.sr1/8.5.9.b.release project structure
i highly appreciate suggestions on how can past error , populate combo-box data postgresql db
Comments
Post a Comment