EntityManager创建本机查询与持久化和注入 [英] EntityManager create native query vs persist and injections

问题描述

 
是 createNativeQuery（） -override>  @ManagedBean 
 @ViewScoped 
 public class UserController {
 
 @PersistenceContext 
 private EntityManager em; 
 
 public用户注册（用户u）{
查询查询= em.createNativeQuery（SELECT r1_register（？，？，？，？，？，？，？））; 
 short i = 0; 
 query.setParameter（++ i，u.getUsername（））; 
 query.setParameter（++ i，u.getPassword（））; 
 query.setParameter（++ i，u.getName（））; 
 query.setParameter（++ i，u.getSurname（））; 
 query.setParameter（++ i，u.getEmail（））; 
 query.setParameter（++ i，u.getBirthdate（））; 
 query.setParameter（++ i，u.getPhoneNumber（））; 
 int id =（int）query.getSingleResult（）; 
 if（id！= 0）u.setIduser（id）; 
 return u; 
} 
} 
  

r1_register 是一个存储的函数，执行INSERT并返回新插入的用户的id。这将是等价的：

  public User register（User u）{
 em。坚持（U）; 
 //获取最后插入的ID（用户ID必须为@Generated）
 em.flush（）; // user id set here 
 return u; 
} 
  

u 两种情况都由用户填写。最后是默认发起的交易？

编辑：例程：

  CREATE DEFINER =`root` @`localhost` FUNCTION`r1_register`（username VARCHAR（45），
 _password VARCHAR（45），
 _name VARCHAR（45），
 surname VARCHAR 45），
 _email VARCHAR（45），
 _birthdate DATE，
 phone_number VARCHAR（10））RETURNS int（11）
 BEGIN 
  - 添加一个新用户。 
  - 开始交易; - 开始交易 -  NOT ALLOWED 
  -  http://stackoverflow.com/questions/16969875/ 
如果r1_check_unique_username（username）= 0 THEN 
返回0; 
 END IF; 
 INSERT IGNORE INTO`hw1_db`.`users`（`username`，`password`，`name`，`surname`，`email`，`birthdate`，`phone_number`）
 VALUES（username ，_password，_name，surname，_email，_birthdate，phone_number）; 
  - 请参阅：http://stackoverflow.com/a/5939840/281545 
  - 这种方法的缺点是你不能回去使用
  -  ids被浪费，因为在重复键的事件
  - 中尝试INSERT IGNORE的尝试失败。我们检查不应该是我们的问题。 
  -  / Transaction 
  -  IF ROW_COUNT（）> 0 THEN 
  -  ROW_COUNT（）返回更新/插入/删除的行数
  -  COMMIT; - 完成交易
  -  ELSE 
  -  ROLLBACK; - 恢复交易开始前所做的所有更改
  -  END IF; 
 RETURN LAST_INSERT_ID（）; 
 END 
  

解决方案

这取决于什么 r1_register 其实在做。如果它只是保存用户，没有别的，比是，他们是等价的，因为这是 EntityManager＃persist 正在做的。但是，如果DB函数执行一些安全检查或写入其他表，那么您需要在JPA中实现它。用于插入用户并获取ID的Btw代码应为

  public User register（User u）{
 em.getTransaction（）。begin（）; 
 em.persist（u）; 
 em.getTransaction（）。commit（）; 
 int id = u.getId（）; 
 return u; 
} 
  

但是您不必调用 EntityManager＃刷新如果您在注册方法被调用后需要该ID，则flush将在每个事务的结尾执行。

Is createNativeQuery() safe against SQL injection if used as in:

@ManagedBean
@ViewScoped
public class UserController {

    @PersistenceContext
    private EntityManager em;

    public User register(User u) {
        Query query = em.createNativeQuery("SELECT r1_register(?,?,?,?,?,?,?)");
        short i = 0;
        query.setParameter(++i, u.getUsername());
        query.setParameter(++i, u.getPassword());
        query.setParameter(++i, u.getName());
        query.setParameter(++i, u.getSurname());
        query.setParameter(++i, u.getEmail());
        query.setParameter(++i, u.getBirthdate());
        query.setParameter(++i, u.getPhoneNumber());
        int id = (int) query.getSingleResult();
        if (id != 0) u.setIduser(id);
        return u;
    }
}

r1_register is a stored function that performs an INSERT and returns the id of the newly inserted user. Would this be equivalent:

public User register(User u) {
    em.persist(u);
    // get the last inserted id (user id must be @Generated)
    em.flush(); // user id set here
    return u;
}

u is in both cases filled by the user. Finally is a transaction initiated by default ?

EDIT: The routine:

CREATE DEFINER=`root`@`localhost` FUNCTION `r1_register`(username VARCHAR(45),
                _password VARCHAR(45),
                _name VARCHAR(45),
                surname VARCHAR(45),
                _email VARCHAR(45),
                _birthdate DATE,
                phone_number VARCHAR(10) ) RETURNS int(11)
BEGIN
-- Adds a new user.
    -- START TRANSACTION; -- Begin a transaction -- NOT ALLOWED
    -- http://stackoverflow.com/questions/16969875/
    IF r1_check_unique_username(username)=0 THEN
        RETURN 0;
    END IF;
    INSERT IGNORE INTO `hw1_db`.`users` (`username`, `password`, `name`, `surname`, `email`, `birthdate`, `phone_number`)
        VALUES (username, _password, _name, surname, _email, _birthdate, phone_number);
    -- see: http://stackoverflow.com/a/5939840/281545
    -- The drawback to this approach is that you cannot go back and use
    -- ids wasted because of failed attempts to INSERT IGNORE in the event
    -- of a duplicate key. Shouldn't be a problem for us as we check.
    -- /Transaction
    -- IF ROW_COUNT() > 0 THEN
    -- ROW_COUNT() returns the number of rows updated/inserted/deleted
    --  COMMIT; -- Finalize the transaction
    -- ELSE
    --  ROLLBACK; -- Revert all changes made before the transaction began
    -- END IF;
    RETURN LAST_INSERT_ID();
END

解决方案

This depends on what r1_register is actually doing. If it just saving the user and nothing else, than yes, they are equivalent because that's what EntityManager#persist is doing. But if the DB function is performing some security checking or writing to other tables, than you need to implement it in JPA too. Btw code for inserting User and obtaining the ID should be

public User register(User u) {
    em.getTransaction().begin();
    em.persist(u);
    em.getTransaction().commit();
    int id = u.getId();
    return u;
}

But you don't have to call EntityManager#flush if you need that id after the register method is called, flush is performed by the end of every transaction.

这篇关于EntityManager创建本机查询与持久化和注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！