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.
/**
*
* @author Mylene E. SereĂąo
*/
import java.sql.*;
import javax.swing.JOptionPane;
public class LoginManager {
private Connection conn;
public LoginManager(Connection conn) {
this.conn = conn;
}
protected int login(String username, String password) throws SQLException {
ResultSet rs;
String userPassword;
int result = 0;
try {
PreparedStatement ps = conn.prepareStatement(“SELECT acct_type, password from accounts WHERE username=?”);
ps.setString(1, username);
rs = ps.executeQuery();
if (rs != null) {
rs.next();
userPassword = rs.getString(“password”);
if(password.equals(userPassword)) {
result = rs.getInt(“acct_type”);
}
}
}
catch(SQLException sqlEx) {
sqlEx.printStackTrace();
}
return result;
}
protected String getPassword(String username) throws SQLException {
ResultSet rs;
String userPassword= null;
try {
PreparedStatement ps = conn.prepareStatement(“SELECT password from accounts WHERE username=?”);
ps.setString(1, username);
rs = ps.executeQuery();
if (rs != null) {
while(rs.next()){
userPassword = rs.getString(“password”);
}
}
}
catch(SQLException sqlEx) {
sqlEx.printStackTrace();
}
return userPassword;
}
protected String getAcctType(int acctType) throws SQLException {
ResultSet rs;
String type= null;
try {
PreparedStatement ps = conn.prepareStatement(“SELECT acct_desc from acct_type WHERE acct_id=?”);
ps.setInt(1, acctType);
rs = ps.executeQuery();
if (rs != null) {
while(rs.next()){
type = rs.getString(“acct_desc”);
}
}
}
catch(SQLException sqlEx) {
sqlEx.printStackTrace();
}
return type;
}
}
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);.
My Lane 