Thursday, December 16, 2010

Accessing MySql Database

Here System is the mysql database name and table name is Administrator

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import modalclasses.Administrator; //user defined class

public class DBHandler {

    private Connection con;
    private Statement st;
    private ResultSet rs;
    private PreparedStatement ps;

    public Connection getMySqlConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/mysql";
        return DriverManager.getConnection(url, "root", "");
    }

    public void addAdmin(Administrator a) throws SQLException, ClassNotFoundException {
        con = getMySqlConnection();
        ps = con.prepareStatement("insert into System.Administrator values(?,?,?,?,?)");
        ps.setString(1, a.getUserName());
        ps.setString(2, String.valueOf(a.getPassword()));
        ps.setString(3, a.getName());
        ps.setString(4, a.getGender());
        ps.setString(5, a.getEmail());
        ps.executeUpdate();
        con.close();
    }

    public Administrator getAdminDetails(String uname) throws ClassNotFoundException, SQLException {
        con = getMySqlConnection();
        st = con.createStatement();
        rs = st.executeQuery("select * from System.Administrator where Username='" + uname + "'");
        if (!(rs.next())) //if Admin is not found
        {
            return null;
        } else {
            return new Administrator(uname,rs.getString("Password"),rs.getString("Name"),rs.getString("Gender"),rs.getString("Email"));
        }
    }

    public void updateAdmin(Administrator a, String uname) throws ClassNotFoundException, SQLException {
        con = getMySqlConnection();
        ps = con.prepareStatement("update System.Administrator set Username=?,Password=?,Name=?,Gender=?,Email=?" + "where Username='" + uname + "'");
        ps.setString(1, a.getUserName());
        ps.setString(2, String.valueOf(a.getPassword()));
        ps.setString(3, a.getName());
        ps.setString(4, a.getGender());
        ps.setString(5, a.getEmail());
        ps.executeUpdate();
        con.close();
    }

    public void deleteAdmin(String uname) throws ClassNotFoundException, SQLException {
        con = getMySqlConnection();
        ps = con.prepareStatement("delete from System.Administrator where Username='" + uname + "'");
        ps.executeUpdate();
        con.close();
    }
}

No comments:

Post a Comment