java - 如何使用spring jdbc在mysql中插入外键

Question

所以目前以下代码可以工作并从我的 java 应用程序插入到 mysql 中:

String sql = "INSERT INTO location(address1, address2, postcode, town)";
sql+= "VALUES(:address1, :address2, :postcode, :town)";

Map parameters = new HashMap();
parameters.put("address1", location.getAddress1());
parameters.put("address2", location.getAddress2());
parameters.put("postcode", location.getPostCode());
parameters.put("town", location.getTown());

this.namedParameterJdbcTemplate.update(sql, parameters);

但是，我不确定如何在位置表中添加“类型”列。此列/属性与另一个名为 location_type 的表相关。所以如果我要通过 mysql 代码插入它，我会这样做

INSERT INTO location(address1, address2, postcode, town, type)
VALUES("2","abc street","ab1 1cd", "nice town", (SELECT id FROM location_type WHERE type="main")  ) 

我将如何从 Java 应用程序执行此操作？

Answer 1

您可以在 Java 应用程序中以相同的方式执行此操作:

String sql = "INSERT INTO location(address1, address2, postcode, town, type)";
             + " VALUES (:address1, :address2, :postcode, :town,"
                           + " (SELECT id FROM location_type WHERE type='main'))";

Map parameters = new HashMap();
parameters.put("address1", address1);
parameters.put("address2", address2);
parameters.put("postcode", postcode);
parameters.put("town", town);

this.namedParameterJdbcTemplate.update(sql, parameters);

数据库没有理由接受来自命令行的 SQL 查询并拒绝来自 Java 应用程序的查询。

如果上面的硬编码 main 实际上是另一个参数，您可以将其设置为与所有其他参数相同的方式:

String sql = "INSERT INTO location(address1, address2, postcode, town, type)";
             + " VALUES (:address1, :address2, :postcode, :town,"
                           + " (SELECT id FROM location_type WHERE type= : type))";

Map parameters = new HashMap();
parameters.put("address1", address1);
parameters.put("address2", address2);
parameters.put("postcode", postcode);
parameters.put("town", town);
parameters.put("type", type);

this.namedParameterJdbcTemplate.update(sql, parameters);