1 / 16

JDBC

JDBC. Java and Databases, including Postgress. JDBC. Developed by Industry leaders Three main goals: JDBC should be an SQL-level API JDBC should capitalize on existing DB APIs JDBC should be simple. SQL level. you should be able to construct SQL statements and embed them in Java API calls

duc
Télécharger la présentation

JDBC

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. JDBC Java and Databases, including Postgress

  2. JDBC • Developed by Industry leaders • Three main goals: • JDBC should be an SQL-level API • JDBC should capitalize on existing DB APIs • JDBC should be simple

  3. SQL level • you should be able to construct SQL statements and embed them in Java API calls • jdbc vs odbc: C based and overly complex • jdbc portability is a big asset

  4. Developing JDBC Applications • Import the jdbc classes • load the jdbc driver • Connect to the database • Submit queries, inserts, updates and process responses Don’t forget to commit any changes! • Disconnect from the database

  5. Import the jdbc files • import java.sql.*; will import all of the sql classes for jdbc This will include Drivers, Driver Managers, Connections, Statements, ResultSets, etc.

  6. Load the Driver • PC: new JdbcOdbcDriver(); • Others: try // load driver { Class.forName( “string name of driver” ); } catch ( Exception e ) { e.printStackTrace(); return; }

  7. Postgress • Driver available at URL • http://www.postgresql.org/ftp/ • Install, then download JDBC JAR file at: http://jdbc.postgresql.org/download.html and place it in your classpath (including the jar name) try // load driver { Class.forName(“org.postgresql.Driver” ); } catch ( Exception e ) { e.printStackTrace(); return; }

  8. The Connection Interface • represents a session with a specific DB • all SQL statements executed and results returned within the context of the connection • A Java application can have any number of connections to one or more DBs • through the connection we have access to the DB and its stored information, including metadata • autocommit is the default – be aware of transactions. Turn off autocommit and commit explicitly. • use DriverManager.getConnection(url) to create a connection • See Java Doc on Connection

  9. Connect to Database try {connection = DriverManager.getConnection (url,usercode,password); System.out.println(“Connected to Database”); // do some db stuff here } catch ( SQLException e ) { e.printStackTrace(); } Postgress URL would start with: jdbc:postgresql:// followed by database. last two arguments are database login: user, password

  10. Database Interaction • All information henceforth is common to all databases. JDBC handles the different databases transparently • Statement used for SQL statements without parameters • PreparedStatement same statement with different explict data values. Precompiled for efficiency with most drivers • CallableStatement used for executing stored functions or procedures • See Java Doc on each of the above

  11. Statement • connection.createStatement() creates a statement for SQL. Statement queryStmt = conn.createStatement(); • statement.executeQuery(SQLstatement as a string); ResultSet result = queryStmt.executeQuery(queryStr);

  12. PeparedStatement • connection.prepareStatement(SQL statement as a String with parameters); PreparedStatement q = conn.prepareStatement(qString); • // set parameters then execute statement • statement.execute(); ResultSet result = q.executeQuery(); • See PrepStatement.java

  13. CallableStatement • connection.prepareCall(“String representing call to a stored procedure/function”); CallableStatement verify = conn.prepareCall( "{ ? = call gord7932.verifyLogin(?,?,?,?,?,?) }" ); • The ? represents parameter in numeric order starting at 1 • See javaCallable.java

  14. ResultSet • public abstract boolean next() • public abstract void close() • public abstract boolean wasNULL() • public abstract XXX getXXX( int column) public abstract XXX getXXX(String columnname) where XXX is a type like String, Int, etc. • ResultSet may be scrollable (navigated in both directions) • See Java Doc and the previous examples

  15. More Connection Methods • public abstract void close() • public abstract void setAutoCommit(boolean val) • public abstract void commit() • public abstract void rollback() • each of these methods throws SQLException

  16. Insert, Delete, Update • use executeUpdate method in either a Statement, PreparedStatement, or CallableStatement

More Related