英文:
Gathering data from resultset to map or list
问题
代码部分不要翻译。
以下是您要翻译的内容：
我有一段代码，从数据库中收集一些数据到结果集（产品数据）。我的产品模型如下所示：
public class Product {
private Integer avail;
private Long id;
private BigDecimal price;
private String url;
private Integer stock;
private String category;
private String title;
private String description;
private List<String> attribute;
private Map<String, String> attributes;
//getters-setters-toString
另一个类用于将产品收集到列表中：
public class Products {
private List<Product> products = null;
public List<Product> getProducts() {
return products;
}
public void setProducts(List<Product> products) {
this.products = products;
}
}
数据库查询的结果如下所示：
=============================================================
products_id||attribute key||attribute value||some other data ...
=============================================================
1 a1:k1 a1:v some other data ...
1 a1:k2 a1:v some other data ...
1 a1:k3 a1:v some other data ...
1 a1:k4 a1:v some other data ...
1 a1:k5 a1:v some other data ...
2 a2:k1 a2:v some other data ...
2 a2:k2 a2:v some other data ...
2 a2:k3 a2:v some other data ...
2 a2:k4 a2:v some other data ...
3 a3:k1 a3:v some other data ...
4 a4:k1 a4:v some other data ...
5 a5:k1 a5:v some other data ...
5 a5:k2 a5:v some other data ...
5 a5:k3 a5:v some other data ...
=============================================================
我需要创建类似以下的List<Product>：
=================================================
products_id | attributes | some other data ...
=================================================
1 k1:v, k2:v... some other data ...
2 k1:v, k2:v... some other data ...
3 k1:v some other data ...
4 k1:v some other data ...
5 k1:v, k2:v... some other data ...
=================================================
选择查询语句：
String query = "SELECT\n" +
"p.products_id, \n" +
"p.products_price_tax, \n" +
"p.products_availability_id, \n" +
"p.products_quantity, \n" +
"p.products_image, \n" +
"cd.categories_name, \n" +
"pd.products_name, \n" +
"pd.products_description, \n" +
"pef.products_extra_fields_name,\n" +
"ptpef.products_extra_fields_value\n" +
"FROM \n" +
"products p \n" +
"LEFT JOIN \n" +
"products_to_categories ptc \n" +
"ON \n" +
"ptc.products_id = p.products_id \n" +
"LEFT JOIN \n" +
"categories_description cd \n" +
"ON \n" +
"cd.categories_id = ptc.categories_id \n" +
"LEFT JOIN \n" +
"categories c \n" +
"ON \n" +
"c.categories_id = cd.categories_id \n" +
"LEFT JOIN \n" +
"products_description pd \n" +
"ON \n" +
"pd.products_id = p.products_id \n" +
"LEFT JOIN\n" +
"products_to_products_extra_fields ptpef\n" +
"ON\n" +
"ptpef.products_id = p.products_id\n" +
"LEFT JOIN\n" +
"products_extra_fields pef\n" +
"ON\n" +
"pef.products_extra_fields_id = ptpef.products_extra_fields_id\n" +
"WHERE \n" +
"p.products_status = 1\n" +
"AND\n" +
"c.categories_id IN (83, 104, 102, 106, 92)";
我尝试构建类似以下的内容：
conn = DriverManager.getConnection("connection data");
selectProducts = conn.prepareCall(query);
rs = selectProducts.executeQuery();
while (rs.next()) {
String tmpId = rs.getString("products_id");
attr = new ArrayList<>();
Product product = new Product();
product.setId(rs.getLong("products_id"));
product.setPrice(rs.getBigDecimal("products_price_tax"));
product.setAvail(rs.getInt("products_availability_id"));
product.setStock(rs.getInt("products_quantity"));
product.setCategory(rs.getString("categories_name"));
product.setTitle(rs.getString("products_name"));
product.setDescription("products_description");
attr.add(rs.getString("products_extra_fields_name"));
attr.add(rs.getString("products_extra_fields_value"));
product.setAttribute(attr);
mainList.add(product);
}
Product tmpProduct = null;
List<Product> tmpProductList = new ArrayList<>();
Map<String, String> tmpAttr = new HashMap<>();
for (int i = 0; i < mainList.size() - 1; i++) {
if (mainList.get(i).getId().equals(mainList.get(i + 1).getId())) {
tmpAttr.put(mainList.get(i).getAttribute().get(1),mainList.get(i).getAttribute().get(2)); //here I am getting NullPointerException
} else {
tmpAttr.put(mainList.get(i).getAttribute().get(0),mainList.get(i).getAttribute().get(1));
tmpProduct = mainList.get(i);
tmpProduct.setAttributes(tmpAttr);
tmpProductList.add(tmpProduct);
tmpAttr = null;
tmpProduct = null;
}
}
所以我试图构建一个新的List<Product>并与之一起工作。是否有更简单的方法来解决这个任务？
英文:
I have code to gather some data from database to resultset (data of products). My product model looks like this:
public class Product {
private Integer avail;
private Long id;
private BigDecimal price;
private String url;
private Integer stock;
private String category;
private String title;
private String description;
private List<String> attribute;
private Map<String, String> attributes;
//getters-setters-toString
Another class is to gather products to list:
public class Products {
private List<Product> products = null;
public List<Product> getProducts() {
return products;
}
public void setProducts(List<Product> products) {
this.products = products;
}
}
Result of my database query looks like this:
=============================================================
products_id||attribute key||attribute valu||some other data ...
=============================================================
1 a1:k1 a1:v some other data ...
1 a1:k2 a1:v some other data ...
1 a1:k3 a1:v some other data ...
1 a1:k4 a1:v some other data ...
1 a1:k5 a1:v some other data ...
2 a2:k1 a2:v some other data ...
2 a2:k2 a2:v some other data ...
2 a2:k3 a2:v some other data ...
2 a2:k4 a2:v some other data ...
3 a3:k1 a3:v some other data ...
4 a4:k1 a4:v some other data ...
5 a5:k1 a5:v some other data ...
5 a5:k2 a5:v some other data ...
5 a5:k3 a5:v some other data ...
=============================================================
I need to create List<Product> like this:
=================================================
products_id | attributes | some other data ...
=================================================
1 k1:v, k2:v... some other data ...
2 k1:v, k2:v... some other data ...
3 k1:v some other data ...
4 k1:v some other data ...
5 k1:v, k2:v... some other data ...
=================================================
Select query:
String query = "SELECT\n" +
"p.products_id, \n" +
"p.products_price_tax, \n" +
"p.products_availability_id, \n" +
"p.products_quantity, \n" +
"p.products_image, \n" +
"cd.categories_name, \n" +
"pd.products_name, \n" +
"pd.products_description, \n" +
"pef.products_extra_fields_name,\n" +
"ptpef.products_extra_fields_value\n" +
"FROM \n" +
"products p \n" +
"LEFT JOIN \n" +
"products_to_categories ptc \n" +
"ON \n" +
"ptc.products_id = p.products_id \n" +
"LEFT JOIN \n" +
"categories_description cd \n" +
"ON \n" +
"cd.categories_id = ptc.categories_id \n" +
"LEFT JOIN \n" +
"categories c \n" +
"ON \n" +
"c.categories_id = cd.categories_id \n" +
"LEFT JOIN \n" +
"products_description pd \n" +
"ON \n" +
"pd.products_id = p.products_id \n" +
"LEFT JOIN\n" +
"products_to_products_extra_fields ptpef\n" +
"ON\n" +
"ptpef.products_id = p.products_id\n" +
"LEFT JOIN\n" +
"products_extra_fields pef\n" +
"ON\n" +
"pef.products_extra_fields_id = ptpef.products_extra_fields_id\n" +
"WHERE \n" +
"p.products_status = 1\n" +
"AND\n" +
"c.categories_id IN (83, 104, 102, 106, 92)";
I was trying to build something like this:
conn = DriverManager.getConnection("connection data");
selectProducts = conn.prepareCall(query);
rs = selectProducts.executeQuery();
while (rs.next()) {
String tmpId = rs.getString("products_id");
attr = new ArrayList<>();
Product product = new Product();
product.setId(rs.getLong("products_id"));
product.setPrice(rs.getBigDecimal("products_price_tax"));
product.setAvail(rs.getInt("products_availability_id"));
product.setStock(rs.getInt("products_quantity"));
product.setCategory(rs.getString("categories_name"));
product.setTitle(rs.getString("products_name"));
product.setDescription("products_description");
attr.add(rs.getString("products_extra_fields_name"));
attr.add( rs.getString("products_extra_fields_value"));
product.setAttribute(attr);
mainList.add(product);
}
// mainList.forEach(product -> System.out.println(product));
Product tmpProduct = null;
List<Product> tmpProductList = new ArrayList<>();
Map<String, String> tmpAttr = new HashMap<>();
for (int i = 0; i < mainList.size() - 1; i++) {
if (mainList.get(i).getId().equals(mainList.get(i + 1).getId())) {
tmpAttr.put(mainList.get(i).getAttribute().get(1),mainList.get(i).getAttribute().get(2)); //here I am getting NullPointerException
} else {
tmpAttr.put(mainList.get(i).getAttribute().get(0),mainList.get(i).getAttribute().get(1));
tmpProduct = mainList.get(i);
tmpProduct.setAttributes(tmpAttr);
tmpProductList.add(tmpProduct);
tmpAttr = null;
tmpProduct = null;
}
}
So I was trying to build new List<Product> and work with it. Is there any simpler way to solve this task?
答案1
得分: 1
你可以使用 jOOQ 库来创建一个流，将其映射为一个 List<Product>。
Connection conn = DriverManager.getConnection("连接数据");
PreparedStatement pSt = connection.prepareStatement(query); //你的 SQL 查询
ResultSet rs = pSt.executeQuery();
List<Product> productList;
try (Stream<Record> productStream = DSL.using(conn).fetchStream(rs)){ //jOOQ DSL
productList = productStream
.map(p -> {
Product product = new Product();
//可能需要在获取后进行类型转换
product.setAvail(p.get("products_availability_id"));
//对于其他你想要映射到 Product 的字段也做同样的操作
return product;
})
.collect(Collectors.toList());
}
英文:
You could use the jOOQ library to create a stream that just gets mapped into a List<Product>
Connection conn = DriverManager.getConnection("connection data");
PreparedStatement pSt = connection.prepareStatement(query); //your SQL query
ResultSet rs = pSt.executeQuery();
List<Product> productList;
try(Stream<Record> productStream = DSL.using(conn).fetchStream(rs)){ //jOOQ DSL
productList = productStream
.map(p -> {
Product product = new Product();
//casting after get might be necessary
product.setAvail(p.get("products_availability_id"));
//do the same for other fields you'd like to map to Product
return product;
})
.collect(Collectors.toList());
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论