current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 0 down vote favorite 
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;
    }
}
share|improve this question

1 Answer 1

up vote 1 down vote

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;
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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