RE: ### Error updating database. Cause: org.apache.ibatis.reflection.ReflectionException - iBatis

[Poitras Christian]

I’m not sure of what your trying to do, but many things seem wrong with your query.

 

First, using SELECT #{xyz} seems wrong since you probably want the content of your Employee/Address tables as values to insert.

Second, it’s impossible to resolve the property of a list using the dot notation. So employee.empNumber is impossible since employee is a list and lists don’t have a empNumber property. You may wish to use <foreach> tag as defined here: http://www.mybatis.org/core/dynamic-sql.html

 

Look at this example:

http://www.mybatis.org/core/sqlmap-xml.html#insert_update_and_delete

<insert id="insertAuthor" parameterType="domain.blog.Author">

  insert into Author (id,username,password,email,bio)

  values (#{id},#{username},#{password},#{email},#{bio})

</insert>

Christian

 

De : mybati...@googlegroups.com [mailto:mybati...@googlegroups.com] De la part de PP
Envoyé : November-14-12 11:15 PM
À : mybati...@googlegroups.com
Objet : ### Error updating database. Cause: org.apache.ibatis.reflection.ReflectionException - iBatis

 

Hi All,

 

When i run insert query through unit testing,i get below exception

org.apache.ibatis.exceptions.IbatisException: 

### Error updating database.  Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'empNumber' in 'class java.util.ArrayList'

### The error may involve WorkplaceDetail.insertWorkplace-Inline

### The error occurred while setting parameters

### Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'empNumber' in 'class java.util.ArrayList'

    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)

    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:100)

Below is my configuration of workplace.xml where i have highlighted insert statement.

<insert id="insertWorkplace" parameterType ="WorkplaceDetail" useGeneratedKeys="true">

   <selectKey resultType="int" keyProperty="code" order="BEFORE">

     select last_insert_rowId() as code

    </selectKey>

    INSERT INTO Workplace (WRegNum,WorkplaceName,EmpNumber,AddressId,notes1) 

    SELECT  

        #{compRegNum},    

    #{plant},       

        #{employee.empNumber},   

    #{address.addressId},

    #{note1}        

     FROM Workplace

        inner join  Employee on (Workplace.[EmpNumber]={employee.empNumber})
        inner join Address on (Workplace.[AddressID]= #{address.addressId});
  </insert>

  <resultMap id="workplaceResult" type="WorkplaceDetail">

        <id property="code" column="WorkplaceCode" jdbcType="INTEGER"/>

        <result property="compRegNum" column="WRegNum" jdbcType="VARCHAR"/>

        <result property="plant" column="WorkplaceName" jdbcType="VARCHAR"/>  

        <result property="note5" column="Notes5" jdbcType="VARCHAR"/>

        <association property="address" column="WorkplaceCode" javaType="Address">

        <id property="addressId" column="Address_id" jdbcType="INTEGER"/>   

        </association>

        <collection property="employee" column="WorkplaceCode" javaType="ArrayList"

        ofType="Employee">

        <id property="empNumber" column="EmpNum" jdbcType="INTEGER"/>

        <result property="empName" column="EmpName" jdbcType="VARCHAR"/>

        </collection>

</resultMap>

Workplace.java (POJO)

public class WorkplaceDetail{

private int code;

private String plant;

private String compRegNum;

private List <Employee> employee = new ArrayList<Employee>();

private Address address;

}

Please help me out to resolve this issue.

[PP]

Hi Christian,

Yes you are true...i am trying to "First, using SELECT #{xyz} seems wrong since you probably want the content of your Employee/Address tables as values to insert."

I will try to do as you suggested...

[PP]

So does this mean that i have to write separate insert query for Workplace, Employee and Address and then populate in table using select joins. Please confirm. And in DAO implementation how should i process insert method.

On Thursday, November 15, 2012 10:44:49 PM UTC+9, christia...@ircm.qc.ca wrote:

[PP]

Hi all, please suggest me alternate way to handle this...issue

[Guy Rouillier]

On 11/19/2012 8:11 PM, PP wrote:
> Hi all, please suggest me alternate way to handle this...issue
>
> On Thursday, November 15, 2012 1:15:17 PM UTC+9, PP wrote:
>
> Hi All,
>
> When i run insert query through unit testing,i get below exception
>
> |org.apache.ibatis.exceptions.IbatisException:

> ### Error updating database. Cause: org.apache.ibatis.reflection.ReflectionException:*There is no getter for property named 'empNumber' in 'class java.util.ArrayList'*

> ### The error may involve WorkplaceDetail.insertWorkplace-Inline
> ### The error occurred while setting parameters
> ### Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'empNumber' in 'class java.util.ArrayList'
> at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
> at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:100)
> |
>
> Below is my configuration of workplace.xml where i have highlighted
> insert statement.
>
> |<insert id="insertWorkplace" parameterType ="WorkplaceDetail" useGeneratedKeys="true">
> <selectKey resultType="int" keyProperty="code" order="BEFORE">
> select last_insert_rowId() as code
> </selectKey>

