Java Tutorial - Java Scipt :
Creating the Database
For this example, we will use the MySQL database. Make sure that MySQL has been installed according to the instructions in Chapter 8. The following script can be executed to create the database. use oldfriends;
drop table users;
create table users (
userid varchar(36) NOT NULL PRIMARY KEY,
first_name varchar(20),
last_name varchar(20),
mi varchar(2),
maiden_name varchar(20),
grad_class int,
login_id varchar(20) NOT NULL UNIQUE,
password varchar(20) NOT NULL,
email varchar(128) NOT NULL
);
The scripts can be executed either from the MySQL command-line interface or from the MySQL Control Center
Insert into users
(userid, first_name, last_name, mi, maiden_name,
grad_class, login_id, password, email)
values (“0000000-000000-00000000-0-00000000-1”, “John”, “Doe”, “X.”,
“”, 76, “jdoe”,”password”,”jdoe@someaddr.org”);
Insert into users
(userid, first_name, last_name, mi, maiden_name,
grad_class, login_id, password, email)
values (“0000000-000000-00000000-0-00000000-2”, “Jane”, “Doe”, “Y.”,
“Smith”, 76, “janedoe”,”password”,”janedoe@someaddr.org”);
Insert into users
(userid, first_name, last_name, mi, maiden_name,
grad_class, login_id, password, email)
values (“0000000-000000-00000000-0-00000000-3”, “Jack”, “Smith”, “Z.”,
“”, 77, “jsmith”,”password”,”smitty@someaddr.org”);
The table uses a 36-character field as the primary key. This allows us to use Globally Unique Identifiers (GUIDs) for our primary key. While GUIDs are not as simple to use in some cases as integer-based keys, GUIDs work very well in clustered environments where simpler key systems are often challenged. A GUID is guaranteed to be unique no matter which machine it was generated on. In our case, these scripts only need a special value to distinguish our test data from real data inserted into the system. The values entered here are not legitimate GUIDs but they will also never clash with a real GUID. We will discuss GUIDs further later in the chapter. For security reasons, we will also want to create a special user to access the database. We will call the user dbuser, and at this time we will grant all privileges to this user. The following commands create the user and grant the privileges:
This new user is restricted in that it cannot grant privileges to others and it is only valid for connections coming from the same network. You will want to replace ‘passwd’ with an appropriate password for your system. Squirrel or the JdbcTest program from Chapter 8 can be used as a quick test of the JDBC connection to the database. The following command should be entered as one line to run JdbcTest : java -cp mysql-connector-java-2.0.14-bin.jar JdbcTest \ com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/oldfriends \ dbuser passwd Now, we need to configure the EJB container to provide us with a Data- Source for our database.
