1 CS 245 Lecture 9: More On Networking JDBC John Hurley Cal State LA
2 Proxy Server Intermediary server between clients and the actual serverProxy processes request Proxy processes response Intranet proxy may restrict outbound/inbound requests the intranet server Jamie Blustein, Dalhousie Univ.
3 What Does a Proxy Server Do?Between client and server Receives the client request Decides if request will go on to the server May have cache & may respond from cache Acts as the client with respect to the server Uses one of it’s own IP addresses to get page from server Jamie Blustein, Dalhousie Univ.
4 Usual Uses for Proxies Firewalls Employee web use control (email etc.)Web content filtering (kids) Black lists (sites not allowed) White lists (sites allowed) Keyword filtering of page content Jamie Blustein, Dalhousie Univ.
5 User Perspective Proxy is invisible to the clientIP address of proxy is the one used or the browser is configured to go there Speed up retrieval if using caching Can implement profiles or personalization Jamie Blustein, Dalhousie Univ.
6 Web Cache Proxy Not the same as browser cache!Store frequently used pages at proxy rather than request the server to find or create again Why? Reduce latency: faster to get from proxy & so makes the server seem more responsive Reduce traffic: reduces traffic to actual server Jamie Blustein, Dalhousie Univ.
7 Proxy Caches Proxy cache serves hundreds/thousands of usersCorporate and intranets often use Most popular requests are generated only once Good news: Proxy cache hit rates often hit 50% Bad news: Stale content (stock quotes) Jamie Blustein, Dalhousie Univ.
8 Don’t Cache Rules HTTP header Object is authenticated or secureCache-control: max-age=xxx, must-revalidate Expires: date… Last-modified: date… Pragma: no-cache (doesn’t always work!) Object is authenticated or secure Fails proxy filter rules URL Meta data MIME type Contents Jamie Blustein, Dalhousie Univ.
9 Getting From Cache Use cache copy if it is freshWithin date constraint Used recently and modified date is not recent Jamie Blustein, Dalhousie Univ.
10 3. Filtering at the Proxy URL lists (black and white lists) Meta dataContent filters Usual use is to discourage users from getting certain content (eg preventing users from accessing youtube at work) Can be used to enhance privacy by blocking JavaScript, third party content, etc. Privoxy Can be used as ad blocker
11 Net Proxies Web sites that serve as proxies avoid censorshipmake surveillance more difficult Free ones are very slow and may not be trustworthy; paid ones defeat the purpose unless user can pay in some anonymous way.
12 TOR A group of volunteer-operated servers intended to enhance privacy and security on the Internet. To create a private network pathway with Tor, the user's software or client incrementally builds a circuit of encrypted connections through relays on the network. The circuit is extended one hop at a time, and each relay along the way knows only which relay gave it data and which relay it is giving data to. No individual relay ever knows the complete path that a data packet has taken. The client negotiates a separate set of encryption keys for each hop along the circuit to ensure that each hop can't trace these connections as they pass through. See https://www.torproject.org/about/overview.html.en
13 TOR Circumvent censorshipMake surveillance and industrial spying more difficult Use untrusted systems (public wifi, hotel connections) without revealing user information Prevent sites you use from knowing where you are located; avoid locality-based price discrimination or blackouts Interfere with marketers' attempts to correlate different aspects of your internet usage
14 TOR
15 TOR Security Both Edward Snowden and the Electronic Frontier Foundation encourage use of TOR for secure communication. However, TOR was originally created by the US Navy for use by personnel using untrusted networks. It is still funded mostly by the US government, which is strongly opposed to any type of data privacy that it can’t itself break, and which has a record of sabotaging encryption. There are several known attacks that might be effective against TOR: The only publicly known defeat of TOR involved US law enforcement hacking criminal sites and inserting JavaScript code that obtained information from client systems (ie, info on the identity of consumers of illegal content.) A resource-rich entity could provide a large number of volunteer nodes and correlate entrance and exit times, reading the unencrypted info at in entrance and exit points. This would give them a *small sample* of TOR traffic, but that might be enough for many purposes. There are few organizations with sufficient resources for this, but it seems obvious that both the NSA and the Chinese government probably do it.
16 What is JDBC? JDBC provides Java applications with access to most relational database systems via SQL JDBC classes are contained within the java.sql package There are few classes There are several interfaces
17 Database Connectivity HistoryBefore APIs like JDBC and (Windows/C) ODBC, database connectivity was tedious Each database vendor provided a function library for accessing their database The connectivity library was proprietary. Changing DBMSs involved rewriting the data access portions of the application If the application was poorly structured, rewriting its data access might involve rewriting the majority of the application The costs incurred generally meant that application developers were stuck with a particular database product for a given application
18 JDBC Architecture With JDBC, the application programmer uses the JDBC API The developer never uses any proprietary APIs Any proprietary APIs are implemented by a JDBC driver There are 4 types of JDBC Drivers Java Application JDBC API JDBC DriverManager JDBC Driver
19 JDBC Drivers There are 4 types of JDBC DriversType 1 - JDBC-ODBC Bridge Type 2 - JDBC-Native Bridge Type 3 - JDBC-Net Bridge Type 4 - Direct JDBC Driver Type 1 only runs on platforms where ODBC is available ODBC must be configured separately Type 2 Drivers map between a proprietary Database API and the JDBC API Type 3 Drivers are used with middleware products Type 4 Drivers are written in Java In most cases, type 4 drivers are preferred
20 JDBC Classes DriverManager Types Date Time TimeStampManages JDBC Drivers Used to Obtain a connection to a Database Types Defines constants which identify SQL types Date Used to Map between java.util.Date and the SQL DATE type Time Used to Map between java.util.Date and the SQL TIME type TimeStamp Used to Map between java.util.Date and the SQL TIMESTAMP type
21 JDBC Interfaces Driver Connection StatementAll JDBC Drivers must implement the Driver interface. Used to obtain a connection to a specific database type Connection Represents a connection to a specific database Used for creating statements Used for managing database transactions Used for accessing stored procedures Used for creating callable statements Statement Used for executing SQL statements against the database
22 JDBC Interfaces ResultSet PreparedStatement CallableStatementRepresents the result of an SQL statement Provides methods for navigating through the resulting data PreparedStatement Similar to a stored procedure An SQL statement (which can contain parameters) is compiled and stored in the database CallableStatement Used for executing stored procedures DatabaseMetaData Provides access to a database's system catalogue ResultSetMetaData Provides information about the data contained within a ResultSet
23 Using JDBC To execute a statement against a database, the following flow is observed Load the driver (Only performed once) Obtain a Connection to the database (Save for later use) Obtain a Statement object from the Connection Use the Statement object to execute SQL. Updates, inserts and deletes return Boolean. Selects return ResultSets Navigate ResultSet, using data as required Close ResultSet Close Statement Do NOT close the connection The same connection object can be used to create further statements A Connection may only have one active Statement at a time. Do not forget to close the statement when it is no longer needed. Close the connection when you no longer need to access the database
24 Loading Drivers Even a good API can have problemsLoading drivers fits into this category The DriverManager is a singleton Each JDBC Driver is also a singleton When a JDBC Driver class is loaded, it must create an instance of itself and register that instance with the JDBC DriverManager How does one load a "class" into the Virtual machine? Use the static method Class.forName() Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
25 Connecting to a DatabaseOnce a Driver is loaded, a connection can be made to the database The connection is defined by URL The URL has the following form: jdbc:driver:databasename Examples: jdbc:odbc:MyOdbcDatabase jdbc:postgres:WebsiteDatabase jdbc:oracle:CustomerInfo A connection is obtained in the following manner: Connection aConnection = DriverManager.getConnection("jdbc:odbc:myDatabase"); Overloaded versions of the getConnection method allow the specification of a username and password for authentication with the database.
26 Using a Connection The Connection interface defines many methods for managing and using a connection to the database public Statement createStatement() public PreparedStatement prepareStatement(String sql) public void setAutoCommit(boolean) public void commit() public void rollback() public void close() The most commonly used method is createStatement() When an SQL statement is to be issued against the database, a Statement object must be created through the Connection
27 Using a Statement The Statement interface defines methods for executing SQL against the database public ResultSet executeQuery(String sql) public boolean execute(String sql) public int executeUpdate(String sql) executeQuery returns a ResultSet All rows and columns which match the query are contained within the ResultSet The developer navigates through the ResultSet and uses the data as required. executeUpdate returns the number of rows changed by the update statement This is used for insert statements, update statements and delete statements execute returns a boolean, for success or failure
28 Using a ResultSet The ResultSet interface defines many navigation methods public boolean first() public boolean last() public boolean next() public boolean previous() The ResultSet interface also defines data access methods public int getInt(int columnNumber) -- Note: Columns are numbered public int getInt(String columnName) -- from 1 (not 0) public long getLong(int columnNumber) public long getLong(String columnName) public String getString(int columnNumber) public String getString(String columnName) There are MANY more methods. Check the API documentation for a complete list
29 SQL Types/Java Types MappingSQL Type Java Type CHAR String VARCHAR String LONGVARCHAR String NUMERIC java.Math.BigDecimal DECIMAL java.Math.BigDecimal BIT boolean TINYINT int SMALLINT int INTEGER int BIGINT long REAL float FLOAT double DOUBLE double BINARY byte[] VARBINARY byte[] DATE java.sql.Date TIME java.sql.Time TIMESTAMP java.sql.Timestamp
30 Example Code: Connection aConnection; try {Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); } catch(ClassNotFoundException x) System.out.println("Cannot find driver class. Check CLASSPATH"); return; aConnection = DriverManager.getConnection("jdbc:odbc:MyDatabase", "Username", "Password"); catch(SQLException x) System.out.println("Exception connecting to database:" + x);
31 Example Code (continued):try { Statement aStmt = aConnection.createStatement(); StringBuffer sb = new StringBuffer("SELECT Employee_id, Employee_Name"); sb.append(" FROM Employee WHERE EmployeeId>100"); ResultSet rs = aStmt.executeQuery(sb.toString()); while(rs.next()) int employeeId = rs.getInt(1); String employeeName = rs.getString(2); System.out.println("Id:" + employeeId + "\nName:" + employeeName); } rs.close(); aStmt.close(); catch(SQLException x) System.out.println("Exception while executing query:" + x);
32 Monster Attacks Database ClientThis example sets up a local (running on your computer) Java client for a database running on a network server. When you take CS320, you will take this one step farther, setting up a server-side application on a web server instead of a local application
33 Monster Attacks DatabaseMariaDB [(none)]> CREATE DATABASE monster_attacks; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> USE monster_attacks; Database changed MariaDB [monster_attacks]> CREATE TABLE attacks(id int PRIMARY KEY AUTO_INCREMENT, monster_name varchar(30), attack_date date, location varchar(30)); Query OK, 0 rows affected (0.00 sec) MariaDB [monster_attacks]> CREATE USER IDENTIFIED BY 'oopsmymistake'; notes: user does not need a login to the VM, just to mysql. In particular, *DEFINITELY DON'T USE AN ACCOUNT WITH SUDO CAPABILITIES* don’t store code like this in public repositories (eg GitHub) without removing the DB login info! MariaDB [monster_attacks]> GRANT SELECT ON monster_attacks.attacks TO 'drfrankenstein'; MariaDB [monster_attacks]> GRANT INSERT ON monster_attacks.attacks TO 'drfrankenstein'; MariaDB [monster_attacks]> GRANT DELETE ON monster_attacks.attacks TO 'drfrankenstein';
34 Start Project And Get ConnectorStart project in Eclipse Go to download the .zip file and unzip it to a convenient location (on the lab computers, this might need to be on the desktop) Add the connector to the build path for your project: right click on the project name choose "BuildPath/Configure Build Path" choose "add external .jars" locate "mysql-connector-java-[release number]-bin.jar" and choose it
35 Sample GUI Client Using JDBCSee code linked to web page Note, though, that it will not work until you get the connector and add it to the build path for the project in which you use the sample code.
36 Export to Runnable .jar .jar (java archive) files are .zip files containing Java class files and other files needed to run Java applications. They should be runnable on any platform with a JVM. Check the run configuration you are using and remember what it is called. Right click on the project name and choose export/runnable .jar. Choose the right run configuration Choose "package required libraries into generated .jar." Notice the path where the .jar will be placed Get the packaged .jar, move it to another computer and try it out.
37 Final Exam The final exam for this class will consist of two sections:Part 1: multiple choice questions on computer organization, operating systems, and networking Part 2: Two programming questions a) Assembly problem, using a system call for output (see lecture 7) but *without* loops or conditionals b) Shell Script problem, similar to one of the last few lab and midterm problems