1 / 18

Using Java with PSQL and Oracle

Using Java with PSQL and Oracle. Thanks to Drs. Raj and Liu for sharing some of these slides TRUDY: ALSO NEED SAMPLE PROGRAM. In this slide deck:. Tips for using PSQL and Oracle Some “gotchas” for working with the Java database interface. psql.

latika
Download Presentation

Using Java with PSQL and Oracle

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. Using Java with PSQL and Oracle Thanks to Drs. Raj and Liu for sharing some of these slides TRUDY: ALSO NEED SAMPLE PROGRAM

  2. In this slide deck: • Tips for using PSQL and Oracle • Some “gotchas” for working with the Java database interface

  3. psql • You will need a separate psql account – see your instructor • Server runs on reddwarf • You need to provide your password each time you invoke psql

  4. psql • Full documentation and a tutorial are available online • Default is a command line interface that isn’t very forgiving with typos or to reissue commands • Several UI skins exist; some are open source – check around or write your own

  5. psql passwords • The psql interpreter will bypass the password prompt if it sees a .pgpass file in your home directory • Enter a single line: localhost:*:*:Your_Username:Your_Password (host:port:db:username:password) • IMPORTANT – SEE NEXT SLIDE

  6. psql passwords • Be sure to chmod 600 to set permissions on the file • Our system admin has approved storing this plain-text password IF you do not use the same password as your CS account • DO NOT USE THE SAME PASSWORD AS YOUR CS ACCOUNT!

  7. Getting started with psql • Assuming you use bash: • Edit your .bashrc file to include the following: export path=/usr/lib/postgresql/9.1/bin/:$path You will also need this for the Java JDBC: exportCLASSPATH=/home/student/abc1234/db/ postgresql-9.1-902.jdbc4.jar:$CLASSPATH (all one line. Substitute “/home/student/abc1234” with your own path name

  8. Integrating psql with Java • You also need the .jar file in your working directory. See my website download page for the link. • Review demo program • Note that much of your understanding will be achieved through experimentation and studying the appropriate javadocs!

  9. Continue to the next slides if using Oracle

  10. Setting Java Environment for Oracle • To use sqlplus and JDBC from a Java program • Ensure CLASSPATH is set up correctly • csh users can source the following file • /usr/local/bin/coraenv • Put it in your .cshrc file • bash users can source the following file • /usr/local/bin/oraenv • Put it in your .bashrcfile • Sample Java program • See course webpage under DBMS Docs • You must modularize this program! Understand password privacy issues involved here!

  11. Using Oracle @ the CS Department • Connecting to Oracle directly • sqlplus • Use this primarily for running DDL scripts • Connecting to a DBMS with a Java program • Use the Oracle JDBC driver • Read CRUD and the javadocs • Demonstration

  12. Java Classes/Interfaces: java.sql, javax.sql Some useful classes and methods • DriverManager • getConnection • Connection • createStatement, prepareStatement, commit, setAutoCommit, close • Statement • executeQuery, executeUpdate, close • PreparedStatement • execute, commit, close, setint, setString • ResultSet • next, getString, getInt

  13. JDBC Introduction • Useful to read the following SUN’s JDBC tutorial • java.sun.com/docs/books/tutorial/index.html • Steps for Oracle (similar for any other DBMS) • Load the driver • Connect to the DBMS • Send SQL to the DBMS • Process the results of your queries • Cleanup

  14. Load the Driver and Connect to Oracle • Several steps rolled into one Class.forName( "oracle.jdbc.driver.OracleDriver"); String url = "jdbc:oracle:thin:@queeg:1521:csodb10"; Connection con = DriverManager.getConnection( url, dbUser, dbPassword );

  15. Send SQL to the DBMS • Just send the SQL … stmt.executeUpdate("insert into A values(2009)" ); • Expect some goodies back String query = "select name, salary, emp_no from employee"; ResultSetrs = stmt.executeQuery( query );

  16. Process Query Results • Get the result set ResultSet rs = stmt.executeQuery( query ); • Process the result set, row by row while ( rs.next() ) { String emp_name = rs.getString( "name“ ); int emp_no = rs.getInt( "emp_no“ ); double salary = rs.getDouble( "salary“ ); System.out.println( emp_name + ", " + emp_no + “ makes $" + salary + “.“ ); }

  17. Data is representedin two places In the database SQL type DB programmer responsibility In Java program Java type Java programmer responsibility get??? calls Java and SQL: Type Mappings

  18. Cleanup • Close out the statement and connection stmt.close( ); con.close( );

More Related