Rmi and jdbc
Download
1 / 39

RMI and JDBC - PowerPoint PPT Presentation


  • 90 Views
  • Uploaded on

RMI and JDBC. Some Database terminology Connecting a remote object to a database Gary Alperson helped developed these slides and the JDBC/RMI example. Database Terminology.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'RMI and JDBC' - zona


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Rmi and jdbc
RMI and JDBC

  • Some Database terminology

  • Connecting a remote object to a database

  • Gary Alperson helped developed these slides and the

  • JDBC/RMI example.


Database terminology
Database Terminology

  • Database: A shared collection of logically related data (and a description of this data) designed to meet the information needs of an organization

  • Relation: A table with columns and rows

  • Attribute: A named column of a relation

  • Tuple: A row in a relation

    Definitions from Database Systems

    by Connolly, Begg, and Strachan





SQL

  • Data Definition Language (DDL)

    • Create tables

    • Modify tables

    • Delete (drop) tables

  • Data Manipulation Language (DML)

    • Insert data

    • Update data

    • Select data


Select statement
Select Statement

We will use this data for our examples


From the broker table select the contents of the last name attribute
From the broker table, select the contents of the last name attribute

Query

SELECT lname

FROM broker;

Results

SQL is not case sensitive. Key SQL words are capitalized

and line breaks are inserted by convention.


From the broker table select all attributes
From the broker table, select all attributes attribute

Query

SELECT *

FROM broker;

Results

* Acts as a wildcard


From the broker table select all attributes where the last name is smith
From the broker table, select all attributes where the last name is Smith

Query

SELECT *

FROM broker

WHERE lname = ‘Smith’;

Results

  • Note that the string is enclosed by single quotes

  • The contents of a string are case sensitive


Use and or or to connect multiple where clauses
Use AND or OR to connect multiple where clauses name is Smith

Query

SELECT *

FROM broker

WHERE lname = ‘Smith’

AND fname = ‘John’;

Results


Example with two tables
Example with two Tables name is Smith

  • One-to-many relationship

  • Each broker may have many customers

  • Each customer is only affiliated with one broker

  • The b_id joins both tables by identifying the unique broker that each customer is associated with


Cartesian product
Cartesian Product name is Smith

When you do a query on multiple tables, SQL begins by creating the Cartesian product, which combines each tuple from one relation from every tuple of the other relation.

(Actual SQL implementations

are free to compute the resulting table efficiently,i.e., the actual Cartesian product may not

be generated at all.)


Query name is Smith

SELECT *

FROM customer, broker

WHERE broker.b_id = 1;

Results

SQL does not realize that the b_id in the customer table is the same as the b_id in the broker table unless you join them in the where clause.


Cartesian Product name is Smith

Query

SELECT *

FROM customer, broker

WHERE broker.b_id = 1

AND broker.b_id = customer.b_id;

Results


ODBC name is Smith

ODBC is a programming interface that enables applications to access data in database systems that use Structured Query Language (SQL) as a data standard.


Creating an odbc connection
Creating an ODBC Connection name is Smith

  • Click on the Start button.

  • Choose Settings, Control Panel

  • Double-click on ODBC Data Sources

  • Choose the System DSN tab

  • Click Add




Java s jdbc
Java’s JDBC name is Smith

  • Allows access to any ANSI SQL-2 DBMS

  • Does its work in terms of SQL

  • The JDBC has classes that represent:

  • database connections

  • SQL Statements

  • Result sets

  • database metadata

  • Can be connected to ODBC


Sql query as a java string
SQL Query as a Java String name is Smith

From both tables select the last names of all customers whose broker’s last name is Smith but whose broker ID is not 1.

The SQL

SELECT customer.lname

FROM customer, broker

WHERE broker.lname = ‘Smith’

AND broker.b_id <> 1

AND broker.b_id = customer.b_id;


Executing a query in Java name is Smith

// Statement aStatement = statement got from connection

String last = “Smith”;

int nonID = 1;

String q = “SELECT customer.lname FROM customer, broker” +

“WHERE broker.lname = \’” + last + “\’ AND broker.b_id” +

“<>” + nonID + “AND broker.b_id = customer.b_id;”);

ResultSet rs = aStatement.executeQuery(q);

  • The slash (\) is the escape character. It precedes the single quote to tell Java to include that quote in the String

  • The String last is outside of the double quotes, because it must be concatonated with the String sent to the database, but it falls within the single quotes so that SQL treats it as a string

  • nonID does not go within single quotes since it is numeric

  • Since the String is an SQL statement, it uses = and <> rather than == and !=


Jdbc and rmi
JDBC and RMI name is Smith

  • Create an ODBC data source that will allow

  • us to connect to an Access database (see above)

  • Create a server class that registers an object with

  • the rmi registry.

  • The remote object must have an interface on the

  • client and the server (needed to create the stub for

  • marshalling)

  • The client accesses the remote object and calls its methods.

  • The remote methods access the database with SQL statements.


// On the server side name is SmithDBServer.java

import java.rmi.Naming;

public class DBServer {

public DBServer() {

try {

StockDB rc = new StockDBImpl();

Naming.rebind("stocks", rc);

} catch(Exception e) {

System.out.println("Trouble: " + e);

}

}

public static void main(String args[]) {

new DBServer();

System.out.println("Server Active");

}

}

Remote object

Register

With rmi registry


Provide an interface for the client and the server name is Smith

// On the client and the server StockDB.java

//This is the interface to the remote object SockDBImpl

import java.sql.*;

