Issue
Does Java JDBC Template have the equivalent of Entity Framework Linq Include? Whats the best way to conduct this if not in the JDBCTemplate library?
For example in .Net, the following example would map a single-to-many database relationship into Product Class, with Suppliers and Locations below automatically.
SQL
select * from dbo.Product
inner join dbo.Suppliers
on suppliers.productId = product.productId
inner join dbo.Locations
on locations.productId = product.productId
Target Class:
public class Product{
private Long productId;
private String productName;
private List<Supplier> suppliers;
private List<Location> locations;
}
C#
List<Product> productList = dbContext.Product.Include(x=>x.Supplier)
.Include(x=>x.Location);
Java JDBC Template Proposed Solution:
How can this be done with Java and JDBC Template? Is there a clean way? I am thinking of converting the sql query into JSON for suppliers and locations, and mapping.
String productQuery =
SELECT pr.productId
,pr.productName
,(select *
from dbo.Supplier supp
where supp.supplierId = pr.supplierId
for json auto) as SuppliersJson
,(select *
from dbo.Location loc
where loc.locationId = pr.locationId
for json auto) as LocationsJson
FROM dbo.Product pr;
With this,
List<Product> products = namedJdbcTemplate.queryForObject(productQuery, new ProductMapper());
@Override
public ProductMapper mapRow(ResultSet rs, int rowNum) throws SQLException {
Product product = new Product();
product.setProductId(rs.getLong("ProductId"));
product.setProductName(rs.getString("ProductName"));
product.setSuppliers(new ObjectMapper().readValue(suppliersJson, Supplier.class));
product.setLocations(new ObjectMapper().readValue(locationsJson, Location.class));
return product;
}
Solution
Java offers you different options to implement the desired behavior.
First, as a naive solution, you could try using JDBC directly and iterate over the different results returned by your query, taking into consideration the last fetched result of every type, and build the corresponding object graph on your own.
For instance, given the following query (note that it is ordered):
select pr.*, sp.*, loc.* from dbo.Product pr
inner join dbo.Suppliers
on suppliers.productId = product.productId
inner join dbo.Locations
on locations.productId = product.productId
order by pr.productId, sp.supplierId, loc.locationId
You can use something like the following to iterate and build your object graph:
// You can use PreparedStatement as well if you prefer to
List<Product> products = new ArrayList<>();
Long lastProductId = null;
Product product = null;
Long lastSupplierId = null;
Supplier supplier = null;
Long lastLocationId = null;
Location location = null;
try (Statement stmt = con.createStatement()) {
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
Long productId = rs.getLong("ProductId");
if (lastProductId == null || productId != lastProductId) {
product = new Product();
products.add(product);
product.setProductId(productId);
product.setProductName(rs.getString("ProductName"));
product.setSuppliers(new ArrayList<>());
product.setLocations(new ArrayList<>());
lastProductId = productId;
lastSupplierId = null;
lastLocationId = null;
}
// Suppliers
Long supplierId = rs.getLong("SupplierId");
if (lastSupplierId == null || supplierId != lastSupplierId) {
supplier = new Supplier();
product.getSuppliers().add(supplier);
supplier.setSupplierId(supplierId);
//...
lastSupplierId = supplierId;
}
// Locations
Long locationId = rs.getLong("LocationId");
if (lastLocationId == null || locationId != lastLocationId) {
location = new Location();
product.getLocations().add(location);
location.setLocationId(locationId);
//...
lastLocationId = locationId;
}
}
} catch (SQLException e) {
e.printStackTrace();
}
With JdbcTemplate probably the way to go would be using queryForList. Consider for instance:
// Every Map in the list is composed by the column names as keys and
// the corresponding values as values
List<Map<String, Object>> rows = jdbcTemplate.queryForList(productQuery);
// The same code as above, but taking the information from the result Maps
List<Product> products = new ArrayList<>();
Long lastProductId = null;
Product product = null;
Long lastSupplierId = null;
Supplier supplier = null;
Long lastLocationId = null;
Location location = null;
for (Map row : rows) {
Long productId = (Long)row.get("ProductId");
if (lastProductId == null || productId != lastProductId) {
product = new Product();
products.add(product);
product.setProductId(productId);
product.setProductName((String)row.get("ProductName"));
product.setSuppliers(new ArrayList<>());
product.setLocations(new ArrayList<>());
lastProductId = productId;
lastSupplierId = null;
lastLocationId = null;
}
// Suppliers
Long supplierId = (Long)row.get("supplierId");
if (lastSupplierId == null || supplierId != lastSupplierId) {
supplier = new Supplier();
product.getSuppliers().add(supplier);
supplier.setSupplierId(supplierId);
//...
lastSupplierId = supplierId;
}
// Locations
Long locationId = (Long)row.get("locationId");
if (lastLocationId == null || locationId != lastLocationId) {
location = new Location();
product.getLocations().add(location);
location.setLocationId(locationId);
//...
lastLocationId = locationId;
}
}
As you can see, both approaches are neither very extensible nor maintainable, although suitable.
Similarly to EntityFramework, Java provides different ORM that you can use to alleviate these problems.
The first one is MyBatis.
Simplifying a lot, with MyBatis you need a SqlSession, an artifact that correctly configured will allow you to obtain a reference to a Mapper.
A Mapper is just an interface that declares different methods, one per every operation you need to perform against the database. A typical code block will be by like the following:
try (SqlSession session = sqlSessionFactory.openSession()) {
ProductMapper mapper = session.getMapper(ProductMapper.class);
//...
}
Where:
public interface ProductMapper {
List<Product> getProducts();
}
For every Mapper you need to provide an associated XML configuration file. This file will define as namespace the Mapper and, typically, for every method defined in the Mapper, the appropriate configuration. For example:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace=“my.pkg.ProducMapper”>
<resultMap id=“productWithSuppliersAndLocationsResultMap" type=“my.pkg.Product”>
<id property=“productId” column=“ProductId” />
<result property=“productName” column=“ProductName” />
<collection property=“suppliers” ofType=“my.pkg.Supplier”>
<id property=“supplierId" column=“SupplierId” />
<!— ... —/>
</collection>
<collection property=“locations” ofType=“my.pkg.Location”>
<id property=“locationId" column=“LocationId” />
<!— ... —/>
</collection>
</resultMap>
<select id=“getProducts” resultMap="productWithSuppliersAndLocationsResultMap">
select pr.*, sp.*, loc.* from dbo.Product pr
inner join dbo.Suppliers
on suppliers.productId = product.productId
inner join dbo.Locations
on locations.productId = product.productId
order by pr.productId, sp.supplierId, loc.locationId
</select>
</mapper>
As you can guess, it will use Reflection to populate the different properties from the provided SQL statement according to the provided resultMap.
It can be easily integrated with Spring as well.
One more option, as explained as well by @utrucceh in his/her answer, is the use of JPA, which is the Java de jure ORM standard.
To deal with it, you first need to transform your plain objects in entities, defining their columns and the relations within them. For example:
@Entity
@Table(name = "Products")
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long productId;
@Column(name = “ProductName”, nullable = false)
private String productName;
@OneToMany(mappedBy = "product")
private List<Supplier> suppliers;
@OneToMany(mappedBy = "product")
private List<Location> locations;
//…
}
@Entity
@Table(name=“Suppliers”)
public class Supplier {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long supplierId;
@ManyToOne(fetch= FetchType.LAZY)
@JoinColumn(name=“productId”, nullable = false)
private Product product;
//…
}
@Entity
@Table(name=“Locations”)
public class Location {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long locationId;
@ManyToOne(fetch= FetchType.LAZY)
@JoinColumn(name=“productId”, nullable = false)
private Product product;
//…
}
This object graph can be queried in different ways.
For instance, you can use the Criteria API.
The code could be something similar to the following:
EntityManager em = entityManagerFactory.createEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root<Product> root = query.from(Product.class);
query.select(root);
// Using fetchJoin will instruct the ORM to fetch, equivalent in a certain
// way to Include(), your results. The JPA implementation will do it best to
// optimize the way to return the actual information
Join<Product, Supplier> joinSuppliers = root.fetchJoin("suppliers", JoinType.INNER);
Join<Product, Location> joinLocations = root.fetchJoin("locations", JoinType.INNER);
List<Product> results = query.getResultList();
As an alternative, you could provide your SQL query as well, for example:
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT DISTINCT pr FROM Product pr
INNER JOIN FETCH pr.suppliers s
INNER JOIN FETCH pr.locations l
");
List<Product> resultList = query.getResultList();
Sorry for the brevity of the examples, but both JPA and MyBatis are very extensive subjects.
JPA implementations like Hibernate can be easily integrated with Spring.
The Spring Data JPA project provides great value added as well.
I realize that in my answer I actually didn't answer your question, I just provided different alternatives to achieve the desired results.
The actual answer is no, AFAIK JdbcTemplate doesn't include any functionally similar to the one provided by Include.
In Entity Framework Include identify the set of related entities you want to be fetched with the parent result.
In Java, the more similar approach to Include will be using JPA, with their entities and relationships as suggested, and the use of fetchJoin or a FETCH JOIN clause in your SQL query like indicated: that will actually fetch the related entities you need to.
Answered By - jccampanero
Answer Checked By - Timothy Miller (JavaFixing Admin)
