JDBC. Preliminaries Database Database Collection of data Collection of data DBMS DBMS Database management system Database management system Stores and.

  • Published on

  • View

  • Download


JDBCPreliminariesDatabaseCollection of dataDBMSDatabase management systemStores and organizes dataSQLRelational databaseStructured Query LanguageRelational DatabaseRelational databaseTableRows, columnsPrimary keyUnique dataSQL statementQuerySQLSQL overviewSQL keywordsSQL keywordDescriptionSELECTRetrieves data from one or more tables.FROMTables involved in the query. Required in every SELECT. WHERECriteria for selection that determine the rows to be retrieved, deleted or updated.GROUP BYCriteria for grouping rows.ORDER BYCriteria for ordering rows.INNER JOINMerge rows from multiple tables.INSERTInsert rows into a specified table.UPDATEUpdate rows in a specified table.DELETEDelete rows from a specified table.Fig. 23.12SQL query keywords.SQL: SELECT QUERYSimplest form of a SELECT querySELECT * FROM tableNameSELECT * FROM authorsSelect specific fields from a tableSELECT authorID, lastName FROM authorsSQL: WHERE CLAUSEspecify the selection criteriaSELECT columnName1, columnName2, FROM tableName WHERE criteriaSELECT title, editionNumber, copyrightFROM titlesWHERE copyright > 2000WHERE clause condition operators, =, =, LIKEwildcard characters % and _SQL: ORDER BY ClauseOptional ORDER BY clauseSELECT columnName1, columnName2, FROM tableName ORDER BY column ASCSELECT columnName1, columnName2, FROM tableName ORDER BY column DESCORDER BY multiple fieldsORDER BY column1 sortingOrder, column2 sortingOrder, Combine the WHERE and ORDER BY clausesSQL: INSERTInsert a row into a tableINSERT INTO tableName ( columnName1, , columnNameN ) VALUES ( value1, , valueN )INSERT INTO authors ( firstName, lastName ) VALUES ( Sue, Smith )SQL: UPDATEModify data in a tableUPDATE tableName SET columnName1 = value1, , columnNameN = valueN WHERE criteriaUPDATE authors SET lastName = Jones WHERE lastName = Smith AND firstName = SueSQL: DELETERemove data from a tableDELETE FROM tableName WHERE criteria DELETE FROM authors WHERE lastName = Jones AND firstName = SueLayers of a DB ApplicationA database application consists of three layersDatabase Management System Application logic (business rules) Presentation logic (interface) Java DB SupportJDBCJava Data Base ConnectivityA set of interfaces to provide consistent API for accessing databases from different vendors Vendors must provide the implementation of these interfaces in order to facilitate a java application to access their databaseJDBCImplemented in java.sql packageProvides set of interfaces to allow access to third party databasesPlatform independentChanges underlying db doesnt cause significant change in the application Also allows access to vendor-specific featuresJDBC DriverJDBC Driver: set of classes that interface with a specific database engine. JDBC drivers exist for every major database including: Oracle, SQL Server, Sybase, and MySQL.JDBC DriverJava ApplicationJDBC Driver ManagerJDBC-ODBC BridgeVendorSpecificJDBC DriverVendorSpecificJDBC DriverDatabaseDatabaseDatabase DriversType 1 - Bridge Drivers Drivers in Java code which connect a Java client to a non-java database service (such as ODBC). Type 2 - Part-Java Drivers (Native)Wraps (possibly existing) native code libraries with Java code to implement JDBC. Type 3 - Network All-Java Drivers (Middleware)All java code which connects to middleware to access a database via a network protocol. This type of driver may be used with applets or servlets. Type 4 - Direct All-Java Drivers (Pure)This provides a pure Java JDBC implementation which can be accessed directly and possibly provide services to networking middleware. JDBC Drivers (Fig.)JDBCType IBridgeType IINativeType IIIMiddlewareType IVPureODBCODBCDriverCLI (.lib)MiddlewareServerJDBC Application ArchitectureJava code calls JDBC libraryJDBC loads a driver Driver talks to a particular databaseCan have more than one driver -> more than one databaseIdeal: can change database engines without changing any application codeApplicationJDBCDriverJDBC InterfacesCallableStatement Connection DatabaseMetaData Driver PreparedStatement ResultSet ResultSetMetaData Statement JDBC ClassesDate DriverManager DriverPropertyInfo Time Timestamp Types JDBCSix step ProcedureLoad the JDBC DriverEstablish the Database ConnectionCreate a Statement ObjectExecute a QueryProcess the ResultsClose the ConnectionJDBC CLASS USAGEDriverManagerDriverConnectionStatementResultSetJDBC: Loading the DriverA simple reference to the driver loads it into the JVMClass.forName(name_of_the_driver)Driver name is a string representing the complete hierarchy of the driver class i.e. the package and class nameFor a JDBC-ODBC DriverClass.forName("sun.jdbc.odbc.JdbcOdbcDriver");Establish the ConnectionStatic method getConnection of Class Driver Manager returns a Connection object. Can throw SQLException Connection con = DriverManager.getConnection(url, user, password ) Connection URLThe only difficulty in establishing a connection is specifying the correct URL.General format of connection URL is jdbc:subprotocol:subname.JDBC indicates that this is a JDBC Connection.The subprotocol identifies the driver to be usedThe subname identifies the database name/location.Connection URLFor example, the following code uses a JDBC-ODBC bridge to connect to the local database db:String url = "jdbc:odbc:db";Connection con = DriverManager.getConnection(url, "cerami", "password");Create a Statement ObjectThe JDBC Statement object sends SQL statements to the database.Statement objects are created from active Connection objects.For example:Statement stat = con.createStatement();SQL calls can then be made on the database through statement objectExecute a QueryexecuteQuery(String query)Executes a select query on the database and returns the selected data in the form of a ResultSet objectThe returned ResultSet object may be empty but cannot be nullThrows SQLExceptionResultSet rs = stat.executeQuery (Select * from table);Execute QueryexecuteUpdate(String query)Executes INSER, UPDATE, DELETE SQL statementsReturns an integer value representing the number of rows affected by this operationThrows SQLExceptionAlso supports DDL statements CREATE TABLE, DROP TABLE, ALTER TABLEint rows = executeUpdate(delete * from table);Process the ResultA ResultSet contains the results of the SQL queryResultSet MethodsAll methods can throw a SQLExceptionClose()Releases the JDBC and database resourcesA ResultSet object is automatically closed when its associated statement object runs a new query.getMetaData()Returns ResultSetMetaData objectThis object contains information about the columns in the ResultSet.ResultSetnext()Attempts to move to the next row of data in the ResultSetReturns true if successful andFalse otherwiseFirst call to the next() method positions the cursor at the first data rowResultSetfindColumn(String)Maps a ResultSet column name to a ResultSet column indexReturns an integer value that corresponds to the specified column nameResultSetgetXXX(String), getXXX(int)Returns the value from the column specified by column name (String version) or column index (int) as an XXX Java typeFirst column index is 1 not 0Returns 0 or null, if the value is a SQL NULLCan call wasNull().Legal getXxx typesdoublebyteintDate Stringfloatshort long Time ObjectClose the ConnectionTo close the database connectionstat.close();con.close()Mapping SQL Types To JAVASQL type Java TypeCHAR, VARCHAR, LONGVARCHARStringNUMERIC, DECIMALjava.math.BigDecimalBITbooleanTINYINTbyteSMALLINTshortINTEGERintBIGINTlongREALfloatFLOAT, DOUBLEdoubleBINARY, VARBINARY, LONGVARBINARYbyte[]DATEjava.sql.DateTIMEjava.sql.TimeTIMESTAMPjava.sql.TimestampTransaction ManagementTransactions are not explicitly opened and closedInstead, the connection has a state called AutoCommit modeif AutoCommit is true, then every statement is automatically committeddefault case: trueTransaction ManagementConnection.setAutoCommit(boolean)if AutoCommit is false, then every statement is added to an ongoing transactionyou must explicitly commit or rollback the transaction using Connection.commit() and Connection.rollback()JDBC Class DiagramStored ProceduresStored proceduresStore SQL statements in a databaseInvoke SQL statements by programs accessing the databaseInterface CallableStatementReceive argumentsOutput parametersMeta DataDatabase/ConnectionDatabaseMetaData: getMetaData();ResultSetResultSetMetaData: getMetaData()Resultset MetadataWhat's the number of columns in the ResultSet? What's a column's name? What's a column's SQL type? What's the column's normal max width in chars? What's the suggested column title for use in printouts and displays? What's a column's number of decimal digits? Does a column's case matter? Will a write on the column definitely succeed? Can you put a NULL in this column? Is a column definitely not writable? Can the column be used in a where clause? Is the column a signed number? Is it possible for a write on the column to succeed? and so on...Database MetadataWhat tables are available?What's our user name as known to the database? Is the database in read-only mode?


View more >