How to check if Radio Buttons and Checkboxes have data

[Ankit Pai]

Queries

You will have to build a dynamic query statement. My advice would be to start with a basic query string: "select * from customers ". Then, if the name and/or City textfields have data in them, add SQL text to your query string. For example, in the second screen above, the final query string would be:

select * from customers where city like 'Tampa%' order by zip

If the user entered 'Forth' for the name, and 'T' for the city, then the query would look like:

select * from customers where name like 'Forth%' and city like 'T%' order by name

Note that I'm using 'like' in the where clause. This means that the query will look for names starting with 'Forth', and cities starting with 'T' -- Tampa in our db.

Also remember to include the logical AND if both name and city textfields are selected. Otherwise, the where clause does not include any logical operator.

Once you have your query string built, you can then execute it. (a executeQuery statement is fine). Loop through the result set and build a nice formatted string for each result, which you will then append to the text area using the appendText() method.

https://gyazo.com/595e7759ac930befc21f871a69f8f6c8

https://gyazo.com/595e7759ac930befc21f871a69f8f6c8https://gyazo.com/14aff4a0e0b6855232927832e4039743

What I have tried:

I have the querey statement

I am just not sure how to check if field and radiobuttons have data, and how would I makea string and add to the statement if they have data in the fields.

[Daniel Demesmaecker]

your 2nd link dosn't work and I think this should be in the javaFX forum or what did you use to make your gui?
If you use javaFx you can asign an id to your radiobuttons and us the isCheckedMethod, when it is checked you can pass the value to your repository to put in the query

[Ron McLeod]

Looks like homework.

Post the code that you are having problems with so that we have a better idea of where you are getting stuck, what fields and radiobuttons you are referring to.

[Ron McLeod]

Welcome to CodeRanch!  

[Ankit Pai]

public static void main(String[] args) throws SQLException
{
String dbURL = "jdbc:derby:firstdb;create=true";

Connection connection = DriverManager.getConnection(dbURL);
if (connection != null) 
{
System.out.println("Connected to database...");
}
else
{
System.out.println("Oh Oh");
}


Statement statement = connection.createStatement(); 

try
{
statement.executeUpdate("DROP TABLE customers");
}
catch (SQLException e)
{
System.out.println("table drop failed -- most likely because it already existed: " + e);
}

// -----------------------------
// create the table
// -----------------------------
System.out.println("creating customers table");

String sql = "CREATE TABLE customers ( " +
"custid INTEGER NOT NULL,"+
"name VARCHAR(20) NOT NULL,"+
"address VARCHAR(20) NOT NULL,"+
"city VARCHAR(16) NOT NULL,"+
"state VARCHAR(2) NOT NULL,"+
"zip VARCHAR(5) NOT NULL)";

statement.executeUpdate(sql);

// -----------------------------
// insert some data into table
// -----------------------------
System.out.println("inserting data into customers table");

statement.executeUpdate("INSERT INTO Customers VALUES" + 
"(1, 'Smith, Tim', '12 Elm St', 'Tampa', 'FL', '34567')," +
"(2, 'Jones, Tom', '435 Oak Dr', 'Dunedin', 'FL', '33245')," +
"(3, 'Avery, Bill', '623 Ash Ave', 'Clearwater', 'FL', '33655'),"+
"(4, 'Kerr, Debra', '1573 Yew Crt', 'Clearwater', 'FL', '33442'),"+
"(5, 'Mattik, Otto', '15 Fir Ct','Oldsmar', 'FL','34567'),"+
"(6, 'Nate, Mary', '22 54th St', 'St. Petersburg', 'FL', '33711'),"+
"(7, 'Tater, Dick', '77 Gunn Hwy', 'Tampa', 'FL', '39844'),"+
"(8, 'Forth, Sally', '555 Pine St', 'Tampa', 'FL', '39812')" );

// -----------------------------
// query the table and displa results
// -----------------------------
ResultSet result = statement.executeQuery("SELECT * FROM customers");

System.out.println("List of Addresses: "); 

while (result.next())
{
System.out.println(
" "+result.getInt("custid")
+ ", "+result.getString("name")
+ ", "+result.getString("address")
+ ", " + result.getString("city")
+ ", "+result.getInt("zip"));
}


// -----------------------------
// clean up
// -----------------------------
result.close();
connection.close();
}

}

This is the database, I also used JavaFx to make a GUI, I used two textfields and a group of three radiobuttons in a togglegroup

[Ankit Pai]

Here is the 2nd link that was not working.

https://gyazo.com/14aff4a0e0b6855232927832e4039743

[Daniel Demesmaecker]

Don't put everything in your main method, you should create dao's...

[Ankit Pai]

I was planning on making methods, but I'm trying to figure out how to get data from textfield and radiobuttons and add the string to the query statement

[Daniel Demesmaecker]

This is not working code, just to give you an idea.

controllerClass:

private Dao dao;
@FXML
private TextField name;
@FXML
private TextField city;
@FXML
private RadioButton orderByCity;
@FXML
private RadioButton orderByName;
@FXML
private RadioButton orderByZip;

@FXML 
private void search(){
    if(orderBycity.isChecked(){
        doa.search(name.getText(), city.getText(), "city");
    } else if (orderByName.isChecked(){
        doa.search(name.getText(), city.getText(), "name");
    } else if(orderByZip.isChecked(){
        doa.search(name.getText(), city.getText(), "zip");
    } else{
        Alert alert = new Alert(AlertType.Warning);
        alert.setContentText("You Have to select something");
        alert.showAndWait();
    }
}

[Daniel Demesmaecker]

You can use a prepared statement to add the variables in your querry

PreparedStatement statement = connection.prepareStatement("select * from table where name like ? and city like ? order by ?");
statement.SetString(1, nameValue);
statement.SetString(2, cityValue);
statement.SetString(3, orderByValue);
ResultSet resultSet=statement.executeQuery();  
while(resultSet.next()){  
Do something with the results...
}  

[Ankit Pai]

Now, how would I add that string to the Query Statement?

[Ankit Pai]

Could you give me an example of a prepared statement?

[Knute Snortum]

https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

[Daniel Demesmaecker]

PreparedStatement statement = connection.prepareStatement("select * from table where name like ? and city like ? order by ?");
statement.SetString(1, nameValue);
statement.SetString(2, cityValue);
statement.SetString(3, orderByValue);
ResultSet resultSet=statement.executeQuery();  
while(resultSet.next()){  
Do something with the results...
}  

[Ankit Pai]

What would I have to do, in order to do this ?

Then, if the name and/or City textfields have data in them, add SQL text to your query string. For example, in the second screen above, the final query string would be:

[Daniel Demesmaecker]

I really don't like the fact that everyone post and just expects you to solve it for them, especially when it's an assignment.
We gave you the tools, now it only takes a litle bit of elbowgreace to figure it out...

[Ankit Pai]

Well Sir, the thing is im trying to understand how

[Daniel Demesmaecker]

Then I would start by folowing a tutorial.
Write a dao (interface) were you write your methods you'll need in your program, then make an implementation of that dao writting out all your querries.

I'll give you one example:

public Class DaoImpl implements Dao{
    String dbURL = "jdbc:derby:firstdb;create=true"; 
    Connection connection = DriverManager.getConnection(dbURL);


    public ResultSet search(String name, String city, String orderBy){
        PreparedStatement statement = connection.prepareStatement("select * from table where name like ? and city like ? order by ?");
        statement.SetString(1, name);  //or with wildcard (1,name + "%")
        statement.SetString(2, city);
        statement.SetString(3, orderBy);
        return statement.executeQuery(); 
    }
}