Lara

JDBC

5-2-2017                                                             CRUD dev in core Java by using JDBC

ClientŕManager.java

package com.lara.client;

 

import java.util.ArrayList;

import java.util.List;

import java.util.Scanner;

 

import com.lara.dao.EducationDAO;

import com.lara.dao.EmployeeDAO;

import com.lara.dao.GenderDAO;

import com.lara.dao.SkillDAO;

import com.lara.db.Util;

import com.lara.entity.Address;

import com.lara.entity.Education;

import com.lara.entity.Employee;

import com.lara.entity.Gender;

import com.lara.entity.Skill;

 

public class Manager

{

            public static void main(String[] args)

            {

                        Scanner sc = new Scanner(System.in);

                        System.out.println("welcome to employee management");

                        do

                        {

                                    System.out.println("enter 1 to insert a new employee");

                                    System.out.println("enter 2 to search for an employee");

                                    int action = sc.nextInt();

                                    if(action == 1)

                                    {

                                                saveEmp(sc);

                                    }

                                    else if(action == 2)

                                    {

                                                searchEmp(sc);

                                    }

                                    else

                                    {

                                                System.out.println("invalid option");

                                    }

                                    System.out.println("do you want to do any another action(y/n)?");

                        }while("y".equals(sc.next()));

            }

            static void saveEmp(Scanner sc)

            {

                        System.out.println("Enter Name");

                        String name = sc.next();

                        System.out.println("Enter Age");

                        Integer age = sc.nextInt();

                        System.out.println("Enter House No");

                        String houseNo = sc.next();

                        System.out.println("Enter Street Name");

                        String streetName = sc.next();

                        List<Gender> genders = GenderDAO.readAllGenders();

                        System.out.println("available genders");

                        for(Gender gender : genders)

                        {

                                    System.out.println("enter " + gender.getId() + " for " + gender.getName());

                        }

                        Integer genderId = sc.nextInt();

                       

                        List<Education> educations = EducationDAO.readAllEducations();

                        System.out.println("available educations");

                        for(Education education: educations)

                        {

                                    System.out.println("enter " + education.getId() + " for " + education.getName());

                        }

                        Integer educationId = sc.nextInt();

                       

                        List<Skill> skills = SkillDAO.readAllSkills();

                        System.out.println("available skills");

                        for(Skill skill: skills)

                        {

                                    System.out.println("enter " + skill.getId() + " for " + skill.getName());

                        }

                        List<Integer> skillIds = new ArrayList<>();

                        Integer skillId;

                        do

                        {

                                    System.out.println("enter any one skill id from the above");

                                    skillId = sc.nextInt();

                                    if(! skillIds.contains(skillId))

                                    {

                                                skillIds.add(skillId);

                                    }

                                    else

                                    {

                                                System.out.println(skillId + " already entered");

                                    }

                                    System.out.println("do you want to enter one more (y/n)?");

                        }while("y".equals(sc.next()));

                       

                        Address address = new Address();

                        address.setHouseNo(houseNo);

                        address.setStreetName(streetName);

                       

                        Employee employee = new Employee();

                        employee.setAddress(address);

                       

                        employee.setName(name);

                        employee.setAge(age);

                        employee.setGenderId(genderId);

                        employee.setEducationId(educationId);

                        employee.setSkillIds(skillIds);

                       

                        int status = EmployeeDAO.saveEmployee(employee);

                       

                        if(status == 1)

                        {

                                    System.out.println(name + " saved successfully");

                        }

                        else

                        {

                                    System.out.println("some db error");

                        }

            }

            private static void searchEmp(Scanner sc)

            {

                        System.out.println("enter search keyword");

                        String keyWord = sc.next();

                        List<Employee> list = EmployeeDAO.searchEmployees(keyWord);

                        if(list.size() != 0)

                        {

                                    System.out.println("Serach results for " + keyWord);

                                    System.out.println("ID \t Name \t Age \t Gender \t Education \t Skills");

                                    System.out.println("---------------------------------------------------");

                                    for(Employee emp : list)

                                    {

                                                System.out.print(emp.getId() + "\t" + emp.getName());

                                                System.out.print("\t" + emp.getAge() + "\t");

                                                System.out.print(Util.getName("GENDER", emp.getGenderId()) + "\t");

                                                System.out.print(Util.getName("EDUCATION", emp.getEducationId()) + "\t");                                     

                                                for(Integer id : emp.getSkillIds())

                                                {

                                                            System.out.print(Util.getName("SKILL", id) + ", ");                                                                                    

                                                }

                                                System.out.println();

                                    }

                        }

                        else

                        {

                                    System.out.println("no serach results to : " + keyWord);

                        }

                       

            }

}

daoŕ GenderDAO.java

package com.lara.dao;

 

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

 

import com.lara.db.Util;

import com.lara.entity.Gender;

 

public class GenderDAO

{

            public static List<Gender> readAllGenders()

            {

                        List<Gender> genders = new ArrayList<>();

                        try(Connection con = Util.getConnection();

                                    Statement stmt = con.createStatement();

                                    ResultSet rs = stmt.executeQuery("SELECT ID, NAME FROM GENDER"))

                        {

                                    Gender gender;

                                    while(rs.next())

                                    {

                                                gender = new Gender();

                                                gender.setId(rs.getInt("ID"));

                                                gender.setName(rs.getString("NAME"));

                                                genders.add(gender);

                                    }

                        }

                        catch(SQLException ex)

                        {

                                    ex.printStackTrace();

                        }

                        return genders;

            }

}

daoŕ EducationDAO.java

package com.lara.dao;

 

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

 

import com.lara.db.Util;

import com.lara.entity.Education;

 

 

public class EducationDAO

{

            public static List<Education> readAllEducations()

            {

                        List<Education> educations = new ArrayList<>();

                        try(Connection con = Util.getConnection();

                                    Statement stmt = con.createStatement();

                                    ResultSet rs = stmt.executeQuery("SELECT ID, NAME FROM EDUCATION"))

                        {

                                    Education education;

                                    while(rs.next())

                                    {

                                                education = new Education();

                                                education.setId(rs.getInt("ID"));

                                                education.setName(rs.getString("NAME"));

                                                educations.add(education);

                                    }

                        }

                        catch(SQLException ex)

                        {

                                    ex.printStackTrace();

                        }

                        return educations;

            }

}

