package edu.columbia.cs.cs1007.db;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
public class Registrars {
private Connection _conn;
/**
* Open the database connection.
* @param dbUser
* @param dbPass
* @return
*/
public String openDBConnection(String dbUser, String dbPass, String dbSID, String dbHost, int port) {
String res="";
try {
_conn = DBUtils.openDBConnection(dbUser, dbPass, dbSID, dbHost, port);
res = DBUtils.testConnection(_conn);
} catch (SQLException sqle) {
sqle.printStackTrace(System.err);
} catch (ClassNotFoundException cnfe) {
cnfe.printStackTrace(System.err);
}
return res;
}
/**
* Close the database connection.
*/
public void closeDBConnection() {
try {
DBUtils.closeDBConnection(_conn);
} catch (SQLException sqle) {
sqle.printStackTrace(System.err);
}
}
/**
* Register a new student in the database.
* @param newStudent
* @return
*/
public Student registerStudent(Student newStudent) {
try {
int sid = 1 + DBUtils.getIntFromDB(_conn, "select max(sid) from Students");
newStudent.setId(sid);
String query = "insert into Students (sid, name) values (" +
newStudent.getId() + ", '" + newStudent.getName() + "')";
DBUtils.executeUpdate(_conn, query);
} catch (SQLException sqle) {
sqle.printStackTrace(System.err);
}
return newStudent;
}
/**
* Update the student's GPA in the database.
* @param sid
* @param gpa
* @return
*/
public Student setGPA(int sid, double gpa) {
Student student = null;
try {
int cnt = DBUtils.getIntFromDB(_conn, "select count(*) from Students where sid = " + sid);
if (cnt == 0) {
return student;
}
String query = "update Students set gpa = " + gpa + " where sid = " + sid;
DBUtils.executeUpdate(_conn, query);
query = "select name, gpa from Students where sid = " + sid;
Statement st = _conn.createStatement();
ResultSet rs = st.executeQuery(query);
rs.next();
student = new Student(sid, rs.getString("name"), rs.getDouble("gpa"));
rs.close();
st.close();
} catch (SQLException sqle) {
sqle.printStackTrace(System.err);
}
return student;
}
/**
* Get the complete roster of students.
* @return
*/
public Student[] getRoster() {
ArrayList<Student> roster = new ArrayList<Student>();
try {
String query = "select sid, name, gpa from Students order by sid";
Statement st = _conn.createStatement();
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
int sid = rs.getInt("sid");
String name = rs.getString("name");
double gpa = rs.getDouble("gpa");
Student student = new Student(sid, name, gpa);
roster.add(student);
}
rs.close();
st.close();
} catch (SQLException sqle) {
sqle.printStackTrace(System.err);
}
return roster.toArray(new Student[0]);
}
public static void main (String args[]) {
if (args.length < 4) {
System.out.println("Not enough arguments: Registrars dbUser dbPass dbSID dbHost");
System.exit(0);
}
String dbUser = args[0].trim();
String dbPass = args[1].trim();
String dbSID = args[2].trim();
String dbHost = args[3].trim();
int dbPort = 1521;
Registrars reg = new Registrars();
String response = reg.openDBConnection(dbUser, dbPass, dbSID, dbHost, dbPort);
System.out.println(response);
Student newStudent = reg.registerStudent(new Student("Julia"));
System.out.println("\nRegistered a new student: " + newStudent.toString());
newStudent = reg.setGPA(newStudent.getId(), 3.9);
System.out.println("\nUpdated GPA for student: " + newStudent.toString());
Student [] roster = reg.getRoster();
System.out.println("\nPrinting the roster");
for (Student student : roster) {
System.out.println(student.toString());
}
reg.closeDBConnection();
}
}
|