|
Database Integration Example: Storing Student Grades
The following values are database and JDBC driver specific. To execute this worksheet, these values must be modified to match your system.
|
|
Initialize the database
|
|
driverString is the name of the driver your JDBC driver provides.
|
>
|
driverString := "com.mysql.jdbc.Driver";
|
|
| (1.1) |
jarFile is the path to the JDBC jar file.
|
>
|
jarFile := "c:/drivers/MySQL/mysql-connector-java-3.0.14-production-bin.jar";
|
|
| (1.2) |
url specifies the host and database to which Maple should connect.
|
>
|
url := "jdbc:mysql://achilles/TestDB";
|
|
| (1.3) |
Load the driver module.
|
>
|
driver := Database:-LoadDriver( 'driver'=driverString, 'classpath'=jarFile );
|
|
| (1.4) |
Open a connection to the database using the loaded driver
|
>
|
connection := driver:-OpenConnection( url, "test", "test" );
|
|
| (1.5) |
|
|
|
Create a table and enter data
|
|
Create a table in the database for storing the marks.
|
>
|
connection:-ExecuteUpdate( "CREATE TABLE marks ( studentid INTEGER PRIMARY KEY, assignment1 FLOAT, assignment2 FLOAT, midterm FLOAT, assignment3 FLOAT, assignment4 FLOAT, assignment5 FLOAT, final FLOAT, average FLOAT )" );
|
|
| (2.1) |
Create a prepared statement to use to insert data into the database.
|
>
|
addStudent := connection:-CreatePreparedStatement( "INSERT INTO marks( studentid, assignment1, assignment2, midterm, assignment3, assignment4, assignment5, final ) VALUES( ?, ?, ?, ?, ?, ?, ?, ? )" );
|
|
| (2.2) |
Insert the student's grades.
|
>
|
for i from 1 to 200 do
addStudent:-Execute( RandomTools:-Generate( integer( range=100000..999999 ) ),
RandomTools:-Generate( float( range=1.0..100.0, method=uniform, digits=4 ) ),
RandomTools:-Generate( float( range=1.0..100.0, method=uniform, digits=4 ) ),
RandomTools:-Generate( float( range=1.0..100.0, method=uniform, digits=4 ) ),
RandomTools:-Generate( float( range=1.0..100.0, method=uniform, digits=4 ) ),
RandomTools:-Generate( float( range=1.0..100.0, method=uniform, digits=4 ) ),
RandomTools:-Generate( float( range=1.0..100.0, method=uniform, digits=4 ) ),
RandomTools:-Generate( float( range=1.0..100.0, method=uniform, digits=4 ) ) );
end do:
connection:-Commit();
|
|
|
|
Retrieve the data and perform statistical analyses
|
|
Get the midterm marks and calculate statistics for the marks.
|
>
|
data := connection:-ExecuteQuery( "SELECT midterm, studentid FROM marks", 'output'=Array );
|
|
[ 2 x 200 2-D Array ]
[ Data Type: anything ]
data := [ Storage: rectangular ]
[ Order: Fortran_order ]
| |
|
>
|
sdata := [seq( data[i][1], i=1..200 ) ]:
|
|
>
|
stats[describe,mean]( sdata );
|
|
| (3.1) |
Find the students that scored in the top 10%.
|
>
|
per := stats[describe,percentile[90]](sdata);
|
|
| (3.2) |
|
>
|
res := connection:-ExecuteQuery( sprintf( "SELECT studentid, midterm FROM marks WHERE midterm > %a ORDER BY midterm", per ) ):
|
|
>
|
while ( res:-Next() )
do
printf( "%a %a\n", res:-GetData( "studentid" ), res:-GetData( "midterm" ) );
end do:
|
|
896323 93.7399978637695314
609830 94.4599990844726564
324457 94.7799987792968750
565262 94.9800033569335938
482587 95.0199966430664063
501432 95.5000000000000000
568821 96.4100036621093750
711138 96.9400024414062500
275474 97.0599975585937500
942666 97.1200027465820313
876509 98.0100021362304688
822684 98.0400009155273438
216985 98.3799972534179688
726280 98.5500030517578126
422179 98.5599975585937500
175996 98.8300018310546876
655788 98.9899978637695312
746577 99.4000015258789063
836032 99.4800033569335938
525989 99.9400024414062500
| |
Find the student with the lowest mark.
|
>
|
x := min( op( sdata ) );
|
|
| (3.3) |
|
>
|
member( x, sdata, '`id`' );
|
|
| (3.4) |
|
| (3.5) |
|
| (3.6) |
Find other marks for this student.
|
>
|
connection:-ExecuteQuery( sprintf( "SELECT * FROM marks WHERE studentid = %d", data[id][2] ), output=table );
|
|
![TABLE([(1, "assignment4") = 91.8700027465820314, (1, "average") = (), (1, "assignment5") = 34.2500000000000000, (1, "assignment3") = 42.7099990844726564, (1, "midterm") = 1.06599998474121094, (1, "studentid") = 379028, (1, "assignment1") = 55.3199996948242188, (1, "final") = 22.1000003814697266, (1, "assignment2") = 80.3600006103515625])](/support/helpjp/helpview.aspx?si=7903/file03824/math251.png)
| (3.7) |
Calculate the weighted average of students' marks and insert them into the table.
|
>
|
resultSet := connection:-ExecuteQuery( "SELECT * FROM marks" );
|
|
| (3.8) |
|
>
|
while ( resultSet:-Next() )
do
ave := ( resultSet:-GetData( "assignment1" )+
resultSet:-GetData( "assignment2" )+
resultSet:-GetData( "assignment3" )+
resultSet:-GetData( "assignment4" )+
resultSet:-GetData( "assignment5" )+
3*resultSet:-GetData( "midterm" )+
5*resultSet:-GetData( "final" ) )/12.0;
resultSet:-UpdateData( "average", ave );
resultSet:-UpdateRow();
end do:
|
Find the students whose average is above 80%.
|
>
|
resultSet := connection:-ExecuteQuery( "SELECT average FROM marks WHERE average > 80" );
|
|
| (3.9) |
|
>
|
while ( resultSet:-Next() )
do
printf( "%a\n", resultSet:-GetData( "average" ) );
end do:
|
|
83.4332962036132813
87.3992004394531250
89.5199966430664064
87.0036010742187500
86.1983032226562500
85.5774993896484376
84.4216995239257813
90.2457962036132813
| |
|
|
|
Remove the table and close the connection
|
|
|
>
|
connection:-ExecuteUpdate( "DROP TABLE marks" );
connection:-Commit();
|
|
| (4.1) |
|
Return to Example Worksheet Index
|
