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