Spring JDBC模板：使用Join执行查询

Question

我正在开发一个使用Spring Boot和JDBCTemplates进行数据库查询的应用程序。

问题是：如果我只需要查询单个表，那么没有问题。但是，如果有多个表需要联接，我该如何执行此任务？

更具体地说，创建表的SQL命令如下：

CREATE TABLE firewall_items 
(
    id       INT NOT NULL AUTO_INCREMENT,
    firewall_id      INT NOT NULL,
    date     DATE,
    src      VARCHAR(15),
    src_port INT,
    dst      VARCHAR(15),
    dst_port INT,
    protocol VARCHAR(4),
    PRIMARY KEY(id)
    );

CREATE TABLE firewalls (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    ip VARCHAR(15) NOT NULL,
    info TEXT,
    PRIMARY KEY(id)
    );

相应的Java类如下所示：

import java.util.Date;

public class FirewallItems 
{
    private Date date;
    private String id;
    private String protocol;
    private String src;
    private String dst;
    private String src_port;
    private String dst_port;
    private String firewall_id;


    public FirewallItems() {}


    public FirewallItems(Date data, String identificativo, String protocollo, String sorgente, String destinazione,
            String porta_sorgente, String porta_destinazione, String firewall_id) 
    {
        super();
        this.date = data;
        this.id = identificativo;
        this.protocol = protocollo;
        this.src = sorgente;
        this.dst = destinazione;
        this.src_port = porta_sorgente;
        this.dst_port = porta_destinazione;
        this.firewall_id = firewall_id;
    }

    /**
     * Return the date of the report
     * @return date
     */
    public Date getDate() 
    {
        return date;
    }

    /**
     * Set the date of the report
     * @param date the report's date
     */
    public void setDate(Date date) 
    {
        this.date = date;
    }

    /**
     * Return the id of the report
     * @return id
     */
    public String getId() 
    {
        return id;
    }

    /**
     * Set the id of the report
     * @param id the report's id
     */
    public void setId(String id) 
    {
        this.id = id;
    }

    /**
     * Return the protocol cecked by report
     * @return protocol
     */
    public String getProtocol() 
    {
        return protocol;
    }

    /**
     * Set the protocol cecked by report
     * @param protocol
     */
    public void setProtocol(String protocol) 
    {
        this.protocol = protocol;
    }

    /**
     * Return the source of firewall's drop
     * @return Src
     */
    public String getSrc() 
    {
        return src;
    }

    /**
     * Set the source of firewall's drop
     * @param src the firewall's source drop
     */
    public void setSrc(String src) 
    {
        this.src = src;
    }

    /**
     * Return the firewall's destionation drop
     * @return dst
     */
    public String getDst() 
    {
        return dst;
    }

    /**
     * Set the firewall's destination drop
     * @param dst the firewall's destination drop 
     */
    public void setDst(String dst) 
    {
        this.dst = dst;
    }

    /**
     * Return the source's port
     * @return src_port
     */
    public String getSrc_port() 
    {
        return src_port;
    }

    /**
     * Set the source's port
     * @param src_port the source's port
     */
    public void setSrc_port(String src_port) 
    {
        this.src_port = src_port;
    }

    /**
     * Return the destination's port
     * @return dst_port
     */
    public String getDst_port() 
    {
        return dst_port;
    }

    /**
     * Set the destination's port
     * @param dst_port the destination's port
     */
    public void setDst_port(String dst_port) 
    {
        this.dst_port = dst_port;
    }

    /**
     * Return the id of firewall associated to report
     * @return firewall_id
     */
    public String getFirewall_id() 
    {
        return firewall_id;
    }

    /**
     * Set the id of firewall associated to report
     * @param firewall_id the id of firewall associated to report
     */
    public void setFirewall_id(String firewall_id) 
    {
        this.firewall_id = firewall_id;
    }   
}

public class Firewall 
{
    private String id;
    private String ip;
    private String info;
    private String name;


    /**
     * Empty constructor, which instantiates a Firewall specimen without setting default values
     */
    public Firewall() {}

    /**
     * Constructor instantiating a Firewall specimen specifying its initial values
     * 
     * @param id the firewall's id code
     * @param ip the firewall's ip code
     * @param info the info about firewall
     * @param name firewall's name
     */
    public Firewall(String id, String ip, String info, String nome) 
    {
        super();
        this.id = id;
        this.ip = ip;
        this.info = info;
        this.name = nome;
    }

    /**
     * Return the firewall's id
     * @return id
     */
    public String getId() 
    {
        return id;
    }

    /**
     * Set firewall's id
     * @param id the firewall's id
     */
    public void setId(String id) 
    {
        this.id = id;
    }

    /**
     * Return the firewall's ip
     * @return ip
     */
    public String getIp() 
    {
        return ip;
    }

