jdbc左联接3个表 [英] jdbc left join 3 tables

问题描述

有三个表<Table 1>, <Table 2> and <Table 3>

我的SQL是这样的:

"Select table1.col1, table1.col2, table1.col3, table2.col4, table2.col5, table2.col6, 
table3.col7, table3.col8 from Table 1 as table1 
LEFT JOIN Table 2 as table2 on (table1.col1 = table2.col4) 
LEFT JOIN Table 3 as table3 on (table1.col1 = table3.col8)"

获取结果集的正常方法是:

The normal way to get the resultset is:

public List getExportDataList() throws ClassNotFoundException, SQLException {
Connection connect = null;
String url = "jdbc:.....";

String username = "username ";
String password = "password ";

try {
   connect = DriverManager.getConnection(url, username, password);
} catch (SQLException ex) {
   System.out.println("in exec");
   System.out.println(ex.getMessage());
}

List dataList = new ArrayList<>();
PreparedStatement pstmt = connect.prepareStatement(
  THE SQL CODE SHOWN ABOVE
}

ResultSet rs = pstmt.executeQuery();

while(rs.next()){
Table1 table1 = new Table1();

table1.setCOL1(rs.getString("col1"));
table1.setCOL2(rs.getString("col2"));

dataList.add(table1);
}
rs.close();
pstmt.close();
connect.close();
return dataList;
}

以便"dataList"可用于显示Primefaces dataTable中的数据.

so that the "dataList" can be used to display the data in Primefaces dataTable.

但是，这种方式只能将表1中的列保存到"dataList".我尝试同时dataList.add(table2)和dataList.add(table3)，但是出现错误:"/reportGenerate.xhtml @ 50,75 value ="#{reportData.dateCreated}:类'net.picary. model.Liaison'没有属性'dateCreated'."

However, this way only can save the columns in Table1 to the "dataList". I tried to dataList.add(table2) as well as dataList.add(table3) at the same time, but there is an error : "/reportGenerate.xhtml @50,75 value="#{reportData.dateCreated}": The class 'net.picary.model.Liaison' does not have the property 'dateCreated'."

有人可以告诉我如何将三个表中的所有选定列保存到"dataList"中吗?还是其他替代方法来实现它?

Can someone tell me how to save all the selected columns from the three tables into "dataList"? Or any alternative way to achieve it?

推荐答案

错误:"/reportGenerate.xhtml @ 50,75 value =#{reportData.dateCreated}":该类 'net.picary.model.Liaison'没有属性'dateCreated'."

error: "/reportGenerate.xhtml @50,75 value="#{reportData.dateCreated}": The class 'net.picary.model.Liaison' does not have the property 'dateCreated'."

您的问题不是Query或JDBC，您应该使用getter和setter确保像这样的属性dateCreated存在于您的net.picary.model.Liaison类中:

Your problem is not with Query or JDBC, you should to make sure that attribute dateCreated exist in your net.picary.model.Liaison class with getter and setter like this :

private Date dateCreated;
public String name;
public String experience;

public Date getDateCreated() {
    return dateCreated;
}

public void setDateCreated(Date dateCreated) {
    this.dateCreated = dateCreated;
}

因此，当您的页面reportGenerate.xhtml尝试加载此属性时，找不到它，因为:

So when your page reportGenerate.xhtml try to load this attribute it not find it, because :

1. 您的班级不存在
2. 它存在，但它是私有的，没有getter和setter.

因此请确保您的属性存在并且具有gtter和setter，这可以解决您的错误.

So make sure that your attribute exist and have gtter and setter, this can solve your error.

编辑

您有两种选择:

1. 获得结果时，应使用3个类型为table1，table2，table3的列表，并将它们填写在同一循环中，如下所示:

    List dataList1 = ...;
    List dataList2 = ...;
    List dataList3 = ...;

    while(rs.next()){
       table1 = new Table1();
       table1.setCOL1(rs.getString("col1"));
       ...
       dataList1.add(table1);

       table2 = new Table2();
       table2.setCOL1(rs.getString("col1"));
       ...
       dataList2.add(table2);

       table3 = new Table3();
       table3.setCOL1(rs.getString("col1"));
       ...
       dataList3.add(table3);
    }

，并且在您的xhtml页面中，您必须使用这三个列表来代替一个

and in your xhtml page you had to use this three List instead to one

1. 创建一个新对象，将这三个表结合起来，如下所示:

    class MyObject {
        private Table1 table1;
        private Table2 table2;
        //constructor
        //getters and setters
    }

然后创建一个List<MyObject> list = ....;并在每个表中设置信息.

Then create a List<MyObject> list = ....; and set information in each table.

希望你明白我的意思，祝你好运

Hope you get my point, good luck

这篇关于jdbc左联接3个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！