Java Tutorial Vol.1 Part 2

I hope you now have established a connection to your MySQL database using DBConnector from the previous tutorial. By the way, here is the database schema in case you haven’t located it on the home page flash widget:

CREATE TABLE `acct_type` (
`acct_id` INT(10) NOT NULL AUTO_INCREMENT,
`acct_desc` VARCHAR(20) NOT NULL,
PRIMARY KEY (`acct_id`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=4

CREATE TABLE `accounts` (
`username` VARCHAR(20) NOT NULL,
`password` VARCHAR(20) NOT NULL,
`acct_type` INT(20) NOT NULL,
PRIMARY KEY (`username`),
INDEX `user_acct_type` (`acct_type`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

CREATE TABLE `team` (
`team_id` INT(10) NOT NULL AUTO_INCREMENT,
`team_name` VARCHAR(10) NOT NULL,
`team_leader` VARCHAR(10) NOT NULL,
PRIMARY KEY (`team_id`),
INDEX `FK1_team_user` (`team_leader`),
CONSTRAINT `FK1_team_user` FOREIGN KEY (`team_leader`) REFERENCES `accounts` 

(`username`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

CREATE TABLE `logs` (
`log_id` INT(10) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(10) NOT NULL,
`log_start` DATETIME NOT NULL,
`log_end` DATETIME NOT NULL,
`log_description` VARCHAR(50) NOT NULL,
PRIMARY KEY (`log_id`),
INDEX `FK1_log_user` (`username`),
CONSTRAINT `FK1_log_user` FOREIGN KEY (`username`) REFERENCES `accounts` (`username`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=4 

Next, we are going to create a class named LoginManager.

In this class, the constructor has a Connection parameter so that the objects of the class would be able to connect to the MySQL database through it. It has a method named login with parameters username and password and which returns an integer (account type).

What is a PreparedStatement?

A PreparedStatement is . It allows you to create dynamic queries. Question marks ? are used as placeholders for values of fields in queries.

Example: PreparedStatement ps = conn.prepareStatement(“SELECT acct_type, password from accounts WHERE username=?”);

You can then set the value of username by using: ps.setString(1, username); username in this case is the parameter passed to the method. The first attribute of setString which is the integer 1 is the ID of the fields in the PreparedStatement.

If there are more unknown fields in the query, example: PreparedStatement ps = conn.prepareStatement(“SELECT acct_type from accounts WHERE username=? and password=?”), then the next statements would be:

ps.setString(1, username);
ps.setString(2, password);

or ps.setString(1, ‘mylene’);
ps.setString(2, ‘thisismypassword’);

A ResultSet object is returned when an executeQuery is performed:
rs = ps.executeQuery();

You can then use the while loop and the next method to access each row in the ResultSet object. However if there is only one row returned by the query, you can skip the while loop.

if (rs != null) {
rs.next();

Next, you can access each field in the row by using methods such as getString and getInt to get Strings and integers correspondingly.

userPassword = rs.getString(“password”);

You can use the name of the field (such as password) to specify which field you want to retrieve or use indexes instead – example, userPassword = rs.getString(2);.