1.创建COFFEES表。(源程序CreateCoffees.java)
//引入JDBC类包
import java.sql.*;
public class CreateCoffees {
public static void main(String args[]) {
String url = "jdbc:odbc:MyDB";//建立数据库URL
Connection con;//创建一个连接对象
String createString;//用来创建表的SQL语句
createString = "create table COFFEES " +
"(COF_NAME VARCHAR(32), " +
"SUP_ID INTEGER, " +
"PRICE FLOAT, " +
"SALES INTEGER, " +
"TOTAL INTEGER)";
Statement stmt;//创建Statement对象
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//装载驱动程序
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url);//建立与数据库的连接
stmt = con.createStatement();
stmt.executeUpdate(createString);//执行SQL语句
stmt.close();//关闭Statement对象
con.close();//关闭当前与数据库的连接
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
}
该实例的运行结果就是在MyDB数据库中创建了一个名为COFFEES的表,但是该表是一张空表,没有任何数据。
例5.2 向COFFEES表中添加数据//引入JDBC类包
import java.sql.*;
public class InsertCoffees {
public static void main(String args[]) {
String url = "jdbc:odbc:MyDB";//建立数据库URL
Connection con;//创建一个连接对象
String s="INSERT INTO COFFEES ";
String s1=s+"VALUES ('Colombian', 101, 7.99, 0, 0)";
String s2=s+"VALUES ('French_Roast', 49, 8.99, 0, 0)";
String s3=s+"VALUES ('Espresso', 150, 9.99, 0, 0)";
String s4=s+"VALUES ('Colombian_Decaf', 101, 8.99, 0, 0)";
String s5=s+"VALUES ('French_Roast_Decaf', 49, 9.99, 0, 0)";
Statement stmt;//创建Statement对象
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//装载驱动程序
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url);//建立与数据库的连接
stmt = con.createStatement();
stmt.executeUpdate(s1);
stmt.executeUpdate(s2);
stmt.executeUpdate(s3);
stmt.executeUpdate(s4);
stmt.executeUpdate(s5);
stmt.close();//关闭Statement对象
con.close();//关闭当前与数据库的连接
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
}
该程序的运行结果即生成如表5-1所示的COFFEES表,包含五条记录。
例5.3 查询COFFEES表中的咖啡名称和单价,并显示出来//引入JDBC类包
import java.sql.*;
public class QueryCoffees {
public static void main(String args[]) {
String url = "jdbc:odbc:MyDB";//建立数据库URL
Connection con;//创建一个连接对象
String query = "SELECT COF_NAME, PRICE FROM COFFEES";
Statement stmt;//创建Statement对象
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//装载驱动程序
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url);//建立与数据库的连接
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);//创建ResultSet对象存储查询结果
while (rs.next()) {//使结果集的访问指针往下移动一行
String s = rs.getString("COF_NAME");//获得COF_NAME列的值
float n = rs.getFloat("PRICE");//获得PRICE列的值
System.out.println(s + " " + n);//将结果显示出来
}
stmt.close();//关闭Statement对象
con.close();//关闭当前与数据库的连接
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
}
运行结果:
Colombian 7.99
French_Roast 8.99
Espresso 9.99
Colombian_Decaf 8.99
French_Roast_Decaf 9.99
例5.4 更新COFFEES表中的列值//引入JDBC类包
import java.sql.*;
public class UpdateCoffees {
public static void main(String args[]) {
String url = "jdbc:odbc:MyDB";//建立数据库URL
Connection con;//创建一个连接对象
String updateString1= "UPDATE COFFEES " +
"SET SALES = 75 " +
"WHERE COF_NAME LIKE 'Colombian'";
String updateString2= "UPDATE COFFEES " +
"SET TOTAL = TOTAL + 75 " +
"WHERE COF_NAME LIKE 'Colombian'";
String query = "SELECT COF_NAME, SALES,TOTAL FROM COFFEES " +
"WHERE COF_NAME LIKE 'Colombian'";
Statement stmt;//创建Statement对象
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//装载驱动程序
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url);//建立与数据库的连接
stmt = con.createStatement();
stmt.executeUpdate(updateString1);
stmt.executeUpdate(updateString2);
ResultSet rs = stmt.executeQuery(query);//创建ResultSet对象存储查询结果
while (rs.next()) {//使结果集的访问指针往下移动一行
String s = rs.getString("COF_NAME");//获得COF_NAME列的值
int n = rs.getInt("SALES");
int m = rs.getInt(3);//结果集中第三列的值是TOTAL的值
System.out.println(n + " pounds of " + s + " sold this week.");
System.out.println(m + " pounds of " + s + " sold to date.");
}
stmt.close();//关闭Statement对象
con.close();//关闭当前与数据库的连接
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
}
运行结果:
75 pounds of Colombian sold this week.
75 pounds of Colombian sold to date.
例5.5 本例是一个连接查询的例子
首先程序创建一个供应商信息表SUPPLIERS表,并按照表5-2所示把数据添加到供应商表中。然后把COFFEES表和SUPPLIERS表连接起来,查询“Acme, Inc.”供应商所提供的所有咖啡名称。
//引入JDBC类包
import java.sql.*;
public class JoinQueryCoffAndSupp {
public static void main(String args[]) {
String url = "jdbc:odbc:MyDB";//建立数据库URL
Connection con;//创建一个连接对象
String createSUPPLIERS = "create table SUPPLIERS " +
"(SUP_ID INTEGER, SUP_NAME VARCHAR(40), " +
"STREET VARCHAR(40), CITY VARCHAR(20), " +
"STATE CHAR(2), ZIP CHAR(5))";
String query = "SELECT COFFEES.COF_NAME " +
"FROM COFFEES, SUPPLIERS " +
"WHERE SUPPLIERS.SUP_NAME LIKE 'Acme, Inc.' " +
"and SUPPLIERS.SUP_ID = COFFEES.SUP_ID";
Statement stmt;//创建Statement对象
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//装载驱动程序
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url);//建立与数据库的连接
stmt = con.createStatement();
stmt.executeUpdate(createSUPPLIERS);
stmt.executeUpdate("INSERT INTO SUPPLIERS VALUES (101, " +
"'Acme, Inc.', '99 Market Street', 'Groundsville', " +
"'CA', '95199')");
stmt.executeUpdate("INSERT INTO SUPPLIERS VALUES (49," +
"'Superior Coffee', '1 Party Place', 'Mendocino', 'CA', " + "'95460')");
stmt.executeUpdate("INSERT INTO SUPPLIERS VALUES (150, " +
"'The High Ground', '100 Coffee Lane', 'Meadows', 'CA', " + "'93966')");
ResultSet rs = stmt.executeQuery(query);//创建ResultSet对象存储查询结果
System.out.println("Coffees bought from Acme, Inc.: ");
while (rs.next()) {//使结果集的访问指针往下移动一行
String s = rs.getString("COF_NAME");//获得COF_NAME列的值
System.out.println(" " + s);
}
stmt.close();//关闭Statement对象
con.close();//关闭当前与数据库的连接
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
}