    /**
     * Set firewall's ip
     * @param ip the firewall's ip
     */
    public void setIp(String ip) 
    {
        this.ip = ip;
    }

    /**
     * Return firewall's info
     * @return info
     */
    public String getInfo() 
    {
        return info;
    }

    /**
     * Set firewall's info
     * @param info firewall's info fields
     */
    public void setInfo(String info) 
    {
        this.info = info;
    }

    /**
     * Return firewall's name
     * @return name
     */
    public String getName()
    {
        return name;
    }

    /**
     * Set firewall's name
     * @param name firewall's name
     */
    public void setName(String name) 
    {
        this.name = name;
    }
}

限制条件是 firewall_Items.firewall_id = firewall.id（这些是必须用来执行连接的变量）。

如果我想执行此查询：

SELECT info, src
FROM firewalls, firewall_items
WHERE firewall_items.firewall_id = firewalls.id;

我的Java代码应该如何编写，使用JdbcTemplate？ 我是否需要向防火墙类添加一个集合来收集FirewallsItems对象，例如ArrayList？

注1：我必须使用JdbcTemplate项目规范。我不能使用Hibernate或其他工具。

注2：我知道rowmapper和resultset是什么，我经常在单个表上使用它们进行查询。我需要了解如何将它们用于带有join的查询，就像示例中的那样。

非常感谢您提前的回复！

- Luca Sepe

Answer 1

在查询表之前，应该使用JOIN关键字将它们连接起来。示例代码如下：

String query= "SELECT firewall_items.src, firewalls.info 
FROM firewall_items 
JOIN firewalls 
ON firewall_items.firewalls_id = firewalls.id"

List<Item> items = jdbcTemplate.query(
          query, 
          new MapSqlParameterSource(), 
          new FirewallInfoRowMapper()
          );

在这里，Item是您检索到的对象。您可以决定它是什么。

查看此文章以获取更多信息。

编辑：

针对您的进一步查询。上面是jdbcTemplate的修改用法，下面是您需要的类。 这要求您拥有Spring。 我假设如果您正在使用jdbcTemplate，则已经拥有Spring。

以下是一张速查表，但请查看此网站，并了解有关使用Java Spring和JdbcTemplates查询数据库的更多信息。

行映射器的正确实现如下：

public class FirewallInfoRowMapper implements RowMapper<FirewallInfo>{
  @Override
  public FirewallInfo mapRow(ResultSet rs, int rowNum) throws SQLException{
    return new FirewallInfo(rs.getString("src"), rs.getString("info")) 
}
}


public class FirewallInfo{
  private String src;
  private String info;

  public FirewallInfo(String src, String info){
    this.src = src;
    this.info = info;
  }

  {}<<< Getters and Setters Here
}

- Vladislav Boshnakov

---

我建议您遵循以下编程规范：1）使用标准的Java字段命名约定；2）使用Lombok，不要手动生成getter/setter；3）在RowMapper实现中使用BeanRowPropertyMapper而不是显式映射。 - Andriy Slobodyanyk

首先，感谢您的回复。我的问题不在于如何构建字符串查询；我已经考虑使用join了。 我的困难在于如何正确实现RowMapper，并为List<Item>选择一个好的类型。 - Luca Sepe

谢谢，Vladislav，没问题。 - Luca Sepe

Answer 2

我知道现在已经很晚了。因此，如果有其他人需要知道如何使用Spring和JDBC模板执行连接查询，那么可能没有太多好的教程。这是我完成它的方法。 首先，请确保在您的DAO类中导入以下jar文件，如下所示：

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

然后在你的 FirewallDao 类中，做如下操作：

public List<Firewall> getData(){
    return template.query("SELECT firewalls.info, firewall_items.src FROM firewalls INNER JOIN firewall_items ON firewall_items.firewall_id = firewalls.id",new RowMapper<Firewall>(){
        public Firewall mapRow(ResultSet rs, int row) throws SQLException {
            Firewall f =new Firewall();
            f.setInfo(rs.getString(1));
            f.setSrc(rs.getString(2));
            return f;
        }
    });
}

在你的控制器中，类似以下代码：

@Autowired
FirewallDao dao;

@RequestMapping("/viewinfo")
public ModelAndView viewinfo(){
    List<Firewall> list=dao.getData();
    return new ModelAndView("viewinfo","list",list);
}

在你的观点中，做一些类似于这样的事情。

<table border="2" width="70%" cellpadding="2">
<tr><th>Info</th><th>Src</th></th><th>Edit</th></tr>
<c:forEach var="f" items="${list}"> 
<tr>
<td>${f.info}</td>
<td>${f.src}</td>
</tr>
</c:forEach>
</table>

- yinka