Simple command line tool to query database. Need code optimized

Question

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;
    }
}

Answer 1

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;
}