Wednesday, March 25, 2015

Solved: JPA persist cannot save anything to database and no exception

I am using Spring JPA to insert data into database. In the persistence.xml, I use JTA transaction type, instead of RESOURCE_LOCAL. This means we use JTA and container managed transactions. 

 <persistence-unit name="name-unit" transaction-type="JTA">  

During the development time, I had some difficulties to use persist to save data into database. Here are a few things I would like to share to  pay attention when we work on this.

1. Setup persistence.xml. The following is what I used. The highlighted ones were new added when the code didn't work.

 <?xml version="1.0" encoding="UTF-8" standalone="no"?>  
 <persistence xmlns="http://java.sun.com/xml/ns/persistence"  
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0"  
      xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">  
      <persistence-unit name="name-unit"           transaction-type="JTA">  
           <provider>org.hibernate.ejb.HibernatePersistence</provider>  
           <jta-data-source>java:/xxx/datasources/dsname</jta-data-source>  
           <class>xxx.model.Class</class>  
                 <exclude-unlisted-classes>false</exclude-unlisted-classes>  
           <properties>  
                <property name="connection.driver_class" value="com.ibm.db2.jcc.DB2Driver" />  
                <property name="hibernate.dialect" value="org.hibernate.dialect.DB2Dialect" />  
                <property name="hibernate.show_sql" value="true" />  
                <property name="hibernate.format_sql" value="true" />  
                <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy" />  
                <property name="hibernate.connection.charSet" value="UTF-8" />  
                <property name="hibernate.max_fetch_depth" value="5" />  
                <property name="hibernate.connection.clientProgramName" value="supsearch" />  
                <property name="hibernate.cache.use_second_level_cache" value="false" />  
                <property name="hibernate.cache.use_query_cache" value="false" />  
                <property name="hibernate.hbm2ddl.import_files_sql_extractor" value="org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor" />  
                <property name="hibernate.validator.apply_to_ddl" value="false" />  
                <property name="hibernate.validator.autoregister_listeners" value="false" />                      
                <property name="jboss.entity.manager.factory.jndi.name" value="java:/name-emf" />  
                <property name="hibernate.transaction.jta.platform" value="org.hibernate.service.jta.platform.internal.JBossAppServerJtaPlatform" />  
                <property name="hibernate.generate_statistics" value="true" />  
       </properties>  
      </persistence-unit>  
 </persistence>  


2. Setup Spring JTA Transaction Manager and other JTA information
Spring will automatically discover the underlying JTA implementation.
We need to add JTA Transaction Manager into applicationContext.xml, in my case it is called web-persistence.xml.


 <?xml version="1.0" encoding="UTF-8" standalone="no"?>  
 <beans xmlns="http://www.springframework.org/schema/beans"   
      xmlns:context="http://www.springframework.org/schema/context"   
      xmlns:jee="http://www.springframework.org/schema/jee"   
      xmlns:tx="http://www.springframework.org/schema/tx"  
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
      xsi:schemaLocation="http://www.springframework.org/schema/beans   
           http://www.springframework.org/schema/beans/spring-beans.xsd       
           http://www.springframework.org/schema/context   
           http://www.springframework.org/schema/context/spring-context.xsd       
           http://www.springframework.org/schema/jee   
           http://www.springframework.org/schema/jee/spring-jee.xsd       
           http://www.springframework.org/schema/tx   
           http://www.springframework.org/schema/tx/spring-tx.xsd">  
      <!-- Note: imported by SecurityConfig.java -->  
  <context:mbean-export/>  
      <jee:jndi-lookup id="entityManagerFactory" jndi-name="persistence/name-unit" expected-type="javax.persistence.EntityManagerFactory" />  
      <bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor">  
           <property name="persistenceUnits">  
                <map>          
                     <entry key="name-unit" value="persistence/name-unit" />  
                </map>  
           </property>  
           <property name="persistenceContexts">  
                <map>  
                     <entry key="name-unit" value="persistence/name-context"/>  
                </map>  
           </property>  
      </bean>  
      <bean class="org.springframework.orm.jpa.JpaTransactionManager" id="transactionManager">  
           <property name="entityManagerFactory" ref="entityManagerFactory" />  
      </bean>  
      <tx:annotation-driven/>       
      <tx:jta-transaction-manager/>  
 </beans>  

3. Injection via @PersistenceContext

The EntityManager itself is created by the container using the information in the persistence.xml, so to use it at runtime, we simply need to request it be injected into one of our components. We do this via @PersistenceContext.

