Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas Oppel

23
Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas Oppel Benjamin Brand Manage your student life

description

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 - PowerPoint PPT Presentation

Transcript of Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas Oppel

Page 1: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

Hochschule UlmSummer Semester 2012Database Programming

Matthias SchmittThomas Oppel

Benjamin Brand

Manage your student life

Page 2: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

Introduction Overview

Thomas Oppel // Matthias Schmitt // Benjamin Brand

What ist Tomabe – Student Self Management Software Product specifications Entity Relationship Model JAVA / JDBC code examples PHP / MYSQLi code examples Live Demo JAVA Software / Website

Page 3: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User Stories

Tomabe is a student self-management software

Create own timetable,Manage courses, electives,tasks, contacts, booksInfos about coursesWarning System

Introduction What is TOMABE ?

Thomas Oppel // Matthias Schmitt // Benjamin Brand

Page 4: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User Stories

MySQL database Software written in java (platform independent) JDBC database connection HTML and CSS for website structure and design Dynamic website generated with PHP MYSQLi database connection interface Subversion version control – TEAM WORK! Flexible software design - model view controller architecture Hash algorithm for password storage (sha1) Modern looking Graphical User Interface with self made logotypes

Introduction Product Specifications

Thomas Oppel // Matthias Schmitt // Benjamin Brand

Page 5: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

Thomas Oppel // Matthias Schmitt // Benjamin Brand

Page 6: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User StoriesJAVA JAVA JDBC Code Examples

Thomas Oppel // Matthias Schmitt // Benjamin Brand

Page 7: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User StoriesJAVA MYSQL Connection JDBC

Thomas Oppel // Matthias Schmitt // Benjamin Brand

// MYSQL Database Connection

public static Connection getConnection() { // hostname String dbHost = "i-intra-02.informatik.hs-ulm.de"; // port - standard: 3306 String dbPort = "3306"; // database name String database = "dapro21"; // database user String dbUser = "dapro21"; // database password String dbPassword = "tomabe"; // Initiate connection object Connection conn = null;

Attributes for the MYSQL database connection

Page 8: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

JAVA MYSQL Connection JDBC

Thomas Oppel // Matthias Schmitt // Benjamin Brand

// 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 (ClassNotFoundException e) { System.out.println(„Driver not found!"); } catch (SQLException e) { System.out.println(“No Database Connection!"); } return con; }

// MYSQL Database Connection with JDBC Interface

Returns a connection object that can be used in other classes.

Part 2

Page 9: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User StoriesJAVA Insert data into a task

Thomas Oppel // Matthias Schmitt // Benjamin Brand

public static void writeToDB(String nickname, int courseID, String description, String deadline) throws SQLException, ParseException { SimpleDateFormat datetimeFormatter = new SimpleDateFormat( "yyyy-MM-dd hh:mm:ss"); Date date = datetimeFormatter.parse(deadline); Timestamp timestamp = new Timestamp(date.getTime()); Connection conn = getConnection(); Statement stmt = conn.createStatement(); stmt.executeUpdate("INSERT INTO task (nickname, courseID, description ,deadline) VALUES('" + nickname + "', " + courseID + ", '" + description + "', '" + timestamp + "')"); stmt.close(); conn.close(); }

// Insert data into table task

query.executeUpdate(mysql)executes the SQL query

update insert into

Get Connection and create a statement object to

execute the SQL query

Page 10: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

Let the user know, which task has to be done „quickly“

User StoriesJAVA Task - WARNING SYSTEM!

Thomas Oppel // Matthias Schmitt // Benjamin Brand

CallableStatement cstmt = conn.prepareCall("CALL setTaskFlaglessSevenDays(?)"); cstmt.setString(1, nickname); ResultSet rs = cstmt.executeQuery();

// Task data will be stored to database - new list with deadline- flag!

Page 11: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User StoriesJAVA Select / Receiving Data from DB

Thomas Oppel // Matthias Schmitt // Benjamin Brand

// Select contacts from student – adding new objects to Arraylist public static ArrayList<Contact> getContactsOfStudent(String nickname)

throws SQLException {

ArrayList<Contact> contacts = new ArrayList<Contact>(); String query =

" 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 + "'"; ResultSet rs = stmt.executeQuery(query); while (rs.next()) { contacts.add(newContact(rs)); } stmt.close(); conn.close(); return contacts; }

Iterate through resultset, while there are further data…

…create new objects which will be added to an ArrayList

Page 12: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User StoriesJAVA Select / Receiving Data from DB

Thomas Oppel // Matthias Schmitt // Benjamin Brand

// Select contacts from student – adding new objects to Arraylist public static Contact newContact(ResultSet rs) throws SQLException {

Contact contact = null; contact = new Contact(rs.getInt("contactID"), rs.getString("prename"), rs.getString("lastname"), rs.getString("mail"), rs.getString("phone"));

return contact;}

This method gets the resultset from the query and creates a new contact object with the specific data of the resultset

Part 2

Page 13: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User StoriesJAVA Delete From student elective

Thomas Oppel // Matthias Schmitt // Benjamin Brand

// Select contacts from student – adding new objects to Arraylist public static void delete(int electiveID) throws SQLException{ Connection con = getConnection(); Statement stmt = con.createStatement(); stmt.executeUpdate("DELETE FROM elective WHERE electiveID=" + electiveID); stmt.close(); con.close(); }

To delete a dataset from the database, we call the function

executeUpdate.

Page 14: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User StoriesJAVA Call User Defined Function

Thomas Oppel // Matthias Schmitt // Benjamin Brand

// Function ectsSum returns the sum of ECTS-Points of a student public static int getEctsSumOfStudent (String nickname) throws SQLException { Connection con = getConnection(); CallableStatement cstmt = con.prepareCall("SELECT ectsSum(?)"); cstmt.setString(1, nickname); ResultSet rs = cstmt.executeQuery(); rs.next(); return rs.getInt(1);

}Calling the user defined function ectsSum