daoŕ SkillDAO.java

package com.lara.dao;

 

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

 

import com.lara.db.Util;

import com.lara.entity.Skill;

 

public class SkillDAO

{

            public static List<Skill> readAllSkills()

            {

                        List<Skill> skills = new ArrayList<>();

                        try(Connection con = Util.getConnection();

                                    Statement stmt = con.createStatement();

                                    ResultSet rs = stmt.executeQuery("SELECT ID, NAME FROM SKILL"))

                        {

                                    Skill skill;

                                    while(rs.next())

                                    {

                                                skill = new Skill();

                                                skill.setId(rs.getInt("ID"));

                                                skill.setName(rs.getString("NAME"));

                                                skills.add(skill);

                                    }

                        }

                        catch(SQLException ex)

                        {

                                    ex.printStackTrace();

                        }

                        return skills;

            }

}

daoŕ AddressDAO.java

package com.lara.dao;

 

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Statement;

 

import com.lara.db.Util;

import com.lara.entity.Address;

 

public class AddressDAO

{

            public static int saveAddress(Address address)

            {

                        int status = 0;

                        StringBuffer sql = new StringBuffer();

                        sql.append("INSERT INTO ADDRESS(HOUSE_NO, STREET_NAME, EMP_ID) ");

                        sql.append("VALUES ");

                        sql.append("('" + address.getHouseNo() + "', ");

                        sql.append("'" + address.getStreetName() + "', ");

                        sql.append(address.getEmpId() + ")");

                        try(Connection con = Util.getConnection();

                                    Statement stmt = con.createStatement())

                        {

                                    status = stmt.executeUpdate(sql.toString());

                        }

                        catch(SQLException ex)

                        {

                                    ex.printStackTrace();

                        }

                        return status;

            }

}

daoŕ EmployeeDAO.java

package com.lara.dao;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

 

import com.lara.db.Util;

import com.lara.entity.Address;

import com.lara.entity.Employee;

 

public class EmployeeDAO

{

            public static int saveEmployee(Employee emp)

            {

                        int status = 0;

                       

                        StringBuffer sql1 = new StringBuffer();

                        sql1.append("INSERT INTO EMPLOYEE(ID, NAME, AGE, GENDER_ID, EDUCATION_ID) ");

                        sql1.append("VALUES ");

                        sql1.append("(EMP_SEQ.NEXTVAL, '" + emp.getName() + "', ");

                        sql1.append(emp.getAge() + ", " + emp.getGenderId() + ", ");

                        sql1.append(emp.getEducationId() + ")");

                       

                        String sql2 = "SELECT MAX(ID) MAX_ID FROM EMPLOYEE";

                        String sql3 = "INSERT INTO EMP_SKILL(EMP_ID, SKILL_ID) VALUES (?, ?)";

                       

                        try(Connection con = Util.getConnection();

                                    Statement stmt1 = con.createStatement();

                                    Statement stmt2 = con.createStatement();

                                    PreparedStatement pstmt = con.prepareStatement(sql3))

                        {

                                    status = stmt1.executeUpdate(sql1.toString());

                                    ResultSet rs = stmt2.executeQuery(sql2);

                                    int empId = 0;

                                    if(rs.next())

                                    {

                                                empId = rs.getInt("MAX_ID");

                                    }

                                    for(Integer skillId : emp.getSkillIds())

                                    {

                                                pstmt.setInt(1, empId);

                                                pstmt.setInt(2, skillId);

                                                pstmt.executeUpdate();

                                    }

                                    Address address = emp.getAddress();

                                    address.setEmpId(empId);

                                    AddressDAO.saveAddress(address);

                        }

                        catch(SQLException ex)

                        {

                                    ex.printStackTrace();

                        }

                        return status;

            }

            public static List<Employee> searchEmployees(String keyWord)

            {

                        List<Employee> list = new ArrayList<>();

                        StringBuffer sql = new StringBuffer();

                        sql.append("SELECT * FROM EMPLOYEE WHERE NAME LIKE '%" + keyWord + "%' OR " );

                        try

                        {

                                    int i = Integer.parseInt(keyWord);

                                    sql.append("ID = " + keyWord + " OR ");

                                    sql.append("AGE = " + keyWord + " OR ");

                        }

                        catch(NumberFormatException ex)

                        {

                                    //ex.printStackTrace();

                        }

                        sql.append("GENDER_ID IN (SELECT ID FROM GENDER WHERE NAME LIKE '%" + keyWord + "%') OR ");

                        sql.append("EDUCATION_ID IN (SELECT ID FROM EDUCATION WHERE NAME LIKE '%" + keyWord + "%') OR ");

                        sql.append("ID IN (SELECT EMP_ID FROM EMP_SKILL WHERE SKILL_ID IN (SELECT ID FROM SKILL WHERE NAME LIKE '%" + keyWord + "%'))");

                        String sql1 = "SELECT SKILL_ID FROM EMP_SKILL WHERE EMP_ID = ?";

                        System.out.println(sql);

                        try(Connection con = Util.getConnection();

                                    Statement stmt = con.createStatement();

                                    ResultSet rs = stmt.executeQuery(sql.toString());

                                    PreparedStatement pstmt = con.prepareStatement(sql1))

                        {

                                    Employee emp = null;

                                    List<Integer> skills = null;

                                    while(rs.next())

                                    {

                                                emp = new Employee();

                                                skills = new ArrayList<>();

                                                emp.setSkillIds(skills);

                                                emp.setId(rs.getInt("ID"));

                                                emp.setName(rs.getString("NAME"));

                                                emp.setAge(rs.getInt("AGE"));

                                                emp.setGenderId(rs.getInt("GENDER_ID"));

                                                emp.setEducationId(rs.getInt("EDUCATION_ID"));

                                                pstmt.setInt(1, emp.getId());

                                                ResultSet rs1 = pstmt.executeQuery();

                                                while(rs1.next())

                                                {

                                                            skills.add(rs1.getInt("SKILL_ID"));

                                                }

                                                list.add(emp);

                                    }

                        }

                        catch(SQLException ex)

                        {

                                    ex.printStackTrace();

                        }          

                        return list;

            }

}

dbŕUtil.java

