1 / 42

Java & Database

Java & Database. Advance Java Programming II . Nacha Chondamrongkul School of Information Technology Mah Fah Luang University. Agenda. Architecture Overview SQL on MySQL JDBC Development Connection Management Building Statement Object Using ResultSet Object Batches

moke
Download Presentation

Java & Database

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. Java & Database Advance Java Programming II Nacha Chondamrongkul School of Information Technology Mah Fah Luang University

  2. Agenda • Architecture Overview • SQL on MySQL • JDBC Development • Connection Management • Building Statement Object • Using ResultSet Object • Batches • Limit the result • Transaction

  3. JDBC Overview • Acronym stands for Java Database Connectivity • JDBC is simply Application Programming Interface (API) that allow you to manipulate the database • JDBC is middleware or intermediary between Java language and database • Can be used from Different type of executable such as applications, applet, Servlet, JSP

  4. What about ODBC • Open Database Connectivity, is an API developed by Microsoft to allow access to databases. • There are drawbacks to using ODBC in the process of accessing a database through Java. • The primary drawback is that the code that implements ODBC is based on the C language and uses a large number of generic pointers. A number of problems occur with interfacing Java to C code, as well as the performance issues.

  5. Architecture Two-Tier Deployment Model: Client-server Client Java Application Java Applet JDBC Server MySQL Database

  6. Architecture [2] Three-Tier Deployment Model Client Java Application Java Applet Business Servlet, EJB JDBC Server MySQL Database

  7. JDBC Driver • Each Database server require a specific JDBC driver Need to compliable with JDBC Specification Client Java Application Java Applet Connector/J Oracle JDBC Driver MySQL Database Oracle Database

  8. JDBC Driver • The JDBC are consisted of • Core API – all classes/interfaces in package java.sql • Optional API - all classes/interfaces in package javax.sql

  9. JDBC Interface These interface are in java.sql package ResultSet ResultSet ResultSet Statement Prepared Statement Callable Statement Connection DriverManager Connector/J MySQL

  10. Java.sql Package • java.sql.DriverManager: The DriverManager class is used to manage all Driver objects. • java.sql.Driver: The Driver interface is implemented by all vendor drivers so that they can be loaded by a static class called DriverManager. The Driver object will automatically create an instance of itself and register with DriverManager. • java.sql.Connection: The Connection interface provides a method for creating a connection to a specific database. All SQL is executed in the context of a Connection object.

  11. Java.sqlPackage [2] • java.sql.Statement: The Statement interface is probably one of the most important interfaces in the JDBC specification. All SQL statements are executed through a Statement object. Each Statement object returns single ResultSetobjects. • java.sql.PreparedStatement: The PreparedStatement interface provides an object for executing precompiled SQL statements against the connected database. • java.sql.CallableStatement: The CallableStatement interface is used to execute stored procedures if supported on the database. Parameters are allowed with the interface as well as escape syntax.

  12. Java.sql Package [3] • java.sql.ResultSet: A ResultSet interface is designed to represent a Result-Set produced from a query of the database. An internal cursor points to the current row of data, and it can be pointed before and after the data. Methods are used to move the cursor to different rows in the ResultSet. By default, the ResultSet isn't updatable, but can be made both scrollable and updatable.

  13. Interface link Connection createStatement Statement PreparedStatement CallableStatement executeQuery ResultSet

  14. Where to get Mysql • http://dev.mysql.com/downloads/

  15. Where to get JDBC Driver • Google “MySQL JDBC Driver”

  16. Java & Database SQL on mySQL

  17. Create Table • The tableis where all of the data is stored in a particular database. Because we are working with a relational database system, the data is stored in rows and columns. Table name create table employee ( emp_idintprimary key, firstnamevarchar(64), lastnamevarchar(64), tstimestamp); Indicate this column is primary key Column name Data type

  18. Insert • With our database and table defined, we need to populate it with sample data. • Here’s the data that we would like to get into the table: Table name to insert Values to insert INSERT INTO employee VALUES(1001, ‘John', ‘Smith', now()); INSERT INTO employee VALUES(1002, ‘Timothy', ‘New', now()); INSERT INTO employee VALUES(1003, ‘Martin', ‘Gros', now());

  19. Select • Once you’ve inserted your data into a database, You can pull data from the database by using the SELECT command Specify columns SELECT * FROM employee; Where clause condition SELECT firstname,lastname FROM employee; SELECT firstname,lastname FROM employee WHERE firstname=‘John’; SELECT * FROM employee ORDER BY firstname; Sort the result by specified column SELECT firstname,lastname FROM employee WHERE firstnameLIKE ‘T%’;

  20. Update • if you want to change the data within a row, you can do this with the UPDATE command Specify columns and value to update UPDATE employee SET lastname='Neumann' WHERE emp_id='1002'; SELECT * FROM employee WHERE emp_id='1002'; Specify which row to be updated

  21. Delete • When data is no longer needed in a database, you can use the DELETE command to remove a row. DELETE FROM employee WHERE emp_id= ‘1003'; Specify which row to be deleted

  22. Show • SHOW Tables - list all table’s name in the connected database • SHOW COLUMNS FROM <table> – display information about columns of a table SHOW COLUMNS FROM employee;

  23. Java & Database JDBC Development

  24. Loading Connector/J Driver • Put the jar file “mysql-connector-java-xxxxx-bin.jar” into JVM class path • Once it finds the file, the code executes the newInstance() method to instantiate a new object from the Driver class. • During the instantiation, the Driver will register itself with a static class called DriverManager, which is responsible for managingall JDBC drivers installed on the current system

  25. Connection Making DriverManager Connector/J MySQL Application Connection can be obtain from DriverManager class using the following methods Connection getConnection(String URL); Connection getConnection(String URL, Properties info); Connection getConnection(String URL, String user, String password); Sample URL: //<host>[:<port>][/<databaseName>] jdbc:mysql://localhost jdbc:mysql://localhost/accounts jdbc:mysql://192.156.44.3/db_dev jdbc:mysql://database.company.com/prod jdbc:mysql://database.company.com:4533/prod

  26. Sample Making Connection Using Properties Properties prop = new Properties(); prop.setProperty("user","testuser"); prop.setProperty("password", "123"); Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test", prop); OR Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test", "testuser", "123"); Wrap Try / Catch around these statement related to JDBC

  27. Statement • The first step in getting data from the MySQL database is to build a Statement object. • Statement Object can be created from connection object Statement statement= connection.createStatement();

  28. Execute SQL • The Statementobject includes several types of query methods • One of that methods is executeQuery() • The method return result as ResultSet object ResultSetrs = statement.executeQuery( "SELECT * FROM employee");

  29. ResultSet • The ResultSet object is the primary storage mechanism for the rows returned from a query on the MySQL database. • Conceptually, the ResultSet object looks like an adjustable two-dimensional array Internal Cursor

  30. Positioning Cursor • Checking the cursor position by the following method • booleanisBeforeFirst() - return true if the cursor is before the first row. • booleanisAfterLast() - return true if the cursor is after the last row. • You can use getRow() method to return the current row number from the ResultSet

  31. Moving Cursor • The method moves the internal cursor to a specific row in the ResultSet. booleanabsolute(int rows) • next() move the cursor to the next row, return true when next row existed, false when next row doesn’t existed • prev() move the cursor to the previous row, return true when previous row existed, false when previous row doesn’t existed

  32. Getter Method • The result set contains the number of getter method to retrieve the value of specified column of the row where the cursor is currently pointing at, such as • getString(String columnName) • getInt(String columnName) • getDouble(String columnName) • getDate(String columnName) • …..

  33. Display the query result ResultSetrs = statement.executeQuery("SELECT * FROM employee"); while(rs.next()){ System.out.println(rs.getString("firstname")); }

  34. Execute Query w/o Result • The operations of insert, delete, and update are considered no-result queries because they don’t return a ResultSetobject after being executed. • We use method executeUpdate() statement.executeUpdate("INSERT INTO employee VALUES (1004, 'Michael', 'Miller', now())"); statement.executeUpdate("DELETE FROM Employee WHERE emp_id='1004'");

  35. Batching • The idea is to provide a mechanism where a large number of updates can be performed in a group with the hopes of better performance from the driver and database server. • void clearBatch()—Clears the current batch queue. • void addBatch(String SQL)—Adds the SQL string to the batch queue. • int[] executeBatch()—Executes the batch queue.

  36. Sample Batching statement.addBatch("INSERT INTO employee VALUES( 1005, 'Michael', 'Miller', now())"); statement.addBatch("INSERT INTO employee VALUES( 1006, 'Natasha', 'Smith', now())"); statement.addBatch("UPDATE employee SET lastname='Miller' WHERE emp_id='1002';"); int result[] = statement.executeBatch();

  37. Close everything • Always close connection / statement / ResultSet after finishing using them, otherwise the application will run out of connection. try{ …. } catch (SQLException e) { e.printStackTrace(); } finally { try { if(connection != null) connection.close(); if(statement != null) statement.close(); if(rs!= null) rs.close(); } catch (SQLException e) { e.printStackTrace(); } }

  38. Limit Result • If you have massive amount of rows (such as2,000,000 rows) you should consider limit result in ResultSet for performance reason • setMaxRows() -specify the total number of rows that can be • returned from a single query against a database • setFetchSize() – allow application to process smaller subsets of data at a time

  39. Transaction All or Nothing • ATOMIC - With Series of update statement, When one update statement need to be done along with other update statements , otherwise do not perform it at all. connection.setAutoCommit(false); statement = connection.createStatement(); statement.executeUpdate("UPDATE employee SET lastname='Gros' WHERE emp_id='1005'"); statement.executeUpdate("UPDATE employee SET lastname='Taylor' WHERE emp_id='1006'"); connection.commit(); If not call, the previous statement doesn’t effect any changes on database

  40. Exercise I • Write the source code that • - get user input for a employee • ID • Firstname • Lastname • Insert a record to employee table • Query all rows from employee and show ID, firstname, lastname of each row to the user

  41. With Factory Pattern Plain Old Java Object (POJO) Employee Class Perform CRUD All SQL lives here Factory Class Delete Update Read Create Main Program

  42. Exercise II Student • Create command line program which that allow user to • Add new record of customer • List all customer • Delete customer by given id • Search customer by name

More Related