Inner Join 3表后无法获得结果 [英] Unable to get result after Inner Join 3 tables
问题描述
我已经声明了一个返回对象数组的列表的 List< Object []> ，其中每个数组代表结果中的一行。
productList =
（List< Object []>） session.createSQLQuery（
SELECT User.username，User.email，Orders.p_id，Orders.o_id，Product.listed_price+
FROM Orders+
INNER JOIN User ON User .u_id = Orders.u_id+
INNER JOIN Product ON Product.p_id = Orders.p_id+
WHERE Product.p_id ='+ p_id +'+
ORDER BY User.username）。list（）;
我有3张表用户，产品和订单。
USER：
----- + ----------- + ------ ------ + --------- + ---------- + ----------- +
u_id |用户名|密码|联系|电子邮件|城市|
------ + ----------- + ------------ + --------- + ---- ----- + ----------- +
产品：
+ ------ + ---------- + -------------- + ------ + -------------- +
| p_id |类别| listed_price | qty |描述|
+ ------ + ---------- + -------------- + ------ + ----- --------- +
ORDERS：
+ ------ + -------- + ------ + - ---- + ----------- +
| o_id |日期| u_id | p_Id | order_qty |
+ ------ + -------- + ------ + ------ + ----------- +
我想要Inner按照以下顺序连同用户表和Product一起使用o_id（来自ORDER）：
+ ----------- + ------- + ------ + - ---- + -------------- +
|用户名|电子邮件| p_id | o_id | listed_price |
+ ----------- + ------- + ------ + ------ + ----------- --- +
在我的ActionClass / View类中声明 public List<对象[]> productList; 从上面的控制器类访问对象列表。
public List< Object []>产品列表;
$ b $ public String listAllProduct（）{
HttpServletRequest request =（HttpServletRequest）ActionContext.getContext（）。get（ServletActionContext.HTTP_REQUEST）;
productList = orderDaoFactory.listProduct（Integer.parseInt（request.getParameter（p_id）））;
System.out.println（\t+ productList.get（0）.toString（））;
返回SUCCESS;
$ b $ p $在我的JSP页面中，我使用了这个列表（对象数组）productList作为迭代器标签来迭代已订购该产品的所有客户。 < s：iterator value =productList>
< tr>
< td>< h4>< s：属性值=用户名/>< / h4>< / td>
< td>< h4>< s：属性值=email/>< / h4>< / td>
< td>< h4>< s：属性值=p_id/>< / h4>< / td>
< td>< h4>< s：属性值=o_id/>< / h4>< / td>
< td>< h4>< s：属性值=listed_price/>< / h4>< / td>
< / tr>
< / s：iterator>
挑战/问题：我的问题是不成功输出调试后出现任何错误。现在空想着使用O / R映射关联对象JOIN与其他人。请告诉我我错在哪里。
>）session.createSQLQuery（
SELECT User，Orders，Product+
FROM User，Orders，Product+
INNER JOIN User ON User.u_id = Orders.u_id +
INNER JOIN Product ON Product.p_id = Orders.p_id+
WHERE Product.p_id ='+ p_id +'+
ORDER BY User.username） .LIST（）;
然后你会得到三个对象。在第一个列表中，您将获得Object [0] = Users，Object [1] = Orders，Object [2] = Product。现在迭代它。
售完后
productList =
（List< Object []>）session.createQuery（
SELECT User，Orders，Product+
FROM User，Orders，Product+
INNER JOIN User ON User.u_id = Orders.u_id+
INNER JOIN Product ON Product.p_id = Orders.p_id+
WHERE Product.p_id ='+ p_id +'+
ORDER BY User.username）。list（）;
语法有什么问题？
I have declare a List<Object[]> which returning list of object array, where each array represents a row in result. And index of values are based on your select statement.
productList =
(List<Object[]>) session.createSQLQuery("
SELECT User.username, User.email, Orders.p_id, Orders.o_id, Product.listed_price " +
"FROM Orders " +
"INNER JOIN User ON User.u_id = Orders.u_id " +
"INNER JOIN Product ON Product.p_id = Orders.p_id " +
"WHERE Product.p_id = '"+p_id +"' " +
"ORDER BY User.username").list();
I have 3 tables User, Product and Orders.
USER:
-----+-----------+------------+---------+----------+-----------+
u_id | username | password | contact | email | city |
------+-----------+------------+---------+---------+-----------+
PRODUCT:
+------+----------+--------------+------+--------------+
| p_id | category | listed_price | qty | description |
+------+----------+--------------+------+--------------+
ORDERS:
+------+--------+------+------+-----------+
| o_id | date | u_id | p_Id | order_qty |
+------+--------+------+------+-----------+
I wants to Inner joins User table with Product along with o_id (from ORDER) in below order:
+-----------+-------+------+------+--------------+
| username | email | p_id | o_id | listed_price |
+-----------+-------+------+------+--------------+
In my ActionClass/View class i declare public List<Object[]> productList; to access object list from controller class above.
public List<Object[]> productList;
public String listAllProduct(){
HttpServletRequest request = (HttpServletRequest) ActionContext.getContext().get(ServletActionContext.HTTP_REQUEST);
productList = orderDaoFactory.listProduct(Integer.parseInt( request.getParameter("p_id")));
System.out.println("\t"+productList.get(0).toString());
return SUCCESS;
}
In my JSP page i'm using this list (array of object) productList as an Iterator tag to iterate all customers who have ordered that product.
<s:iterator value="productList">
<tr>
<td><h4><s:property value="username"/></h4></td>
<td><h4><s:property value="email"/></h4></td>
<td><h4><s:property value="p_id"/></h4></td>
<td><h4><s:property value="o_id"/></h4></td>
<td><h4><s:property value="listed_price"/></h4></td>
</tr>
</s:iterator>
Challenges/Issues: My problem is not to get output successfully even not getting any error after debugging. Empty mind now thinking to use O/R mapping to associate Objects JOIN with others. Please suggest me where i am wrong. Your suggestions appreciable.
productList =
(List<Object[]>) session.createSQLQuery("
SELECT User, Orders, Product" +
"FROM User,Orders,Product " +
"INNER JOIN User ON User.u_id = Orders.u_id " +
"INNER JOIN Product ON Product.p_id = Orders.p_id " +
"WHERE Product.p_id = '"+p_id +"' " +
"ORDER BY User.username").list();
Then you will get three object. In first list you will get Object[0]=Users,Object[1]=Orders,Object[2]=Product. Now iterate it.
after edition
productList =
(List<Object[]>) session.createQuery("
SELECT User, Orders, Product" +
"FROM User,Orders,Product " +
"INNER JOIN User ON User.u_id = Orders.u_id " +
"INNER JOIN Product ON Product.p_id = Orders.p_id " +
"WHERE Product.p_id = '"+p_id +"' " +
"ORDER BY User.username").list();
what is the problem with is syntax??
这篇关于Inner Join 3表后无法获得结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
