sql - Spring JDBCTemplate : execute a query with 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 是什么，我经常将它们用于单个表的查询。我需要的是了解如何将它们用于带有连接的查询，就像示例中那样。

非常感谢您的回复!

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 是您检索到的对象。你决定那是什么。

看this文章了解更多信息

编辑:

响应您的进一步询问。以上是jdbcTemplate修改后的用法，下面可以找到你需要的类。这需要你有 Spring .我假设如果您使用的是 jdbcTemplate，那么您已经拥有了 Spring。

下面是备忘单，但请看this站点并了解有关使用 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
}