package com.lara.db;

 

import java.io.FileReader;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Properties;

 

public class Util

{

            private static final String DRIVER_CLASS_NAME;

            private static final String URL;

            private static final String USERNAME;

            private static final String PASSWORD;

           

            static

            {

                        Properties pr = new Properties();

                        try(FileReader fin = new FileReader("db-constants.properties"))

                        {

                                    pr.load(fin);

                        }

                        catch(IOException ex)

                        {

                                    ex.printStackTrace();

                        }

                        DRIVER_CLASS_NAME = pr.getProperty("driverClassName");

                        URL = pr.getProperty("url");

                        USERNAME = pr.getProperty("username");

                        PASSWORD = pr.getProperty("password");

            }          

            static

            {

                        try

                        {

                                    Class.forName(DRIVER_CLASS_NAME);

                        }

                        catch(ClassNotFoundException ex)

                        {

                                    ex.printStackTrace();

                        }

            }

            public static Connection getConnection()

            throws SQLException

            {

                        return DriverManager.getConnection(URL, USERNAME, PASSWORD);

            }

            public static String getName(String tableName, Integer id)

            {

                        String sql = "SELECT NAME FROM " + tableName + " WHERE ID = " + id;

                        String name = null;

                        try(Connection con = Util.getConnection();

                                    Statement stmt = con.createStatement();

                                    ResultSet rs = stmt.executeQuery(sql))

                        {

                                    if(rs.next())

                                    {

                                                name = rs.getString("NAME");

                                    }

                        }

                        catch(SQLException ex)

                        {

                                    ex.printStackTrace();

                        }

                       

                        return name;

            }

}

entityŕ Gender.java

package com.lara.entity;

 

public class Gender

{

            private Integer id;

            private String name;

            public Integer getId()

            {

                        return id;

            }

            public void setId(Integer id)

            {

                        this.id = id;

            }

            public String getName()

            {

                        return name;

            }

            public void setName(String name)

            {

                        this.name = name;

            }

           

}

entityŕ Education.java

package com.lara.entity;

 

public class Education

{

            private Integer id;

            private String name;

            public Integer getId()

            {

                        return id;

            }

            public void setId(Integer id)

            {

                        this.id = id;

            }

            public String getName()

            {

                        return name;

            }

            public void setName(String name)

            {

                        this.name = name;

            }

}

entityŕ Skill.java

 package com.lara.entity;

 

public class Skill

{

            private Integer id;

            private String name;

            public Integer getId()

            {

                        return id;

            }

            public void setId(Integer id)

            {

                        this.id = id;

            }

            public String getName()

            {

                        return name;

            }

            public void setName(String name)

            {

                        this.name = name;

            }

}

entityŕ Address.java

package com.lara.entity;

 

public class Address

{

            private String houseNo;

            private String streetName;

            private Integer empId;

            public String getHouseNo()

            {

                        return houseNo;

            }

            public void setHouseNo(String houseNo)

            {

                        this.houseNo = houseNo;

            }

            public String getStreetName()

            {

                        return streetName;

            }

            public void setStreetName(String streetName)

            {

                        this.streetName = streetName;

            }

            public Integer getEmpId()

            {

                        return empId;

            }

            public void setEmpId(Integer empId)

            {

                        this.empId = empId;

            }

}

entityŕ Employee.java

package com.lara.entity;

 

import java.util.List;

 

public class Employee

{

            private Integer id;

            private String name;

            private Integer age;

            private Integer genderId;

            private Integer educationId;

            private List<Integer> skillIds;

            private Address address;

            public Integer getId()

            {

                        return id;

            }

            public void setId(Integer id)

            {

                        this.id = id;

            }

            public String getName()

            {

                        return name;

            }

            public void setName(String name)

            {

                        this.name = name;

            }

            public Integer getAge()

            {

                        return age;

            }

            public void setAge(Integer age)

            {

                        this.age = age;

            }

            public Integer getGenderId()

            {

                        return genderId;

            }

            public void setGenderId(Integer genderId)

            {

                        this.genderId = genderId;

            }

            public Integer getEducationId()

            {

                        return educationId;

            }

            public void setEducationId(Integer educationId)

            {

                        this.educationId = educationId;

            }

            public List<Integer> getSkillIds()

            {

                        return skillIds;

            }

            public void setSkillIds(List<Integer> skillIds)

            {

                        this.skillIds = skillIds;

            }

            public Address getAddress()

            {

                        return address;

            }

            public void setAddress(Address address)

            {

                        this.address = address;

            }

           

}

testŕ TestGenderDAO.java

package com.lara.test;

 

import java.util.List;

 

import com.lara.dao.GenderDAO;

import com.lara.entity.Gender;

 

public class TestGenderDAO

{

            public static void main(String[] args)

            {

                        List<Gender> genders = GenderDAO.readAllGenders();

                        for(Gender gender : genders)

                        {

                                    System.out.println(gender.getId() + ":" + gender.getName());

                        }

            }

}

test--TestEducationDAO.java

package com.lara.test;

 

import java.util.List;

 

import com.lara.dao.EducationDAO;

import com.lara.entity.Education;

 

public class TestEducationDAO

{

            public static void main(String[] args)

            {

                        List<Education> educations = EducationDAO.readAllEducations();

                        for(Education education : educations)

                        {

                                    System.out.println(education.getId() + ":" + education.getName());

                        }

            }

}

test-TestSkillDAO.java

package com.lara.test;

 

import java.util.List;

 

import com.lara.dao.SkillDAO;

import com.lara.entity.Skill;

 

public class TestSkillDAO

{

            public static void main(String[] args)

            {

                        List<Skill> list = SkillDAO.readAllSkills();

                        for(Skill skill : list)

                        {

                                    System.out.println(skill.getId() + ":" + skill.getName());

                        }

            }

}

test- TestEmployeeDAO.java

package com.lara.test;

 

import java.util.Arrays;

 

import com.lara.dao.EmployeeDAO;

import com.lara.entity.Address;

import com.lara.entity.Employee;

 

public class TestEmployeeDAO

{

            public static void main(String[] args)

