-
1. Re: JDBC with SEAM
stephen Apr 16, 2008 3:04 PM (in response to ashwin)Short answer: Just use Seam, but don't use JPA.
Slightly longer answer:
If you are new to too many technologies, I think it's a good idea to leave JPA out for a while.
A JPA EntityManager, the different phases in the JSF lifecycle, Seam conversations, database transactions, web requests/responses/redirects all interact in an intricate (even though well defined and meaningful) way.You can simply define your Seam components the
usual
way, but load data data from the db using JDBC.
For example you can load data in a @Create method of a scoped seam component and store it in a member of the component to be accessed by your facelets page. -
2. Re: JDBC with SEAM
mmichalek.mmichalek.micros-retail.com Apr 16, 2008 5:00 PM (in response to ashwin)You'll also probably need to disable transaction management to go with your own persistence. Make sure you have something like this in your components.xml:
<core:init transaction-management-enabled="false" debug="false" />
-
3. Re: JDBC with SEAM
kariem Apr 17, 2008 12:36 PM (in response to ashwin)If you want to leverage the low-level EJB3 entity bean benefits (connection pooling, data source management …), you can still use EJB3 without ORM. The entity manager provides some methods to send native SQL queries: createNativeQuery(…) or createNamedQuery(String) (can also be used for standard EJB queries).
This could help you put a foot into the EJB3 (entity) space. You can build on it in a later stage, as soon as you feel comfortable with the other (new) technologies.
As Stephen said, it is important not use too many new technologies at once.
-
4. Re: JDBC with SEAM
admin.admin.email.tld Apr 17, 2008 7:29 PM (in response to ashwin)Be prepared for the following types of problems when using JDBC with Seam apps. It is not recommending for a variety of reasons to use JDBC with Seam (although if you do it right, you may not face any significant problems). This is an example of a serialization problem I'm trying to fix right now. It happens after the SFSB successfully passivates and then I execute a use case. Fix is unknown and very few hits in google on this...
09:09:47,654 ERROR [ExceptionFilter] exception root cause javax.faces.FacesException: javax.el.ELException: /TechDeployToUser.xhtml @109,146 value="#{techDeployToUser.hardwareEquipmentForUserLocation}": Error reading 'hardwareEquipmentForUserLocation' on type org.javassist.tmp.java.lang.Object_$$_javassist_5 at javax.faces.component.UIData.getValue(UIData.java:585) at org.ajax4jsf.component.UIDataAdaptor.getValue(UIDataAdaptor.java:1459) at org.ajax4jsf.component.SequenceDataAdaptor.getDataModel(SequenceDataAdaptor.java:70) at org.ajax4jsf.component.SequenceDataAdaptor.createDataModel(SequenceDataAdaptor.java:64) at org.ajax4jsf.component.UIDataAdaptor.getExtendedDataModel(UIDataAdaptor.java:592) at org.ajax4jsf.component.UIDataAdaptor.setRowKey(UIDataAdaptor.java:327) at org.ajax4jsf.component.UIDataAdaptor.iterate(UIDataAdaptor.java:968) at org.ajax4jsf.component.UIDataAdaptor.processDecodes(UIDataAdaptor.java:1001) at org.ajax4jsf.component.UIDataAdaptor.processDecodes(UIDataAdaptor.java:1011) at javax.faces.component.UIComponentBase.processDecodes(UIComponentBase.java:1001) at javax.faces.component.UIComponentBase.processDecodes(UIComponentBase.java:1001) at javax.faces.component.UIComponentBase.processDecodes(UIComponentBase.java:1001) at javax.faces.component.UIComponentBase.processDecodes(UIComponentBase.java:1001) at javax.faces.component.UIViewRoot.processDecodes(UIViewRoot.java:493) at org.ajax4jsf.component.AjaxViewRoot.access$001(AjaxViewRoot.java:57) at org.ajax4jsf.component.AjaxViewRoot$1.invokeRoot(AjaxViewRoot.java:260) at org.ajax4jsf.context.JsfOneOneInvoker.invokeOnRegionOrRoot(JsfOneOneInvoker.java:56) at org.ajax4jsf.context.AjaxContextImpl.invokeOnRegionOrRoot(AjaxContextImpl.java:170) at org.ajax4jsf.component.AjaxViewRoot.processDecodes(AjaxViewRoot.java:276) at com.sun.faces.lifecycle.ApplyRequestValuesPhase.execute(ApplyRequestValuesPhase.java:101) at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:251) at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:117) at javax.faces.webapp.FacesServlet.service(FacesServlet.java:244) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at jcifs.http.NtlmHttpFilter.doFilter(NtlmHttpFilter.java:118) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:83) at org.jboss.seam.debug.hot.HotDeployFilter.doFilter(HotDeployFilter.java:68) at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69) at org.jboss.seam.web.MultipartFilter.doFilter(MultipartFilter.java:85) at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69) at org.jboss.seam.web.ExceptionFilter.doFilter(ExceptionFilter.java:64) at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69) at org.jboss.seam.web.RedirectFilter.doFilter(RedirectFilter.java:44) at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69) at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:141) at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:281) at org.jboss.seam.web.Ajax4jsfFilter.doFilter(Ajax4jsfFilter.java:60) at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69) at org.jboss.seam.web.LoggingFilter.doFilter(LoggingFilter.java:58) at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69) at org.jboss.seam.servlet.SeamFilter.doFilter(SeamFilter.java:158) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175) at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:433) at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104) at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:241) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:580) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447) at java.lang.Thread.run(Unknown Source) Caused by: javax.el.ELException: /TechDeployToUser.xhtml @109,146 value="#{techDeployToUser.hardwareEquipmentForUserLocation}": Error reading 'hardwareEquipmentForUserLocation' on type org.javassist.tmp.java.lang.Object_$$_javassist_5 at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:76) at javax.faces.component.UIData.getValue(UIData.java:582) ... 62 more Caused by: java.lang.RuntimeException: java.lang.RuntimeException: org.jboss.serial.exception.SerializationException: Could not create instance of com.microsoft.sqlserver.jdbc.SQLServerConnectionSecurityManager - com.microsoft.sqlserver.jdbc.SQLServerConnectionSecurityManager at org.jboss.ejb3.interceptor.LifecycleInterceptorHandler.postActivate(LifecycleInterceptorHandler.java:152) at org.jboss.ejb3.stateful.StatefulContainer.invokePostActivate(StatefulContainer.java:400) at org.jboss.ejb3.stateful.StatefulBeanContext.postActivate(StatefulBeanContext.java:381) at org.jboss.ejb3.cache.simple.StatefulSessionFilePersistenceManager.activateSession(StatefulSessionFilePersistenceManager.java:316) at org.jboss.ejb3.cache.simple.SimpleStatefulCache.get(SimpleStatefulCache.java:387) at org.jboss.ejb3.cache.simple.SimpleStatefulCache.get(SimpleStatefulCache.java:375) at org.jboss.ejb3.stateful.StatefulInstanceInterceptor.invoke(StatefulInstanceInterceptor.java:61) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101) at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:77) at org.jboss.ejb3.security.Ejb3AuthenticationInterceptor.invoke(Ejb3AuthenticationInterceptor.java:106) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101) at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:46) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101) at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101) at org.jboss.ejb3.stateful.StatefulContainer.localInvoke(StatefulContainer.java:204) at org.jboss.ejb3.stateful.StatefulLocalProxy.invoke(StatefulLocalProxy.java:100) at $Proxy178.getHardwareEquipmentForUserLocation(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.jboss.seam.util.Reflections.invoke(Reflections.java:21) at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31) at org.jboss.seam.intercept.ClientSideInterceptor$1.proceed(ClientSideInterceptor.java:76) at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56) at org.jboss.seam.ejb.RemoveInterceptor.aroundInvoke(RemoveInterceptor.java:41) at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68) at org.jboss.seam.core.SynchronizationInterceptor.aroundInvoke(SynchronizationInterceptor.java:32) at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68) at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:106) at org.jboss.seam.intercept.ClientSideInterceptor.invoke(ClientSideInterceptor.java:54) at org.javassist.tmp.java.lang.Object_$$_javassist_5.getHardwareEquipmentForUserLocation(Object_$$_javassist_5.java) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at javax.el.BeanELResolver.getValue(BeanELResolver.java:62) at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:53) at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:64) at org.jboss.el.parser.AstPropertySuffix.getValue(AstPropertySuffix.java:53) at org.jboss.el.parser.AstValue.getValue(AstValue.java:67) at org.jboss.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:186) at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:71) ... 63 more Caused by: java.lang.RuntimeException: org.jboss.serial.exception.SerializationException: Could not create instance of com.microsoft.sqlserver.jdbc.SQLServerConnectionSecurityManager - com.microsoft.sqlserver.jdbc.SQLServerConnectionSecurityManager at org.jboss.ejb3.stateful.StatefulBeanContext.extractBeanAndInterceptors(StatefulBeanContext.java:809) at org.jboss.ejb3.stateful.StatefulBeanContext.getInterceptorInstances(StatefulBeanContext.java:768) at org.jboss.ejb3.interceptor.LifecycleInvocationContextImpl.getLifecycleInvocationContext(LifecycleInvocationContextImpl.java:65) at org.jboss.ejb3.interceptor.LifecycleInterceptorHandler.postActivate(LifecycleInterceptorHandler.java:143) ... 106 more Caused by: org.jboss.serial.exception.SerializationException: Could not create instance of com.microsoft.sqlserver.jdbc.SQLServerConnectionSecurityManager - com.microsoft.sqlserver.jdbc.SQLServerConnectionSecurityManager at org.jboss.serial.classmetamodel.ClassMetaData.newInstance(ClassMetaData.java:342) at org.jboss.serial.persister.RegularObjectPersister.readData(RegularObjectPersister.java:239) at org.jboss.serial.objectmetamodel.ObjectDescriptorFactory.readObjectDescriptionFromStreaming(ObjectDescriptorFactory.java:412) at org.jboss.serial.objectmetamodel.ObjectDescriptorFactory.objectFromDescription(ObjectDescriptorFactory.java:82) at org.jboss.serial.objectmetamodel.DataContainer$DataContainerDirectInput.readObject(DataContainer.java:643) at org.jboss.serial.persister.RegularObjectPersister.readSlotWithFields(RegularObjectPersister.java:353) at org.jboss.serial.persister.RegularObjectPersister.defaultRead(RegularObjectPersister.java:273) at org.jboss.serial.persister.RegularObjectPersister.readData(RegularObjectPersister.java:241) at org.jboss.serial.objectmetamodel.ObjectDescriptorFactory.readObjectDescriptionFromStreaming(ObjectDescriptorFactory.java:412) at org.jboss.serial.objectmetamodel.ObjectDescriptorFactory.objectFromDescription(ObjectDescriptorFactory.java:82) at org.jboss.serial.objectmetamodel.DataContainer$DataContainerDirectInput.readObject(DataContainer.java:643) at org.jboss.serial.persister.RegularObjectPersister.readSlotWithFields(RegularObjectPersister.java:353) at org.jboss.serial.persister.RegularObjectPersister.defaultRead(RegularObjectPersister.java:273) at org.jboss.serial.persister.RegularObjectPersister.readData(RegularObjectPersister.java:241) at org.jboss.serial.objectmetamodel.ObjectDescriptorFactory.readObjectDescriptionFromStreaming(ObjectDescriptorFactory.java:412) at org.jboss.serial.objectmetamodel.ObjectDescriptorFactory.objectFromDescription(ObjectDescriptorFactory.java:82) at org.jboss.serial.objectmetamodel.DataContainer$DataContainerDirectInput.readObject(DataContainer.java:643) at org.jboss.serial.persister.ArrayPersister.readObjectArray(ArrayPersister.java:196) at org.jboss.serial.persister.ArrayPersister.readData(ArrayPersister.java:172) at org.jboss.serial.objectmetamodel.ObjectDescriptorFactory.readObjectDescriptionFromStreaming(ObjectDescriptorFactory.java:412) at org.jboss.serial.objectmetamodel.ObjectDescriptorFactory.objectFromDescription(ObjectDescriptorFactory.java:82) at org.jboss.serial.objectmetamodel.DataContainer$DataContainerDirectInput.readObject(DataContainer.java:643) at org.jboss.serial.io.JBossObjectInputStream.readObjectOverride(JBossObjectInputStream.java:163) at java.io.ObjectInputStream.readObject(Unknown Source) at org.jboss.serial.io.MarshalledObject.get(MarshalledObject.java:68) at org.jboss.ejb3.stateful.StatefulBeanContext.extractBeanAndInterceptors(StatefulBeanContext.java:780) ... 109 more Caused by: java.lang.InstantiationException: com.microsoft.sqlserver.jdbc.SQLServerConnectionSecurityManager at java.lang.Class.newInstance0(Unknown Source) at java.lang.Class.newInstance(Unknown Source) at org.jboss.serial.classmetamodel.ClassMetaData.newInstance(ClassMetaData.java:334) ... 134 more
-
5. Re: JDBC with SEAM
admin.admin.email.tld Apr 17, 2008 7:42 PM (in response to ashwin)from Seam2.0.0.GA ref doc:
8.2.1. Disabling Seam-managed transactions
Seam transaction management is enabled by default for all JSF requests. If you want to disable this feature, you
can do it in components.xml:<core:init transaction-management-enabled="false"/> <transaction:no-transaction />
In my inherited Seam app using JDBC, I don't have the above metadata/code in the components/xml with no problems yet AFAIK.
I was wondering what happens when you use JDBC API within a EJB SFSB method that has REQUIRED transaction type by default??? That enables by default CMT but not sure if that's required with JDBC and if not then what/how are transactions handled?
resourcelocal or JTA, does that come into play here??
-
6. Re: JDBC with SEAM
tom_goring Apr 17, 2008 7:52 PM (in response to ashwin)Hi,
I have a mix of EJB3 entities (standard seam stuff) and a few SLSB that use a raw JDBC connection to do some native queries with no problems.
E.g.
@Stateless @Local(BankBalanceManager.class) @Name("bankBalanceManager") @AutoCreate public class BankBalanceManagerBean implements BankBalanceManager { @Resource (mappedName=BLJndi.DataSourceName) private DataSource jnetDS; public BankTransactionSummary findBankTransactionSummary(Company company, BankFilter bankFilter) { // do some jdbc } }
The stack trace above looks like you are attempting to serializing the connection in your UI component tree. It look like a coding error to me.... can you show your 'hardwareEquipmentForUserLocation' class.
-
7. Re: JDBC with SEAM
admin.admin.email.tld Apr 18, 2008 4:37 PM (in response to ashwin)I can't post the entire code for this class, it's approx. 1000 lines long and the message text must be less than 32678 chars.
public List<TbHardwareObject> getHardwareEquipmentForUserLocation() { params = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap(); String userLocationNo = ""; try { if (getLocNo() != null && getLocNo().trim().length()>0 && (params.get("isAssignBtnClicked") == null && params.get("isRemoveBtnClicked") == null)) { userLocationNo = getLocNo().trim(); con = DriverManager.getConnection(SHIMSConstants.URL, SHIMSConstants.USERNAME, SHIMSConstants.PASSWORD); sqlStringBuffer = new StringBuffer(); sqlStringBuffer.append(" SELECT hardware.hardwareid HardwareId, "); sqlStringBuffer.append(" location.locationno LocationNo ,"); sqlStringBuffer.append(" hardware.coxbarcode BarCode ,"); sqlStringBuffer.append(" owners.ownertypecode OwnerTypeCode ,"); sqlStringBuffer.append(" owners.ownertypedesc OwnerGroup ,"); sqlStringBuffer.append(" vendor.hardwarevendordesc Vendor ,"); sqlStringBuffer.append(" types.hardwaretypedesc HardwareType ,"); sqlStringBuffer.append(" model.hardwaremodel Model ,"); sqlStringBuffer.append(" hardware.enteredbyuser AssignedBy ,"); sqlStringBuffer.append(" hardware.firstentereddate AssignedDate ,"); sqlStringBuffer.append(" '"+SHIMSConstants.CURRENTLY_OWNER_BY_USER+"'"+ " HardwareStatus "); sqlStringBuffer.append(" FROM tblocation location ,"); sqlStringBuffer.append(" tbhardware hardware ,"); sqlStringBuffer.append(" tbhardwarevendor vendor ,"); sqlStringBuffer.append(" trownertype owners ,"); sqlStringBuffer.append(" trhardwaretype types ,"); sqlStringBuffer.append(" tbhardwaremodel model ,"); sqlStringBuffer.append(" trhardwarestatus hwstatus "); sqlStringBuffer.append(" WHERE location.locationno = ").append("'"+userLocationNo+"'"); sqlStringBuffer.append(" AND hwstatus.hardwarestatus = ").append("'"+SHIMSConstants.DEPLOYED_TO_USER_STATUS_ID+"'"); sqlStringBuffer.append(" AND hwstatus.hardwarestatus = hardware.currentstatus "); sqlStringBuffer.append(" AND location.locationno = hardware.currentlocationno "); sqlStringBuffer.append(" AND hardware.ownertypecode = owners.ownertypecode "); sqlStringBuffer.append(" AND hardware.hardwaremodelid = model.hardwaremodelid "); sqlStringBuffer.append(" AND model.hardwarevendorid = vendor.hardwarevendorid "); sqlStringBuffer.append(" AND model.hardwaretypeid = types.hardwaretypeid "); sqlStringBuffer.append(" ORDER BY hardware.coxbarcode "); Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery(sqlStringBuffer.toString()); rs.last(); int count = rs.getRow(); rs.beforeFirst(); if (count == 0) { userLocMsg = SHIMSConstants.USER_MSG; userLocEquipList.clear(); } if (rs!=null && count > 0 && params.get("isAssignBtnClicked") == null && params.get("isRemoveBtnClicked") == null) { userLocMsg = ""; isAssignToTechBtn = false; userLocEquipList.clear(); while (rs.next()) { if (rs.getString("AssignedDate") != null) { month = rs.getString("AssignedDate").toString().substring(5, 7); day = rs.getString("AssignedDate").toString().substring(8, 10); year = rs.getString("AssignedDate").toString().substring(0, 4); localAssignedDate = month + "/"+day+"/"+year; } tbHardwareObject = new TbHardwareObject(); tbHardwareObject.setHardwareId(rs.getString("HardwareId")); tbHardwareObject.setHistoricalStatus(rs.getString("HardwareStatus")); tbHardwareObject.setLocationNo(rs.getString("LocationNo")); tbHardwareObject.setModifiedByUser(""); tbHardwareObject.setEnteredByUser(""); tbHardwareObject.setBarCode(rs.getString("BarCode")); tbHardwareObject.setHistoricalOwnerTypeCode(rs.getString("OwnerTypeCode")); tbHardwareObject.setHistoricalOwnerTypeDesc(rs.getString("OwnerGroup")); tbHardwareObject.setManufacturer(rs.getString("Vendor")); tbHardwareObject.setType(rs.getString("HardwareType") ); tbHardwareObject.setModel(rs.getString("Model")); tbHardwareObject.setUnitPrice(""); tbHardwareObject.setAssignedBy(rs.getString("AssignedBy")); tbHardwareObject.setAssignedDate(localAssignedDate); tbHardwareObject.setAssignedToTech(""); userLocEquipList.add(tbHardwareObject); } } } } catch (SQLException se) { log.info("We got an exception in *** getHardwareEquipmentForUserLocation()*** while executing a Prepare Statement for SQL Query:" + "- Possibly bad SQL, or check the connection."); se.printStackTrace(); System.exit(1); } finally { cleanUp(); } return userLocEquipList; }
-
8. Re: JDBC with SEAM
admin.admin.email.tld Apr 18, 2008 4:41 PM (in response to ashwin)package com.cox.shims.session; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.DateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Map; import javax.annotation.PostConstruct; import javax.ejb.PostActivate; import javax.ejb.PrePassivate; import javax.ejb.Remove; import javax.ejb.Stateful; import javax.faces.component.html.HtmlInputText; import javax.faces.context.FacesContext; import javax.faces.event.AbortProcessingException; import javax.faces.event.ValueChangeEvent; import jcifs.smb.NtlmPasswordAuthentication; import org.hibernate.annotations.Cache; import org.jboss.seam.ScopeType; import org.jboss.seam.annotations.Destroy; import org.jboss.seam.annotations.In; import org.jboss.seam.annotations.Logger; import org.jboss.seam.annotations.Name; import org.jboss.seam.annotations.Scope; import org.jboss.seam.contexts.Context; import org.jboss.seam.faces.FacesMessages; import org.jboss.seam.log.Log; import org.jboss.seam.security.Identity; import com.cox.shims.entity.UTbUser; import com.cox.shims.utils.SHIMSConstants; @Stateful //AS 041508 - testing session scope for conversation ended error @Scope(ScopeType.SESSION) // //@Cache(org.jboss.ejb3.cache.NoPassivationCache.class) @Name("techDeployToUser") public class TechDeployToUserAction implements TechDeployToUserLocal{ @Logger Log log; //externalizable (according to Pete Muir) which implements serializable @In private FacesMessages facesMessages; //serializable @In Identity identity; //serializable @In (required=false) UTbUser user; //serializable @In Context sessionContext; //not serializable private List<TbHardwareObject> hardwareTableList = new ArrayList(); //not serializable private List<TbHardwareObject> locationsList = new ArrayList(); //not serializable private List<TbHardwareObject> userLocEquipList = new ArrayList(); //not serializable private List<TbHardwareObject> newlyAssignedByTechEquipList= new ArrayList(); //not serializable private List<TbHardwareObject> removedFromUserEquipList = new ArrayList(); //not serializable Connection con = null; //not serializable Statement stmt = null; //not serializable PreparedStatement preparedStmt = null; //not serializable ResultSet rs = null; //not serializable StringBuffer sqlStringBuffer = null; //serializable String locNo; //serializable String techLocMsg = ""; //serializable String userLocMsg = SHIMSConstants.USER_MSG; //serializable String month="", day="", year="", localAssignedDate=""; //serializable Map params; //not serializable String msgConfirm; //serializable String compId; //serializable String compMsg; //serializable String techLocationName; //serializable String userLocationName; //serializable String networkLogin; //serializable boolean isAssignToTechBtn = true; // n/a boolean isSubmitByTechBtn = true; // n/a Date now = new Date(); //serializable TbHardwareObject tbHardwareObject = null; //serializable after modding the class to implement serializable //@Begin(join=true) public List getAssignedToTechHardwareList() { params = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap(); if (params != null) { log.info("inputlocno = " + params.get("inputlocno")); log.info("isAssignBtnClicked = " + params.get("isAssignBtnClicked")); log.info("isRemoveBtnClicked = " + params.get("isRemoveBtnClicked")); //AS 041508 - commenting line below as I'm not sure //if (params.get("inputlocno") == null && (params.get("isAssignBtnClicked")== null && params.get("isRemoveBtnClicked") == null)) if ((params.get("isAssignBtnClicked")== null && params.get("isRemoveBtnClicked") == null)) { try { if (user != null) { networkLogin = user.getNetworkLogin(); } else { networkLogin = (((NtlmPasswordAuthentication) sessionContext.get("NtlmHttpAuth")).getDomain() +"\\"+((NtlmPasswordAuthentication) sessionContext.get("NtlmHttpAuth")).getUsername()); } try { if (networkLogin != null) { try { log.info("Inside getAssignedToTechHardwareList"); con = DriverManager.getConnection(SHIMSConstants.URL, SHIMSConstants.USERNAME, SHIMSConstants.PASSWORD); hardwareTableList = buildAssignedToTechHardwareList(con, preparedStmt, networkLogin); } catch (SQLException se) { log.info("We got an exception while creating a statement:" + "that probably means we're no longer connected."); se.printStackTrace(); System.exit(1); } finally { cleanUp(); } } } catch (Exception e) { e.printStackTrace(); } } catch(Exception exp) { exp.printStackTrace(); } } } if (hardwareTableList !=null && hardwareTableList.size()>0) { techLocMsg = ""; } return hardwareTableList; } private List buildAssignedToTechHardwareList(Connection con, PreparedStatement preparedStmt, String networkLogin) { try { sqlStringBuffer = new StringBuffer(); sqlStringBuffer.append(" SELECT "); sqlStringBuffer.append(" hardware.hardwareid HardwareId ,"); sqlStringBuffer.append(" location.locationno LocationNo ,"); sqlStringBuffer.append(" hardware.coxbarcode BarCode ,"); sqlStringBuffer.append(" owners.ownertypecode OwnerTypeCode ,"); sqlStringBuffer.append(" owners.ownertypedesc OwnerGroup ,"); sqlStringBuffer.append(" vendor.hardwarevendordesc Vendor ,"); sqlStringBuffer.append(" types.hardwaretypedesc HardwareType ,"); sqlStringBuffer.append(" model.hardwaremodel Model ,"); sqlStringBuffer.append(" hardware.enteredbyuser AssignedBy ,"); sqlStringBuffer.append(" hardware.firstentereddate AssignedDate ,"); sqlStringBuffer.append("'"+SHIMSConstants.AVAILABLE_FOR_DEPLOYMENT+"'"+ " HardwareStatus "); sqlStringBuffer.append(" FROM u_tbuser tbuser ,"); sqlStringBuffer.append(" tblocation location ,"); sqlStringBuffer.append(" tbhardware hardware ,"); sqlStringBuffer.append(" tbhardwarevendor vendor ,"); sqlStringBuffer.append(" trownertype owners ,"); sqlStringBuffer.append(" trhardwaretype types ,"); sqlStringBuffer.append(" tbhardwaremodel model ,"); sqlStringBuffer.append(" trhardwarestatus hwstatus "); sqlStringBuffer.append(" WHERE tbuser.networklogin = "+"'"+networkLogin+"'" ); sqlStringBuffer.append(" AND tbuser.userid = location.userid "); sqlStringBuffer.append(" AND location.locationno = hardware.currentlocationno "); sqlStringBuffer.append(" AND hardware.currentstatus = "+"'"+SHIMSConstants.ASSIGN_TO_TECH_STATUS_ID+"'"); sqlStringBuffer.append(" AND hardware.ownertypecode = owners.ownertypecode "); sqlStringBuffer.append(" AND hardware.hardwaremodelid = model.hardwaremodelid "); sqlStringBuffer.append(" AND model.hardwarevendorid = vendor.hardwarevendorid "); sqlStringBuffer.append(" AND model.hardwaretypeid = types.hardwaretypeid "); sqlStringBuffer.append(" AND hardware.currentstatus = hwstatus.hardwarestatus "); sqlStringBuffer.append(" ORDER BY hardware.coxbarcode "); log.info("buildAssignedToTechHardwareList(): sqlStringBuffer = " + sqlStringBuffer); preparedStmt = con.prepareStatement(sqlStringBuffer.toString()); } catch (SQLException se) { log.info("We got an exception in *** buildAssignedToTechHardwareList()*** while preparing the SQL Query:" + "that probably means our SQL is invalid"); se.printStackTrace(); System.exit(1); } try { rs = preparedStmt.executeQuery(); if (rs!=null) { //AS 04/11/08 - clear out hardwareTableList b/c there were duplicate entries displaying in the dataTable if (hardwareTableList != null) { hardwareTableList.clear(); } while (rs.next()) { if (rs.getString("AssignedDate") != null) { month = rs.getString("AssignedDate").toString().substring(5, 7); day = rs.getString("AssignedDate").toString().substring(8, 10); year = rs.getString("AssignedDate").toString().substring(0, 4); localAssignedDate = month + "/"+day+"/"+year; } tbHardwareObject = new TbHardwareObject(); tbHardwareObject.setHardwareId(rs.getString("HardwareId")); tbHardwareObject.setHistoricalStatus(rs.getString("HardwareStatus")); tbHardwareObject.setLocationNo(rs.getString("LocationNo")); tbHardwareObject.setModifiedByUser(""); tbHardwareObject.setEnteredByUser(""); tbHardwareObject.setBarCode(rs.getString("BarCode")); tbHardwareObject.setHistoricalOwnerTypeCode(rs.getString("OwnerTypeCode")); tbHardwareObject.setHistoricalOwnerTypeDesc(rs.getString("OwnerGroup")); tbHardwareObject.setManufacturer(rs.getString("Vendor")); tbHardwareObject.setType(rs.getString("HardwareType") ); tbHardwareObject.setModel(rs.getString("Model")); tbHardwareObject.setUnitPrice(""); tbHardwareObject.setAssignedBy(rs.getString("AssignedBy")); tbHardwareObject.setAssignedDate(localAssignedDate); tbHardwareObject.setAssignedToTech(""); hardwareTableList.add(tbHardwareObject); } } } catch (SQLException se) { log.info("We got an exception in *** buildAssignedToTechHardwareList()*** while executing a Prepare Statement for SQL Query:" + "- Possibly bad SQL, or check the connection."); se.printStackTrace(); System.exit(1); } finally { this.cleanUp(); } return hardwareTableList; } public void validateUserLocation(ValueChangeEvent event) throws AbortProcessingException { HtmlInputText myInput = (HtmlInputText)event.getComponent(); String userLocation = ""; FacesContext fc = FacesContext.getCurrentInstance(); fc.getExternalContext().getRequestMap().put("focusId",(event.getComponent().getClientId(FacesContext.getCurrentInstance()))); boolean invalidFormat = false; boolean invalidRangeChk = false; boolean invalidLengthChk = false; boolean notInInvChk = false; try { if (myInput.getValue() != null) { userLocation = myInput.getValue().toString().trim(); if (userLocation.length()>0) { int len= userLocation.length() ; int s_charcode = 0; for (int i=0;i<len;i++) { s_charcode = userLocation.charAt(i); if(!((s_charcode>=48 && s_charcode<=57))) { userLocationName = ""; myInput.setStyle("border-right-color: Red; border-bottom-color: Red; border-top-width: medium; border-top-color: Red; border-right-width: medium; border-left-width: medium; border-left-color: Red; border-bottom-width: medium"); setCompId(fc.getExternalContext().getRequestMap().get("focusId").toString()); setCompMsg("YES"); setTechLocMsg(userLocation+ " HAS INVALID FORMAT - PLEASE ENTER A NUMERIC LOCATION NO (EX: 1234)"); setUserLocMsg(SHIMSConstants.USER_MSG); invalidFormat = true; } } } if( invalidFormat == false) { if (userLocation.length()>0 && Integer.valueOf(userLocation) < 1000 && Integer.valueOf(userLocation) > 9999) { userLocationName = ""; myInput.setStyle("border-right-color: Red; border-bottom-color: Red; border-top-width: medium; border-top-color: Red; border-right-width: medium; border-left-width: medium; border-left-color: Red; border-bottom-width: medium"); setCompId(fc.getExternalContext().getRequestMap().get("focusId").toString()); setCompMsg("YES"); setTechLocMsg(userLocation+ " - NOT IN PERMITTED RANGE OF 1000 & 99999."); setUserLocMsg(SHIMSConstants.USER_MSG); invalidRangeChk = true; } } else if (invalidFormat== false && invalidRangeChk== false && (userLocation.length() > 0 && userLocation.length() < 4)) { userLocationName = ""; myInput.setStyle("border-right-color: Red; border-bottom-color: Red; border-top-width: medium; border-top-color: Red; border-right-width: medium; border-left-width: medium; border-left-color: Red; border-bottom-width: medium"); setCompId(fc.getExternalContext().getRequestMap().get("focusId").toString()); setCompMsg("YES"); setTechLocMsg(userLocation+" INVALID LENGTH "+" - PERMITTED LOCATION LENGTH MUST BE 4."); setUserLocMsg(SHIMSConstants.USER_MSG); invalidLengthChk = true; } if (invalidFormat== false && invalidRangeChk == false && invalidLengthChk == false) { if (Integer.valueOf(findLocation(userLocation)) == 0) { userLocationName = ""; myInput.setStyle("border-right-color: Red; border-bottom-color: Red; border-top-width: medium; border-top-color: Red; border-right-width: medium; border-left-width: medium; border-left-color: Red; border-bottom-width: medium"); setCompId(fc.getExternalContext().getRequestMap().get("focusId").toString()); setCompMsg("YES"); setTechLocMsg(userLocation+ " - INVALID LOCATION."); setUserLocMsg(SHIMSConstants.USER_MSG); notInInvChk = true; } } if (invalidFormat == false && invalidRangeChk == false && invalidLengthChk == false && notInInvChk == false) { log.info("ALL VALIDATIONS PASSED"); userLocationName = getFirstNameLastName(userLocation, SHIMSConstants.USER_MSG_2); myInput.setStyle("border-right-color: GREEN; border-bottom-color: GREEN; border-top-width: medium; border-top-color: GREEN; border-right-width: medium; border-left-width: medium; border-left-color: GREEN; border-bottom-width: medium"); setCompId(fc.getExternalContext().getRequestMap().get("focusId").toString()); setCompMsg("NO"); setTechLocMsg(""); setUserLocMsg(""); isAssignToTechBtn = false; } } } catch (Exception e) { e.printStackTrace(); } } private String findLocation(String userLocationNo) { try { con = DriverManager.getConnection(SHIMSConstants.URL, SHIMSConstants.USERNAME, SHIMSConstants.PASSWORD); sqlStringBuffer = new StringBuffer(); sqlStringBuffer.append(" SELECT COUNT(*) LOC_CNT FROM TBLOCATION WHERE LOCATIONNO = ").append("'"+userLocationNo+"'"); preparedStmt = con.prepareStatement(sqlStringBuffer.toString()); rs = preparedStmt.executeQuery(); while (rs.next()) { sqlStringBuffer = new StringBuffer(); sqlStringBuffer.append(rs.getString("LOC_CNT")); } } catch (SQLException se) { log.info("We got an exception in *** findLocation()*** while executing a Prepare Statement for SQL Query:" + "- Possibly bad SQL, or check the connection."); se.printStackTrace(); System.exit(1); } finally { cleanUp(); } return sqlStringBuffer.toString(); } private String getFirstNameLastName(String userLocationNo, String locMsg) { try { con = DriverManager.getConnection(SHIMSConstants.URL, SHIMSConstants.USERNAME, SHIMSConstants.PASSWORD); sqlStringBuffer = new StringBuffer(); sqlStringBuffer.append(" SELECT "); sqlStringBuffer.append(" tbuser.LastName NameLast, tbuser.FirstName NameFirst"); sqlStringBuffer.append(" FROM tblocation location, u_tbuser tbuser "); sqlStringBuffer.append(" WHERE location.LocationNo = ").append("'"+userLocationNo+"'"); sqlStringBuffer.append(" AND location.userid = tbuser.userid "); preparedStmt = con.prepareStatement(sqlStringBuffer.toString()); rs = preparedStmt.executeQuery(); while (rs.next()) { sqlStringBuffer = new StringBuffer(); sqlStringBuffer.append(rs.getString ("NameLast")).append(", ").append(rs.getString ("NameFirst")).append(locMsg); } } catch (SQLException se) { log.info("We got an exception in *** getFirstNameLastName()*** while executing a Prepare Statement for SQL Query:" + "- Possibly bad SQL, or check the connection."); se.printStackTrace(); System.exit(1); } finally { cleanUp(); } return sqlStringBuffer.toString(); } public List autoSuggestLocNosList(Object event) { String suggestString = event.toString(); try { con = DriverManager.getConnection(SHIMSConstants.URL, SHIMSConstants.USERNAME, SHIMSConstants.PASSWORD); sqlStringBuffer = new StringBuffer(); sqlStringBuffer.append("SELECT LocationNo "); sqlStringBuffer.append("FROM tblocation "); sqlStringBuffer.append("WHERE LocationNo ").append(" LIKE " + " '%"+suggestString+"%' "); sqlStringBuffer.append("AND LocationTypeCode ").append(" NOT IN "+"('"+SHIMSConstants.STORAGE_OR_WAREHOUSE+"')"); stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery(sqlStringBuffer.toString()); rs.last(); int count = rs.getRow(); rs.beforeFirst(); locationsList.clear(); if (count ==0) { tbHardwareObject = new TbHardwareObject(); tbHardwareObject.setHistoricalStatus(SHIMSConstants.NO_MATCHING_LOCATIONS_FOUND); locationsList.add(tbHardwareObject); } else if (rs!=null && count > 0) { while (rs.next()) { tbHardwareObject = new TbHardwareObject(); tbHardwareObject.setLocationNo(rs.getString("LocationNo")); locationsList.add(tbHardwareObject); } } } catch (SQLException se) { log.info("We got an exception in *** autoSuggestLocNosList()*** while executing a Prepare Statement for SQL Query:" + "- Possibly bad SQL, or check the connection."); se.printStackTrace(); System.exit(1); } finally { cleanUp(); } return locationsList; }
-
9. Re: JDBC with SEAM
admin.admin.email.tld Apr 18, 2008 4:42 PM (in response to ashwin)public List<TbHardwareObject> getHardwareEquipmentForUserLocation() { params = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap(); String userLocationNo = ""; try { if (getLocNo() != null && getLocNo().trim().length()>0 && (params.get("isAssignBtnClicked") == null && params.get("isRemoveBtnClicked") == null)) { userLocationNo = getLocNo().trim(); con = DriverManager.getConnection(SHIMSConstants.URL, SHIMSConstants.USERNAME, SHIMSConstants.PASSWORD); sqlStringBuffer = new StringBuffer(); sqlStringBuffer.append(" SELECT hardware.hardwareid HardwareId, "); sqlStringBuffer.append(" location.locationno LocationNo ,"); sqlStringBuffer.append(" hardware.coxbarcode BarCode ,"); sqlStringBuffer.append(" owners.ownertypecode OwnerTypeCode ,"); sqlStringBuffer.append(" owners.ownertypedesc OwnerGroup ,"); sqlStringBuffer.append(" vendor.hardwarevendordesc Vendor ,"); sqlStringBuffer.append(" types.hardwaretypedesc HardwareType ,"); sqlStringBuffer.append(" model.hardwaremodel Model ,"); sqlStringBuffer.append(" hardware.enteredbyuser AssignedBy ,"); sqlStringBuffer.append(" hardware.firstentereddate AssignedDate ,"); sqlStringBuffer.append(" '"+SHIMSConstants.CURRENTLY_OWNER_BY_USER+"'"+ " HardwareStatus "); sqlStringBuffer.append(" FROM tblocation location ,"); sqlStringBuffer.append(" tbhardware hardware ,"); sqlStringBuffer.append(" tbhardwarevendor vendor ,"); sqlStringBuffer.append(" trownertype owners ,"); sqlStringBuffer.append(" trhardwaretype types ,"); sqlStringBuffer.append(" tbhardwaremodel model ,"); sqlStringBuffer.append(" trhardwarestatus hwstatus "); sqlStringBuffer.append(" WHERE location.locationno = ").append("'"+userLocationNo+"'"); sqlStringBuffer.append(" AND hwstatus.hardwarestatus = ").append("'"+SHIMSConstants.DEPLOYED_TO_USER_STATUS_ID+"'"); sqlStringBuffer.append(" AND hwstatus.hardwarestatus = hardware.currentstatus "); sqlStringBuffer.append(" AND location.locationno = hardware.currentlocationno "); sqlStringBuffer.append(" AND hardware.ownertypecode = owners.ownertypecode "); sqlStringBuffer.append(" AND hardware.hardwaremodelid = model.hardwaremodelid "); sqlStringBuffer.append(" AND model.hardwarevendorid = vendor.hardwarevendorid "); sqlStringBuffer.append(" AND model.hardwaretypeid = types.hardwaretypeid "); sqlStringBuffer.append(" ORDER BY hardware.coxbarcode "); Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery(sqlStringBuffer.toString()); rs.last(); int count = rs.getRow(); rs.beforeFirst(); if (count == 0) { userLocMsg = SHIMSConstants.USER_MSG; userLocEquipList.clear(); } if (rs!=null && count > 0 && params.get("isAssignBtnClicked") == null && params.get("isRemoveBtnClicked") == null) { userLocMsg = ""; isAssignToTechBtn = false; userLocEquipList.clear(); while (rs.next()) { if (rs.getString("AssignedDate") != null) { month = rs.getString("AssignedDate").toString().substring(5, 7); day = rs.getString("AssignedDate").toString().substring(8, 10); year = rs.getString("AssignedDate").toString().substring(0, 4); localAssignedDate = month + "/"+day+"/"+year; } tbHardwareObject = new TbHardwareObject(); tbHardwareObject.setHardwareId(rs.getString("HardwareId")); tbHardwareObject.setHistoricalStatus(rs.getString("HardwareStatus")); tbHardwareObject.setLocationNo(rs.getString("LocationNo")); tbHardwareObject.setModifiedByUser(""); tbHardwareObject.setEnteredByUser(""); tbHardwareObject.setBarCode(rs.getString("BarCode")); tbHardwareObject.setHistoricalOwnerTypeCode(rs.getString("OwnerTypeCode")); tbHardwareObject.setHistoricalOwnerTypeDesc(rs.getString("OwnerGroup")); tbHardwareObject.setManufacturer(rs.getString("Vendor")); tbHardwareObject.setType(rs.getString("HardwareType") ); tbHardwareObject.setModel(rs.getString("Model")); tbHardwareObject.setUnitPrice(""); tbHardwareObject.setAssignedBy(rs.getString("AssignedBy")); tbHardwareObject.setAssignedDate(localAssignedDate); tbHardwareObject.setAssignedToTech(""); userLocEquipList.add(tbHardwareObject); } } } } catch (SQLException se) { log.info("We got an exception in *** getHardwareEquipmentForUserLocation()*** while executing a Prepare Statement for SQL Query:" + "- Possibly bad SQL, or check the connection."); se.printStackTrace(); System.exit(1); } finally { cleanUp(); } return userLocEquipList; } public void techHardwareAssignmentsToUser() { log.info("Inside techHardwareAssignmentsToUser() ***** "); params = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap(); if (params.get("isAssignBtnClicked") != null && "Y".equalsIgnoreCase((String)params.get("isAssignBtnClicked"))) { int counter = 0; TbHardwareObject assignToUserTbHardwareObject=null; String assignParamCode = (String)params.get("paramAssignBarCode"); log.info("Barcode to be removed = "+assignParamCode); if (hardwareTableList!=null) { for (int i=0;i<hardwareTableList.size();i++) { if (assignParamCode.trim().equalsIgnoreCase(hardwareTableList.get(i).getBarCode().trim())) { assignToUserTbHardwareObject = new TbHardwareObject(); assignToUserTbHardwareObject.setHardwareId(hardwareTableList.get(i).getHardwareId()); assignToUserTbHardwareObject.setHistoricalStatus(SHIMSConstants.NEWLY_ASSIGNED_BY_TECH); assignToUserTbHardwareObject.setLocationNo(hardwareTableList.get(i).getLocationNo()); assignToUserTbHardwareObject.setBarCode(hardwareTableList.get(i).getBarCode()); assignToUserTbHardwareObject.setHistoricalOwnerTypeCode(hardwareTableList.get(i).getHistoricalOwnerTypeCode()); assignToUserTbHardwareObject.setHistoricalOwnerTypeDesc(hardwareTableList.get(i).getHistoricalOwnerTypeDesc()); assignToUserTbHardwareObject.setManufacturer(hardwareTableList.get(i).getManufacturer()); assignToUserTbHardwareObject.setType(hardwareTableList.get(i).getType()); assignToUserTbHardwareObject.setModel(hardwareTableList.get(i).getModel()); assignToUserTbHardwareObject.setAssignedBy(hardwareTableList.get(i).getAssignedBy()); assignToUserTbHardwareObject.setAssignedDate(hardwareTableList.get(i).getAssignedDate()); userLocEquipList.add(assignToUserTbHardwareObject); newlyAssignedByTechEquipList.add(assignToUserTbHardwareObject); hardwareTableList.remove(i); } } } if (hardwareTableList.size() == 0) { techLocMsg = SHIMSConstants.TECH_MSG; } if (userLocEquipList.size() > 0) { userLocMsg = ""; } for (int i=0;i<userLocEquipList.size();i++) { if (SHIMSConstants.NEWLY_ASSIGNED_BY_TECH.equalsIgnoreCase(userLocEquipList.get(i).getHistoricalStatus().trim())) { counter = counter + 1; } } if (counter > 0) { isSubmitByTechBtn = false; } } } public void techHardwareRemovalFromUser() { try { params = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap(); if (params.get("isRemoveBtnClicked") != null && "Y".equalsIgnoreCase((String)params.get("isRemoveBtnClicked"))) { String paramRemoveBarCode = (String)params.get("paramRemoveBarCode"); TbHardwareObject assignToTechTbHardwareObject = null; if (userLocEquipList != null) { for (int i=0;i<userLocEquipList.size();i++) { if (paramRemoveBarCode.trim().equalsIgnoreCase(userLocEquipList.get(i).getBarCode().toString().trim())) { assignToTechTbHardwareObject = new TbHardwareObject(); assignToTechTbHardwareObject.setHardwareId(userLocEquipList.get(i).getHardwareId()); assignToTechTbHardwareObject.setHistoricalStatus(SHIMSConstants.REMOVED_FROM_USER_BY_TECH); assignToTechTbHardwareObject.setLocationNo(userLocEquipList.get(i).getLocationNo()); assignToTechTbHardwareObject.setBarCode(userLocEquipList.get(i).getBarCode()); assignToTechTbHardwareObject.setHistoricalOwnerTypeCode(userLocEquipList.get(i).getHistoricalOwnerTypeCode()); assignToTechTbHardwareObject.setHistoricalOwnerTypeDesc(userLocEquipList.get(i).getHistoricalOwnerTypeDesc()); assignToTechTbHardwareObject.setManufacturer(userLocEquipList.get(i).getManufacturer()); assignToTechTbHardwareObject.setType(userLocEquipList.get(i).getType()); assignToTechTbHardwareObject.setModel(userLocEquipList.get(i).getModel()); assignToTechTbHardwareObject.setAssignedBy(userLocEquipList.get(i).getAssignedBy()); assignToTechTbHardwareObject.setAssignedDate(userLocEquipList.get(i).getAssignedDate()); hardwareTableList.add(assignToTechTbHardwareObject); removedFromUserEquipList.add(assignToTechTbHardwareObject); userLocEquipList.remove(i); } } if (userLocEquipList.size() == 0) { userLocMsg = SHIMSConstants.USER_MSG; } if (hardwareTableList.size() > 0) { techLocMsg = ""; } isSubmitByTechBtn = false; } } } catch(Exception e) { log.error("error occurred in techHardwareRemovalFromUser(): ", e); } } //@End(beforeRedirect=true) //@End public void submitNewlyAssignedEquipment() { try { params = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap(); if (params.get("isSubmitBtnClicked") != null && "Y".equalsIgnoreCase((String)params.get("isSubmitBtnClicked"))) { if (newlyAssignedByTechEquipList != null && newlyAssignedByTechEquipList.size()>0) { populateHardwareHistory(newlyAssignedByTechEquipList, SHIMSConstants.ASSIGN_TO_TECH_STATUS_ID, getLoggedInUserLocationNo() ); populateHardware(newlyAssignedByTechEquipList, SHIMSConstants.DEPLOYED_TO_USER_STATUS_ID, getLocNo()); //AS 041608 - clear the list so there are no "extra" update statements in the sqlStringBuffer for subsequent submissions newlyAssignedByTechEquipList.clear(); } if (removedFromUserEquipList != null && removedFromUserEquipList.size()>0) { populateHardwareHistory(removedFromUserEquipList, SHIMSConstants.DEPLOYED_TO_USER_STATUS_ID, getLocNo()); populateHardware(removedFromUserEquipList, SHIMSConstants.ASSIGN_TO_TECH_STATUS_ID, getLoggedInUserLocationNo()); //AS 041608 - clear the list so there are no "extra" update statements in the sqlStringBuffer for subsequent submissions removedFromUserEquipList.clear(); } } facesMessages.add("Your request processed successfully ...."); } catch (Exception se) { log.info("We got an exception in *** submitNewlyAssignedEquipment()*** while executing a Statement for UPDATE Query:" + "- Possibly bad SQL, or check the connection."); se.printStackTrace(); System.exit(1); } finally { cleanUp(); } } private void populateHardwareHistory(List<TbHardwareObject> populateList, String status, String location) { sqlStringBuffer = new StringBuffer(); try { con = DriverManager.getConnection(SHIMSConstants.URL, SHIMSConstants.USERNAME, SHIMSConstants.PASSWORD); if (populateList != null && populateList.size() > 0) { for (int i=0;i<populateList.size();i++) { sqlStringBuffer.append(" INSERT INTO TBHARDWAREHISTORY VALUES ( "); sqlStringBuffer.append(Integer.valueOf(populateList.get(i).getHardwareId())).append(","); sqlStringBuffer.append("'").append(status).append("',"); sqlStringBuffer.append("'").append(location).append("',"); sqlStringBuffer.append("'").append(DateFormat.getInstance().format(now)).append("',"); sqlStringBuffer.append(null+","); sqlStringBuffer.append("'").append(populateList.get(i).getAssignedBy()).append("',"); sqlStringBuffer.append("'").append(populateList.get(i).getHistoricalOwnerTypeCode()).append("') "); } } log.info("populateHardwareHistory(): sqlStringBuffer = " + sqlStringBuffer); preparedStmt = con.prepareStatement(sqlStringBuffer.toString()); preparedStmt.executeUpdate(); } catch (SQLException se) { log.info("We got an exception in *** populateHardwareHistory()*** while executing a Statement for UPDATE Query:" + "- Possibly bad SQL, or check the connection."); se.printStackTrace(); System.exit(1); } finally { cleanUp(); } } private void populateHardware(List<TbHardwareObject> populateList, String status, String location) { sqlStringBuffer = new StringBuffer(); try { con = DriverManager.getConnection(SHIMSConstants.URL, SHIMSConstants.USERNAME, SHIMSConstants.PASSWORD); if (populateList != null && populateList.size() > 0) { for (int i=0;i<populateList.size();i++) { sqlStringBuffer.append(" UPDATE "); sqlStringBuffer.append(" TBHARDWARE "); sqlStringBuffer.append(" SET CURRENTLOCATIONNO = ").append("'"+location+"' ,");; sqlStringBuffer.append(" CURRENTSTATUS = ").append("'"+status+"' ,"); sqlStringBuffer.append(" FIRSTENTEREDDATE = ").append("'").append(DateFormat.getInstance().format(now)).append("',"); sqlStringBuffer.append(" ENTEREDBYUSER = ").append("'").append(populateList.get(i).getAssignedBy()).append("'"); sqlStringBuffer.append("WHERE COXBARCODE = ").append("'").append(populateList.get(i).getBarCode()).append("'"); } } log.info("populateHardware(): sqlStringBuffer = " + sqlStringBuffer); preparedStmt = con.prepareStatement(sqlStringBuffer.toString()); preparedStmt.executeUpdate(); } catch (SQLException se) { log.info("We got an exception in *** populateHardware()*** while executing a Statement for UPDATE Query:" + "- Possibly bad SQL, or check the connection."); se.printStackTrace(); System.exit(1); } finally { cleanUp(); } } private String getLoggedInUserLocationNo() { String loggedInUserLocationNo = ""; try { if (getNetworkLogin() != null) { sqlStringBuffer = new StringBuffer(); try { con = DriverManager.getConnection(SHIMSConstants.URL, SHIMSConstants.USERNAME, SHIMSConstants.PASSWORD); sqlStringBuffer.append("SELECT LOCATIONNO FROM TBLOCATION WHERE NETWORKLOGIN = ").append("'"+getNetworkLogin()+"'"); Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery(sqlStringBuffer.toString()); rs.last(); int count = rs.getRow(); rs.beforeFirst(); if (count >0 && rs!= null) { while (rs.next()) { loggedInUserLocationNo = rs.getString("LOCATIONNO"); } } } catch (SQLException se) { log.info("We got an exception in *** populateHardware()*** while executing a Statement for UPDATE Query:" + "- Possibly bad SQL, or check the connection."); se.printStackTrace(); System.exit(1); } finally { cleanUp(); } } } catch(Exception e) { e.printStackTrace();} return loggedInUserLocationNo; } private void cleanUp() { try { if (rs != null) { rs.close(); } if (preparedStmt != null) { preparedStmt.close(); } if (stmt != null) { stmt.close(); } if (con != null) { con.close(); } } catch(SQLException e) { e.printStackTrace(); } } //@End(beforeRedirect=true) //@End public String cancel() { log.info("in cancel()"); return "cancelled"; } @PostConstruct private void postConstruct() { log.info("inside postConstruct"); } @PostActivate private void postActivate() { log.info("inside postActivate"); } @PrePassivate private void cleanupBeforePassivation() { log.info("inside cleanupBeforePassivation()"); /*cleanUp(); //con = null; <-- this does not cause the passivation failure stmt = null; preparedStmt = null; rs = null; */ //sessionContext = null; <-- this does not cause the passivation failure // replaced Map params instance variable with local variables instead... params = null; //<-- root cause identified! at least this variable not being set to null causes the // javax.ejb.EJBException: Could not passivate; failed to save state error... } @Destroy @Remove public void destroy() { log.info("Inside destroy ============>"); } public String getLocNo() { return locNo; } public void setLocNo(String locNo) { this.locNo = locNo; } public List<TbHardwareObject> getUserLocEquipList() { return userLocEquipList; } public void setUserLocEquipList(List<TbHardwareObject> userLocEquipList) { this.userLocEquipList = userLocEquipList; } public String getUserLocMsg() { return userLocMsg; } public void setUserLocMsg(String userLocMsg) { this.userLocMsg = userLocMsg; } public String getTechLocMsg() { return techLocMsg; } public void setTechLocMsg(String techLocMsg) { this.techLocMsg = techLocMsg; } public String getCompId() { return compId; } public void setCompId(String compId) { this.compId = compId; } public String getCompMsg() { return compMsg; } public void setCompMsg(String compMsg) { this.compMsg = compMsg; } public String getMsgConfirm() { return msgConfirm; } public void setMsgConfirm(String msgConfirm) { this.msgConfirm = msgConfirm; } public boolean isAssignToTechBtn() { return isAssignToTechBtn; } public void setAssignToTechBtn(boolean isAssignToTechBtn) { this.isAssignToTechBtn = isAssignToTechBtn; } public boolean isSubmitByTechBtn() { return isSubmitByTechBtn; } public void setSubmitByTechBtn(boolean isSubmitByTechBtn) { this.isSubmitByTechBtn = isSubmitByTechBtn; } public String getUserLocationName() { return userLocationName; } public void setUserLocationName(String userLocationName) { this.userLocationName = userLocationName; } public List<TbHardwareObject> getRemovedFromUserEquipList() { return removedFromUserEquipList; } public void setRemovedFromUserEquipList( List<TbHardwareObject> removedFromUserEquipList) { this.removedFromUserEquipList = removedFromUserEquipList; } public String getNetworkLogin() { return networkLogin; } public void setNetworkLogin(String networkLogin) { this.networkLogin = networkLogin; } public String getTechLocationName() { if (user != null) { networkLogin = user.getNetworkLogin(); log.info("Here...1"); } else { networkLogin = (((NtlmPasswordAuthentication) sessionContext.get("NtlmHttpAuth")).getDomain() +"\\"+((NtlmPasswordAuthentication) sessionContext.get("NtlmHttpAuth")).getUsername()); } techLocationName = getFirstNameLastName(getLoggedInUserLocationNo(), SHIMSConstants.TECH_MSG_2); return techLocationName; } public void setTechLocationName(String techLocationName) { this.techLocationName = techLocationName; } }
-
10. Re: JDBC with SEAM
tom_goring Apr 18, 2008 11:17 PM (in response to ashwin)Without looking too closely
con
is a instance variable of that class. So it will be serialised if required.... Thus you get your problem.I think you need to consider refactoring the class into serveral. One that fetches the data, one that binds to you jsf page, etc, etc.
Hope this helps
-