fetch datas from PostgreSQL database

Question

I have created two tables in a PostgreSQL database named doctor and department. I have used dept_id as foreign key in the doctor table. Now I have listed the departments in Eclipse but when I'm clicking a department the doctors in the particular table should be listed. So I request you to please provide me code for getting the doctor_list from the doctor table. Can anyone please help me?

Answer 1

Create the tables

create table Department (
dept_id serial primary key, 
dept_name text);

create table Doctor (
id serial primary key, 
name text not null, 
dept_id integer references Department (dept_id)
);

Add the data

insert into department(dept_name) values('Main dept');
insert into department(dept_name) values('Another dept');
insert into doctor(name, dept_id) values('Adam', 1);
insert into doctor(name, dept_id) values('Eva', 2);

Simple query to get list of doctors for a particular department.

select * from doctor where dept_id = 1;

Answer 2

Assuming your using Java (if not, then ignore this answer), and you are making simple java application (not Java EE) you have to firstly make a connection to database. This tutorial will show you how to do it. then you have to use it. Here is explained how to do it (with making connection again).

Modyfying code from second link your could look something like:

private void LoadDoctors(Connection conn, LinkedList listOfBlogs)
  {
    try 
    {
      Statement st = conn.createStatement();
      ResultSet rs = st.executeQuery("SELECT d.id as \"did\",d.name as \"dnme\",dd.id as \"ddid\",dd.name as \"ddname\" FROM doctors d JOIN departments dd ON dd.id=d.dept_id ORDER BY id");
      while ( rs.next() )
      {
        Doctor doc= new Doctor ();
        doc.id        = rs.getInt("did");
        doc.name= rs.getString("dname");
        doc.department= new Department(rs.getInt("ddid"),rs.getInt("ddname"));
        list.add(doc);
      }
      rs.close();
      st.close();
    }
    catch (SQLException se) {
      System.err.println("Threw a SQLException creating the list of blogs.");
      System.err.println(se.getMessage());
    }
  }