Transcript MySQL & JDCB

MySQL & JDCB
Αλέξανδρος Καρακασίδης
Δεκέμβριος 2004
// Main Class. It also inherits from JFrame to open a window
public class Example1 extends JFrame{
// Connect to the database test, at server localhost
static String url="jdbc:mysql://localhost/test";
// The connection object. This holds all connection information
static Connection con;
…
}
Main Function
Example1(login,pass); // Constructor – Connection
Initialization
app.createTable(); // Creation of the Table in the Database
app.insertData(); // Data Insertion
app.getData(); // Data Selection and Display in a Window
app.dropTable(); // Creation of the Table in the Database
Connection Initialization
public void myinit(String login, String pass){
//Driver to be added at: $JAVA_HOME/jre/lib/ext/
//Driver refers to a .jar file, e.g., mysql-connector-java-3.0.9-stable-bin.jar
// Load the Driver by adding the proper path inside the jar
try {
Class.forName("com.mysql.jdbc.Driver");
//Class.forName("org.gjt.mm.mysql.Driver");
} catch (java.lang.ClassNotFoundException e) {...}
// Connect to the Database
try {
con = DriverManager.getConnection (url, login, pass);
} catch(SQLException ex) {...}
}
public void createTable()
Statement stmt; // Used to send the SQL Query through the connection to the DBMS
// Query to create the table
String query="CREATE TABLE `partsupp` ( PS_PARTKEY` int(11) NOT NULL default
'0',"+
"`PS_SUPPKEY` int(11) NOT NULL default '0', `PS_AVAILQTY` int(11) NOT NULL
default '0',"+
"`PS_SUPPLYCOST` decimal(10,0) NOT NULL default '0',`PS_COMMENT`
varchar(199) NOT NULL default '') COMMENT='TPC-H partsup table'";
try // Connect to the DBMS and send the query
{
stmt = con.createStatement(); // Create the statement
stmt.executeUpdate(query); // Execute UPDATE. CAUTION! It is an update!
stmt.close(); // Close the statement
}
catch(SQLException ex){
System.err.println("SQLException: "+ex.getMessage()); }
public void insertData()
(1/2)
public void insertData()
{
Statement stmt; // Used to send the SQL Query through the
connection to the DBMS
String data= // Data to insert
"2|3|8895|378.49|furiously_even_asymptotes_are_furiously_regular_plate
|\n“ +…. +
"2|939|3025|306.39|deposits_according_to_the_final,_special_foxes_dete
c|\n";
// Separate the rows
StringTokenizer rowt = new StringTokenizer(data,"\n");
…
public void insertData()
(2/2)
int num_rows = rowt.countTokens(); // Count rows
try{
for(int j=0; j<num_rows; j++){
row = rowt.nextToken(); // Take each row
StringTokenizer st = new StringTokenizer(row,"|"); // Separate Fields
for(int i=0; i<5; i++) {mytoken[i] = st.nextToken();} // we have 5 fields
query ="INSERT INTO `partsupp` VALUES ('"+ // Query to insert data
mytoken[0]+"','"+mytoken[1]+"','"+mytoken[2]+"','"+mytoken[3]+"','"+mytoken
[4]+"')";
stmt.executeUpdate(query); // Execute UPDATE. CAUTION! It is an update!
}
stmt.close();
} catch(SQLException ex) {…}}
public void getData()
ResultSet res; // The cursor that holds the result
String query="SELECT PS_AVAILQTY,PS_SUPPLYCOST FROM
`partsupp`";
try {
stmt = con.createStatement();
// Execute QUERY. CAUTION! It is a query returning results!
res = stmt.executeQuery(query);
while( res.next() ) { // Extract each result row and print it on screen
qty = res.getInt("PS_AVAILQTY");
cost = res.getInt("PS_SUPPLYCOST");
System.out.println(qty+":"+cost);
} // End while
stmt.close();
}
catch(SQLException ex) {..}
public void dropTable()
// Can You Explain it?
Statement stmt;
String query="DROP TABLE `partsupp`";
try{
stmt = con.createStatement();
stmt.executeUpdate(query);
stmt.close();
}
catch(SQLException ex) {
System.err.println("SQLException: "+ex.getMessage());
}