public interface StockDB extends java.rmi.Remote {

public String getCustomers() throws java.rmi.RemoteException;

public String getStocks() throws java.rmi.RemoteException;

public String getPortfolio() throws java.rmi.RemoteException;

}


The remote object connects to jdbc
The remote object connects to JDBC name is Smith

// DBImpl2.java

// The remote object

import java.util.*;

import java.sql.*;

import java.io.*;

public class StockDBImpl2 extends

java.rmi.server.UnicastRemoteObject

implements StockDB {

private Connection con;

private Statement s;


//The constructor runs the inherited constructor name is Smith

public StockDBImpl2() throws java.rmi.RemoteException {

super();

}

// This private method is called to connect to the database.

private void connectToDB() throws SQLException {

DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());

con = DriverManager.getConnection("jdbc:odbc:stocks");

}

// This private method is called to disconnect from the database.

private void disconnectFromDB() throws SQLException{

con.close();

}


private String getXML(ResultSet rs, ResultSetMetaData rsm) name is Smith

throws SQLException {

// Currently, this method only returns a non-xml String

// representing the rows of the result set

String answer;

answer = "";

while(rs.next()) {

for(int col = 1; col <= rsm.getColumnCount(); col++) {

answer += rs.getString(col);

}

}

return answer;

}


public String getCustomers() name is Smith

throws java.rmi.RemoteException {

try {

connectToDB();

Statement s = con.createStatement();

ResultSet rs;

ResultSetMetaData rsm;

rs = s.executeQuery("select * from customer");

rsm = rs.getMetaData();

String answer = getXML(rs,rsm);

rs.close();

s.close();

disconnectFromDB();

return answer;

}

catch(SQLException sq) {

System.out.println("SQLException: "+sq);

}

return null;

}


public String getStocks() name is Smith

throws java.rmi.RemoteException {

return null;

}

public String getPortfolio()

throws java.rmi.RemoteException {

return null;

}

}


The client makes requests on the remote object
The client makes requests on the remote object name is Smith

// StockDBClient.java

import java.io.*;

import java.rmi.*;

import java.net.*;

import java.sql.*;

public class StockDBClient {


public static StockDB stockDataBase; name is Smith

public static void main(String[] args) {

try {

stockDataBase =

(StockDB)Naming.lookup(

"rmi://mccarthy.heinz.cmu.edu/stocks");

String xml = stockDataBase.getCustomers();

System.out.println(xml);

xml = stockDataBase.getPortfolio();

System.out.println(xml);

xml = stockDataBase.getStocks();

System.out.println(xml);

}


catch (MalformedURLException murle) { name is Smith

System.out.println();

System.out.println("MalformedURLException");

System.out.println(murle);

}

catch (RemoteException re) {

System.out.println();

System.out.println("RemoteException");

System.out.println(re);

}

catch (NotBoundException nbe) {

System.out.println();

System.out.println("NotBoundException");

System.out.println(nbe);

}


catch (java.lang.ArithmeticException ae) { name is Smith

System.out.println();

System.out.println("ArithmeticException");

System.out.println(ae);

}

catch(IOException e) {

System.out.println();

System.out.println("IOException");

System.out.println(e);

}

}

}


Example database schema
Example Database Schema name is Smith

There are three tables. Both customer and stocks have a one-to-many relationship with portfolios


Customer
Customer name is Smith

----------------------------------------------------------------

| id | lname | fname |

----------------------------------------------------------------

| 1 | Jones | Robert |

----------------------------------------------------------------

| 2 | Smith | Elaine |

----------------------------------------------------------------

| 3 | Chan | Jane |

----------------------------------------------------------------

| 4 | Morales | Hector |

----------------------------------------------------------------

| 5 | Schwartz | Michael |

----------------------------------------------------------------


Stocks
Stocks name is Smith

----------------------------------------------------------

| symbol | company | price |

---------------------------------------------------------

| COMS | 3Com Corporation | 12.9375 |

---------------------------------------------------------------------

| IBM | International Business Machines | 96.625 |

---------------------------------------------------------------------

| INTC | Intel Corporation | 34.125 |

---------------------------------------------------------------------

| ORCL | Oracle Corporation | 26.4375 |

---------------------------------------------------------------------

| SE | 7Eleven Inc. | 8.0625 |

---------------------------------------------------------------------


Portfolio
Portfolio name is Smith

----------------------------------------------------------------

| id | symbol | num_shares |

----------------------------------------------------------------

| 1 | COMS | 1250 |

----------------------------------------------------------------

| 1 | INTC | 300 |

----------------------------------------------------------------

| 1 | ORCL | 450 |

----------------------------------------------------------------

| 2 | COMS | 750 |

----------------------------------------------------------------

| 2 | IBM | 900 |


--------------------------------------------------------------------------------------------------------------------------------

| 2 | INTC | 600 |

----------------------------------------------------------------

| 3 | COMS | 3232 |

----------------------------------------------------------------

| 3 | IBM | 1000 |

----------------------------------------------------------------

| 3 | SE | 200 |

----------------------------------------------------------------

| 4 | COMS | 333 |

----------------------------------------------------------------

| 4 | IBM | 250 |

----------------------------------------------------------------

| 4 | INTC | 400 |

----------------------------------------------------------------

| 5 | COMS | 750 |

----------------------------------------------------------------

| 5 | IBM | 250 |

----------------------------------------------------------------

| 5 | ORCL | 1000 |

----------------------------------------------------------------

| 5 | SE | 900 |

----------------------------------------------------------------