            {

                        Address address = new Address();

                        address.setHouseNo("123/B");

                        address.setStreetName("BTM");

                       

                        Employee emp = new Employee();

                        emp.setName("Ramu");

                        emp.setAge(22);

                        emp.setGenderId(1);

                        emp.setEducationId(2);

                        emp.setSkillIds(Arrays.asList(1, 2, 3));

                        emp.setAddress(address);

                       

                        int status = EmployeeDAO.saveEmployee(emp);

                        System.out.println(status);

                       

            }

}

db-constants.properties

driverClassName=oracle.jdbc.driver.OracleDriver

url=jdbc:oracle:thin:@localhost:1521:XE

username=system

password=great123

 

sql

DROP TABLE GENDER CASCADE CONSTRAINTS;

CREATE TABLE GENDER (ID NUMBER UNIQUE, NAME VARCHAR2(20));

INSERT INTO GENDER VALUES(1, 'MALE');

INSERT INTO GENDER VALUES(2, 'FEMALE');

 

DROP TABLE EDUCATION CASCADE CONSTRAINTS;

CREATE TABLE EDUCATION (ID NUMBER UNIQUE, NAME VARCHAR2(20));

INSERT INTO EDUCATION VALUES(1, 'BE');

INSERT INTO EDUCATION VALUES(2, 'MCA');

INSERT INTO EDUCATION VALUES(3, 'ME');

 

DROP TABLE SKILL CASCADE CONSTRAINTS;

CREATE TABLE SKILL (ID NUMBER UNIQUE, NAME VARCHAR2(20));

INSERT INTO SKILL VALUES(1, 'C');

INSERT INTO SKILL VALUES(2, 'C++');

INSERT INTO SKILL VALUES(3, 'JAVA');

 

DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;

CREATE TABLE EMPLOYEE (ID NUMBER UNIQUE,

                               NAME VARCHAR2(50),

                               AGE NUMBER,

                               GENDER_ID NUMBER,

                               EDUCATION_ID NUMBER,

CONSTRAINT EMPFK1 FOREIGN KEY(GENDER_ID) REFERENCES GENDER(ID),

CONSTRAINT EMPFK2 FOREIGN KEY(EDUCATION_ID) REFERENCES EDUCATION(ID));

DROP TABLE ADDRESS CASCADE CONSTRAINTS;

CREATE TABLE ADDRESS(HOUSE_NO VARCHAR2(20),

                             STREET_NAME VARCHAR2(20),

                             EMP_ID NUMBER UNIQUE,

CONSTRAINT ADDFK1 FOREIGN KEY(EMP_ID) REFERENCES EMPLOYEE(ID));

 

DROP TABLE EMP_SKILL CASCADE CONSTRAINTS;

CREATE TABLE EMP_SKILL(EMP_ID NUMBER,

                               SKILL_ID NUMBER,

CONSTRAINT ESFK1 FOREIGN KEY(EMP_ID) REFERENCES EMPLOYEE(ID),

CONSTRAINT ESFK2 FOREIGN KEY(SKILL_ID) REFERENCES SKILL(ID));

 

CREATE SEQUENCE EMP_SEQ;

 

 

 

 ----------------------------

Saturday (18-02-2017) and Sunday(19-02-2017)

client=Manager.java

   package com.lara.client;

 

import java.util.ArrayList;

import java.util.List;

import java.util.Scanner;

 

import com.lara.db.dao.EducationDAO;

import com.lara.db.dao.GenderDAO;

import com.lara.db.dao.SkillDAO;

import com.lara.db.dao.StudentDAO;

import com.lara.entity.Address;

import com.lara.entity.Education;

import com.lara.entity.Gender;

import com.lara.entity.Skill;

import com.lara.entity.Student;

 

public class Manager

{

            public static void main(String[] args)

            {

                        Scanner sc = new Scanner(System.in);

                        do

                        {

                                    System.out.println("ENTER 1 FOR INSERTION");

                                    System.out.println("ENTER 2 FOR SEARCH");

                                    int action = sc.nextInt();

                                    if(action == 1)

                                    {

                                                insert(sc);

                                    }

                                    else if(action == 2)

                                    {

                                                search(sc);

                                    }

                                    else

                                    {

                                                System.out.println("INVALID OPTION");

                                    }

                                    System.out.println("YOU WANT TO CONTINUE(Y/N)?");

                        }while("y".equalsIgnoreCase(sc.next()));

            }

            private static void insert(Scanner sc)

            {

                        Student student = new Student();

                        Address address = new Address();

                        student.setAddress(address);

                       

                        System.out.println("ENTER NAME");

                        student.setName(sc.next());

                       

                        System.out.println("ENTER AGE");

                        student.setAge(sc.nextInt());

                       

                        System.out.println("ENTER EMAIL");

                        student.setEmail(sc.next());

                       

                        List<Gender> genders = GenderDAO.readGenders();

                        for(Gender gender : genders)

                        {

                                    System.out.println("ENTER " + gender.getId() + " FOR " + gender.getName());

                        }

                        Gender gender = new Gender();

                        gender.setId(sc.nextInt());

                        student.setGender(gender);

                        //student.getGender().setId(sc.nextInt());

                       

                        List<Education> educations = EducationDAO.readEducations();

                        for(Education education : educations)

                        {

                                    System.out.println("ENTER " + education.getId() + " FOR " + education.getName());

                        }

                        Education education = new Education();

                        education.setId(sc.nextInt());

                        student.setEducation(education);

                       

                        List<Skill> skills = SkillDAO.readSkills();

                        for(Skill skill : skills)

                        {

                                    System.out.println("ENTER " + skill.getId() + " FOR " + skill.getName());

                        }

                        ArrayList<Skill> selectedSkills = new ArrayList<>();

                        Skill skill = null;

                        do

                        {

                                    skill = new Skill();

                                    skill.setId(sc.nextInt());

                                    selectedSkills.add(skill);

                                    System.out.println("YOU WANT TO ENTER ONE MORE SKILL(Y/N)?");

                        }while("y".equalsIgnoreCase(sc.next()));

                        student.setSkills(selectedSkills);

                       

                        System.out.println("ENTER HOUSE NO");

                        address.setHouseNo(sc.next());

                       

                        System.out.println("ENTER STREET NAME");

                        address.setStreetName(sc.next());

                       

                        int status = StudentDAO.saveStudent(student);

                        if(status == 1)

                        {

                                    System.out.println(student.getName() + " SAVED SUCCESSFULLY");

                        }

                        else

                        {

                                    System.out.println("some db error");

                        }                     

            }

