jdbc left join 3 tables

Question

There are three tables <Table 1>, <Table 2> and <Table 3>

My SQL is something like this:

"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;
}

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

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'."

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

Answer 1

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

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;
}

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

1. Not exist in your class
2. It exist but it is private and without getter and setter.

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

EDIT

You have two choices :

1. When you get your result you should to use 3 List of type table1, table2, table3 and fill them in the same loop like this :

    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);
    }

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

2. Create a new Object which combine this three Table like this :

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

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

Hope you get my point, good luck

Answer 2

First, Check whether column names (col1,col2,col3,..) are with same name in your code in java with all the three db tables (table1,table2,table3).

(ie)

...
Table1 table1 = new Table1();

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

dataList.add(table1);
....

In the above code check you have "col1" is in same name with db column in Table1,Table2,Table3.If the column name(s) is different in database and in your code ,add aliases to your MySQL query and match it with same name in your java code.

If the column name matches, then set the column value for your table 2 and 3.

Then, add it to your Datalist.

 ....
 ....
 table2.setCOL1(rs.getString("col1"));
 dataList.add(table2);
 ....

and so on. Also, check you have added try/catch for your code to avoid exceptions like Nullpointer,SQLException etc.. which may arise during SQL transactions.