CODEWITHSUNDEEP
hibernate
Priya
Priya

Reputation: 147

How to set columns value as Arraylist in hibernate

Here I want to set 2 columns values from list so i can update multiple rows at a time I tried this but it's not working.

String hql = "UPDATE empDTO set empstatus =(:status) , empreason = 
            (:reason) WHERE enpID=(:id) and createdOn =:CreatedOn ";
            Query query = session.createQuery(hql);
            query.setParameterList("status", status);//status is list
            query.setParameterList("reason", reason);//reason is list
            query.setParameterList("id", id);//id is list
            query.setParameter("CreatedOn",new Date());
             query.executeUpdate();
            rowAffected = query.executeUpdate();

org.hibernate.exception.DataException: could not execute update query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:102) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

Caused by: java.sql.SQLException: Operand should contain 1 column(s)

Upvotes: 2

Views: 516

Answers (1)

soorapadman
soorapadman

Reputation: 4509

I would suggest you BatchPreparedStatementSetter to do the

You create a DTO to update a list of employeeUpdateDTO,

public class EmployeeUpdateDTO {
    private Integer empId;
    private String reason;
    private String status;

    public Integer getEmpId() {
        return empId;
    }

    public void setEmpId(Integer empId) {
        this.empId = empId;
    }

    public String getReason() {
        return reason;
    }

    public void setReason(String reason) {
        this.reason = reason;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }
}

Add this to your Implementation ;

    public void updateEmployee(List<EmployeeUpdateDTO> employeeUpdateDTOs) {
            String updateQuery = "UPDATE empDTO set empstatus =?,empreason =? WHERE enpID=? and createdOn =?";
            try {
                int[] updateCounts = jdbcTemplate.batchUpdate(updateQuery,
                        new BatchPreparedStatementSetter() {
                            public void setValues(PreparedStatement ps, int i) throws SQLException {

                                ps.setInt(1, employeeUpdateDTOs.get(i).empId);
                                ps.setString(2, employeeUpdateDTOs.get(i).getReason());
                                ps.setString(3, employeeUpdateDTOs.get(i).getStatus());
 ps.setDate(4, new java.sql.Date(System.currentTimeMillis())));
                            }

                            public int getBatchSize() {
                                return employeeUpdateDTOs.size();
                            }
                        });

            } catch (Exception e) {
                e.printStackTrace();

            }

        }

Upvotes: 0

Related Questions