db002/src/DB002.java

   1 import java.sql.*;
   2 import java.util.Date;
   3
   4 public class DB002
   5 {
   6         private final static String DB_URL      = "jdbc:mysql://robocomp.dyndns.org/team3";
   7         private final static String DB_USER     = "team3";
   8         private final static String DB_PASS     = "team3";
   9
  10         private final static String STUDENT     = "Thompson";
  11
  12         private int     id_student;
  13         private RepetoireList list_repetoire;
  14         private ScalesList list_scales;
  15         private SightreadingList list_sightreading;
  16
  17         public DB002()
  18         {
  19                 Connection conn = null;
  20
  21                 try {
  22                         conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
  23                         System.out.println("Database connected");
  24
  25                         id_student = get_student_id(conn, STUDENT);
  26                         list_repetoire = get_student_repetoire(conn, id_student);
  27                         list_scales = get_student_scales(conn, id_student);
  28                         list_sightreading = get_student_sightreading(conn, id_student);
  29                 }
  30                 catch (SQLException se) {
  31                         se.printStackTrace();
  32                 }
  33                 finally {
  34                         try {
  35                                 if (conn != null) {
  36                                         conn.close();
  37                                         System.out.println("Database disconnected");
  38                                 }
  39                         }
  40                         catch (Throwable t)
  41                         { }
  42                 }
  43         }
  44
  45         public String toString()
  46         {
  47                 StringBuilder sb = new StringBuilder();
  48
  49                 sb.append(STUDENT).append("\n");
  50
  51                 sb.append(list_repetoire.get(0)).append("\n").
  52                         append(list_scales.get(0)).append("\n").
  53                         append(list_sightreading.get(0));
  54
  55                 return sb.toString();
  56         }
  57
  58         public static void main(String[] args)
  59         {
  60                 DB002 app = new DB002();
  61
  62                 System.out.println(app);
  63         }
  64
  65         /* *note - in the event that there are > 1 students with the same last name,
  66          * we just return the first id in the database. */
  67         private int get_student_id(Connection conn, String lname)
  68                 throws SQLException
  69         {
  70                 if (conn == null) {
  71                         return -1;
  72                 }
  73
  74                 Statement st = conn.createStatement();
  75                 st.executeQuery("select * from student where LName=\'" + lname +
  76                                 "\';");
  77
  78                 ResultSet result_set = st.getResultSet();
  79                 if (!result_set.next()) {
  80                         return -1;
  81                 }
  82
  83                 int id = result_set.getInt("idStudent");
  84                 return id;
  85         }
  86
  87         private RepetoireList get_student_repetoire(Connection conn, int sid)
  88                 throws SQLException
  89         {
  90                 if (conn == null) {
  91                         return null;
  92                 }
  93
  94                 Statement st = conn.createStatement();
  95                 st.executeQuery("select * from repetoire where Student_idStudent=" +
  96                                 sid + ";");
  97
  98                 return new RepetoireList(st.getResultSet());
  99         }
 100
 101         private ScalesList get_student_scales(Connection conn, int sid)
 102                 throws SQLException
 103         {
 104                 if (conn == null) {
 105                         return null;
 106                 }
 107
 108                 Statement st = conn.createStatement();
 109                 st.executeQuery("select * from scales where Student_idStudent=" +
 110                                 sid + ";");
 111
 112                 return new ScalesList(st.getResultSet());
 113         }
 114
 115         private SightreadingList get_student_sightreading(Connection conn, int sid)
 116                 throws SQLException
 117         {
 118                 if (conn == null) {
 119                         return null;
 120                 }
 121
 122                 Statement st = conn.createStatement();
 123                 st.executeQuery("select * from sightreading where Student_idStudent=" +
 124                                 sid + ";");
 125
 126                 return new SightreadingList(st.getResultSet());
 127         }
 128 }