1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
package oraJdbc;
import java.sql.*;
import java.util.*;
/**
* @author schestag
*
* Diese Klasse enth�lt Beispiele f�r alle wichtigen Statement-Klassen
* der SQL-API auf der Datenbank flugdb des Teil I der Vorlesung
* sowie Beisiele f�r Objekte vom Typ ResultSetMetaData und DatabaseMetaData
*/
public class MyJdbcStatement {
static private Connection con;
static private Statement stmt;
static private String query;
static private PreparedStatement pStmt;
static private CallableStatement cStmt;
static private ResultSet rs;
public static void main(String args[])
throws SQLException {
// 1. Treiber-Klasse f�r JdbcOdbcDriver laden und registrieren
String driverClass =
"sun.jdbc.odbc.JdbcOdbcDriver";
try {
Class.forName(driverClass);
}
catch (ClassNotFoundException exc) {
System.out.println("c1: "+exc.getMessage());
System.exit(1);
}
// Wieviele Elemente sind derzeit bei der Klasse DriverManager
// registriert?
Enumeration e = DriverManager.getDrivers();
while (e.hasMoreElements())
System.out.println("Beim DriverManager registriert: " +
e.nextElement() + "\n");
// 2. Connection aufbauen
try {
String url = "jdbc:odbc:MyORA9";
con = DriverManager.getConnection(url,"ischestag","inge");
System.out.println("Jetzt ist die Connection da");
// Transaktionsmanagement: AutoCommit off
con.setAutoCommit(false);
// Isolationlevel setzen
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
con.commit();
}
catch (SQLException exc) {
System.out.println("c2: "+exc.getMessage());
}
// Database Metadaten
try {
DatabaseMetaData dbm = con.getMetaData();
ResultSet rsm = dbm.getTableTypes();
while (rsm.next())
System.out.println(rsm.getString(1));
} catch(SQLException exc) {
System.out.println("c2a: "+exc.getMessage());
}
// 3. SLQ-Anweisungen ausf�hren
// Javavariablen zum Auslesen der Daten
int vPerNr, vPStd;
try {
stmt =con.createStatement();
// PILOT-Tabelle lesen
rs = stmt.executeQuery("Select per_nr, p_std from pilot");
while (rs.next()) {
vPerNr=rs.getInt(1);
vPStd =rs.getInt(2);
System.out.println("Ergebnis: " + vPerNr + " " + vPStd);
}
ResultSetMetaData rsm = rs.getMetaData();
int anzCol = rsm.getColumnCount();
for (int i=1; i<=anzCol; i++) {
String cn = rsm.getColumnName(i);
String ctn = rsm.getColumnTypeName(i);
System.out.println(cn + " " + ctn);
}
rs.close();
}
catch(SQLException exc) {
System.out.println("c3: "+exc.getMessage());
}
// 4. PreparedStatement
try {
pStmt = con.prepareStatement("insert into flug values (?,?,?,?,?)");
pStmt.setString(1,"LH-999");
pStmt.setString(2,"Frankfurt");
pStmt.setString(3,"Hamburg");
pStmt.setDouble(4,0.5);
pStmt.setInt(5,400);
pStmt.executeUpdate();
con.commit();
} catch(SQLException exc) {
con.rollback();
System.out.println("c4: "+exc.getMessage());
}
// 5. StoredProcedure-Aufruf (CallableStatement)
boolean type;
try {
cStmt = con.prepareCall("{call neugehalt(?,?)}");
cStmt.setDouble(1, 1.01);
cStmt.setDouble(2, 1.02);
// Type == true <=> ResultSet, Type == fals <=> nur Updates
type = cStmt.execute();
con.commit();
while (true) {
System.out.println("so viele Updates: "+cStmt.getUpdateCount());
type = cStmt.getMoreResults();
if (cStmt.getUpdateCount() == -1)
break;
}
} catch(SQLException exc) {
con.rollback();
System.out.println("c5: "+exc.getMessage());
}
// 6. Connection schliessen
try {
con.close();
}
catch(SQLException exc) {
System.out.println("close connection: "+exc.getMessage());
}
}
}
|
