Fajrina Blog Spot

package MyProjectAPI.bean;

public class staff

{

private String _staffId;

private String _staffPassword;

private String _accessLevel;

private String _status;

private String _staffName;

private String _staffIc;

private String _staffPhone;

private String _staffEmail;

public String get_staffId() {

return _staffId;

}

public void set_staffId(String id) {

_staffId = id;

}

public String get_staffPassword() {

return _staffPassword;

}

public void set_staffPassword(String password) {

_staffPassword = password;

}

public String get_accessLevel() {

return _accessLevel;

}

public void set_accessLevel(String level) {

_accessLevel = level;

}

public String get_status() {

return _status;

}

public void set_status(String _status) {

this._status = _status;

}

public String get_staffName() {

return _staffName;

}

public void set_staffName(String name) {

_staffName = name;

}

public String get_staffIc() {

return _staffIc;

}

public void set_staffIc(String ic) {

_staffIc = ic;

}

public String get_staffPhone() {

return _staffPhone;

}

public void set_staffPhone(String phone) {

_staffPhone = phone;

}

public String get_staffEmail() {

return _staffEmail;

}

public void set_staffEmail(String email) {

_staffEmail = email;

}

}

package MyProjectAPI.datasource;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class ConnectDB

{

//driver for JavaDB

private static String _driver ="org.apache.derby.jdbc.EmbeddedDriver";

private static String dbName="MyDB";

private static String connectionURL = "jdbc:derby:" + ConnectDB.dbName + ";create = true";

//function for connect to JavaDB

public static Connection connectDbase() throws ClassNotFoundException, SQLException

{

Class.forName(ConnectDB._driver);

return DriverManager.getConnection(ConnectDB.connectionURL);

}

//function for connect to Ms. Access

/*public static Connection connectDbase() throws ClassNotFoundException, SQLException

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

String dbPath = "C:\\Program Files\\Apache Software Foundation\\Tomcat 6.0\\webapps\\FTMKIS\\DB\\FTMKIS.mdb";

//String dbPath = "C:\\DB\\FTMKIS.mdb";

String myDB = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+dbPath;

return DriverManager.getConnection(myDB,"","");

}*/

//driver for MySQL

/*private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

private static String mysql_host = "localhost";

private static String mysql_user = "root";

private static String mysql_password = "1234";

private static String mysql_db = "ftmkis";

private static final String DATABASE_URL = "jdbc:mysql://localhost/ftmkis";

function for connect to MySQL

public static Connection connectDbase() throws ClassNotFoundException, SQLException

{

Class.forName(ConnectDB.JDBC_DRIVER);

return DriverManager.getConnection(ConnectDB.DATABASE_URL,ConnectDB.mysql_user,ConnectDB.mysql_password);

}*/

}

This is example the engine of code use to create, update, view,delete, list,checkpassword, checkId.

package MyProjectAPI.function;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Vector;

import MyProjectAPI.bean.staff;

import MyProjectAPI.datasource.ConnectDB;

public class staffDB

