代码之家 › 专栏 › 技术社区 › teowey

使用JPQL从数据库检索行数据

jpql jpa java

teowey · 技术社区 · 9 年前

我编写了一个JavaEE应用程序来转换货币。用户输入要转换的值,“从”货币和“到”货币,从数据库表中检索转换率。

! [Netbeans中的表片段] https://s16.postimg.org/8m242gyyd/Screen_Shot_2016_12_03_at_23_45_23.png

我的货币控制器有以下代码:

public double convertTo(double value, String fromCur, String toCur) {
    TypedQuery<Rate> query = em.createQuery(""
            + "SELECT c FROM Rate c WHERE c.fromCur = 'BRA' AND c.toCur = 'EUR'", Rate.class);

    query.setParameter("tocurrency", toCur);
    query.setParameter("fromcurrency", fromCur);

    Rate result = query.getSingleResult();

    if (result == null) {
        throw new EntityNotFoundException("Can't find rate");
    }

    return value * result.getValue();

}

我的目标是获得从“BRA”到“EUR”的汇率,例如,计算转换金额并返回。当我运行应用程序时,我得到异常:

getsingleresult()未检索任何实体

必须有一种更简单的方法从数据库中检索速率,以使用实体管理器计算转换,但我找不到正确的查询方法或JPA解决方案。有什么建议吗?

将代码转换为java实体速率。java如下所示:

package currency.model;

import java.io.Serializable;
import java.math.BigDecimal;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 *
 * @author teowey
 */
@Entity
public class Rate implements ConversionDTO, Serializable {


@Id
@GeneratedValue
private Integer conversionid;

private double rate;
private BigDecimal value;

private String fromCur;
private String toCur;
/**
* Creates a new instance of Account
*/
public Rate() {
}

@Override
public Integer getConversionId() {
    return conversionid;
}

public void setConversionId(Integer conversionid) {
    this.conversionid = conversionid;
}
@Override
public BigDecimal getValue() {
    return value;
}

public void setRate(double rate) {
    this.rate = rate;
}

public double getRate(String conversion) {
    return rate;
}

public String getFromCur() {
    return fromCur;
}

public void setFromCur(String from) {
    this.fromCur = from;
}

public String getToCur() {
    return toCur;
}

public void setToCur(String to) {
    this.toCur = to;
}

}

2 回复 | 直到 9 年前

Bob Lukens 9 年前

试图查看代码中是否存在导致问题的小问题,但没有找到。

下面是我测试的方法,其中有一些注释和调试代码。

// changed input amount to big decimal to avoid floating point issues
public BigDecimal convertTo(String from, String to, BigDecimal value) {

    // changed to validate the currency code - note BRA will fail - need to change to BRL - remove this code if you dont need it.
    Currency fromCurr = Currency.getInstance(from);
    Currency toCurr = Currency.getInstance(to);

    // debugging code - to help troubleshoot (only use this on small table) - will print out the contents of the table so you can see if data is not correct
    List<Rate> rates = em.createQuery("SELECT c FROM Rate c", Rate.class).getResultList();
    if (rates == null || rates.isEmpty()) {
        throw new RuntimeException("rates table is empty");
    }

    for (Rate r : rates) {
        // push to sysout or whatever logger you are using
        System.out.println(String.format("Rate:fromCur[%s], toCur[%s]", r.getFromCur(), r.getToCur()));
    }
    // end debugging code - remove when no longer needed.

    // changed the query to use bind parameters - :fromCurrency, :toCurrency
    TypedQuery<Rate> query = em.createQuery(
            "SELECT c FROM Rate c WHERE c.fromCur = :fromCurrency AND c.toCur = :toCurrency", Rate.class);

    query.setParameter("fromCurrency", fromCurr.getCurrencyCode());
    query.setParameter("toCurrency", toCurr.getCurrencyCode());

    Rate rate = query.getSingleResult();
    if (rate == null) {
        throw new EntityNotFoundException("Can't find rate");
    }

    // changed to use big decimal, scale and rounding as well as avoid floating point issues
    BigDecimal converted =
            value.multiply(rate.getValue()).setScale(toCurr.getDefaultFractionDigits(), RoundingMode.HALF_DOWN);

    return converted;
}

我包含了一些关于处理货币的注释,以帮助您避免潜在的陷阱。希望这能帮助你前进

karan 8 年前

只需要实现JPQl查询。简单地说,我是这样做的。这里我的表名是farmer,类名是Fammer,我为数据库使用了注释。

public static void main(String[] args) {
    EntityManager entityMgr = getEntityManager();
    entityMgr.getTransaction().begin();
    Query q = entityMgr.createQuery("SELECT s FROM Farmer s");

    List<Farmer> list = q.getResultList();
    for(Farmer s:list) {
         System.out.println("Id : "+s.getId()+" Farmer NAME : "+s.getName()+" location : "+s.getVillage());
      }
    entityMgr.getTransaction().commit();
    entityMgr.clear();
}