that will return one column with the resultset

Stored procedure -> CallableStatementCallableStatement objects are created with the

Connection method prepareCall

Page 15: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User StoriesJAVA Call Stored Procedure

Thomas Oppel // Matthias Schmitt // Benjamin Brand

public void writeToDBInsertStudent() throws SQLException { Connection con = getConnection(); CallableStatement cstmt = 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(); }

// Insert data into student by calling a procedure

Call the procedure, set Strings for IN-Paramter

Page 16: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User Stories

Thomas Oppel // Matthias Schmitt // Benjamin Brand

PHP PHP / MYSQLi Code Examples

Page 17: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User StoriesPHP MYSQL Connection

Thomas Oppel // Matthias Schmitt // Benjamin Brand

// MYSQLi Connection to our database

Connection to the MYSQL database via the MYSQLi

interface.

Class Connection { function getConnection() { $db = new mysqli('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 Error number: $db->connect_errno Error message: $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;

Page 18: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User StoriesPHP Insert Into / fill in new user

Thomas Oppel // Matthias Schmitt // Benjamin Brand

// 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 SET nickname='$_nickname', prename='$_prename', lastname ='$_lastname', password ='$_passwort';"; $result = $con->query($_sql);

Get POST inputs from register formular…

…and take these attributes to fill in a new student to the

related DB table

Page 19: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User StoriesPHP Select instruction / Receive data

Thomas Oppel // Matthias Schmitt // Benjamin Brand

// Select data from course and build course list function createCourseList($db) { SESSION_START(); $course = "SELECT name, mark, ects FROM student_takes_course st 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>';} …

Fetch the rows from the resultset as objects

and build the table with the received data.

Access to data via calling the resultset

row with the column name.

Page 20: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User StoriesPHP Delete Data

Thomas Oppel // Matthias Schmitt // Benjamin Brand

// Delete task from database using a combo box

function deleteTask ($db) { $postvar = $_POST["deleteTask"]; $sql = "DELETE FROM task WHERE taskID = $postvar"; $db->query($sql); }

Choose a task from the combo box and delete it by pressing the button

Page 21: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User StoriesPHP Update Instruction / Update

data

Thomas Oppel // Matthias Schmitt // Benjamin Brand

// Update course -> set mark and ects with SQL-Update instruction function setMarkAndECTS($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 mark and

ECTS-point to an existing course

Page 22: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

User StoriesPHP Call User Defined Function

Thomas Oppel // Matthias Schmitt // Benjamin Brand

// Select User Defined Function avaMark and SumEcts function avaMarkAndSumEcts($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) {…

}

echo '<td >' .$row[0]. '</td>';…

Set individual mark and ECTS-point to an existing

course

Returns an array that corresponds to the

fetched row

Page 23: Hochschule Ulm Summer Semester 2012 Database Programming Matthias Schmitt Thomas  Oppel

Thomas Oppel // Matthias Schmitt // Benjamin Brand

LIVE DEMO JAVA / WEBSITE

WEBSITE

JAVA