Accessing Databases Using JDBC - Java Programming: Comprehensive Concepts and Techniques - page 710

47
48 try

49 {

50 stmt.executeUpdate ( "DROP TABLE Users" ) ;

51 }

52 catch ( Exception e )

53 {

54 System .out.println ( "Could not drop Users table: "

55 + e.getMessage ()) ;

56 }

57
58 try

59 {

60 stmt.executeUpdate ( "DROP TABLE Stocks" ) ;

61 }

62 catch ( Exception e )

63 {

64 System .out.println ( "Could not drop Stocks table: "

65 + e.getMessage ()) ;

66 }

67
68 ///////// Create the database tables /////////////

69 System .out.println ( "\nCreating tables ............" ) ;

70
71

// Create Stocks table with primary key index

72 try

73 {

74 System .out.println ( "Creating Stocks table with primary key index..." ) ;

75 stmt.executeUpdate ( "CREATE TABLE Stocks ("

76 + "symbol TEXT(8) NOT NULL "

77 + "CONSTRAINT PK_Stocks PRIMARY KEY, "

78 + "name TEXT(50)"

79 + ")" ) ;

80 }

81 catch ( Exception e )

82 {

83 System .out.println ( "Exception creating Stocks table: "

84 + e.getMessage ()) ;

85 }

86
87

// Create Users table with primary key index

88 try

89 {

90 System .out.println ( "Creating Users table with primary key index..." ) ;

91 stmt.executeUpdate ( "CREATE TABLE Users ("

92 + "userID TEXT(20) NOT NULL "

93 + "CONSTRAINT PK_Users PRIMARY KEY, "

94 + "lastName TEXT(30) NOT NULL, "

95 + "firstName TEXT(30) NOT NULL, "

96 + "pswd LONGBINARY, "

97 + "admin BIT"

98 + ")" ) ;

99 }

100 catch ( Exception e )

101 {

102 System .out.println ( "Exception creating Users table: "

103 + e.getMessage ()) ;

104 }

105
106

// Create UserStocks table with foreign keys to Users and Stocks tables

107 try

108 {

109 System .out.println ( "Creating UserStocks table ..." ) ;

110 stmt.executeUpdate ( "CREATE TABLE UserStocks ("

111 + "userID TEXT(20) "

112 + "CONSTRAINT FK1_UserStocks REFERENCES Users (userID), "

113 + "symbol TEXT(8), "

114 + "CONSTRAINT FK2_UserStocks FOREIGN KEY (symbol) "

115 + "REFERENCES Stocks (symbol))" ) ;

FIGURE 11-17