            private static void search(Scanner sc)

            {

                        System.out.println("Enter Search Keyword");

                        String keyWord = sc.next();

                        List<Student> students = StudentDAO.search(keyWord);

                        if(students.size() == 0)

                        {

                                    System.out.println("No students with search key :" + keyWord);

                                    return;

                        }

                        System.out.println("serach results:" + students.size() + " students found");

                        System.out.println("------------------------------------------------------");

                        for(Student student : students)

                        {

                                    System.out.print(student.getId() + "\t");

                                    System.out.print(student.getName() + "\t");

                                    System.out.print(student.getAge() + "\t");

                                    System.out.print(student.getEmail() + "\t");

                                    System.out.print(student.getGender().getName() + "\t");

                                    System.out.print(student.getEducation().getName() + "\t");

                                    System.out.print(student.getAddress().getHouseNo() + "\t");

                                    System.out.print(student.getAddress().getStreetName() + "\t");

                                    for(Skill skill : student.getSkills())

                                    {

                                                System.out.print(skill.getName() + "\t");

                                    }

                                    System.out.println();

                        }

            }         

}    

 

db—dao--EducationDAO.java

package com.lara.db.dao;

 

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

 

import com.lara.db.util.ConnectionFactory;

import com.lara.entity.Education;

import com.lara.entity.Gender;

 

public class EducationDAO

{

            public static List<Education> readEducations()

            {

                        List<Education> educations = new ArrayList<>();

                        try(Connection con = ConnectionFactory.getConnection();

                                    Statement stmt = con.createStatement();

                                    ResultSet rs = stmt.executeQuery("SELECT ID, NAME FROM EDUCATION"))

                        {

                                    Education education;

                                    while(rs.next())

                                    {

                                                education = new Education();

                                                education.setId(rs.getInt("ID"));

                                                education.setName(rs.getString("NAME"));

                                                educations.add(education);

                                    }

                        }

                        catch(SQLException ex)

                        {

                                    ex.printStackTrace();

                        }                      

                        return educations;

            }

}

db—dao-GenderDAO.java

package com.lara.db.dao;

 

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

 

import com.lara.db.util.ConnectionFactory;

import com.lara.entity.Gender;

 

public class GenderDAO

{

            public static List<Gender> readGenders()

            {

                        List<Gender> genders = new ArrayList<>();

                        try(Connection con = ConnectionFactory.getConnection();

                                    Statement stmt = con.createStatement();

                                    ResultSet rs = stmt.executeQuery("SELECT ID, NAME FROM GENDER"))

                        {

                                    Gender gender;

                                    while(rs.next())

                                    {

                                                gender = new Gender();

                                                gender.setId(rs.getInt("ID"));

                                                gender.setName(rs.getString("NAME"));

                                                genders.add(gender);

                                    }

                        }

                        catch(SQLException ex)

                        {

                                    ex.printStackTrace();

                        }                      

                        return genders;

            }

}

db—dao-- SkillDAO.java

package com.lara.db.dao;

 

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

 

import com.lara.db.util.ConnectionFactory;

import com.lara.entity.Education;

import com.lara.entity.Skill;

 

public class SkillDAO

{

            public static List<Skill> readSkills()

            {

                        List<Skill> skills = new ArrayList<>();

                        try(Connection con = ConnectionFactory.getConnection();

                                    Statement stmt = con.createStatement();

                                    ResultSet rs = stmt.executeQuery("SELECT ID, NAME FROM SKILL"))

                        {

                                    Skill skill;

                                    while(rs.next())

                                    {

                                                skill = new Skill();

                                                skill.setId(rs.getInt("ID"));

                                                skill.setName(rs.getString("NAME"));

                                                skills.add(skill);

                                    }

                        }

                        catch(SQLException ex)

                        {

                                    ex.printStackTrace();

                        }                      

                        return skills;

            }

}

db—dao-- AddressDAO.java

 package com.lara.db.dao;

 

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Statement;

 

import com.lara.db.util.ConnectionFactory;

import com.lara.entity.Address;

 

public class AddressDAO

{

            public static int saveAddress(Address address)

            {

                        int status = 0;

                        StringBuffer sql = new StringBuffer();

                        sql.append("INSERT INTO ADDRESS(HOUSE_NO, STREET_NAME, STUDENT_ID) ");

                        sql.append("VALUES('" + address.getHouseNo() + "', ");

                        sql.append("'" + address.getStreetName() + "', ");

                        sql.append(address.getStudentId() + ")");

                        System.out.println("insert address:" + sql);

                        try(Connection con = ConnectionFactory.getConnection();

                                    Statement stmt = con.createStatement())

                        {

                                    status = stmt.executeUpdate(sql.toString());

                        }

                        catch(SQLException ex)

                        {

                                    ex.printStackTrace();

                        }

                        return status;

            }

}

db—dao--StudentDAO.java

package com.lara.db.dao;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

 

import com.lara.db.util.ConnectionFactory;

import com.lara.entity.Address;

import com.lara.entity.Education;

import com.lara.entity.Gender;

import com.lara.entity.Skill;

import com.lara.entity.Student;

 

public class StudentDAO

{

            public static int saveStudent(Student student)

