JDBC IWhy do we have databases?Four perspectives on the databaseProgrammer’s viewJDBCHigher-level optionDatabase tableKey relationshipsBasic termsSQLQuerySortingChoosingSQL LanguageSELECT statementWhole tableSimple criteriaComplex criteriaNoteJoinJoin exampleJoin example cont’dSlide 23Slide 24Loading the driverCreating a connectionProperty fileUsing propertiesStatementExecuting a statementResultSetIteration using ResultSetBetter solutionExampleJDBC IIS 3134.15.2003Why do we have databases?Four perspectives on the databaseLarge data storePersistent data storeQuery serviceTransaction serviceProgrammer’s viewNothow it workshow to administer ithow to design a databaseDatabase servicespersistent storagesophisticated queryingtransactionsJDBCGroup of Java classesCorrespond to basic database conceptsConnecting to the databaseIssuing a queryExamining resultsHigher-level optionEnterprise JavaBeanssimply state that an object is going to be persistentthe EJB “container” uses JDBC to save the object in a databaseSE 554Database table1103PaulMcCartney999871500JohnLennon999961101AaronPresley343450124ElvisPresley32324175FredFlintstone20203053SuzyQ56782175RobinBurke12341Fuel OptionDaysCar typeFirst nameLast nameIDRow #Key relationships175FredFlintstone2020053SuzyQ5678175RobinBurke1234Fuel OptionDaysCar typeFirst nameLast nameIDSUV5Luxury4Full size3Mid-size2Compact1Subcompact0DescriptionIDBasic termsTablebasic unit of organizationRow / Recordsingle “entry”Columnnamed attributed of a rowViewa dynamically created tableQueryan operation on the databasetypically retrieval of a viewRecord set / result set / row setthe information returned by a querySQLDeclarative languagenot proceduralDescribe what you wantdatabase figures out “how”In generaldo as much in SQL as you canQuerySELECT LastName, FirstName FROM ReservationsSortingSELECT * FROM ReservationsORDER BY Reservations.LastName;ChoosingSELECT * FROM ReservationsWHERE (LastName = ‘Presley’) AND (CarType = 1)SQL LanguageCase insensitiveStrings enclosed in single quotesWhitespace ignoredCommands for(creating and structuring databases)retrieving datainserting rowsdeleting rowsDifferent versionsSQL-92 most widely supportedSELECT statementSELECT { columns } FROM { table(s) } WHERE { criteria } ... other options ... ;Whole tableSELECT *FROM ReservationsSimple criteriaSELECT * FROM ReservationsWHERE (LastName = ‘Presley’) AND (CarType = 1)Complex criteriaSELECT titles.type, titles.title FROM titles t1WHERE titles.price > (SELECT AVG(t2.price) FROM titles t2 WHERE t1.type = t2.type)NoteDot syntax for columnstable.columntitles.typeTable aliasestable aliastitles t1JoinSELECT Reservations.ID, Reservations.LastName, Reservations.FirstName, CarType.Description, Reservation.Days, FuelOption.DescriptionFROM Reservations, CarType, FuelOptionWHERE Reservations.CarType = CarType.ID AND Reservations.FuelOption = FuelOption.IDJoin example175RobinBurke1234Fuel OptionDaysCar typeFirst nameLast nameIDPre-paid fuel1DescriptionIDSUV5DescriptionIDPre-paid fuel7SUVRobinBurke1234F.DescriptionR.DaysC.DescriptionR.FirstNameR.LastNameR.IDJoin example cont’dWhat if?SUV5DescriptionIDSport Ut.5JDBCJDBCDriverManagerknows how to connect to differents DBsConnectionrepresents a connection to a particular DBStatementa query or other request made to a DBResultSetresults returned from a queryLoading the driverClass.forName("sun.jdbc.odbc.JdbcOdbcDriver");What is this?Creating a connectionClass.forName("sun.jdbc.odbc.JdbcOdbcDriver");String url = “jdbc:odbc:mydatabase”;Connection conn = DriverManager.getConnection(url, ”UserName", ”Password");Why not “new Connection ()”?Property filejdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriverjdbc.url=jdbc:odbc:rentalcarsjdbc.username=PUBLIC jdbc.password=PUBLICUsing propertiespublic static Connection getConnection() throws SQLException, IOException { Properties props = new Properties(); String fileName = “rentalcars.properties"; FileInputStream in = new FileInputStream(fileName);props.load(in); String drivers = props.getProperty("jdbc.drivers"); if (drivers != null) System.setProperty("jdbc.drivers", drivers); String url = props.getProperty("jdbc.url"); String username = props.getProperty("jdbc.username"); String password = props.getProperty("jdbc.password"); return DriverManager.getConnection(url, username, password);}StatementClass.forName("sun.jdbc.odbc.JdbcOdbcDriver");String url = “jdbc:odbc:mydatabase”;Connection conn = DriverManager.getConnection(url, ”UserName", ”Password");Statement stmt = conn.createStatement();Executing a statementClass.forName("sun.jdbc.odbc.JdbcOdbcDriver");String url = “jdbc:odbc:mydatabase”;Connection conn = DriverManager.getConnection(url, ”UserName", ”Password");Statement stmt = conn.createStatement();String sql = “SELECT * FROM Reservations WHERE (LastName = ‘Burke’);”;stmt.execute (sql);ResultSetClass.forName("sun.jdbc.odbc.JdbcOdbcDriver");String url = “jdbc:odbc:mydatabase”;Connection conn = DriverManager.getConnection(url, ”UserName", ”Password");Statement stmt = conn.createStatement();String sql = “SELECT * FROM Reservations WHERE (LastName = ‘Burke’);”;ResultSet rs = stmt.executeQuery (sql);rs.next();int carType = rs.getInt (“CarType”);Iteration using ResultSetint totalDays = 0;String sql = “SELECT * FROM Reservations;”;ResultSet rs = stmt.executeQuery (sql);while (rs.next()){ totalDays += rs.getInt (“Days”);}Better solutionint totalDays = 0;String sql = “SELECT SUM(Days) FROM Reservations;”;ResultSet rs = stmt.executeQuery (sql);rs.next();totalDays = rs.getInt (1);ExampleConnectionStatementResultSetProperties / property
View Full Document