java - How to Query a Postgresql table with spaces in java

Question

So when I gather user input and use that value within a SELECT statement, It it only reads the the last string after space. So, for example if I search for 'New York', only 'York' will show. Maybe I can do a LIKE '%?', Then store all the values returned in an array and match the user input exactly with the value of the city name in the array and return it, but there must be a way to just return what i want from the query.

My table:

   public void createTable(){
        try{
            Statement stmt = this.conn.createStatement();
            stmt.execute("CREATE TABLE IF NOT EXISTS Cities (" +
                    "name varchar(50)," +
                    "population int," +
                    "latitude double precision," +
                    "longitude double precision" +
                    ")");
            System.out.println("Cities table created.");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }

here is my function that queries

    public void readLine(String city){
        try{
            String sqlLike = "SELECT * FROM cities " +
                    "WHERE cities.name = ?";
            PreparedStatement ps = this.conn.prepareStatement(sqlLike);
            ps.setString(1, city);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                System.out.println();
                System.out.print(rs.getString(1));
                System.out.print(" " + rs.getString(2));
                System.out.print(" " + rs.getString(3));
                System.out.print(" " + rs.getString(4) + "\n");
            }
        }catch(Exception e){
            System.out.println(e);
        }

    }

Here is my Main that prompts user for input. No validation yet so don't mind that

   public static void main(String[] args){
        Database dbConn = new Database();
        dbConn.createTable();
//        dbConn.insertIntoDatabase();
        Scanner scnr = new Scanner(System.in);
        String citySelect = "";

        while(!citySelect.equals("exit".toLowerCase())){
            System.out.print("Please enter the name of the city in question: ");
            citySelect = scnr.next();
            dbConn.readLine(citySelect.toLowerCase());
        }

    }

Any Advice guys?

标签： javadatabasepostgresql

Answer 1

问题不在于数据库表或查询，它们在存储或检索包含空格的城市名称时没有问题。

问题是Scanner类默认使用空格作为分隔符（来自类的 Javadoc）：

Scanner 使用分隔符模式将其输入分解为标记，默认情况下匹配空格。

因此，每次调用 Scanner 的 next() 时，都会根据分隔符获取下一个字符串标记。

为了更改默认行为，请为 Scanner 设置不同的分隔符（可能不会出现在您的结果中）。例如，在您的代码中，在 main 方法的 while 循环之前进行以下调用：

scnr.useDelimiter(", ");