什么是使用本地查询更新数据库中数据的最佳方法?

huangapple go评论78阅读模式
英文:

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();

如果 rule 对象中除了 rule_id(因为它是必需的)之外的任何数据为空,这个查询将会失败。所以我可以为 Rule 对象中的每个值格式化字符串,并将它们连接起来,排除 Rule 对象中为空的值,但是否有更好的方法来实现这一点呢?因为如果有数百列的话,这样做就不是理想的过程。那么 JPA 是否有任何可以满足我的要求的功能呢?

英文:
    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.

答案1

得分: 0

JPA的做法是创建实体映射:

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

    @ManyToOne
    private Server server;

    @OneToOne
    private Alert alert;

    private String ruleExpression;

    private double ruleFrequency;
}

一个Spring JPA仓库:

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

以及一个处理持久化的服务:

@Service
public class RuleService {
    @Autowired 
    RuleRepository ruleRepository;

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

使用方式如下:

Rule rule = getRule();
// 修改 rule
ruleService.update(rule);

JPA提供者将在幕后为您生成查询,无需复杂的本地查询。

英文:

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&lt;Rule, Integer&gt; {
}

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.

huangapple
  • 本文由 发表于 2020年10月14日 00:28:12
  • 转载请务必保留本文链接:https://go.coder-hub.com/64339335.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定