i had project needs set sms gateway works java-ee project. gammu 1.36.0 selected.
backend db postgresql 9.4, table inbox , sequence created hold in come sms.
inbox created use:
create table inbox ( "updatedindb" timestamp(0) without time zone not null default localtimestamp(0), "receivingdatetime" timestamp(0) without time zone not null default localtimestamp(0), "text" text not null, "sendernumber" varchar(20) not null default '', "coding" varchar(255) not null default 'default_no_compression', "udh" text not null, "smscnumber" varchar(20) not null default '', "class" integer not null default '-1', "textdecoded" text not null default '', "id" serial primary key, "recipientid" text not null, "processed" boolean not null default 'false', check ("coding" in ('default_no_compression','unicode_no_compression','8bit','default_compression','unicode_compression'))); id sequence number hold in:
--create sequence inbox_id_seq; table structure looks like:
smsd-> \d inbox table "public.inbox" column | type | modifiers -------------------+--------------------------------+---------------------------------------------------------------- updatedindb | timestamp(0) without time zone | not null default ('now'::text)::timestamp(0) without time zone receivingdatetime | timestamp(0) without time zone | not null default ('now'::text)::timestamp(0) without time zone text | text | not null sendernumber | character varying(20) | not null default ''::character varying coding | character varying(255) | not null default 'default_no_compression'::character varying udh | text | not null smscnumber | character varying(20) | not null default ''::character varying class | integer | not null default (-1) textdecoded | text | not null default ''::text id | integer | not null default nextval('"inbox_id_seq"'::regclass) recipientid | text | not null processed | boolean | not null default false indexes: "inbox_pkey" primary key, btree ("id") check constraints: "inbox_coding_check" check ("coding"::text = (array['default_no_compression'::character varying, 'unicode_no_compression'::character varying, '8bit'::character varying, 'default_compression'::character varying, 'unicode_compression'::character varying]::text[])) triggers: update_timestamp before update on inbox each row execute procedure update_timestamp() smsd-> please note: column ids' position 10 in table.
this table working gammu 1.36.0, send , receive sms properly.
when try develop ejb bring inbox information web application.
i using jpa 2.1, implementation eclipselink 2.5.2. persistence.xml looks like:
<persistence-unit name="smsdjpanbpu" transaction-type="resource_local"> <provider>org.eclipse.persistence.jpa.persistenceprovider</provider> <mapping-file>com/longz/smsd/model/smsdinboxentity.xml</mapping-file> <mapping-file>com/longz/smsd/model/smsdoutboxentity.xml</mapping-file> <mapping-file>com/longz/smsd/model/smsdsentitemsentity.xml</mapping-file> <class>com.longz.smsd.model.smsdinboxentity</class> <class>com.longz.smsd.model.smsdoutboxentity</class> <class>com.longz.smsd.model.smsdsentitemsentity</class> <properties> <property name="eclipselink.jdbc.url" value="jdbc:postgresql://10.0.1.100:5433/smsd"/> <property name="eclipselink.jdbc.driver" value="org.postgresql.driver"/> <property name="eclipselink.jdbc.user" value="any"/> <property name="eclipselink.jdbc.password" value="any"/> <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/> </properties> </persistence-unit> jpa entity bean defined:
@entity @table(name = "inbox", schema = "public", catalog = "smsd") @namedqueries({ /*@namedquery(name = "inbox.findall", query = "select inboxentity order i.id desc"),*/ @namedquery(name = "inbox.findall", query = "select smsdinboxentity order i.id desc"), ...... public class smsdinboxentity implements serializable{ ...... @id @sequencegenerator(name="job_misfire_id_generator", sequencename="inbox_id_seq",schema = "public", allocationsize=1) @generatedvalue(strategy = generationtype.sequence, generator="job_misfire_id_generator") @column(name = "id", nullable = false, insertable = true, updatable = true) public int getid() { return id; } ...... } stateless session bean as:
@remote(inboxentityfacaderemote.class) @stateless(mappedname= "inboxentityfacadeejb") public class inboxentityfacade extends abstractfacade<smsdinboxentity> implements inboxentityfacaderemote { @persistencecontext(unitname = "smsdjpanbpu") private entitymanager em; @override protected entitymanager getentitymanager() { return em; } public inboxentityfacade() { super(smsdinboxentity.class); } @override public list<smsdinboxentity> findall(){ query query = em.createnamedquery("inbox.findall"); return new linkedlist<smsdinboxentity>(query.getresultlist()); /*return super.findall();*/ } i tried use findall() function list sms in inbox. function call named query statement:
@namedquery(name = "inbox.findall", query = "select smsdinboxentity order i.id desc"), test ejb client as:
try { context context = new initialcontext(properties); inboxentityfacaderemote inboxenfaremote = (inboxentityfacaderemote)context.lookup("inboxentityfacadeejb#com.longz.smsd.remote.inboxentityfacaderemote"); system.out.println("inboxentityfacadeejb found."); list<com.longz.smsd.model.smsdinboxentity> todaysemails = inboxenfaremote.findall(); system.out.println("records found: "+ todaysemails.size()); todaysemails.stream().foreach((e) -> { system.out.println(e.gettextdecoded()); }); }catch (namingexception e) { e.printstacktrace(); } } everything work fine far. strange error thrown:
run: inboxentityfacadeejb found. exception in thread "main" javax.ejb.ejbexception: ejb exception: ; nested exception is: javax.persistence.persistenceexception: exception [eclipselink-4002] (eclipse persistence services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.databaseexception internal exception: org.postgresql.util.psqlexception: error: column "id" not exist position: 8 error code: 0 call: select id, class, coding, processed, receivingdatetime, recipientid, sendernumber, smscnumber, text, textdecoded, udh, updatedindb smsd.public.inbox order id desc query: readallquery(name="inbox.findall" referenceclass=smsdinboxentity sql="select id, class, coding, processed, receivingdatetime, recipientid, sendernumber, smscnumber, text, textdecoded, udh, updatedindb smsd.public.inbox order id desc") @ weblogic.ejb.container.internal.remotebusinessintfproxy.unwrapremoteexception(remotebusinessintfproxy.java:117) @ weblogic.ejb.container.internal.remotebusinessintfproxy.invoke(remotebusinessintfproxy.java:92) @ com.sun.proxy.$proxy0.findall(unknown source) @ weblogicejbclient.weblogicinboxejbclient.main(weblogicinboxejbclient.java:38) caused by: javax.persistence.persistenceexception: exception [eclipselink-4002] (eclipse persistence services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.databaseexception internal exception: org.postgresql.util.psqlexception: error: column "id" not exist position: 8 error code: 0 call: select id, class, coding, processed, receivingdatetime, recipientid, sendernumber, smscnumber, text, textdecoded, udh, updatedindb smsd.public.inbox order id desc query: readallquery(name="inbox.findall" referenceclass=smsdinboxentity sql="select id, class, coding, processed, receivingdatetime, recipientid, sendernumber, smscnumber, text, textdecoded, udh, updatedindb smsd.public.inbox order id desc") @ org.eclipse.persistence.internal.jpa.queryimpl.getdetailedexception(queryimpl.java:378) @ org.eclipse.persistence.internal.jpa.queryimpl.executereadquery(queryimpl.java:260) @ org.eclipse.persistence.internal.jpa.queryimpl.getresultlist(queryimpl.java:469) @ com.longz.smsd.ejb.inboxentityfacade.findall(inboxentityfacade.java:36) @ com.longz.smsd.ejb.inboxentityfacadeejb_s9wt3_inboxentityfacaderemoteimpl.__wl_invoke(unknown source) @ weblogic.ejb.container.internal.sessionremotemethodinvoker.invoke(sessionremotemethodinvoker.java:34) @ com.longz.smsd.ejb.inboxentityfacadeejb_s9wt3_inboxentityfacaderemoteimpl.findall(unknown source) @ com.longz.smsd.ejb.inboxentityfacadeejb_s9wt3_inboxentityfacaderemoteimpl_wlskel.invoke(unknown source) @ weblogic.rmi.internal.basicserverref.invoke(basicserverref.java:701) @ weblogic.rmi.cluster.clusterableserverref.invoke(clusterableserverref.java:231) @ weblogic.rmi.internal.basicserverref$1.run(basicserverref.java:527) @ weblogic.security.acl.internal.authenticatedsubject.doas(authenticatedsubject.java:363) @ weblogic.security.service.securitymanager.runas(securitymanager.java:146) @ weblogic.rmi.internal.basicserverref.handlerequest(basicserverref.java:523) @ weblogic.rmi.internal.wls.wlsexecuterequest.run(wlsexecuterequest.java:118) @ weblogic.work.executethread.execute(executethread.java:311) @ weblogic.work.executethread.run(executethread.java:263) caused by: exception [eclipselink-4002] (eclipse persistence services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.databaseexception internal exception: org.postgresql.util.psqlexception: error: column "id" not exist position: 8 error code: 0 @ org.eclipse.persistence.exceptions.databaseexception.sqlexception(databaseexception.java:340) @ org.eclipse.persistence.internal.databaseaccess.databaseaccessor.basicexecutecall(databaseaccessor.java:682) @ org.eclipse.persistence.internal.databaseaccess.databaseaccessor.executecall(databaseaccessor.java:558) @ org.eclipse.persistence.internal.sessions.abstractsession.basicexecutecall(abstractsession.java:2002) @ org.eclipse.persistence.sessions.server.serversession.executecall(serversession.java:570) @ org.eclipse.persistence.internal.queries.datasourcecallquerymechanism.executecall(datasourcecallquerymechanism.java:242) @ org.eclipse.persistence.internal.queries.datasourcecallquerymechanism.executecall(datasourcecallquerymechanism.java:228) @ org.eclipse.persistence.internal.queries.datasourcecallquerymechanism.executeselectcall(datasourcecallquerymechanism.java:299) @ org.eclipse.persistence.internal.queries.datasourcecallquerymechanism.selectallrows(datasourcecallquerymechanism.java:694) @ org.eclipse.persistence.internal.queries.expressionquerymechanism.selectallrowsfromtable(expressionquerymechanism.java:2738) @ org.eclipse.persistence.internal.queries.expressionquerymechanism.selectallrows(expressionquerymechanism.java:2691) @ org.eclipse.persistence.queries.readallquery.executeobjectlevelreadquery(readallquery.java:495) @ org.eclipse.persistence.queries.objectlevelreadquery.executedatabasequery(objectlevelreadquery.java:1168) @ org.eclipse.persistence.queries.databasequery.execute(databasequery.java:899) @ org.eclipse.persistence.queries.objectlevelreadquery.execute(objectlevelreadquery.java:1127) @ org.eclipse.persistence.queries.readallquery.execute(readallquery.java:403) @ org.eclipse.persistence.queries.objectlevelreadquery.executeinunitofwork(objectlevelreadquery.java:1215) @ org.eclipse.persistence.internal.sessions.unitofworkimpl.internalexecutequery(unitofworkimpl.java:2896) @ org.eclipse.persistence.internal.sessions.abstractsession.executequery(abstractsession.java:1804) @ org.eclipse.persistence.internal.sessions.abstractsession.executequery(abstractsession.java:1786) @ org.eclipse.persistence.internal.sessions.abstractsession.executequery(abstractsession.java:1751) @ org.eclipse.persistence.internal.jpa.queryimpl.executereadquery(queryimpl.java:258) ... 15 more caused by: org.postgresql.util.psqlexception: error: column "id" not exist position: 8 @ org.postgresql.core.v3.queryexecutorimpl.receiveerrorresponse(queryexecutorimpl.java:2198) @ org.postgresql.core.v3.queryexecutorimpl.processresults(queryexecutorimpl.java:1927) @ org.postgresql.core.v3.queryexecutorimpl.execute(queryexecutorimpl.java:255) @ org.postgresql.jdbc2.abstractjdbc2statement.execute(abstractjdbc2statement.java:561) @ org.postgresql.jdbc2.abstractjdbc2statement.executewithflags(abstractjdbc2statement.java:419) @ org.postgresql.jdbc2.abstractjdbc2statement.executequery(abstractjdbc2statement.java:304) @ org.eclipse.persistence.internal.databaseaccess.databaseaccessor.executeselect(databaseaccessor.java:1007) @ org.eclipse.persistence.internal.databaseaccess.databaseaccessor.basicexecutecall(databaseaccessor.java:642) ... 35 more java result: 1 build successful (total time: 1 second) seems psql trying find "id" column in position 8 instead of position 10 , "id" column located in position 10 in inbox table.
any idea , advice please! appreciated!!
your problem case-sensitivity: jpa works in case insensitive mode default (i.e. won't quote identifiers in queries), while postgresql convert unquoted identifiers lower-case (this way postgresql tries achieve case insensitivity). but, defined "id" column in case-sensitive mode (note quotes).
an ugly workaround is, define entity in case-sensitive way:
@column(name = "\"id\"") or, can define table in case-insensitive way (which preferred):
create table inbox ( id serial primary key -- ... ); 
Comments
Post a Comment