// -----
public static int insert(UserInfo user) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
// This method adds a new record to the Users table in the database
String query =
"INSERT INTO user_info (firstName, lastName, address, affiliation, "
+ "memDur, username, password, cardType, cardNumber, email) "
+ "VALUES (?, ?, ?, ?, "
+ "?, ?, ?, ?, ?, ?)";
try {
ps = connection.prepareStatement(query);
ps.setString(1, user.getFirstName());
ps.setString(2, user.getLastName());
ps.setString(3, user.getAddress());
ps.setString(4, user.getAffiliation());
ps.setString(5, user.getMemDur());
ps.setString(6, user.getUsername());
ps.setString(7, user.getPassword());
ps.setString(8, user.getCardType());
ps.setString(9, user.getCardNumber());
ps.setString(10, user.getEmail());
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
return 0;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
public static UserInfo selectUser(String username) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String query = "SELECT * FROM user_info " + "WHERE username = ?";
try {
ps = connection.prepareStatement(query);
ps.setString(1, username);
rs = ps.executeQuery();
UserInfo user = null;
if (rs.next()) {
user = new UserInfo();
user.setFirstName(rs.getString("firstName"));
user.setLastName(rs.getString("lastName"));
user.setAddress(rs.getString("address"));
user.setAffiliation(rs.getString("affiliation"));
user.setMemDur(rs.getString("memDur"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setCardType(rs.getString("cardType"));
user.setCardNumber(rs.getString("cardNumber"));
user.setCardNumber(rs.getString("email"));
}
return user;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
public UserInfo getUser(String username) {
UserInfo user = null;
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = connection.prepareStatement("select * from user_info where username=?");
ps.setString(1, username);
rs = ps.executeQuery();
if (rs.next()) {
user = new UserInfo();
user.setFirstName(rs.getString("firstName"));
user.setLastName(rs.getString("lastName"));
user.setAddress(rs.getString("address"));
user.setAffiliation(rs.getString("affiliation"));
user.setMemDur(rs.getString("memDur"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setCardType(rs.getString("cardType"));
user.setCardNumber(rs.getString("cardNumber"));
user.setCardNumber(rs.getString("email"));
}
} catch (Exception e) {
System.out.println(e);
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return user;
}