Select on Northwind database using JPA and JDBC Template

Refresh

April 2019

Views

98 time

1

I would like to perform select statement on Northwind database like this bellow.

select distinct b.*, a.CategoryName
from Categories a
inner join Products b on a.CategoryID = b.CategoryID
where b.Discontinued = 'N'
order by b.ProductName;  

I have two problems regarding this operation :

  • I have created POJO for tables categories and products like bellow

Table Products

@Entity
public class Products {
  @Id
  private Long productid;
  private String productname;
  private Long supplierid;
  @ManyToOne
  @JoinColumn(name = "categories", referencedColumnName = "categoryid")
  private Categories categoryid;
  private String quantityperunit;
  private Double unitprice;
  private Long unitsinstock;
  private Long unitsonorder;
  private Long reorderlevel;
  private String discontinued;

Table Categories

@Entity
public class Categories {
  @Id
  private Long categoryid;
  private String categoryname;
  private String description;
  private String picture;

Now I have no idea how to write rowmapper for this tables (please find below ????)

private static final RowMapper<Products> productsRowMapper = (rs, rowNum) ->{
        Products products = new Products();
        products.setProductid(rs.getLong("ProductID"));
        products.setProductname(rs.getString("ProductName"));
        products.setSupplierid(rs.getLong("SupplierID"));
        products.setCategoryid(rs.?????
        products.setQuantityperunit(rs.getString("QuantityPerUnit"));
        products.setUnitprice(rs.getDouble("UnitPrice"));
        products.setUnitsinstock(rs.getLong("UnitsInStock"));
        products.setUnitsonorder(rs.getLong("UnitsOnOrder"));
        products.setReorderlevel(rs.getLong("ReorderLevel"));
        products.setDiscontinued(rs.getString("Discontinued"));
        return products;
    };
  • the second problem is that I don't know if the annotations on the column categoryid in the table products are correct?

After correction

   @Repository
public class JdbcProductsDao implements ProductsDao{

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public JdbcProductsDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    private static final RowMapper<Products> productsRowMapper = (rs, rowNum) ->{
        Products products = new Products();
        products.setProductid(rs.getLong("ProductID"));
        products.setProductname(rs.getString("ProductName"));
        products.setSupplierid(rs.getLong("SupplierID"));
        products.setCategoryid(new Categories(rs.getString("CategoryName")));
        products.setQuantityperunit(rs.getString("QuantityPerUnit"));
        products.setUnitprice(rs.getDouble("UnitPrice"));
        products.setUnitsinstock(rs.getLong("UnitsInStock"));
        products.setUnitsonorder(rs.getLong("UnitsOnOrder"));
        products.setReorderlevel(rs.getLong("ReorderLevel"));
        products.setDiscontinued(rs.getString("Discontinued"));
        return products;
    };


    public Products findByProductName(String productname) {
        String sql = "SELECT * FROM products WHERE ProductName = ?";
        return jdbcTemplate.queryForObject(sql, productsRowMapper, productname);
    }

    public List<Products> sortByProductName(){
        String sql = "SELECT * FROM products order by ProductName asc";
        return jdbcTemplate.query(sql, productsRowMapper);
        }

Table Categories

@Entity
public class Categories {
  @Id
  private Long categoryid;
  @Column(name = "CategoryName")
  private String categoryname;
  private String description;
  private String picture;

  @OneToMany(mappedBy="categoryid")
  private List<Products> products;

Table Products

   @Entity
public class Products {
  @Id
  private Long productid;
  @Column(name = "ProductName")
  private String productname;
  private Long supplierid;
  @ManyToOne
  private Categories categoryid;
  private String quantityperunit;
  private Double unitprice;
  private Long unitsinstock;
  private Long unitsonorder;
  private Long reorderlevel;
  private String discontinued;

CategoryName

1 answers

0

Now I have no idea how to write rowmapper for this tables (please find below ????)

You have to set an Object like this :

products.setCategoryid(new Categories(rs.getString("a.categoryname"));

I assume you have an constructor Categories(String categoryname)

Note : If you want to get more information, you have to change your query and your constructor as well.


the second problem is that I don't know if the annotations on the column categoryid in the table products are correct?

I think there are no need to use @JoinColumn(name = "categories", referencedColumnName = "categoryid") just use :

@ManyToOne
private Categories categoryid;

In your Categories entities add this :

@OneToMany(mappedBy="categoryid")
List<Products> products;