            {

                        int results = 0;

                       

                        StringBuffer sql1 = new StringBuffer();

                        sql1.append("INSERT INTO STUDENT(ID, NAME, AGE, EMAIL, GENDER_ID, EDUCATION_ID) ");

                        sql1.append("VALUES (STUDENT_SEQ.NEXTVAL, '" + student.getName() + "', ");

                        sql1.append(student.getAge() + ", '" + student.getEmail() + "', ");

                        sql1.append(student.getGender().getId() + ", ");

                        sql1.append(student.getEducation().getId() + ")");

                        String sql2 = "SELECT MAX(ID) MAX_ID FROM STUDENT";                     

                        String sql3 = "INSERT INTO STUDENT_SKILL(STUDENT_ID, SKILL_ID) VALUES(?, ?)";

                        System.out.println("student insert:" + sql1);

                        System.out.println("max id read:" + sql2);

                        System.out.println("inserting into intermediate table:" + sql3);

                        ResultSet rs = null;

                        try(Connection con = ConnectionFactory.getConnection();

                                    Statement stmt1 = con.createStatement();

                                    Statement stmt2 = con.createStatement();

                                    PreparedStatement pstmt = con.prepareStatement(sql3))

                        {

                                    results = stmt1.executeUpdate(sql1.toString());

                                    rs = stmt2.executeQuery(sql2);

                                    int studentId = 0;

                                    if(rs.next())

                                    {

                                                studentId = rs.getInt("MAX_ID");                                            

                                    }

                                    student.getAddress().setStudentId(studentId);

                                    results = AddressDAO.saveAddress(student.getAddress());

                                    for(Skill skill : student.getSkills())

                                    {

                                                pstmt.setInt(1, studentId);

                                                pstmt.setInt(2, skill.getId());

                                                pstmt.executeUpdate();

                                    }

                        }

                        catch(SQLException ex)

                        {

                                    ex.printStackTrace();

                        }

                        finally

                        {

                                    try

                                    {

                                                if(rs != null)

                                                {

                                                            rs.close();

                                                            rs = null;

                                                }

                                    }

                                    catch(SQLException ex)

                                    {

                                                ex.printStackTrace();

                                    }

                        }                      

                        return results;

            }

            public static List<Student> search(String keyWord)

            {

                        List<Student> students = new ArrayList<Student>();

                       

                        StringBuffer sql = new StringBuffer();

                        sql.append("SELECT S.ID S_ID, S.NAME S_NAME, S.AGE S_AGE, S.EMAIL S_EMAIL, ");

                        sql.append("G.ID G_ID, G.NAME G_NAME, ");

                        sql.append("E.ID E_ID, E.NAME E_NAME, ");

                        sql.append("A.HOUSE_NO A_HOUSE_NO, A.STREET_NAME A_STREET_NAME, ");

                        sql.append("SK.ID SK_ID, SK.NAME SK_NAME ");

                        sql.append("FROM STUDENT S, GENDER G, EDUCATION E, ADDRESS A, ");

                        sql.append("STUDENT_SKILL SS, SKILL SK ");

                        sql.append("WHERE S.GENDER_ID = G.ID AND S.EDUCATION_ID = E.ID AND ");

                        sql.append("S.ID = A.STUDENT_ID  AND S.ID = SS.STUDENT_ID AND SS.SKILL_ID = SK.ID AND ");

                        sql.append("(S.NAME LIKE '%" + keyWord + "%' OR ");

                        sql.append("S.EMAIL LIKE '%" + keyWord + "%' OR ");

                        try

                        {

                                    int i = Integer.parseInt(keyWord);

                                    sql.append("S.AGE = " + keyWord + " OR ");

                        }

                        catch(NumberFormatException ex)

                        {

                                   

                        }                      

                        sql.append("E.NAME LIKE '%" + keyWord + "%' OR ");

                        sql.append("G.NAME LIKE '%" + keyWord + "%' OR ");

                        sql.append("A.STREET_NAME LIKE '%" + keyWord + "%' OR ");

                        sql.append("A.HOUSE_NO LIKE '%" + keyWord + "%' OR ");

                        sql.append("SK.NAME LIKE '%" + keyWord + "%') ");

                        sql.append("ORDER BY S.ID ");

                       

                        try(Connection con = ConnectionFactory.getConnection();

                                    Statement stmt = con.createStatement();

                                    ResultSet rs = stmt.executeQuery(sql.toString()))

                        {

                                    Student student = null;

                                    int studentId = -10;

                                    while(rs.next())

                                    {

                                                if(studentId != rs.getInt("S_ID"))

                                                {

                                                            student = new Student();

                                                            studentId = rs.getInt("S_ID");

                                                            student.setId(studentId);

                                                            student.setName(rs.getString("S_NAME"));

                                                            student.setAge(rs.getInt("S_AGE"));

                                                            student.setEmail(rs.getString("S_EMAIL"));

                                                            Gender gender = new Gender();

                                                            gender.setId(rs.getInt("G_ID"));

                                                            gender.setName(rs.getString("G_NAME"));

                                                            student.setGender(gender);

                                                            Education education = new Education();

                                                            education.setId(rs.getInt("E_ID"));

                                                            education.setName(rs.getString("E_NAME"));

                                                            student.setEducation(education);

                                                            Address address = new Address();

                                                            address.setHouseNo(rs.getString("A_HOUSE_NO"));

                                                            address.setStreetName(rs.getString("A_STREET_NAME"));

                                                            student.setAddress(address);

                                                            student.setSkills(new ArrayList<>());

                                                            students.add(student);

                                                }

                                                Skill skill = new Skill();

                                                skill.setId(rs.getInt("SK_ID"));

                                                skill.setName(rs.getString("SK_NAME"));

                                                student.getSkills().add(skill);

                                    }

                        }

                        catch(SQLException ex)

                        {

                                    ex.printStackTrace();

                        }                      

                        return students;

            }

}

 

 

 

 

 

db—util-- ConnectionFactory.java

package com.lara.db.util;

 

import java.io.FileReader;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.Properties;

 

public class ConnectionFactory

{

            private static final String DRIVER_CLASS_NAME;

            private static final String URL;

            private static final String USERNAME;

            private static final String PASSWORD;

           

           

            static

            {

                        Properties pr = new Properties();

                        try(FileReader fin = new FileReader("constants.properties"))

                        {

                                    pr.load(fin);

                        }

                        catch(IOException ex)

                        {

                                    ex.printStackTrace();

                        }

                        DRIVER_CLASS_NAME = pr.getProperty("driverClass");

                        URL = pr.getProperty("url");

                        USERNAME = pr.getProperty("username");

                        PASSWORD = pr.getProperty("password");

            }

            static

            {

                        try

                        {

                                    Class.forName(DRIVER_CLASS_NAME);

                        }

                        catch(ClassNotFoundException ex)

                        {

                                    ex.printStackTrace();

                        }

            }

            public static Connection getConnection() throws SQLException

            {

                        return DriverManager.getConnection(URL, USERNAME, PASSWORD);

            }

}

db—util--TestConnection.java

package com.lara.db.util;

 

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

 

public class TestConnection

{

            public static void main(String[] args)

