joinquerycoffandsupp.java

来自「一个简单的连接数据库的小程序,适合新手学习JAVA用」· Java 代码 · 共 42 行

import java.sql.*;

public class JoinQueryCoffAndSupp {
	public static void main(String args[]) {
		String url = "jdbc:odbc:MyDB";
		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";
		String s1 ="INSERT INTO SUPPLIERS VALUES(101, 'Acme,Inc.','99 Market Street','Groundsville','CA','95199')";
                String s2 ="INSERT INTO SUPPLIERS VALUES(49,'Superior Coffees','1 Party Place','Mendocino','CA','95460')";
                String s3 ="INSERT INTO SUPPLIERS VALUES(150,'The High Gound','100 Coffee Lane','Meadows','CA','93966')";
                Statement stmt;
		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(s1);
			stmt.executeUpdate(s2);
			stmt.executeUpdate(s3);
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				String s = rs.getString("COF_NAME");
				System.out.println("   " + s);
			}
			stmt.close();
			con.close();
		} catch (SQLException ex) {
			System.err.println("SQLException:" + ex.getMessage());
		}
	}
}