Web Application Development Databasebacked Web Application Programming Muhammad

Web Application Development Database-backed Web Application Programming Muhammad Ali Versonic Pte Asher Imtiaz Forman Christian College

Agenda �My. SQL GUI �JDBC �Writing a Student CRUD

Web Application Development Database Connectivity with My. SQL Muhammad Ali Versonic Pte Asher Imtiaz Forman Christian College

My. SQL �Full-featured Database Management System �Most popular for Web applications Over 6 million installations Over 20 OS are supported �Libraries for accessing My. SQL are available in all major programming languages �Used in high scale WWW products including Google, Wikipedia and Facebook http: //en. wikipedia. org/wiki/My. SQL

Partial Student Table for FCC-SAMS DB �Roll Number �First Name �Last Name �Gender �Address �Email �Phone �CGPA

SQLYog �Graphical User Interface (GUI) tool for My. SQL �Created by Webyog, based in Bangalore, India. �DEMO with the Implementation http: //en. wikipedia. org/wiki/SQLyog

Java Database Connectivity(JDBC) �An API for Java programming language �Define how a client may access a DB �Provides methods for querying and updating data in a DB http: //en. wikipedia. org/wiki/Java_Database_Connectivity

The Java JDBC Architecture �JDBC is a ‘standard’ API programmers use to access database systems �Various JDBC drivers are available that interface with particular database systems �A few database systems (HSQLDB and Derby) use JDBC as their native API

Database Access Architecture Point: Translation to vendor format is performed on the client • No changes needed to server • Driver (translator) needed on client Slide Credit: Programming the World Wide Web Book by Sebesta

JDBC Data Types JDBC Type BIT TINYINT SMALLINT INTEGER BIGINT REAL FLOAT DOUBLE BINARY VARBINARY LONGVARBINARY CHAR VARCHAR LONGVARCHAR Java Type boolean byte short int long float double byte[] String JDBC Type NUMERIC DECIMAL DATE TIMESTAMP CLOB BLOB ARRAY DISTINCT STRUCT REF Java Type Big. Decimal java. sql. Date java. sql. Timestamp Clob Blob Array mapping of underlying type Struct Ref JAVA_OBJECT underlying Java class Slide Credit: Marty Hall www. coreservlets. com

Steps o) Import Package – java. sql package. 1) Load the driver a) Not required in Java 6, so Java 6 needs only 6 steps. 2) 3) 4) 5) 6) 7) Define the Connection URL Establish the Connection Create a Statement object Execute a query Process the results Close the connection Slide Credit: Marty Hall www. coreservlets. com

Steps 3 1 Driver Manager creates Connection creates 2 Establish Link To DB 6 Statement SQL creates Result. Set 4 Data 5 Driver Database Slide Credit: Umair Javed, LUMS

Connection URL �The database connection URL is specified by a string of the form jdbc: subprotocol_name: more_info The subprotocol name is mysql for My. SQL other_info might include the database name and a query string providing values such as a username or password

// Class. for. Name("com. mysql. jdbc. Driver"); Connection connection = Driver. Manager. get. Connection("jdbc: mysql: //localhost: 3306", "root", "abc"); Statement statement = connection. create. Statement(); int row. Count = statement. execute. Update(“INSERT INTO sams_student(std_roll_number, std_name) VALUES(‘ 11 -10307’, ‘Ahmed’)”); connection. close();