            {

                        try(Connection con = ConnectionFactory.getConnection();

                                    Statement stmt = con.createStatement();

                                    ResultSet rs = stmt.executeQuery("SELECT ID, NAME FROM GENDER"))

                        {

                                    while(rs.next())

                                    {

                                                System.out.println(rs.getInt("ID") + ":" + rs.getString("NAME"));

                                    }

                        }

                        catch(SQLException ex)

                        {

                                    ex.printStackTrace();

                        }

                        System.out.println("done");

            }

}

entity-- Gender.java

        package com.lara.entity;

 

public class Gender

{

            private Integer id;

            private String name;

            public Integer getId()

            {

                        return id;

            }

            public void setId(Integer id)

            {

                        this.id = id;

            }

            public String getName()

            {

                        return name;

            }

            public void setName(String name)

            {

                        this.name = name;

            }

           

           

}

           

 

entity--Skill.java 

package com.lara.entity;

 

public class Skill

{

            private Integer id;

            private String name;

            public Integer getId()

            {

                        return id;

            }

            public void setId(Integer id)

            {

                        this.id = id;

            }

            public String getName()

            {

                        return name;

            }

            public void setName(String name)

            {

                        this.name = name;

            }

           

}

entity--Student.java 

          package com.lara.entity;

 

import java.util.ArrayList;

 

public class Student

{

            private Integer id;

            private String name;

            private Integer age;

            private String email;

            private Gender gender;

            private Education education;

            private ArrayList<Skill> skills;

            private Address address;

            public Integer getId()

            {

                        return id;

            }

            public void setId(Integer id)

            {

                        this.id = id;

            }

            public String getName()

            {

                        return name;

            }

            public void setName(String name)

            {

                        this.name = name;

            }

            public Integer getAge()

            {

                        return age;

            }

            public void setAge(Integer age)

            {

                        this.age = age;

            }

            public String getEmail()

            {

                        return email;

            }

            public void setEmail(String email)

            {

                        this.email = email;

            }

            public Gender getGender()

            {

                        return gender;

            }

            public void setGender(Gender gender)

            {

                        this.gender = gender;

            }

            public Education getEducation()

            {

                        return education;

            }

            public void setEducation(Education education)

            {

                        this.education = education;

            }

            public ArrayList<Skill> getSkills()

            {

                        return skills;

            }

            public void setSkills(ArrayList<Skill> skills)

            {

                        this.skills = skills;

            }

            public Address getAddress()

            {

                        return address;

            }

            public void setAddress(Address address)

            {

                        this.address = address;

            }

           

           

}        

entity--Education.java

package com.lara.entity;

 

public class Education

{

            private Integer id;

            private String name;

            public Integer getId()

            {

                        return id;

            }

            public void setId(Integer id)

            {

                        this.id = id;

            }

            public String getName()

            {

                        return name;

            }

            public void setName(String name)

            {

                        this.name = name;

            }

           

}

 

entity--Address.java

 package com.lara.entity;

 

public class Address

{

            private String houseNo;

            private String streetName;

            private Integer studentId;

            public String getHouseNo()

            {

                        return houseNo;

            }

            public void setHouseNo(String houseNo)

            {

                        this.houseNo = houseNo;

            }

            public String getStreetName()

            {

                        return streetName;

            }

            public void setStreetName(String streetName)

            {

                        this.streetName = streetName;

            }

            public Integer getStudentId()

            {

                        return studentId;

            }

            public void setStudentId(Integer studentId)

            {

                        this.studentId = studentId;

            }

           

}

Constants.properties 

driverClass=oracle.jdbc.driver.OracleDriver

url=jdbc:oracle:thin:@localhost:1521:XE

username=system

password=great123

sql

DROP TABLE GENDER CASCADE CONSTRAINTS;

DROP SEQUENCE GENDER_SEQ;

CREATE TABLE GENDER(ID NUMBER UNIQUE, NAME VARCHAR(90));

CREATE SEQUENCE GENDER_SEQ;

INSERT INTO GENDER VALUES(GENDER_SEQ.NEXTVAL, 'MALE');

INSERT INTO GENDER VALUES(GENDER_SEQ.NEXTVAL, 'FEMALE');

SELECT * FROM GENDER;

 

DROP TABLE EDUCATION CASCADE CONSTRAINTS;

DROP SEQUENCE EDUCATION_SEQ;

CREATE TABLE EDUCATION(ID NUMBER UNIQUE, NAME VARCHAR(90));

CREATE SEQUENCE EDUCATION_SEQ;

INSERT INTO EDUCATION VALUES(EDUCATION_SEQ.NEXTVAL, 'BE');

INSERT INTO EDUCATION VALUES(EDUCATION_SEQ.NEXTVAL, 'MCA');

INSERT INTO EDUCATION VALUES(EDUCATION_SEQ.NEXTVAL, 'MBA');

SELECT * FROM EDUCATION;

 

DROP TABLE SKILL CASCADE CONSTRAINTS;

DROP SEQUENCE SKILL_SEQ;

CREATE TABLE SKILL(ID NUMBER UNIQUE, NAME VARCHAR(90));

CREATE SEQUENCE SKILL_SEQ;

INSERT INTO SKILL VALUES(SKILL_SEQ.NEXTVAL, 'C');

INSERT INTO SKILL VALUES(SKILL_SEQ.NEXTVAL, 'C++');

INSERT INTO SKILL VALUES(SKILL_SEQ.NEXTVAL, 'JAVA');

SELECT * FROM SKILL;

 

DROP TABLE STUDENT CASCADE CONSTRAINTS;

DROP SEQUENCE STUDENT_SEQ;

CREATE TABLE STUDENT(ID NUMBER UNIQUE,

                             NAME VARCHAR(90),

                             AGE NUMBER,

                             EMAIL VARCHAR(90),

                             GENDER_ID NUMBER,

                             EDUCATION_ID NUMBER,

CONSTRAINT SFK1 FOREIGN KEY(GENDER_ID) REFERENCES GENDER(ID),                      

CONSTRAINT SFK2 FOREIGN KEY(EDUCATION_ID) REFERENCES EDUCATION(ID));

CREATE SEQUENCE STUDENT_SEQ;

 

DROP TABLE ADDRESS CASCADE CONSTRAINTS;

