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;

MySQL: Marks Input Design (Used in IComis)

To implement this User Interface:




Scenario:

1. Form loads the Program Combo Box with all the
available Programs
2. User selects a program and it loads the Session
Combo Box
3. User clicks on the Load Subjects Button, and it
loads the Subjects Combo Box.
4. User clicks on Load Students Button, and it
loads the Students Combo Box.


This loads the Program Combo Box:

SELECT name FROM program ORDER BY name;

This loads the Session Combo Box:

SELECT DISTINCT session FROM ssession;

This loads the Subjects Combo Box when the
Load Subjects Button is pressed:

SELECT DISTINCT code FROM ssubject
WHERE sessionid IN
(SELECT sessionid FROM ssession WHERE session='JAN 2009'
AND studentid IN (SELECT studentid
FROM student WHERE program='DICT'));


This loads the Students Combo Box when the
Load Students Button is pressed:

SELECT studentid,name FROM student
WHERE studentid IN
(SELECT studentid FROM ssession
WHERE session='JAN 2009' AND sessionid IN
(SELECT sessionid FROM ssubject
WHERE code='CSC1b')) AND program='DICT'
ORDER BY name;


Sample Java Code:

cbxStudent.removeAllItems();
try {
//---init cbxStudent---
statement = connection.createStatement();
String sSQL =
"SELECT studentid,name FROM student "
+ "WHERE studentid IN "
+ "(SELECT studentid FROM ssession "
+ "WHERE session='"
+ cbxSession.getSelectedItem().toString().trim()
+ "' AND sessionid IN "
+ "(SELECT sessionid FROM ssubject "
+ "WHERE code='"
+ cbxSubject.getSelectedItem().toString().trim()
+ "')) AND program='"
+ cbxProgramName.getSelectedItem().toString().trim() +"'"
+ " ORDER BY name ";

rsStudent = statement.executeQuery(sSQL);


while (rsStudent.next()) {
cbxStudent.addItem(rsStudent.getString("name"));
}
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "btnLoadStudents: " + e.toString());
//System.exit(1);
} finally {
try {
statement.close();
} catch (Exception e) {
}
}

MySQL: Drilling-down type nested queries (Not Used )

Scenario:

1. List all students in doing DICT for JAN 2009:
2. User selects Darren, then list all subjects taken by Darren.
3. User selects CSC1b subject for coursemarks input.

Below are MySQL Query Browser queries for the Scenario
above. Using nested queries provides the solution.






Text Version:

SELECT * FROM student WHERE program='DICT'
AND studentid IN
(SELECT studentid FROM ssession WHERE session='JAN 2009');


SELECT * FROM ssubject
WHERE sessionid IN
(SELECT sessionid FROM ssession WHERE session='JAN 2009'
AND studentid IN (SELECT studentid
FROM student WHERE program='DICT' and name='Darren'));



SELECT * FROM ssubject
WHERE sessionid IN
(SELECT sessionid FROM ssession WHERE session='JAN 2009'
AND studentid IN (SELECT studentid
FROM student WHERE program='DICT' and name='Darren'))
AND code='CSC1b';

MySQL: Decimal, Nested Query, ResultSet Pointer Manipulation

FLOAT:
FLOAT(4,2) will give 1.1 for data input 1.1


DECIMAL:
DECIMAL(4,2) will give 1.10 for data input 1.1
4 means 4 digits exclusing decimal point.
eg. 12.34 is four digits.
2 means how many decimal places.

So use decimal.

To get decimal from a ResultSet:
double f = resultSet.getDouble("credithour");


assuming credithour is a DECIMAL(10,2)
you will get 12.34

To input a decimal:
double value = Double.parseDouble((String)table.getValueAt(rowl,col));
Then, use the value in an SQL statement:

INSERT INTO table(name, marks) VALUES('James',value);

Example of a Nested Query:
select * from psubject
where pid in ( select pid from program where name='DICT' )
AND sem=1;

ResultSet Pointer:
After every update or query, the ResultSet points to the location
before the first row. You need to do this before getting anything
out again:

if(rsPsubject.next()) populateSubjectListForm();

Moving ResultSet to updated row (after an update):
After doing any updates, your ResultSet points to location
before the firt row. If you wish to go back to the previous
row, extract the primary key first before doing the update,
the do the update, then use a simple loop to get back to
the previous row:

//---get the primary key of the current row---
int currentStudentID = rsStudent.getInt("studentid");



//---Do your update here:



requeryStudent();

//---go back to row before the update---
rsStudent.next();
while(!(rsStudent.getInt("studentid")==currentStudentID)) rsStudent.next();