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’dJDBCJDBCLoading the driverCreating a connectionProperty fileUsing propertiesStatementExecuting a statementResultSetIteration using ResultSetBetter solutionExampleJDBC IIS 3134.15.2003Why do we have databases?Four perspectives on the database Large data store Persistent data store Query service Transaction serviceProgrammer’s view Not how it works how to administer it how to design a database Database services persistent storage sophisticated querying transactionsJDBC Group of Java classes Correspond to basic database concepts Connecting to the database Issuing a query Examining resultsHigher-level option Enterprise JavaBeans simply state that an object is going to be persistent the EJB “container” uses JDBC to save the object in a database SE 554Database table1103PaulMcCartney999871500JohnLennon999961101AaronPresley343450124ElvisPresley32324175FredFlintstone20203053SuzyQ56782175RobinBurke12341Fuel OptionDaysCar typeFirst nameLast nameIDRow #Key relationships175FredFlintstone2020053SuzyQ5678175RobinBurke1234Fuel OptionDaysCar typeFirst nameLast nameIDSUV5Luxury4Full size3Mid-size2Compact1Subcompact0DescriptionIDBasic terms Table basic unit of organization Row / Record single “entry” Column named attributed of a row View a dynamically created table Query an operation on the database typically retrieval of a view Record set / result set / row set the information returned by a querySQL Declarative language not procedural Describe what you want database figures out “how” In general do as much in SQL as you canQuerySELECT LastName, FirstNameFROM ReservationsSortingSELECT * FROM ReservationsORDER BY Reservations.LastName;ChoosingSELECT * FROM ReservationsWHERE (LastName = ‘Presley’)AND (CarType = 1)SQL Language Case insensitive Strings enclosed in single quotes Whitespace ignored Commands for (creating and structuring databases) retrieving data inserting rows deleting rows Different versions SQL-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.titleFROM titles t1WHERE titles.price >(SELECT AVG(t2.price)FROM titles t2WHERE t1.type = t2.type)Note Dot syntax for columns table.column titles.type Table aliases table alias titles t1JoinSELECT Reservations.ID, Reservations.LastName, Reservations.FirstName, CarType.Description, Reservation.Days, FuelOption.DescriptionFROM Reservations, CarType, FuelOptionWHERE Reservations.CarType = CarType.IDAND Reservations.FuelOption = FuelOption.IDJoin example175RobinBurke1234Fuel OptionDaysCar typeFirst nameLast nameIDPre-paid fuel1DescriptionIDSUV5DescriptionIDPre-paid fuel7SUVRobinBurke1234F.DescriptionR.DaysC.DescriptionR.FirstNameR.LastNameR.IDJoin example cont’d What if?SUV5DescriptionIDSport Ut.5JDBCJDBC DriverManager knows how to connect to differents DBs Connection represents a connection to a particular DB Statement a query or other request made to a DB ResultSet results 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);Example Connection Statement ResultSet Properties / property
View Full Document