0
\$\begingroup\$
public class ConsoleQuery {
    public static void main(String[] args) throws SQLException {
        if (args.length == 0) {
            System.out.println("You must specify name");
            return;
        }
        String name = args[0];
        Map < String, Object > device = getDeviceByName(name);
        if (device != null) {
            System.out.printf("Found device by name %s: %s",
                name, device);
        } else {
            System.out.printf("There is no device by name %s", name);
        }
    }

    private static Map < String, Object > getDeviceByName(String name) throws SQLException {
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "test", "test");
        PreparedStatement statement = connection.prepareStatement("SELECT id, mac, name FROM device");
        ResultSet rs = statement.executeQuery();
        List < Map < String, Object >> deviceList = new LinkedList < Map < String, Object >> ();
        try {
            ResultSetMetaData rsmd = rs.getMetaData();
            Integer columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                Map < String, Object > row = new LinkedHashMap < String, Object > ();
                for (int i = 1; i <= columnCount; i++) {
                    row.put(rsmd.getColumnName(i), rs.getObject(i));
                }
                deviceList.add(row);
            }
        } finally {
            rs.close();
        }
        for (Map < String, Object > device: deviceList) {
            if (name.equals(String.valueOf(device.get("name")))) {
                return device;
            }
        }
        return null;
    }
}
\$\endgroup\$
1
\$\begingroup\$

Well, the most important optimization you can make to this code is to improve your SQL query. You are currently pulling down the entire db but only care about one row; you're doing half your query on the client side. Use SQL, not Java, for selecting data from the db! Consider the following version of getDeviceByName:

private static Map<String, Object> getDeviceByName(String name) throws SQLException {
    DriverManager.registerDriver(new com.mysql.jbdc.Driver());
    Connection connection = DriverManager.getConnection(
        "jdbc:mysql://127.0.0.1:3306/test", "test", "test");
    PreparedStatement statement = connection.prepareStatement(
        "SELECT id, mac, name FROM device WHERE name = ? LIMIT 1");
    statement.setString(1, name);
    ResultSet rs = statement.executeQuery();
    try {
        if (!rs.next()) {
            return null;
        }
        return readRowIntoMap(rs);
    } finally {
        rs.close();
    }
}

private Map<String, Object> readRowIntoMap(ResultSet rs) throws SQLException {
    ResultSetMetaData metadata = rs.getMetaData();
    Map<String, Object> row = new LinkedHashMap<>();
    for (int i = 1; i <= metadata.getColumnCount(); i++) {
        row.put(metadata.getColumnName(i), rs.getObject(i));
    }
    return row;
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.