DOC PREVIEW
DePaul IS 313 - JDBC 1

This preview shows page 1-2-16-17-18-33-34 out of 34 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

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

DePaul IS 313 - JDBC 1

Download JDBC 1
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view JDBC 1 and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view JDBC 1 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?