1 / 23

Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas Oppel

Manage your student life. Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas Oppel Benjamin Brand. Introduction Overview. What ist Tomabe – Student Self Management Software Product specifications Entity Relationship Model JAVA / JDBC code examples

lucian
Download Presentation

Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas Oppel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Manage yourstudentlife Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas Oppel Benjamin Brand

  2. IntroductionOverview • What ist Tomabe – Student Self Management Software • Productspecifications • EntityRelationship Model • JAVA / JDBC codeexamples • PHP / MYSQLicodeexamples • Live Demo JAVA Software / Website Thomas Oppel // Matthias Schmitt // Benjamin Brand

  3. User Stories • IntroductionWhatis TOMABE ? • Tomabeis a studentself-managementsoftware • Create owntimetable, • Managecourses, electives, • tasks,contacts, books • Infos aboutcourses • Warning System Thomas Oppel // Matthias Schmitt // Benjamin Brand

  4. User Stories • IntroductionProductSpecifications • MySQLdatabase • Software written in java (platformindependent) • JDBC databaseconnection • HTML and CSS forwebsitestructureand design • Dynamic websitegeneratedwith PHP • MYSQLidatabaseconnectioninterface • Subversion versioncontrol – TEAM WORK! • Flexible software design - model view controller architecture • Hash algorithm for password storage (sha1) • Modern looking Graphical User Interface with self made logotypes Thomas Oppel // Matthias Schmitt // Benjamin Brand

  5. Thomas Oppel // Matthias Schmitt // Benjamin Brand

  6. User Stories • JAVAJAVA JDBC Code Examples Thomas Oppel // Matthias Schmitt // Benjamin Brand

  7. User Stories JAVA MYSQL Connection JDBC // MYSQL Database Connection publicstaticConnectiongetConnection(){ // hostname StringdbHost="i-intra-02.informatik.hs-ulm.de"; // port - standard: 3306 StringdbPort="3306"; // database name Stringdatabase="dapro21"; // database user StringdbUser="dapro21"; // database password StringdbPassword="tomabe"; • // Initiate connection object Connectionconn=null; Attributes forthe MYSQL databaseconnection Thomas Oppel // Matthias Schmitt // Benjamin Brand

  8. JAVAMYSQL Connection JDBC Part 2 // MYSQL Database Connection with JDBC Interface • // load the needed database driver from class com.mysql.jdbc.Driver • try{ • Class.forName("com.mysql.jdbc.Driver"); • // establishing the connection • con=DriverManager.getConnection("jdbc:mysql://"+dbHost+":" • +dbPort+"/"+database,dbUser,dbPassword); • }catch(ClassNotFoundExceptione){ • System.out.println(„Driver not found!"); • }catch(SQLExceptione){ • System.out.println(“No Database Connection!"); • } • returncon; • } • Returns a connection object that can be used in other classes. Thomas Oppel // Matthias Schmitt // Benjamin Brand

  9. User Stories • JAVAInsert datainto a task // Insert data into table task • publicstaticvoidwriteToDB(Stringnickname,intcourseID,Stringdescription,Stringdeadline) • throwsSQLException,ParseException{ • SimpleDateFormatdatetimeFormatter=newSimpleDateFormat("yyyy-MM-ddhh:mm:ss"); • Datedate=datetimeFormatter.parse(deadline); • Timestamptimestamp=newTimestamp(date.getTime()); • Connectionconn=getConnection(); • Statementstmt=conn.createStatement(); • stmt.executeUpdate("INSERT INTO task (nickname, courseID, description ,deadline) • VALUES('"+nickname+"', "+courseID+", '"+description+"', '"+timestamp+"')"); • stmt.close(); • conn.close(); • } Get Connection andcreate a statementobjecttoexecutethe SQL query query.executeUpdate(mysql) executesthe SQL query update  insertinto Thomas Oppel // Matthias Schmitt // Benjamin Brand

  10. User Stories • JAVATask -WARNING SYSTEM! // Task data will be stored to database - new list with deadline- flag! • CallableStatementcstmt=conn.prepareCall("CALL setTaskFlaglessSevenDays(?)"); • cstmt.setString(1,nickname); • ResultSetrs=cstmt.executeQuery(); Lettheuserknow, whichtaskhastobedone „quickly“ Thomas Oppel // Matthias Schmitt // Benjamin Brand

  11. User Stories • JAVASelect / Receiving Data from DB // Select contacts from student – adding new objects to Arraylist • publicstaticArrayList<Contact> getContactsOfStudent(Stringnickname) • throwsSQLException{ • ArrayList<Contact>contacts=newArrayList<Contact>(); • Stringquery= • " SELECT DISTINCT contact.contactID, contact.prename as 'prename', contact.lastname as 'lastname'," • +" contact.mail as 'mail', contact.phone as 'phone' FROM student JOIN student_has_contacts " • +" ON student.nickname = student_has_contacts.nickname " • +" JOIN contact ON student_has_contacts.contactID = contact.contactID WHERE student.nickname = '" • +nickname+"'"; • ResultSetrs=stmt.executeQuery(query); • while(rs.next()){ • contacts.add(newContact(rs)); • } • stmt.close(); • conn.close(); • returncontacts; • } Iteratethroughresultset, whiletherearefurtherdata… …createnewobjectswhich will beaddedto an ArrayList Thomas Oppel // Matthias Schmitt // Benjamin Brand

  12. User Stories • JAVASelect / Receiving Data from DB Part 2 // Select contacts from student – adding new objects to Arraylist • publicstaticContactnewContact(ResultSetrs)throwsSQLException{ • Contactcontact=null; • contact=newContact(rs.getInt("contactID"),rs.getString("prename"), • rs.getString("lastname"),rs.getString("mail"), • rs.getString("phone")); • returncontact; • } Thismethodgetstheresultsetfromthequeryandcreates a newcontactobjectwiththespecificdataoftheresultset Thomas Oppel // Matthias Schmitt // Benjamin Brand

  13. User Stories • JAVADelete Fromstudentelective // Select contacts from student – adding new objects to Arraylist • publicstaticvoiddelete(intelectiveID)throwsSQLException{ • Connectioncon=getConnection(); • Statementstmt=con.createStatement(); • stmt.executeUpdate("DELETE FROM elective WHERE electiveID="+electiveID); • stmt.close(); • con.close(); • } Todelete a datasetfromthedatabase, wecallthefunctionexecuteUpdate. Thomas Oppel // Matthias Schmitt // Benjamin Brand

  14. User Stories • JAVACallUser DefinedFunction // Function ectsSum returns the sum of ECTS-Points of a student • publicstaticintgetEctsSumOfStudent(Stringnickname)throwsSQLException{ • Connectioncon=getConnection(); • CallableStatementcstmt=con.prepareCall("SELECT ectsSum(?)"); • cstmt.setString(1,nickname); • ResultSetrs=cstmt.executeQuery(); • rs.next(); • returnrs.getInt(1); • } Calling theuserdefinedfunctionectsSumthat will returnonecolumnwiththeresultset Storedprocedure -> CallableStatement CallableStatement objects are created with the Connection method prepareCall Thomas Oppel // Matthias Schmitt // Benjamin Brand

  15. User Stories JAVACallStoredProcedure // Insert data into student by calling a procedure • publicvoidwriteToDBInsertStudent()throwsSQLException{ • Connectioncon=getConnection(); • CallableStatementcstmt=conn.prepareCall("CALL fillInStudent(?,?,?,?)"); • cstmt.setString(1,this.nickname); • cstmt.setString(2,this.name); • cstmt.setString(3,this.lastName); • cstmt.setString(4,this.password); • cstmt.executeUpdate(); • cstmt.close(); • con.close(); • fillInRelation(); • } Call theprocedure, set Strings for IN-Paramter Thomas Oppel // Matthias Schmitt // Benjamin Brand

  16. User Stories PHPPHP / MYSQLi Code Examples Thomas Oppel // Matthias Schmitt // Benjamin Brand

  17. User Stories • PHPMYSQL Connection // MYSQLi Connection to our database • ClassConnection { • functiongetConnection() { • $db=newmysqli('i-intra-02.informatik.hs-ulm.de','dapro21','tomabe','dapro21'); • // Checking whether an error occurred • if(mysqli_connect_errno()){ • echo"Connection failed! SQL State:$db->sqlstate • Errornumber: $db->connect_errno • Errormessage: $db->connect_error\n"; • } • else{ • echo"Successfully connected to $db->host_info \n"; • echo"Server: $db->server_info / Version: $db->server_version\n"; • echo"Client: $db->client_info / Version: $db->client_version\n"; • $db->close(); • } • return$db; Connection tothe MYSQL database via theMYSQLiinterface. Thomas Oppel // Matthias Schmitt // Benjamin Brand

  18. User Stories • PHPInsert Into / fill in newuser // Insert new user into student table via formular $POST • $_nickname=$_POST["nickname1"]; • $_passwort=$_POST["password1"]; • $_passwort=sha1($_passwort); • $_prename=$_POST["prename"]; • $_lastname=$_POST["lastname"]; • $_sql="INSERT INTO student • SETnickname='$_nickname', • prename='$_prename', • lastname='$_lastname', • password='$_passwort';"; • $result=$con->query($_sql); Get POST inputsfromregisterformular… …andtaketheseattributestofill in a newstudenttotherelated DB table Thomas Oppel // Matthias Schmitt // Benjamin Brand

  19. User Stories • PHPSelect instruction / Receivedata // Select data from course and build course list • functioncreateCourseList($db) { • SESSION_START(); • $course="SELECT name, mark, ects FROM student_takes_coursest JOIN course c • ON st.courseID = c.courseID WHERE nickname ='".$_SESSION['nickname']."'"; • $result=$db->query($course); • … • if($result){ • while($row=$result->fetch_object()){ • echo'<tr class="row'.$this->countup().'">'; • echo'<td class="col1 cell">'.$row->name.'</td>'; • echo'<td class="col1 cell">'.$row->mark.'</td>'; • echo'<td class="col1 cell">'.$row->ects.'</td>'; • } • … Fetchtherowsfromtheresultsetasobjectsandbuildthetablewiththereceiveddata. Access todata via callingtheresultsetrowwiththecolumnname. Thomas Oppel // Matthias Schmitt // Benjamin Brand

  20. User Stories • PHPDelete Data // Delete task from database using a combo box Choose a taskfromthecombo box anddeleteitbypressingthebutton • functiondeleteTask($db) { • $postvar=$_POST["deleteTask"]; • $sql="DELETE FROM task WHERE taskID = $postvar"; • $db->query($sql); • } Thomas Oppel // Matthias Schmitt // Benjamin Brand

  21. User Stories • PHPUpdate Instruction / Update data // Update course -> set mark and ects with SQL-Update instruction • functionsetMarkAndECTS($db,$mark,$ects){ • $course=$_POST["course"]; • $array=explode(" ",$test); • $courseID=$array[0]; • $semester=$array[1]; • $sqlset="UPDATE student_takes_course SET mark = $mark, ects = $ects • WHERE nickname = '".$_SESSION['nickname']."' AND courseID = $courseID"; • $db->query($sqlset); • } Set individual markand ECTS-pointto an existingcourse Thomas Oppel // Matthias Schmitt // Benjamin Brand

  22. User Stories • PHPCall User DefinedFunction // Select User Defined Function avaMark and SumEcts • functionavaMarkAndSumEcts($db) { • $sql="SELECT avaMark ('".$_SESSION['nickname']."')"; • $sql_2="SELECT ectsSum('".$_SESSION['nickname']."')"; • $result=$db->query($sql); • $result_2=$db->query($sql2); • … • while($row=mysqli_fetch_array($result)){ • … • while($row=mysqli_fetch_array($result_2){ • … • } Set individual markand ECTS-pointto an existingcourse Returns an array that corresponds to the fetched row echo '<td >' .$row[0].'</td>'; … Thomas Oppel // Matthias Schmitt // Benjamin Brand

  23. LIVE DEMO JAVA / WEBSITE WEBSITE JAVA Thomas Oppel // Matthias Schmitt // Benjamin Brand

More Related