{

public int checkId(String condition) throws ClassNotFoundException, SQLException

{

Connection conn = ConnectDB.connectDbase();

int updateStatus = 0;

PreparedStatement prep = conn.prepareStatement("SELECT COUNT(_staffId)FROM tbl_staff WHERE _staffId= ?");

prep.setString(1, condition);

ResultSet resultset = prep.executeQuery();

while(resultset.next())

{

updateStatus = resultset.getInt(1);

}

if(conn != null)

conn.close();

return updateStatus;

}

public int checkPassword(String _userId, String _password) throws ClassNotFoundException, SQLException

{

int updateStatus = 0;

String pass = String.valueOf(_password.hashCode());

Connection conn = ConnectDB.connectDbase();

PreparedStatement prep = conn.prepareStatement("SELECT COUNT(_staffId) FROM tbl_staff WHERE _staffId=? AND _staffPassword = ?");

prep.setString(1, _userId);

prep.setString(2, pass);

ResultSet resultset = prep.executeQuery();

while(resultset.next())

{

updateStatus = resultset.getInt(1);

}

if(conn != null)

conn.close();

return updateStatus;

}

public int addStaff(staff Staff)throws SQLException, ClassNotFoundException

{

int updateStatus;

Connection conn = ConnectDB.connectDbase();

PreparedStatement prep = conn.prepareStatement("INSERT INTO tbl_staff(_staffId,_staffPassword,_accessLevel," +

"_status,_staffName,_staffIc,_staffPhone,_staffEmail) VALUES(?,?,?,?,?,?,?,?)");

prep.setString(1, Staff.get_staffId());

prep.setString(2, Staff.get_staffPassword());

prep.setString(3, Staff.get_accessLevel());

prep.setString(4, Staff.get_status());

prep.setString(5, Staff.get_staffName());

prep.setString(6, Staff.get_staffIc());

prep.setString(7, Staff.get_staffPhone());

prep.setString(8, Staff.get_staffEmail());

updateStatus = prep.executeUpdate();

if(conn != null)

conn.close();

return updateStatus;

}

public int updateStaff(staff Staff) throws SQLException, ClassNotFoundException

{

int updateStatus;

Connection conn = ConnectDB.connectDbase();

String state = "UPDATE tbl_staff SET";

state += " _staffPassword = ?,_accessLevel=?,_status=?,_staffName=?,_staffIc=?,_staffPhone=?," +

"_staffEmail=? WHERE _staffId = ?";

PreparedStatement prep = conn.prepareStatement(state);

prep.setString(1, Staff.get_staffId());

prep.setString(2, Staff.get_staffPassword());

prep.setString(3, Staff.get_accessLevel());

prep.setString(4, Staff.get_status());

prep.setString(5, Staff.get_staffName());

prep.setString(6, Staff.get_staffIc());

prep.setString(7, Staff.get_staffPhone());

prep.setString(8, Staff.get_staffEmail());

updateStatus = prep.executeUpdate();

if(conn!=null)

conn.close();

return updateStatus;

}

public int updateAdminStatus(String status, String statusvalue, String Staff) throws SQLException, ClassNotFoundException

{

int updateStatus;

Connection conn = ConnectDB.connectDbase();

String state = "UPDATE tbl_staff SET "+status+" = ? WHERE _staffId = ?";

PreparedStatement prep = conn.prepareStatement(state);

prep.setString(1, statusvalue);

prep.setString(2, Staff);

updateStatus = prep.executeUpdate();

if(conn!=null)

conn.close();

return updateStatus;

}

public int deleteStaff(String Staff) throws ClassNotFoundException, SQLException

{

int updateStatus;

Connection conn = ConnectDB.connectDbase();

PreparedStatement prep = conn.prepareStatement("DELETE FROM tbl_staff WHERE _staffId = ?");

prep.setString(1, Staff);

updateStatus = prep.executeUpdate();

if(conn!=null)

conn.close();

return updateStatus;

}

public int resetPassword(String staffId) throws ClassNotFoundException, SQLException

{

int updateStatus;

String pass = String.valueOf("PaS$w0rd1357".hashCode());

Connection conn = ConnectDB.connectDbase();

PreparedStatement prep = conn.prepareStatement("UPDATE tbl_staff SET _staffPassword = ? WHERE _staffId = ?");

prep.setString(1, pass);

prep.setString(2, staffId);

updateStatus = prep.executeUpdate();

if(conn!=null)

conn.close();

return updateStatus;

}

public int changePassword(String staffId, String password) throws ClassNotFoundException, SQLException

{

int updateStatus;

String pass = String.valueOf(password.hashCode());

Connection conn = ConnectDB.connectDbase();

PreparedStatement prep = conn.prepareStatement("UPDATE tbl_staff SET _staffPassword = ? WHERE _staffId = ?");

prep.setString(1, pass);

prep.setString(2, staffId);

updateStatus = prep.executeUpdate();

if(conn!=null)

conn.close();

return updateStatus;

}

public Vector getStaffList(String condition) throws ClassNotFoundException, SQLException

{

Vector vecStaff = new Vector ();

Connection conn = ConnectDB.connectDbase();

Statement prep = conn.createStatement();

String state = "SELECT * FROM tbl_staff";

if(!condition.equals(null))

state = "SELECT * FROM tbl_staff "+condition;

ResultSet rs = prep.executeQuery(state);

while(rs.next())

{

staff Staff = new staff();

Staff.set_staffId(rs.getString("_staffId"));

Staff.set_staffPassword(rs.getString("_staffPassword"));

Staff.set_accessLevel(rs.getString("_accessLevel"));

Staff.set_status(rs.getString("_status"));

Staff.set_staffName(rs.getString("_staffName"));

Staff.set_staffPhone(rs.getString("_staffPhone"));

Staff.set_staffPhone(rs.getString("_staffPhone"));

Staff.set_staffEmail(rs.getString("_staffEmail"));

vecStaff.add(Staff);

}

if(conn!=null)

conn.close();

return vecStaff;

}

}