postgresql - how to invoke a pl/pgsql from java -


my pl/pgsql function works fine. tested it

    create or replace function topalerte()           returns void           $$            declare              max_var_risk varchar(70);             max_mvar_risk varchar(70);             max_incvar_risk varchar(70);             max_cvar_risk varchar(70);             amount varchar(70);             delta varchar(70);             net_exposure varchar(70);            begin              truncate tops;              select names risk risk.var_mc_risk =(select                max(risk.var_mc_risk) risk) max_var_risk ;             select names risk risk.mvar_mc_risk =(select max(risk.mvar_mc_risk) risk) max_mvar_risk ;             select names risk risk.inc_var_mc_risk =(select max(risk.inc_var_mc_risk) risk) max_incvar_risk ;             select names risk risk.cvar_mc_risk =(select max(risk.cvar_mc_risk) risk) max_cvar_risk ;             select names risk risk.amount =(select max(risk.amount) risk) amount ;             select names risk risk.delta =(select max(risk.delta) risk) delta;             select names risk risk.net_exposure =(select max(risk.net_exposure) risk) net_exposure  ;              insert tops values                (max_var_risk, max_mvar_risk, max_incvar_risk,   max_cvar_risk,amount,delta,net_exposure);            end ;            $$            language 'plpgsql';

i want invoke spring boot, here dao:

public interface toprepository {     public list gettopsalert()throws throwable; }

here service(interface implementation):

public class topalertmetier implements toprepository{          @override         public list gettopsalert() throws throwable {         list<string> mylist= new arraylist<>();         class.forname("org.postgresql.driver");         connection connect= null;         connect = (connection)           drivermanager.getconnection("jdbc:postgresql://localhost:5432/bourse","postgres","123456");         java.sql.callablestatement proc =  connect.preparecall("{topalerte()}");         proc.registeroutparameter(1, java.sql.types.varchar);         proc.executequery();          resultset results = (resultset) proc.getobject(1);         mylist.add(results.getstring(0));         mylist.add(results.getstring(1));         mylist.add(results.getstring(2));         mylist.add(results.getstring(3));         mylist.add(results.getstring(4));         mylist.add(results.getstring(5));         mylist.add(results.getstring(6));          return mylist;   }

and controller :

  @restcontroller   public class topcontroller {        @autowired       toprepository toprepository;        @requestmapping(value="/alert",method = requestmethod.get)       public  @responsebody list alert() throws throwable{           return toprepository.gettopsalert();       }

the list shows names of max values ,so have contain values of table "tops" when running server : mvn spring-boot: run log show exception :

field toprepository in com.friendly_road.flight.controller.topcontroller required bean of type 'com.friendly_road.flight.dao.toprepository' not found.

this way use stored procedures within dao kind of "handmade" , dangerous. implemented configuration steps done on every method invocation , error-prone. it's not configurable anymore related database configuration , on.

the text says using spring boot, why don't use datasourceautoconfiguration , maintain database configuration within application.properties / application.yml file? way let spring manage datasource. ease transactional handling , using connection pooling , it's lot more convenient.

here nice article that.

in addition that, if use spring data jpa repositories interface not have need implement procedure invocation on own. stored procedure call easy realized in manner (assuming have top entity):

@repository public interface toprepository extends crudrepository<top, long> {      ...      @procedure(procedurename = "topalerte")     list gettopalerte();      ...  }

Comments

Post a Comment

Popular posts from this blog

inversion of control - Autofac named registration constructor injection -

does autofac support specifying registration name in components' constructors? example: ninject's namedattribute . you need use autofac.extras.attributed package on top achieve this so let's have 1 interface , 2 classes: public interface ihello { string sayhello(); } public class englishhello : ihello { public string sayhello() { return "hello"; } } public class frenchhello : ihello { public string sayhello() { return "bonjour"; } } then have consumer class, in want select instance injected: public class helloconsumer { private readonly ihello helloservice; public helloconsumer([withkey("en")] ihello helloservice) { if (helloservice == null) { throw new argumentnullexception("helloservice"); } this.helloservice = helloservice; } public string sayhello() { return this.helloservice.sayhello()...
Read more

verilog - Systemverilog dynamic casting issues -

i've code snippet following in testbench function void write_to_port( my_data_type_base data ); my_data_type_extended data_ext; if(!$cast(data_ext, data)); `uvm_error(get_type_name(), "failed cast"); `uvm_info(get_name(), $psprintf("data_ext :\n%s", data_ext.sprint()), uvm_medium) // write data_ext out port.... endfunction when run it, i'm getting uvm_error "failed cast." i'm not quire sure why $cast not returning 1. can see, i'm printing out extended class data item after casting uvm_info. can see it's being cast properly. if don't use $cast if condition, don't runtime error. isn't coding practice use if dynamic cast check if $cast returning 1 ? what might reason behind cast not returning 1 in above case? i think semicolon on line 'if' not belong? i think consumes if statement, , uvm_error executes regardless of how if evaluates: if(!$cast(data_ext, data)); <- no semico...
Read more

ios - Change Storyboard View using Seague -

i have following code checks connection server, , depending on want change views if no connection found. nsstring *connect = [nsstring stringwithcontentsofurl:[nsurl urlwithstring:@"http://myserver.com"] encoding:nsutf8stringencoding error:nil]; if (connect == null) { [self performseguewithidentifier:@"network_failure" sender:self]; } else { // other code here } my problem not change views. have added storyboard segue between 2 views identifier of network_failure . not have navigation controller or that, problem? thanks help! no not problem. first of all, in objectivec exists nil object oriented null . must use that. so: if(connect == nil) or better: if(!connect) so due fact nil not same of null , probably code does't enter in if scope . you have been able discover breakpoint. anyway: be sure set trigger segue (line) 2 view controller , , name network_failure (that not best name. should instead view1toview2 ) as, m...
Read more