Første |
|
JDBC serie, Planche nr. 17 |
![]() |
Udskrift af : JDBC |
String url = "jdbc:odbc:Fred"; Connection con = DriverManager.getConnection(url, "Fernanda", "J8");Databasenavn 'fred', bruger 'fernanda' og kodeord 'J8'
SUPPLIER database
COF_NAME
Colombian
French_Roast
Espresso
Colombian_Decaf
French_Roast_Decaf
SUP_ID
Skrevet i Java-kode svarer det til:CREATE TABLE COFFEES (COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, SALES INTEGER, TOTAL INTEGER)
Sendes afsted medString createTableCoffees = "CREATE TABLE COFFEES " + "(COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, " + "SALES INTEGER, TOTAL INTEGER)";
Statement stmt = con.createStatement(); stmt.executeUpdate(createTableCoffees);executeUpdate() bruges med DDL-udtryk
Statement stmt = con.createStatement(); stmt.executeUpdate( "INSERT INTO COFFEES " + "VALUES ('Colombian', 101, 7.99, 0, 0)"); stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('French_Roast', 49, 8.99, 0, 0)"); stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('Espresso', 150, 9.99, 0, 0)"); stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('Colombian_Decaf', 101, 8.99, 0, 0)"); stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('French_Roast_Decaf', 49, 9.99, 0, 0)");
SELECT * FROM COFFEESGiver i et almindeligt SQL-miljø svaret:
COF_NAME SUP_ID PRICE SALES TOTAL --------------- ------ ----- ----- ----- Colombian 101 7.99 0 0 French_Roast 49 8.99 0 0 Espresso 150 9.99 0 0 Colombian_Decaf 101 8.99 0 0 French_Roast_Decaf 49 9.99 0 0
SELECT COF_NAME, PRICE FROM COFFEESVille ligne noget i retning af:
COF_NAME PRICE -------- ---------- ----- Colombian 7.99 French_Roast 8.99 Espresso 9.99 Colombian_Decaf 8.99 French_Roast_Decaf 9.99Følgende udtryk udtrækker kun kaffe, der koster under $9,00 pundet
SELECT COF_NAME, PRICE FROM COFFEES WHERE PRICE < 9.00Resultat
COF_NAME PRICE -------- ------- ----- Colombian 7.99 French_Roast 8.99 Colombian Decaf 8.99
ResultSet rs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");Gå frem, række efter række, via metoden next()
og læs værdierne i en række med getXXX
String query = "SELECT COF_NAME, PRICE FROM COFFEES"; ResultSet rs = stmt.executeQuery(query); while (rs.next()) { String s = rs.getString("COF_NAME"); Float n = rs.getFloat("PRICE"); System.out.println(s + " " + n); }Ventet Javaoutput:
Colombian 7.99 French_Roast 8.99 Espresso 9.99 Colombian_Decaf 8.99 French_Roast_Decaf 9.99
String s = rs.getString("COF_NAME");eller
String s = rs.getString(1); float n = rs.getFloat(2);
|
|||||||||||||||||||
|
|||||||||||||||||||
|
|||||||||||||||||||
|
|||||||||||||||||||
|
|||||||||||||||||||
|
|||||||||||||||||||
|
|||||||||||||||||||
|
|||||||||||||||||||
|
|||||||||||||||||||
|
|||||||||||||||||||
|
|||||||||||||||||||
|
|||||||||||||||||||
|
|||||||||||||||||||
|
|||||||||||||||||||
|
|||||||||||||||||||
|
|||||||||||||||||||
|
An "x" indicates that the
getXXX
method may legally be used to retrieve the given JDBC type.
An "
X
" indicates that the
getXXX
method is recommended for retrieving the given JDBC type.
String updateString = "UPDATE COFFEES " + "SET SALES = 75 " + "WHERE COF_NAME LIKE 'Colombian'"; stmt.executeUpdate(updateString);tabellen COFFEES vil nu se således ud:
COF_NAME SUP_ID PRICE SALES TOTAL -------- ------ ----- ----- ----- Colombian 101 7.99 75 0 French_Roast 49 8.99 0 0 Espresso 150 9.99 0 0 Colombian_Decaf 101 8.99 0 0 French_Roast_Decaf 49 9.99 0 0Vælg den række, der lige er opdateret - hent værdierne i COF_NAME og SALES og udskriv værdierne:
String query = "SELECT COF_NAME, SALES FROM COFFEES " + "WHERE COF_NAME LIKE 'Colombian'"; ResultSet rs = stmt.executeQuery(query); while (rs.next()) { String s = rs.getString("COF_NAME"); int n = rs.getInt("SALES"); System.out.println(n + " pounds of " + s + " sold this week.") }Det skulle give udskriften:
75 pounds of Colombian sold this week.
rs.next(); String s = rs.getString(1); int n = rs.getInt(2); System.out.println(n + " pounds of " + s + " sold this week.")Opdater TOTAL-kolonnen
String updateString = "UPDATE COFFEES " + "SET TOTAL = TOTAL + 75 " + "WHERE COF_NAME LIKE 'Colombian'"; stmt.executeUpdate(updateString); String query = "SELECT COF_NAME, TOTAL FROM COFFEES " + "WHERE COF_NAME LIKE 'Colombian'"; ResultSet rs = stmt.executeQuery(query); while (rs.next()) { String s = rs.getString(1); int n = rs.getInt(2); System.out.println(n + " pounds of " + s + " sold to date.") }Her bruges kolonnenummer-metoden istedet for kolonnenavnet.
Tag dem ind i et nyt katalog JavaOpg10 og ret dem til.