CREATE TABLE ADDRESS(HOUSE_NO VARCHAR(90),

                             STREET_NAME VARCHAR(90),

                             STUDENT_ID NUMBER UNIQUE,

CONSTRAINT AFK1 FOREIGN KEY(STUDENT_ID) REFERENCES STUDENT(ID));

 

 

DROP TABLE STUDENT_SKILL CASCADE CONSTRAINTS;

CREATE TABLE STUDENT_SKILL(STUDENT_ID NUMBER,

                                       SKILL_ID NUMBER,

CONSTRAINT SSFK1 FOREIGN KEY(STUDENT_ID) REFERENCES STUDENT(ID),

CONSTRAINT SSFK2 FOREIGN KEY(SKILL_ID) REFERENCES SKILL(ID));

 

COMMIT;

 

 

INSERT INTO STUDENT VALUES(STUDENT_SEQ.NEXTVAL, 'RAMANA', 22, 'R@RAMAN.COM', 1, 3);

SELECT * FROM STUDENT;

INSERT INTO ADDRESS VALUES('123/B', 'BTM', 21);

INSERT INTO STUDENT_SKILL VALUES(21, 1);

INSERT INTO STUDENT_SKILL VALUES(21, 2);

 

INSERT INTO STUDENT VALUES(STUDENT_SEQ.NEXTVAL, 'SWATHI', 23, 'S@GMAIL.COM', 2, 2);

 

SELECT * FROM STUDENT;

SELECT * FROM STUDENT_SKILL;

INSERT INTO ADDRESS VALUES('123/C', 'KORA', 22);

INSERT INTO STUDENT_SKILL VALUES(22, 2);

INSERT INTO STUDENT_SKILL VALUES(22, 3);

 

 

SELECT * FROM STUDENT S, GENDER G WHERE S.GENDER_ID = G.ID;

 

SELECT * FROM STUDENT S, GENDER G WHERE S.GENDER_ID = G.ID AND

S.NAME LIKE '%U%';

 

SELECT * FROM STUDENT S, GENDER G, EDUCATION E

WHERE S.GENDER_ID = G.ID AND S.EDUCATION_ID = E.ID AND

E.NAME LIKE '%M%';

 

SELECT * FROM STUDENT S, GENDER G, EDUCATION E

WHERE S.GENDER_ID = G.ID AND S.EDUCATION_ID = E.ID AND

E.NAME LIKE '%M%';

 

SELECT * FROM STUDENT S, GENDER G, EDUCATION E, ADDRESS A

WHERE S.GENDER_ID = G.ID AND S.EDUCATION_ID = E.ID AND S.ID = A.STUDENT_ID AND

(E.NAME LIKE '%F%' OR G.NAME LIKE '%F%' OR A.HOUSE_NO LIKE '%2%');

 

SELECT * FROM STUDENT S, GENDER G, EDUCATION E, ADDRESS A

WHERE S.GENDER_ID = G.ID AND S.EDUCATION_ID = E.ID AND

S.ID = A.STUDENT_ID AND

(E.NAME LIKE '%F%' OR G.NAME LIKE '%F%' OR A.STREET_NAME LIKE '%T%');

 

SELECT * FROM STUDENT S, GENDER G, EDUCATION E, ADDRESS A,

STUDENT_SKILL SS, SKILL SK

WHERE S.GENDER_ID = G.ID AND S.EDUCATION_ID = E.ID AND

S.ID = A.STUDENT_ID 

AND S.ID = SS.STUDENT_ID AND SS.SKILL_ID = SK.ID AND

(E.NAME LIKE '%F%' OR G.NAME LIKE '%M%' OR

A.STREET_NAME LIKE '%T%' OR SK.NAME LIKE '%A%');

 

 

SELECT * FROM STUDENT S, GENDER G, EDUCATION E, ADDRESS A, STUDENT_SKILL SS, SKILL SK

WHERE S.GENDER_ID = G.ID AND S.EDUCATION_ID = E.ID AND S.ID = A.STUDENT_ID 

AND S.ID = SS.STUDENT_ID AND SS.SKILL_ID = SK.ID AND

(E.NAME LIKE '%F%' OR G.NAME LIKE '%M%' OR A.STREET_NAME LIKE '%T%' OR SK.NAME LIKE '%A%')

ORDER BY S.ID;

 

SELECT * FROM EDUCATION;

 

 

 

SELECT * FROM STUDENT S, GENDER G, EDUCATION E, ADDRESS A,

STUDENT_SKILL SS, SKILL SK

WHERE S.GENDER_ID = G.ID AND S.EDUCATION_ID = E.ID AND

S.ID = A.STUDENT_ID  AND S.ID = SS.STUDENT_ID AND SS.SKILL_ID = SK.ID AND

 

(S.NAME LIKE '%S%' OR

S.EMAIL LIKE '%R%' OR

S.AGE = 227 OR

E.NAME LIKE '%F%' OR

G.NAME LIKE '%M%' OR

A.STREET_NAME LIKE '%T%' OR

A.HOUSE_NO LIKE '%T%' OR

SK.NAME LIKE '%A%')

ORDER BY S.ID;

 

SELECT S.ID S_ID, S.NAME S_NAME, S.AGE S_AGE, S.EMAIL S_EMAIL,

G.ID G_ID, G.NAME G_NAME,

E.ID E_ID, E.NAME E_NAME,

A.HOUSE_NO A_HOUSE_NO, A.STREET_NAME A_STREET_NAME,

SK.ID SK_ID, SK.NAME SK_NAME

FROM STUDENT S, GENDER G, EDUCATION E, ADDRESS A,

STUDENT_SKILL SS, SKILL SK

WHERE S.GENDER_ID = G.ID AND S.EDUCATION_ID = E.ID AND

S.ID = A.STUDENT_ID  AND S.ID = SS.STUDENT_ID AND SS.SKILL_ID = SK.ID AND

(S.NAME LIKE '%S%' OR

S.EMAIL LIKE '%R%' OR

S.AGE = 227 OR

E.NAME LIKE '%F%' OR G.NAME LIKE '%M%' OR

A.STREET_NAME LIKE '%T%' OR A.HOUSE_NO LIKE '%T%' OR

SK.NAME LIKE '%A%')

ORDER BY S.ID;