@PersistenceContext should put into DAO impl. In my case it is called repository impl.

 @Repository  
 public class XxxxClassRepositoryImpl implements  
           XxxxClassRepository, Serializable {  
      private static final long serialVersionUID = 1L;  
      @PersistenceContext   
      private EntityManager emgr;  


4. Add @Transactional Annotation 

In the DAO impl layer and service impl layer (if you have), we need to add @Transcational annotation in. This annotation can be added in the class level and method level.

  @Repository   
  @Transactional  
  public class XxxxClassRepositoryImpl implements   
       XxxxClassRepository, Serializable {   
    private static final long serialVersionUID = 1L;   
    @PersistenceContext    
    private EntityManager emgr;   


If you want to learn how Spring transactional really work, I found this blog is very helpful.
http://blog.jhades.org/how-does-spring-transactional-really-work/



Thursday, March 19, 2015

How to generate JPA entity from table by using Eclipse?

We've created tables in our database. We are going to use Eclipse to generate JPA entities from those existing tables.

1. Create or convert to a JPA project.
In Eclipse, if this is a new project, make it as a JPA project. If a project has been created and it is not a JPA project, we need to convert it into a JPA project.
To convert a current project to JPA project, you can just right click the project name, and choose Configure, then choose Convert to JPA Project.




Then click finish button.

2. Create a database connection under Data Source Explorer 

First click on the Window menu on top. Choose Show View, then click other.


Choose Data Source Explorer. Then click OK.


Now Data Source Explorer will show on the lower tab. Choose it, and right click on the Database Connections, you can create a new db connection here.







After a new connection is created, when we click on the name, the database will be connected. We can see tables here.

3. Generate JPA entities.

Right click on the project name, choose new, then Other, if JPA Entities from Tables not show.



Choose a connection and table names you would like to generate JPA entities.




Write down your package name.


Write down your class name.Then Finish.








































4. Check your generated entity code to make sure if you need to modify anything.




How to read image from BLOB column by using JPA and Spring MVC?

I need to read image from blob column and put it into a JSP to display in a web page.
I am using JPA and Spring MVC.
This is my sample FILESTORE table. The data type for column FILE_OBJ_L  is BLOB.



1. The JPA Entity is as below.

 @Entity  
 @Table(name="FILE_STORE")  
 @NamedQuery(name="FileStore.findAll", query="SELECT f FROM FileStore f")  
 public class FileStore implements Serializable {  
      private static final long serialVersionUID = 1L;  
      @Id  
      @Column(name="FILE_ID")  
      private Long fileId;  
      @Column(name="FILE_NAME")  
      private String fileName;  
      @Lob  
      @Column(name="FILE_OBJ_L",length = 31457280)  
   @Basic(fetch = FetchType.LAZY)  
      private byte[] fileObjL;  
      private String mimetype;  
      public FileStore() {  
      }  
      public Long getFileId() {  
           return this.fileId;  
      }  
      public void setFileId(Long fileId) {  
           this.fileId = fileId;  
      }  
      public String getFileName() {  
           return this.fileName;  
      }  
      public void setFileName(String fileName) {  
           this.fileName = fileName;  
      }  
      public byte[] getFileObjL() {  
           return this.fileObjL;  
      }  
      public void setFileObjL( byte[] fileObjL) {  
           this.fileObjL = fileObjL;  
      }  
 :  
 :  

2. DAO access layer (repository).

 @Override  
      public FileStore findByFileId(Long fileId) {  
           EntityManager emgr = emf.createEntityManager();  
           try {  
                Query query = emgr  
                          .createQuery("SELECT f FROM FileStore f WHERE f.fileId =:fileId");  
                query.setParameter("fileId", fileId);  
                FileStore filestore = (FileStore) query.getSingleResult();               // test and return  
                return filestore;  
           } catch (Exception ex) {  
                log.warn(  
                          "Error during jpa query for filestore by file id: {} {}",  
                          fileId, ex.getMessage() + " | " + ex.getCause());  
           } finally {  
                if (emgr != null) {  
                     emgr.close();  
                }  
           }  
           return null;  
      }  

3. I also have a service layer to call dao (repository).

 import org.springframework.beans.factory.annotation.Autowired;  
 import org.springframework.dao.DataAccessException;  
 import model.FileStore;  
 import repository.FileStoreRepository;  
 import org.springframework.stereotype.Service;  
 import org.springframework.transaction.annotation.Transactional;  
 @Service  
 public class FileStoreServiceImpl implements FileStoreService {  
   @Autowired  
      private FileStoreRepository filestoreRepository;  
   @Autowired  
   public FileStoreServiceImpl(FileStoreRepository filestoreRepository) {  
     this.filestoreRepository = filestoreRepository;  
   }    
   // FileStore requests by fileid  
      @Override  
      @Transactional(readOnly = true)  
      public FileStore findByFileId(Long fileid) throws DataAccessException {  
           return filestoreRepository.findByFileId(fileid);  
      }  
 }  

4. Spring MVC controller class.

 import javax.servlet.http.HttpServletResponse;  
 import org.springframework.beans.factory.annotation.Autowired;  
 import org.springframework.stereotype.Controller;  
 import org.springframework.web.bind.annotation.PathVariable;  
 import org.springframework.web.bind.annotation.RequestMapping;  
 import model.FileStore;  
 import service.FileStoreService;  
 @Controller  
 public class FileController {  
      @Autowired  
      private FileStoreService fileStoreService;  
      @RequestMapping("/download/{fileId}")  
      public String download(@PathVariable("fileId") Long fileId,  
                HttpServletResponse response) {  
           try {  
                if (fileId == null) {  
                     response.sendError(HttpServletResponse.SC_NOT_FOUND); // 404.  
                } else {  
                     // Lookup file by fileId in database.  
                     FileStore file = fileStoreService.findByFileId(fileId);  
                     // Check if file is actually retrieved from database.  
                     if (file != null) {  
                          if (file.getFileObjL() != null) {  
                               response.setContentType(file.getMimetype());  
                               response.setHeader("Content-Length",  
                                         String.valueOf(file.getFileObjL().length));  
                               // Write file content to response.  
                               response.getOutputStream().write(file.getFileObjL());  
                          }  
                     }  
                }  
           } catch (IOException e) {  
                e.printStackTrace();  
           }  
           return null;  
      }  
 }  

5. JSP page.

 <img src="${pageContext.request.contextPath}/download/7">  

Here, fieId=7

Or just simply test it through URL:http: //localhost:8080/supsearch/download/7

6. Note:

- Remember, HTTP does not allow to get image blob directly from the object. Each image needs to be a separate request to a separate URL. So you will need a controller to write the blob to the response stream.


- When I worked on this, I also got an error when it called the JPA DAO. The error is
SQL Error: -134, SQLState: 42907 on blob column.
This means the string is too long.
At that time I used the query as below.


 Query query = emgr.createQuery("SELECT distinct f FROM FileStore f WHERE f.fileId =:fileId");  

Then I changed the query, and removed "distinct" since the fileId is unique one. Then everything works fine.

 Query query = emgr.createQuery("SELECT f FROM FileStore f WHERE f.fileId =:fileId");  

 IBM wrote:  
 SQL0134N Improper use of a string column, host variable, constant, or function name.  
 Explanation: The use of the string name is not permitted.  
 An expression resulting in a string data type with a maximum length greater than 255 bytes is not permitted in:  
 * A SELECT DISTINCT statement  
 * A GROUP BY clause  
 * An ORDER BY clause  
 * A column function with DISTINCT  
 * A SELECT or VALUES statement of a set operator other than UNION ALL.  
 An expression resulting in a LONG VARCHAR or LONG VARGRAPHIC data type is not permitted in:  
 * A predicate other than EXISTS or NULL  
 * A column function  
 * The SELECT clause of a subquery of a predicate other than EXISTS or NULL  
 * The SELECT clause of a subselect in an INSERT statement  
 * The value expression of a SET clause in an UPDATE statement unless the expression is a LONG VARCHAR or LONG VARGRAPHIC host variable  
 * A SELECT statement of a set operator (except UNION ALL)  
 * VARGRAPHIC scalar function.  
 Federated system users: in a pass-through session, a data source-specific restriction can cause this error. See the SQL Reference documentation for the failing data sources.  
 The statement cannot be processed.  
 User Response: The requested operation on the string is not supported.  
 Note:  
 If it is unclear as to how the 255 byte limit is being exceeded, consider that codepage conversion operations may be required to evaluate the string expression. Depending on the source and target codepages, the target may have a greater length attribute than the source. For more information, refer to the SQL Reference for discussions on string restrictions and string conversions.  
 sqlcode:-134  
 sqlstate: 42907  


Tuesday, March 17, 2015

JPA error: "Null value was assigned to a property of primitive type setter of MyDomain.myAttribute"

I have a database table, one of the columns is REF_ID(INTEGER), and this column can be set as null. When I used Eclipse JPA tool to generate JPA Entities from tables, this attribute was generated as this.

 @Column(name="REF_ID")  
      private int refId;  

When I tested it, and set null value to this attribute, I got the error "Null value was assigned to a property of primitive type setter of MyDomain.myAttribute".

To solve the problem, I just simply changed the int data type to Integer. Then everything works perfectly fine.

A null value cannot be assigned to a primitive type, like int, long, boolean, etc. If the database column that corresponds to the field in your object can be null, then your field should be a wrapper class, like Integer, Long, Boolean, etc.

Monday, March 16, 2015

How to create a drag and drop sortable table with add/remove a row function by using jQuery?

This is an example to create a table which could be sorted by drag and drop, also the row can be added and removed. You can see the table example here.

In this example, Sortable table part, I used jQuery sortable widget.
The html code as below.
 <table id="classtable" class="table table-striped">  
                <thead>  
                     <tr>  
                          <th>Class Internal Name</th>  
                          <th>Display Name</th>  
                          <th>Enabled</th>  
                          <th>Search Weight</th>  
                          <th>Delete class</th>  
                     </tr>  
                </thead>  
                <tbody id="sortable">  
                     <tr>  
                          <td><input type="text" id="internalname" name="internalname" placeholder="Class Internal Name" value='Agreement Supplier'></td>  
                          <td><input type="text" id="displayname" name="displayname" placeholder="Display Name" value='Agreement Supplier'></td>  
                          <td><input type="checkbox" name="enabled" value='1'></td>  
                          <td><input type="text" id="weight1" name="weight1" value='1'></td>                 
                          <td><button type="button" class="removebutton" title="Remove this class"><span class="glyphicon glyphicon-remove "></span></button> </td>  
                     </tr>  
                     <tr>  
                          <td><input type="text" id="internalname" name="internalname" placeholder="Class Internal Name" value='California Supplier'></td>  
                          <td><input type="text" id="displayname" name="displayname" placeholder="Display Name" value='California Supplier'></td>  
                          <td><input type="checkbox" name="enabled" value='1'></td>  
                          <td><input type="text" id="weight1" name="weight1" value='1'></td>  
                          <td><button type="button" class="removebutton" title="Remove this class"><span class="glyphicon glyphicon-remove "></span></button> </td>  
                     </tr>  
                     <tr>  
                          <td><input type="text" id="internalname" name="internalname" placeholder="Class Internal Name" value='Small Business'></td>  
                          <td><input type="text" id="displayname" name="displayname" placeholder="Display Name" value='Small Business'></td>  
                          <td><input type="checkbox" name="enabled" value='1'></td>  
                          <td><input type="text" id="weight1" name="weight1" value='1'></td>  
                          <td><button type="button" class="removebutton" title="Remove this class"><span class="glyphicon glyphicon-remove "></span></span></button> </td>  
                     </tr>                      
                </tbody>  
 </table>  

The javascript code is as below.
 $("#sortable").sortable();  

Add a new row javascript:
 $("#addbutton").click(function () {  
             $("#classtable").each(function () {  
                         var i=0;  
                var tds = '<tr class="ui-sortable-handle" >';  
                tds += '<td><input type="text" id="internalname" name="internalname" placeholder="Class Internal Name"></td>'+  
                          '<td><input type="text" id="displayname" name="displayname" placeholder="Display Name"></td>'+  
                          '<td><input type="checkbox" name="enabled"></td>'+  
                          '<td><input type="text" id="weight1" name="weight1"></td>'+  
                          '<td><button type="button" class="removebutton" title="Remove this class"><span class="glyphicon glyphicon-remove "></span></button> </td>';  
               tds += '</tr>';  
                if ($('tbody', this).length > 0) {  
                  $('tbody', this).append(tds);  
                } else {  
                  $(this).append(tds);  
                }  
              });  
           });  

Remove a row javascript:
 $("#classtable").on("click",".removebutton",function () {  
              if (confirm("Do you want to delete?")){  
                   $(this).closest('tr').remove();  
               }  
              return false;  
            });   


Tuesday, March 10, 2015

How to upload a file into blob column in a table by using Talend?



I created a tool to manually upload any test files into blob column in a table for my development.
The tool is created by Talend open studio.

The file name is put into tFixedFlowInput component.



This is tMap setup.





This is the tDB2Output setup.




From the tMap above, you may see I have a routine, ReadfromFile. (circle in red.)
The code for this routine is as  below.



 package routines;  
 public class ReadfromFile {  
 public static byte[] ByteArrayFromFile(String filepath) {  
 try{   
 System.out.println(filepath);       
 java.io.File file=new java.io.File(filepath);  
 java.io.FileInputStream fis = new java.io.FileInputStream(file);  
 int fileLength = (int) file.length();  
 byte[] incoming_file_data = new byte[fileLength]; // allocate byte array of right size  
 fis.read(incoming_file_data, 0, fileLength ); // read into byte array  
 fis.close();  
 return incoming_file_data;  
 }catch(Exception err){  
 err.printStackTrace();  
 return null;  
 }  
 }  
 }