MySQL: How to Join Multiple Tables

How to join 3 tables: student, ssession, ssubject


student:



ssession:



ssubject:



SQL query to join all 3 tables above:

SELECT student.name,ssession.sem,student.id,ssubject.grade,ssubject.total,
ssubject.exam,ssubject.cwtotal FROM student
INNER JOIN ssession
ON student.studentid = ssession.studentid
INNER JOIN ssubject
ON ssession.sessionid=ssubject.sessionid
WHERE ssession.session='JAN 2009'
AND ssubject.code='ACC1101';

produces this:



The basic syntax for multiple inner joins:

SELECT fields... FROM firstTable
INNER JOIN secondTable
ON firstTable.primaryKey=secondTable.foreignKey
INNER JOIN thirdTable
ON secondTable.primaryKey=thirdTable.foreignKey
WHERE conditions...

The SELECT fields... can be from any of the 3 tables being joined!

Note:
For Delete DO NOT use INNER JOIN.
You should use LEFT JOIN. Left join will delete the rows
even if table 2 and/or 3 has no corresponding row.
Inner join will fail to delete unless all three tables are
successfully joined.

MySQL: How to edit Group Properties for iReport

I'm using Netbeans 6.5 with iReport Plugins.
Below is how to access the Group Properties:



The steps:

1. Click on the Group Header.
2. Click on the Properties Panel on the right.

MySQL: How to create .jasper and read them from within Netbeans

Download iReports plugin for Netbeans. I'm using Netbeans 6.5

Then, unzip the zipped plugin. You should have 4 .nbm files
after unzipping:







Install each plugin into Netbeans:




Create a new report from within Netbeans:




When you click Preview Report, the .jrxml file is
compiled into a .jasper file. See red-circled above.

Then write code to load and display the .jasper file:



Text Version of code:

private void miReportJasperExtensionActionPerformed(java.awt.event.ActionEvent evt) {
runReportJasperExtension("src/myreport/report1.jasper");
}


private void runReportJasperExtension(String reportFile) {
try{
Class.forName(JDBC_DRIVER).newInstance();
connection = DriverManager.getConnection(DATABASE_URL, "loginname", "passwd");
String jasperPrint = JasperFillManager.fillReportToFile(reportFile,null,connection);
JasperViewer.viewReport(jasperPrint,false,true);

}catch(Exception ex) {
String connectMsg = "Could not view " + ex.getMessage() + " " + ex.getLocalizedMessage();
System.out.println(connectMsg);
}finally {
try {
statement.close();
} catch (Exception e) {
}
}
}

Note that miReportJasperExtension is my JMenuItem, you
can use a JButton instead.

You also need these variables:

private Connection connection;
private String JDBC_DRIVER = "com.mysql.jdbc.Driver";
private String DATABASE_URL = "jdbc:mysql://localhost/icomis";



Run your program and the JasperViewer will show the report:




Everything is done from within Netbeans. You do not need
to use the standalone iReport tool.

MySQL: How to embed JasperReports (.jasper) in your Java application

To embed Jasper Reports you need to use iReport
to create the .jasper file (eg "report.jasper").
I was not able to get Java
to compile a .jrxml directly. Besides even if it could,
it could slow down your Java application, because it
needs time to compile. So I opted to use iReport
to create a Report and snag the .jasper file right
out of the JasperReport folder and dump it into
my Java applications folder.

I'm using Netbeans 6.5.
In the Project Properties of your java application,
if you use the default JasperReport Library, it will create a
huge library folder in the dist folder which is
about 25MB and this is bad for Java WebStart
applications.

I have picked out the bare minimum libraries and
copied it out to another folder and use it to
add to the project library folder. The bare
minimum libraries needed for your java application to
read and display .jasper file:

This reduces the size of the lib to about 7 MB.

The source code
The source code that loads the .jasper file and displays it
is as below:

private void runReportJasperExtension(String reportFile) {
try{
Class.forName(JDBC_DRIVER).newInstance();
connection = DriverManager.getConnection(DATABASE_URL, "user", "password");
String jasperPrint = JasperFillManager.fillReportToFile(reportFile,null,connection);
JasperViewer.viewReport(jasperPrint,false,true);

}catch(Exception ex) {
String connectMsg = "Could not view " + ex.getMessage() + " " + ex.getLocalizedMessage();
System.out.println(connectMsg);
}finally {
try {
statement.close();
} catch (Exception e) {
}
}
}

My jasper file is "report.jasper". To call the method:

runReportJasperExtension("report.jasper") ;

Put "report.jasper" in the root folder of your Java
Project and create the following variables:


private Connection connection;
private String JDBC_DRIVER = "com.mysql.jdbc.Driver";
private String DATABASE_URL = "jdbc:mysql://localhost/icomis";

MySQL: Moving ResultSet pointer after a Query

Every query must have these 4 lines:

Line1:
statement = connection.createStatement();

Line 2:
String sQL = "SELECT * FROM ssession WHERE studentid=" + studentid
+" AND sem IN (SELECT MAX(sem) FROM ssession WHERE studentid="
+ studentid + ")";

Line 3:
ResultSet rsMaxSem = statement.executeQuery(sQL);

Line 4:
rsMaxSem.next( );

The last line (Line 4) is important. After every query, the pointer location points
to the position BEFORE the first row. As such, you need to move it to
the first row before you can get anything out of it.

Note that the first line (Line 1) is also important. If you reuse a previous
statement object without creating a new one. It will cause
the previously created ResultSet to close!

MySQL: Using MAX( ) to select highest sem record

This query selects the highest sem record for studentid 14:

String sQL = "SELECT * FROM ssession WHERE studentid=" + studentid
+" AND sem IN (SELECT MAX(sem) FROM ssession WHERE studentid="
+ studentid + ")";

MySQL: Inequality Queries

SELECT * FROM ssubject WHERE status NOT IN ('W','X');

SELECT * FROM ssession WHERE sem<=2;

To get Cummulative Credit Points from Sem 1 and Sem 2:

SELECT SUM(crptearn) FROM ssession WHERE sem<=2
AND studentid=10;