Technologies Internet et Education, © TECFA2. Une introduction avec les exemples Coffee-break
# Host: tecfa2 Database: COFFEEBREAK >CREATE TABLE COFFEES ( COF_NAME varchar(32), SUP_ID int(11), PRICE float(10,2), SALES int(11), TOTAL int(11) ); # Insertion de 2 lignes .... INSERT INTO COFFEES VALUES ('Colombian',101,7.99,0,0); INSERT INTO COFFEES VALUES ('Espresso',150,9.99,0,0);
Et qui donne le resultat suivant (avec 'mysqlshow'):
Database: COFFEEBREAK Table: COFFEES Rows: 12 +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | COF_NAME | varchar(32) | | PRI | | | | SUP_ID | int(11) | YES | | | | | PRICE | float(10,2) | YES | | | | | SALES | int(11) | YES | | | | | TOTAL | int(11) | YES | | | | +----------+-------------+------+-----+---------+-------+
try { Class.forName("org.gjt.mm.mysql.Driver"); } catch(Exception ex) { .... return; }
String url = "
jdbc:mysql://tecfa2.unige.ch:3306/COFFEEBREAK
";
String user = "
nobody
";
String password =
null
;
try {
con = DriverManager.getConnection(url, user, password);
}
Statement stmt = con.createStatement() ;
String queryString = " SELECT COF_NAME, PRICE FROM COFFEES";
... stmt. executeQuery (queryString);
String queryString = " INSERT INTO COFFEES values (NULL, 'Vivian coffee', '100')";
... stmt. executeUpdate (queryString);
String queryString = "SELECT COF_NAME, PRICE FROM COFFEES";
ResultSet rs = stmt.executeQuery(queryString);
String updateString = " UPDATE COFFEES SET SALES = 75 WHERE COF_NAME LIKE 'Colombian'";
int updateRowCount = stmt. executeUpdate (updateString);
PreparedStatement prep = con. prepareStatement (
"INSERT into Data values ( ?, ? )");
prep.setString (1, "Jim");
prep.setInt(2, 70);
Par ex: setInt , setFloat , setDouble pour Integer, Float et Double, setString pour char et varchar, et setDate pour dates.
voir: http://tecfa.unige.ch/guides/java/tutorial/jdbc/basics/retrieving.html
getInt , getFloat , getDouble pour Integer, Float et Double,
getString pour char et varchar,
while (rs.next()) {
String s = rs.getString("COF_NAME");
float n = rs.getFloat("PRICE");
System.out.println(s + " " + n);
}
ResultSetMetaData
rsMeta = (ResultSetMetaData) rs.
getMetaData
();
// Get the N of Cols in the ResultSet
int noCols = rsMeta.
getColumnCount
();
// initalize the Vector that holds labels
String [] resColNames = new String [noCols];
for (int c=1; c<=noCols; c++) {
String el = rsMeta.
getColumnLabel
(c);
resColNames[c-1]= el; }
int noCols = rsMeta.
getColumnCount
();
int line=0;
String [][]
resultData = new String [100][noCols];
while (rs.next()) {
for (int c=1; c<=noCols; c++) {
String el = rs.getString(c);
resultData[line][c-1] = el;
}
line++; }
catch(SQLException ex) {
System.err.println("==> SQLException: ");
while (ex != null) {
System.out.println("Message: " + ex.
getMessage
());
System.out.println("SQLState: " + ex.
getSQLState
());
System.out.println("ErrorCode: " + ex.
getErrorCode
());
ex = ex.
getNextException
();
System.out.println("");
}
}
import java.sql.*;
public class
QueryCoffees
{
public static void main(String args[]) {
// ---- configure this for your site
String username = "
nobody
";
String password =
null
;
// The URL that will connect to TECFA's MySQL server
// Syntax: jdbc:TYPE:machine:port/DB_NAME
String url = "jdbc:mysql://tecfa2.unige.ch:3306/COFFEEBREAK";
// A canned query string
String queryString = "
SELECT COF_NAME, PRICE FROM COFFEES
";
// ---- configure END
// INSTALL/load the Driver (Vendor specific Code)
try {
Class.forName("org.gjt.mm.mysql.Driver");
} catch (java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
Connection con;
Statement stmt;
// Connection to the database at URL with usename and password
con = DriverManager.getConnection(url,username,password);
System.out.println ("Ok, connection to the DB worked.");
System.out.println ("Let's see can retrieve something with: " + queryString);
// Create a Statement Object
stmt = con.createStatement();
// Send the query and bind to the result set
ResultSet rs = (ResultSet) stmt.executeQuery(queryString);
while (rs.next()) {
String s = rs.getString("COF_NAME");
float n = rs.getFloat("PRICE");
System.out.println(s + " " + n);
}
// Close resources
stmt.close();
con.close();
}
// print out decent error messages
catch(SQLException ex) {
System.err.println("==> SQLException: ");
while (ex != null) {
System.out.println("Message: " + ex.getMessage ());
System.out.println("SQLState: " + ex.getSQLState ());
System.out.println("ErrorCode: " + ex.getErrorCode ());
ex = ex.getNextException();
System.out.println("");
}
-- TIE