Transcript HowTo-project - School of Computer Science

Carnegie Mellon Univ.
Dept. of Computer Science
15-415 - Database Applications
C. Faloutsos
Guidelines for project
Carnegie Mellon
Overview
•
•
•
•
concepts of SQL programs
walkthrough of HW3_Create.java
walkthrough of showAll.java
(all on the web site)
Carnegie Mellon
15-415 - C. Faloutsos
2
ORACLE
• see the two Java programs:
– HW3_Create.java
– showAll.java
• Concepts
– embedded SQL
– cursor
Carnegie Mellon
15-415 - C. Faloutsos
3
Outline of an SQL application
•
•
•
•
•
establish connection with ORACLE server
authenticate (user/password)
execute SQL statement(s)
process results
close connection
Carnegie Mellon
15-415 - C. Faloutsos
4
Pictorially:
andrew machine
dbclass.intro.cs.cmu.edu
eg., sun4.andrew
JDBC/ODBC
Windows NT box;
HW3_Create.java
with ORACLE Server
HW3_Create.class
Carnegie Mellon
15-415 - C. Faloutsos
5
HW1_Create.java
• Purpose: to load the parent-child table
• No need for you to understand it fully - but
you MUST run it, to load the (parent,
child) table!
legend:
interesting observation
very important point
Carnegie Mellon
15-415 - C. Faloutsos
6
Walk-through HW3_Create.java
import java.io.*;
import java.util.*;
import java.sql.*;
public class HW1_Create {
static final String DbURL =
"jdbc:oracle:thin:@dbclass.intro.cs.cmu.edu:1521:dbintro";
//Oracel server at cs.cmu
static final String OraDriver = "oracle.jdbc.driver.OracleDriver";
//Oracle driver
static final String User = "your-andrew-id";
static final String Passwd = "your-oracle-password";
Carnegie Mellon
15-415 - C. Faloutsos
7
Walk-through HW3_Create.java
static final String Passwd = "your-oracle-password";
static final String fileName="PC.txt";
//file name for text data
public static void main(String[] args) {
Connection con = null;
try {
// Load the Oracle Driver
Class.forName(OraDriver);
// Get a Connection to the database
con = DriverManager.getConnection(DbURL, User,
Passwd);
// Create a Statement object
Statement stmt = con.createStatement();
Carnegie Mellon
15-415 - C. Faloutsos
8
Walk-through HW3_Create.java
// Create a table named as PC (varchar2(10), varchar2(10));
String sqlSt =
"CREATE TABLE PC (parent varchar2(10), child varchar2(10))";
stmt.executeQuery(sqlSt);
Carnegie Mellon
15-415 - C. Faloutsos
9
Walk-through HW3_Create.java
rest of program:
• read input file
• insert one tuple at a time
• close connection
Carnegie Mellon
15-415 - C. Faloutsos
10
Walk-through HW3_Create.java
while ((line = in.readLine()) != null) {
// read in the names into ‘parent’ and ‘child’
// Execute a SQL - insert statement
sqlSt = "INSERT INTO PC (parent, child) VALUES ('"
+ parent + "', '" + child + "')";
System.out.println("===" + (i++) + "===>"
+ sqlSt);
stmt.executeQuery(sqlSt);
}
in.close();
con.commit();
}
Carnegie Mellon
15-415 - C. Faloutsos
11
Overview
• concepts of SQL programs
• walkthrough of HW3_Create.java
• walkthrough of showAll.java
Carnegie Mellon
15-415 - C. Faloutsos
12
Walk-through showAll.java
• purpose: print all (parent, child) pairs
• note: very useful for the project: just
change
– (a) the SQL statements
– (b) the processing of the results
Carnegie Mellon
15-415 - C. Faloutsos
13
Walk-through showAll.java
// after opening the connection …
String sqlSt = "SELECT * FROM PC";
Carnegie Mellon
15-415 - C. Faloutsos
14
Walk-through showAll.java
ResultSet rs = stmt.executeQuery(sqlSt);
while (rs.next()) {
System.out.println( rs.getString("parent") +
";" + rs.getString("child") );
}
Carnegie Mellon
15-415 - C. Faloutsos
15
Conclusions
• concepts of SQL programs
• necessary: HW1_Create.java
• modify showAll.java wrt
– SQL statements
– result processing
Carnegie Mellon
15-415 - C. Faloutsos
16