> * INSERT INTO Workplace (WRegNum,WorkplaceName,EmpNumber,AddressId,notes1)

> SELECT
> #{compRegNum},
> #{plant},
> #{employee.empNumber},
> #{address.addressId},
> #{note1}
> FROM Workplace
> inner join Employee on (Workplace.[EmpNumber]={employee.empNumber})

The error is here - you are missing the "#" before the squiggle bracket.
However, I'm not following what you are trying to accomplish with this
select statement. Your select list is being filled with values from your
parameter, i.e., constants.


> inner join Address on (Workplace.[AddressID]= #{address.addressId});*

> </insert>
>
> <resultMap id="workplaceResult" type="WorkplaceDetail">
> <id property="code" column="WorkplaceCode" jdbcType="INTEGER"/>
> <result property="compRegNum" column="WRegNum" jdbcType="VARCHAR"/>
> <result property="plant" column="WorkplaceName" jdbcType="VARCHAR"/>
>
> <result property="note5" column="Notes5" jdbcType="VARCHAR"/>
> <association property="address" column="WorkplaceCode" javaType="Address">
> <id property="addressId" column="Address_id" jdbcType="INTEGER"/>
> </association>
> <collection property="employee" column="WorkplaceCode" javaType="ArrayList"
> ofType="Employee">
> <id property="empNumber" column="EmpNum" jdbcType="INTEGER"/>
> <result property="empName" column="EmpName" jdbcType="VARCHAR"/>
> </collection>
> </resultMap>
> |
>
> |
> |
>
> Workplace.java (POJO)
>
> |public class WorkplaceDetail{
> private int code;
> private String plant;
> private String compRegNum;
> private List <Employee> employee = new ArrayList<Employee>();
> private Address address;
> }
> |
>
> Please help me out to resolve this issue.
>

--
Guy Rouillier

[PP]

Basically i want to the content of your Employee/Address tables as values to insert of Workplace table. My actual scenario is...i have one table at GUI which gets populated from different DB tables and also insert . I don have issue with select...but insert operation i am facing issue..it is like 1+N insert.

[Guy Rouillier]

On 11/20/2012 10:23 PM, PP wrote:
> Basically i want to the content of your Employee/Address tables as
> values to insert of Workplace table. My actual scenario is...i have
> one table at GUI which gets populated from different DB tables and also
> insert . I don have issue with select...but insert operation i am facing
> issue..it is like 1+N insert.

Why can't you do this with a insert-select statement? I see that is
what you have in your original post, but you have all MyBatis parameter
markers in your SELECT statement, e.g., #{compRegNum}. Because you have
those, you are selecting all constant values. If you want to do that,
you don't need the SELECT at all; just put the MyBatis parameter markers
into the values clause for the insert.

But from your description, what you really want to do is a simple insert
select, where the items in the select list are columns from the source
table.

Sorry, this is all I can say unless you can clarify your scenario
better. If you have a bunch of rows in a GUI which need to get inserted
into a table, then iterate over those rows and invoke a simple insert
statement each iteration, inserting a single row; using MyBatis
parameter markers for the values clause in that insert statement.

--
Guy Rouillier

[Poitras Christian]

Hi,

Maybe what you wish to do looks like this.

<insert id="insertWorkspace" parameterType="WorkspaceDetail">
INSERT INTO Workspace
SELECT comRegNum, plant, empNumber, addressId, note1
FROM Employee
JOIN Address ON Employee.id = Address.empId
WHERE Employee.id IN (
<forEach item="emp" collection="employee" separator=",">
#{emp.id}
</forEach>
)
</insert>

But, doing such a select will make it impossible for MyBatis to set the id in the WorspaceDetail since more than one row is inserted.
If you need the id supplied by the database, then you have to insert each row one by one. The iteration will then be in your service class and the insert will be trivial like this one.

<insert id="insertAuthor" parameterType="domain.blog.Author">
insert into Author (id,username,password,email,bio)
values (#{id},#{username},#{password},#{email},#{bio})
</insert>

Christian

-----Message d'origine-----
De : mybati...@googlegroups.com [mailto:mybati...@googlegroups.com] De la part de Guy Rouillier
Envoyé : November-21-12 12:38 AM
À : mybati...@googlegroups.com
Objet : Re: ### Error updating database. Cause: org.apache.ibatis.reflection.ReflectionException - iBatis

[PP]

Thanks Christian. So i have to do something like

<insert id="insertAddress " parameterType="domain.blog.Author"> 

  insert into Workplace(id,username,password,email,bio) 

  values (#{id},#{username},#{password},#{email},#{bio}) 
</insert> 

<insert id="insertAddress " parameterType="domain.blog.Author"> 

  insert into Employee(id,username,password,email,bio) 

  values (#{id},#{username},#{password},#{email},#{bio}) 
</insert> 

<insert id="insertAddress " parameterType="domain.blog.Author"> 

  insert into Address (id,username,password,email,bio) 

  values (#{id},#{username},#{password},#{email},#{bio}) 
</insert> 

Then in DAO i have to manage these insert something like below right

public void insert()

session.insert("WorkplaceDetail.insertWorkplace", workplaceDetail);

for(Employee e: workplace.getEmployee()){

session.insert("Employee..insertEmployee", emp);

}

Address a= workplace.getAddress()

session.insert("Address..insertAddress", a);
}

[PP]

I have 3 different tables Workplace,Address,Employee

In Workplace table, Address_Id and EmployeeId are foreign keys. 

In java side i have  POJO

public class Workplace{

private string companyName;

private List<Employees> employees;

private Address address

}

Now in GUI, i have a table where values come from these DB tables. for example,

CompanyName ,  EmployeeName,     Address
Oracle,                  Guy                    c-102, New jersy

These vales are displayed using multi joins in Select query. this is simple. 

select companyName, employee.empName, address.address from workplace

 inner join on Employee where workplace.empId=employee.employeeId

 inner join on address where workplace.addressId=Address.addressId;

But for inserting I am facing issue while using ibatis. 

insert into workplace (cmpanyName,EmployeeId, addressId) values (#{companyName},#{Employee.EmployeeId},#{Address.addressId});

I guess now you have understood my scenerio. :)

[Guy Rouillier]

On 11/22/2012 8:48 PM, PP wrote:
> I have 3 different tables*Workplace,Address,Employee*
> In Workplace table, *Address_Id *and *EmployeeId *are *foreign *keys.

>
> In java side i have POJO
>
> public class Workplace{
> private string companyName;
> private List<Employees> employees;
> private Address address
> }
>
>
> Now in GUI, i have a table where values come from these DB tables. for
> example,
>

> *CompanyName , EmployeeName, Address
> **Oracle, Guy c-102, New jersy*

>
>
> These vales are displayed using multi joins in Select query. this is
> simple.

> *select companyName, employee.empName, address.address from workplace*
> * inner join on Employee where workplace.empId=employee.employeeId*
> * inner join on address where workplace.addressId=Address.addressId;*

>
> But for inserting I am facing issue while using ibatis.

> *insert into workplace (cmpanyName,EmployeeId, addressId) values
> (#{companyName},#{Employee.EmployeeId},#{Address.addressId});*
> *
> *

> I guess now you have understood my scenerio. :)

Yes, I think I do. Your GUI contains a table or grid; in that grid, you
display data from a database, and also allow the end-user to enter data
into new rows. If the user enters new rows, you are asking how to
persist those new rows into the database using MyBatis. Correct?

If so, then in your application back end, iterate over the new table
rows. For each new row, perform a simple insert as shown on pages 24-25
of the User's Guide.

--
Guy Rouillier

[PP]

you are right Guy... I will try to get this.. if you have any example please help me out. :)

Thanks a lot.

[Guy Rouillier]

I think any examples I provide would just confuse the issue. Your
solution is okay:

<insert id="insertWorkplace" parameterType="map">

insert into workplace (cmpanyName,EmployeeId, addressId)
values
(
#{companyName},

#{employee.EmployeeId},
#{address.addressId}
)
</insert>

For this to work, the method in your mapper.java would need to look
something like this:

int insertWorkplace
(
@Param("companyName") String companyName,
@Param("employee") Employee employee,
@Param("address") Address address
);

--
Guy Rouillier

[PP]

Thanks Guy... :)

But here Employee is collection

 <collection property="employee" column="WorkplaceCode" javaType="ArrayList"   ofType="Employee"> 
              <id property="empNumber" column="EmpNum" jdbcType="INTEGER"/> 
              <result property="empName" column="EmpName" jdbcType="VARCHAR"/> 
              </collection> 

But i don't think making Employee.employeeId will work, since it is arraylist. And will throw any error of "can't employeeId in workplace.java" 

<insert id="insertWorkplace" parameterType="map"> 
        insert into workplace (cmpanyName,EmployeeId, addressId) 
        values 
                ( 
                #{companyName}, 
                #{employee.EmployeeId}, 
                #{address.addressId} 
                ) 
        </insert> 

[Guy Rouillier]

On 11/25/2012 3:11 AM, PP wrote:
> Thanks Guy... :)
>
> But here Employee is collection

Well, I think you get the idea. Iterate over your collection in your
code, and use the insert statement one at a time. The values passed to
the insert statement need to be scalar values.

>
> <collection property="employee" column="WorkplaceCode"
> javaType="ArrayList" ofType="Employee">
> <id property="empNumber" column="EmpNum"
> jdbcType="INTEGER"/>
> <result property="empName" column="EmpName"
> jdbcType="VARCHAR"/>
> </collection>
>
> But i don't think making Employee.employeeId will work, since it is
> arraylist. And will throw any error of "can't employeeId in workplace.java"
>
> <insert id="insertWorkplace" parameterType="map">
> insert into workplace (cmpanyName,EmployeeId, addressId)
> values
> (
> #{companyName},

> * #{employee.EmployeeId}, *

--
Guy Rouillier

[PP]

I created the two insert statement now one for Workplace and another for employee in ibatis xml

<insert id="insertWorkplace" parameterType ="WorkplaceDetail" keyProperty="code" useGeneratedKeys="true">

    insert into Workplace (WorkplaceName,WRegNum,NumOfEmployees) values

  (#{plant},#{compRegNum},#{numOfEmps});

  </insert>  

  <insert id="insertEmployeeToWorkplace" parameterType ="WorkplaceDetail">

insert into Employee (EmpNumber,EmpName) values

(#{EmpNum},#{EmpName});  

  </insert>

  

  

  

  public synchronized void saveWorkplaceDetail(WorkplaceDetail workplaceDetail)

throws SQLException {

//SqlSessionFactory sqlSessionFactory = ConnectionFactory.getSqlSessionFactory();

session = sqlSessionFactory.openSession();    

logger.debug("begin insert at "+this.getClass().getName());  

workplaceDetail.setPlant(workplaceDetail.getPlant());

workplaceDetail.setCompRegNum(workplaceDetail.getCompRegNum());

workplaceDetail.setNumOfEmps(workplaceDetail.getNumOfEmps());

List<Employee> list= new ArrayList<Employee>();

for (Employee employee: list){

System.out.println("Start Insert-7" + list.add(employee));

employee.setEmpNumber(employee.getEmpNumber());

employee.setEmpName(employee.getEmpName());

workplaceDetail.setEmployee(list);

session.insert("Employee.insertEmployeeToWorkplace", employee);

}

session.insert("WorkplaceDetail.insertWorkplace", workplaceDetail);

 

logger.debug("end insert at "+this.getClass().getName());

session.commit();

session.close();

}

Unit test----

@Test

public void testInsert() throws SQLException{

WorkplaceDetail actual = new WorkplaceDetail();

actual.setPlant("GreenWorks Pharma and Cosmetics Ltd ");

actual.setCompRegNum("WHIULDCOS001A");

actual.setNumOfEmps("700");

List<Employee> list= new ArrayList<Employee>();

for (Employee employee: list){

employee.setEmpNumber(195643);

employee.setEmpName("Jaycee Kite");

list.add(employee);

actual.setEmployee(list);

System.out.println("Start Insert-7" + employee.getEmpNumber());

}

workplaceDaoImpl.saveWorkplaceDetail(actual);

assertEquals(573, actual.getCode());

WorkplaceDetail expected = workplaceDaoImpl.getWorkplaceDetailsById(actual.getCode()); //id = 21

assertEquals(actual, expected);

assertNotSame(actual, expected);

}

But still i am not able to insert after iterating Employee, but i am able to insert other values of workplace. Please help me.

[Guy Rouillier]

On 11/28/2012 12:08 AM, PP wrote:
>
> I created the two insert statement now one for Workplace and another for
> employee in ibatis xml
>

> <insert id="*insertWorkplace*" parameterType ="WorkplaceDetail"

> keyProperty="code" useGeneratedKeys="true">
> insert into Workplace (WorkplaceName,WRegNum,NumOfEmployees) values
> (#{plant},#{compRegNum},#{numOfEmps});
> </insert>

> <insert id="*insertEmployeeToWorkplace*" parameterType

> ="WorkplaceDetail">
> insert into Employee (EmpNumber,EmpName) values
> (#{EmpNum},#{EmpName});
> </insert>

Why are you specifying a parameterType of WorkplaceDetail when inserting
Employee? Your parameterType should be Employee.

> public synchronized void saveWorkplaceDetail(WorkplaceDetail
> workplaceDetail)
> throws SQLException {
> //SqlSessionFactory sqlSessionFactory =
> ConnectionFactory.getSqlSessionFactory();
> session = sqlSessionFactory.openSession();
> logger.debug("begin insert at "+this.getClass().getName());
> workplaceDetail.setPlant(workplaceDetail.getPlant());
> workplaceDetail.setCompRegNum(workplaceDetail.getCompRegNum());
> workplaceDetail.setNumOfEmps(workplaceDetail.getNumOfEmps());

> *List<Employee> list= new ArrayList<Employee>();*
> *for (Employee employee: list){*
> *System.out.println("Start Insert-7" + list.add(employee));*
> *employee.setEmpNumber(employee.getEmpNumber());*
> *employee.setEmpName(employee.getEmpName());*
> *workplaceDetail.setEmployee(list);*
> *session.insert("Employee.insertEmployeeToWorkplace", employee);*
> *}*

Why are you invoking setEmployee(list); repeatedly within this loop?
And what is the point of setting a list of employees in workplaceDetail?
You won't be able to persist a list; you have to persist each member
of the list independently.

> But still i am not able to insert after iterating Employee, but i am
> able to insert other values of workplace. Please help me.

I'm not surprised with the errors above.

--
Guy Rouillier