What is the best way to update data in database using Native Query

    EntityManager em = entityManagerFactory.createEntityManager();
    String str = String.format("UPDATE service.rules SET server_id='%2$s', alert_id='%3$s', rule_expression='%4$s', rule_frequncy='%5$s' WHERE rule_id='%1$s'",Rule.getRuleId(),Rule.getServerId(),Rule.getAlertId(),Rule.getRuleExpression(),Rule.getRuleFrequency());
    Query query = em.createNativeQuery(str);
    em.getTransaction().begin();
    query.executeUpdate();
    em.getTransaction().commit();

Here if one of data in rule object is null apart from rule_id(As it’s necessary) this query will fail, so instead I can format string for each value in Rule object and concat them excluding which has null value in Rule object but is there any better way to do this? Because if there are 100s of columns then this will not be ideal process. So does JPA has anything which can fulfill my requirement.

Answer

The JPA way of doing it is to create entity mappings:

@Entity
public class Rule {
    @Id
    private int id;

    @ManyToOne
    private Server server;

    @OneToOne
    private Alert alert;

    private String ruleExpression;

    private double ruleFrequency;
}

A spring jpa repository:

@Repository
class RuleRepository extends JpaRepository<Rule, Integer> {
}

And a service to handle persistance:

@Service
public class RuleService {
    @Autowired RuleRepository ruleRepository;

    @Transactional
    public Rule update( Rule rule ) {
        return ruleRepository.save( rule );
    }
}

And use it like:

Rule rule = getRule();
//change rule
ruleService.update(rule);

The JPA provider will generate the query for you behind the curtains, no need for complicated native queries.