CS 3220 Web and Internet Programming Database Access

CS 3220 Web and Internet Programming Database Access with JDBC Chengyu Sun California State University, Los Angeles

Client-Server Architecture of Databases Client mysql My. SQL Workbench PHPMy. Admin … Applications Server DB DB DB A library that sends SQL and other commands from client to server, and get the results from server to client.

JDBC Java Program JDBC API DBMS Independent JDBC Driver DB Server com. mysql. jdbc. Driver My. SQL

About JDBC Java Data. Base Connectivity JDBC API is DBMS independent JDBC Driver implements JDBC API for a particular DBMS

Example: Hello. JDBC. java Load JDBC driver n Only need to be done once per application Make connections Execute queries Process results Handle exceptions (and close connections)

JDBC Basics … import java. sql. *; Initialize driver n Class. for. Name("com. mysql. jdbc. Driver") Create connection n URL w jdbc: mysql: //[host: port]/[database][? user=cs 3220 stu 31& password=abcd] n n Driver. Manager. get. Connection( URL ) Driver. Manager. get. Connection( URL, user, pass )

… JDBC Basics Create statement n Statement stmt = c. create. Statement(); w stmt. execute. Query(String sql) w stmt. execute. Update(String sql) Get result back n Result. Set rs http: //docs. oracle. com/javase/tutorial/jdbc/index. html

DB Query Results In a program, we want to n n n Access each record Access each attribute in a record Access the name of each attribute select * from items; name price quantity Milk 3. 89 2 Beer 6. 99 1

JDBC Result. Set – Row Access Cursor Record 1 Record 2 Record 3 Results … next() – move cursor down one row n n n Cursor starts from before the 1 st record true if the current record is valid false if no more records

Common Code for Processing Result. Set Process each row n while(rs. next()) {…} Check whether a result set is empty n if(rs. next()) {…}

JDBC Result. Set – Column Access the columns of current row get. Xxx( String column. Name ) n E. g. get. String( “user” ); get. Xxx( int column. Index ) n n column. Index starts from 1 E. g. get. String( 1 ); http: //docs. oracle. com/javase/7/docs/api/java/sql/Result. Set. html

JDBC Result. Set – Access Column Names Result. Set. Meta. Data meta = rs. get. Meta. Data(); Result. Set. Meta. Data n get. Column. Name( column. Index ) w Column name n get. Column. Label( column. Index ) w Column title for display or printout

Handle Exceptions catch( SQLException e ) { throw new Servlet. Exception( e ); } finally { try { if( c != null ) c. close(); } catch( SQLException e ) { throw new Servlet. Exception( e ); } }

Example: Guest. Book (JDBC) – Display Create a guest_book table Retrieve the entries in a servlet Display the entries in a JSP

Example: Guest. Book (JDBC) – Add Save new guest book entries to the database n execute. Query() vs. execute. Update() Potential problems of handing user input n n Special characters SQL injection attack

Example: SQL Injection Attack User input should NOT be trusted Regular user input n n Username: cysun Password: abcd Malicious user input n n Username: someuser Password: something’ or ‘ 1 Prevent SQL injection attack?

Prepared Statements with parameters String sql = “insert into items values (? , ? )”; Prepared. Statement pstmt =c. prepare. Statement(sql); pstmt. set. String(1, “orange”); pstmt. set. Big. Decimal(2, 0. 59); pstmt. set. Int(3, 4); pstmt. execute. Update();

Benefits of Prepared Statements Special characters are properly handled Secure if the SQL statement is constructed from user input The SQL statement is more readable Better performance

Beyond the Basics. . . Transaction n ACID transaction disable auto commit send queries/updates exception commit rollback enable auto commit

. . . Beyond the Basics. . . It’s rather expensive to open a db connection n So how about once we open a connection, we leave it open forever? ? Connection Pool n n Max number of connections Max number of idle connections And many other configurable parameters http: //tomcat. apache. org/tomcat-8. 5 -doc/jndidatasource-examples-howto. html

. . . Beyond the Basics Mismatch between an OO design and a relational design Object-Relational Mapping n JPA and Hibernate http: //hibernate. org/orm/