CODEWITHSUNDEEP
javamysqlarrays
user3046636
user3046636

Reputation: 693

count rows in column by java array in mysql

I have a problem.

There is an array of strings in my java program. In my database i have the strings as well , but they are in ether column a or column b. I need to know in my java program if the element in my array is an element a or an element b. I kind of have an blackout on how to do this.

I have the mysql connection with queries working, no problem there.

The java class currently is : 

public static String getCountedDatesTypes(String check) throws ClassNotFoundException {
    String wholeDataModel = "";

    String dates;
    String myDriver = "org.mariadb.jdbc.Driver";
    Class.forName(myDriver);

    Connection con = null;
    Statement st = null;
    ResultSet rs = null;

    String url = "jdbc:mariadb://localhost:3306/house";
    String user = "root";
    String password = "supermanspassword";

    try {

        String tmpblack = "";
        String tmpnormal = "";
        String tmpcheap = "";

        con = DriverManager.getConnection(url, user, password);
        st = con.createStatement();

        String sqlQuery = String.format("select count(black) as black,count(normal) as normal ,count(cheap) as cheap from dates;");
        rs = st.executeQuery(sqlQuery);

        while (rs.next()) {
            String b = rs.getString("black");

            String n = rs.getString("normal");

            String c = rs.getString("cheap");

            System.out.println(b + " " + n + " " + c);

        }

    } catch (SQLException ex) {
        Logger lgr = Logger.getLogger(Mysql_interaction.class.getName());
        lgr.log(Level.SEVERE, ex.getMessage(), ex);

    } finally {
        try {
            if (rs != null) {

                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (con != null) {
                con.close();
            }

        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Mysql_interaction.class.getName());
            lgr.log(Level.WARNING, ex.getMessage(), ex);
        }
    }

Database (super simple):

    MariaDB [house]> select * from dates;
    +----------+----------+----------+
    | black    | normal   | cheap    |
    +----------+----------+----------+
    | 5/5/2015 | NULL     | NULL     |
    | NULL     | 6/5/2015 | NULL     |
    | NULL     | NULL     | 7/5/2015 |
    | NULL     | NULL     | 8/5/2015 |
    +----------+----------+----------+

I need to calculate the prices of the selected dates in the array i have in a java class. I need to know how many of normal and ceap there are. Sof if date 6/5/2015 and 8/5/2015 are provided, i need to be able to know this is 1 normal and 1 cheap.

String check is supposed to be a array of type string later when this works.

Can someone help me please with this problem.

Regards, Rick

Upvotes: 0

Views: 143

Answers (1)

hmrojas.p
hmrojas.p

Reputation: 582

Maybe you need to use a Map, you can use TreeMap or HashMap with another collection to store all values of each type of your Strings, it would be something like this:

...

Map <String, List<String>> map = new HashMap <String, List<String>> ();

List<String> b= new ArrayList<String>();
List<String> n= new ArrayList<String>();
List>String> c= new ArrayList<String>();

String sqlQuery = String.format("select * from dates;");

rs = st.executeQuery(sqlQuery);

while (rs.next()) {
    b.add(rs.getString("black") != null ? rs.getString("black") : "");
    n.add(rs.getString("normal") != null ? rs.getString("normal") : "");
    c.add(rs.getString("cheap") != null ? rs.getString("cheap") : "");
}

map.put("b", b);
map.put("n", n);
map.put("c", c);

...

In this way you can know the type of your Strings.

I hope this information helps you.

Good Luck.

Upvotes: 1

Related Questions