如何在单个图形上从两个不同的MySQL表生成结果

奥西里斯93

我想生成一个具有两列的图形。一个用于报价，另一个用于发票（将比较两个）。报价单和发票在两个单独的表中，分别称为quotedb和invoicedb。我的问题是我只能让它生成报价。我是图形新手，所以我的问题是，如何操纵下面的编码，以便它可以显示两个图形中的数据。

这是从调用数据的代码quotedb：

//declaring the type of category in the column
String text =cboQIMonth.getSelectedItem().toString();
String empno = cboEmployee.getSelectedItem().toString();
String year = cboQIYear.getSelectedItem().toString();

//connecting the database
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/salventri","root","password");
//select statement to retrieve data for the graph
PreparedStatement stmt = con.prepareStatement("select * from quotedb where EXTRACT(MONTH from quote_date)='" + monthnum + "' and EXTRACT(YEAR from quote_date) ='" + year + "' and username=" + empno +"");
ResultSet rs = stmt.executeQuery();
DefaultCategoryDataset ddataset = new DefaultCategoryDataset();
while (rs.next() ) 
{
    //data to be displayed on the graph 
    ddataset.setValue(rs.getInt("quote_total"),
    rs.getString("quote_number") + " by " + rs.getInt("username"),
    monthnum + " " + cboQIYear.getSelectedItem().toString());                            
}

威尔·沙克福德

如果可能，请在SQL查询中合并两个表，以便获得包含所有所需数据的单个ResultSet。

如果quote_db包含以下内容：

+----------+-------------+
| QUOTE_ID | QUOTE_TOTAL |
+----------+-------------+
| quoteA   |          90 |
| quoteB   |         190 |
| quoteC   |         290 |
| quoteD   |         390 |
+----------+-------------+

和invoice_db包含以下内容：

+------------+---------------+----------+
| INVOICE_ID | INVOICE_TOTAL | QUOTE_ID |
+------------+---------------+----------+
| invoiceA   |           100 | quoteA   |
| invoiceB   |           200 | quoteB   |
| invoiceC   |           300 | quoteC   |
| invoiceD   |           400 | quoteD   |
+------------+---------------+----------+

然后这段代码：

PreparedStatement stmt = con.prepareStatement("select * from INVOICE_DB as iv, QUOTE_DB as qt where qt.QUOTE_ID = iv.QUOTE_ID");
ResultSet rs = stmt.executeQuery();
DefaultCategoryDataset ddataset = new DefaultCategoryDataset();
while (rs.next()) {
    ddataset.setValue(rs.getInt("invoice_total"),
           "invoice_total",
           rs.getString("invoice_id"));
    ddataset.setValue(rs.getInt("quote_total"),
           "quote_total",
           rs.getString("invoice_id"));
}
JFreeChart chart = ChartFactory.createBarChart3D("Compare Invoice and Quote", "Invoice ID's", "total", ddataset);
chart.getTitle().setPaint(Color.RED);
CategoryPlot p = chart.getCategoryPlot();
p.setRangeGridlinePaint(Color.BLUE);
ChartFrame frame2 = new ChartFrame("Compare Invoice and Quote", chart);
frame2.setVisible(true);
frame2.setSize(450, 350);

会产生这种情节：

如果不合理地加入他们，可以只做两个查询：

PreparedStatement stmt = con.prepareStatement("select * from INVOICE_DB");
ResultSet rs = stmt.executeQuery();
DefaultCategoryDataset ddataset = new DefaultCategoryDataset();
while (rs.next()) {
    ddataset.setValue(rs.getInt("invoice_total"),
           "invoice_total",
           rs.getString("invoice_id"));
}
stmt = con.prepareStatement("select * from QUOTE_DB");
rs = stmt.executeQuery();
while (rs.next()) {
    ddataset.setValue(rs.getInt("quote_total"),
           "quote_total",
           rs.getString("quote_id"));
}
JFreeChart chart = ChartFactory.createBarChart3D("Compare Invoice and Quote", "Invoice ID's", "total", ddataset);
chart.getTitle().setPaint(Color.RED);
CategoryPlot p = chart.getCategoryPlot();
p.setRangeGridlinePaint(Color.BLUE);
ChartFrame frame2 = new ChartFrame("Compare Invoice and Quote", chart);
frame2.setVisible(true);
frame2.setSize(450, 350);

结果图很难比较发票和报价：