How to use select (jdbc) with the statement prepared using javafx

advertisements

I have a jdbc code and I want to select my data from database using prepared statement and javaFX . I mean i want to use java fx to get the String part . here is my data access code ,But I dont know how to write the controller method with Parameters and how to iterate the list.

      public ArrayList selectByNameAndFamily(Person person) throws Exception {
    preparedStatement = connection.prepareStatement("select * from person where name = ? and family = ?");
    preparedStatement.setString(1,person.getName());
    preparedStatement.setString(2,person.getFamily());
    ResultSet resultSet = preparedStatement.executeQuery();
    ArrayList<Person> list = new ArrayList<>();
    while (resultSet.next()) {
        person.setPhone(resultSet.getLong("phone"));
        person.setId(resultSet.getLong("id"));
        person.setPlaque(resultSet.getLong("plaque"));
        person.setUnit(resultSet.getLong("unit"));
        person.setName(resultSet.getString("name"));
        person.setFamily(resultSet.getString("family"));
        person.setAddress1(resultSet.getString("Address1"));
        person.setAddress2(resultSet.getString("address2"));
        person.setAddress3(resultSet.getString("address3"));

        list.add(person);
    }
    return list;

*note my table object name is Person and I already made object of PreparedStatement in my code .


I created this. Some of my methods and variables are different from yours. Also, I used SQLITE and created a sample database. This is not a javaFX problem, this is a java problem.

public static void main(String[] args) {

    try
    {
        Class.forName("org.sqlite.JDBC");
        Scanner input = new Scanner(System.in);
        Person person = new Person();

        System.out.println("Enter person name: ");
        person.setName(input.nextLine());
        System.out.println("Enter person family: ");
        person.setFamilyName(input.nextLine());

        ArrayList<Person> peopleFromDatabase = selectByNameAndFamily(person);

        for(Person entry : peopleFromDatabase)
        {
             System.out.println("PERSON:");
             System.out.println("\t" + entry.getName());
             System.out.println("\t" + entry.getID());
             System.out.println("\t" + entry.getFamilyName());
             System.out.println("\t" + entry.getAddress1());

             System.out.println();
        }
    }
    catch (ClassNotFoundException ex)
    {
        Logger.getLogger(TestingGround.class.getName()).log(Level.SEVERE, null, ex);
    }
}

public static ArrayList<Person> selectByNameAndFamily(Person person)
{
    ArrayList<Person> list = new ArrayList();
    String sql = "select * from person where name = ? and family_name = ?";

    try(Connection con = DriverManager.getConnection("jdbc:sqlite:family.db");
        PreparedStatement prestmt = con.prepareStatement(sql); )
    {
        prestmt.setString(1, person.getName());
        prestmt.setString(2, person.getFamilyName());
        ResultSet rs = prestmt.executeQuery();

        while (rs.next()) {
            Person returningPerson = new Person();

            returningPerson.setID(rs.getLong("id"));
            returningPerson.setName(rs.getString("name"));
            returningPerson.setFamilyName(rs.getString("family_name"));
            returningPerson.setPhone(rs.getString("phone"));
            returningPerson.setPlaque(Long.parseLong(rs.getString("plaque")));
            returningPerson.setUnit(Long.parseLong(rs.getString("unit")));
            returningPerson.setAddress1(rs.getString("Address1"));
            returningPerson.setAddress2(rs.getString("address2"));
            returningPerson.setAddress3(rs.getString("address3"));

            list.add(returningPerson);
        }

    }
    catch (SQLException ex)
    {
        Logger.getLogger(TestingGround.class.getName()).log(Level.SEVERE, null, ex);
    }

    return list;
}