如何在 JDBC 中按照 ID 进行搜索

huangapple 未分类评论60阅读模式
英文:

how to search by id in jdbc

问题

我在我的代码中使用了JDBC,以下是我编写的用于搜索id的代码:

public Item findById(String id) {
    Item result = null;

    String s = "SELECT id, key, name, creat, description FROM trackersql WHERE id = ?";
    
    try (Connection connection = init()) {
        PreparedStatement statement = connection.prepareStatement(s);
        statement.setString(1, id);
        
        ResultSet resultSet = statement.executeQuery();

        while(resultSet.next()) {
            result = new Item();
            result.setId(resultSet.getString("id"));
            result.setKey(resultSet.getString("key"));
            result.setName(resultSet.getString("name"));
            result.setCreat(resultSet.getLong("creat"));
            result.setDescription(resultSet.getString("description"));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return result;
}

我在代码中进行检查的方式如下:

try {
    DriverManager.registerDriver(new org.postgresql.Driver());
} catch (SQLException e) {
    e.printStackTrace();
}
TrackerSQL trackerSQL = new TrackerSQL();
Item item1 = new Item("1", "1", "test1", 1, "test1");

trackerSQL.init();

System.out.println(trackerSQL.findById(item1.getId()));

但是我得到了以下错误:

ERROR: syntax error (approximate position: ":") Position: 69 at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273) 	at ru.job4j.trackersql2.TrackerSQL.findById(TrackerSQL.java:104) 	at
ru.job4j.trackersql2.TrackerSQL.main(TrackerSQL.java:188) null

我犯了什么错误?

英文:

I use in my code jdbc
wrote the following code to search id:

public Item findById(String id) {
        Item result = null;

        String s = "SELECT id, key, name, creat, description FROM trackersql WHERE id = :id";

        try (Connection connection = init()) {
            PreparedStatement statement = connection.prepareStatement(s);

            ResultSet resultSet = statement.executeQuery();

            while(resultSet.next()) {
                Item item = new Item();

                statement.setString(1, id);
                statement.setString(2, item.getKey());
                statement.setString(3, item.getName());
                statement.setLong(4, item.getCreat());
                statement.setString(5, item.getDescription());
                statement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

doing my code check this way:

try {
            DriverManager.registerDriver(new org.postgresql.Driver());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        TrackerSQL trackerSQL = new TrackerSQL();
        Item item1 = new Item("1", "1", "test1", 1, "test1");

        trackerSQL.init();

        System.out.println(trackerSQL.findById(item1.getId()));

but I get the following error:

> ERROR: syntax error (approximate position: ":") Position: 69 at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
> at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
> at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273) at ru.job4j.trackersql2.TrackerSQL.findById(TrackerSQL.java:104) at
> ru.job4j.trackersql2.TrackerSQL.main(TrackerSQL.java:188) null

what is my mistake?

答案1

得分: 0

JDBC不支持命名参数。将 :id 替换为 ?。同时,您从未绑定参数。

String s = "SELECT id, key, name, creat, description FROM trackersql WHERE id = ?";
try (Connection connection = init()) {
    PreparedStatement statement = connection.prepareStatement(s);
    statement.setString(1, id);
    ResultSet resultSet = statement.executeQuery();

您的代码中还存在另一个错误:

statement.setString(1, id);
statement.setString(2, item.getKey());
statement.setString(3, item.getName());
statement.setLong(4, item.getCreat());
statement.setString(5, item.getDescription());
statement.executeUpdate();

应该修改为:

Item item = new Item();

item.setId(1, rs.getString(1));
item.setKey(2, rs.getString(2));
return item;
英文:

JDBC does not know named parameter. repalce :id with ?. Also you never bind the parameter

String s = "SELECT id, key, name, creat, description FROM trackersql WHERE id = ?";
try (Connection connection = init()) {
    PreparedStatement statement = connection.prepareStatement(s);
    statement.setString(1,id)
    ResultSet resultSet = statement.executeQuery();

There is an other erro in your code:

            statement.setString(1, id);
            statement.setString(2, item.getKey());
            statement.setString(3, item.getName());
            statement.setLong(4, item.getCreat());
            statement.setString(5, item.getDescription());
            statement.executeUpdate();

must be

          Item item = new Item();

            item.setId(1, rs.getString(1));
            item.setKey(2, rs.getString(2));
          return item;

huangapple
  • 本文由 发表于 2020年5月3日 18:20:24
  • 转载请务必保留本文链接:https://java.coder-hub.com/61572857.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定