Spring JDBCTemplates：使用Join执行查询

Question

我正在使用Spring Boot和JDBCT模板开发一个用于数据库查询的应用程序。

问题是这样的:如果我必须在一个表上询问db，我没有问题。但是，如果我有一个join，我该如何执行这个任务？

更具体地说，创建表的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（因此，这些是我必须用来执行 join 的变量）。

现在，如果我想执行此查询：

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

我的java代码必须如何，使用jdbctemplate？我是否应该向防火墙类添加一个集合来收集FirewallsItems的对象，如ArrayList？

注意1:我必须使用jdbctemplate项目规范。我不会用Hibernate或者其他仪器。

注意2：我知道行映射器和结果集是什么，我通常将它们与单个表上的查询一起使用。我需要了解的是如何使用它们进行连接查询，就像示例一样。

非常感谢提前回复！

Answer 1

我知道很晚了。那里没有很多好的教程，所以万一有人需要知道如何使用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>

Answer 2

在查询表之前，您应该使用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。

下面是一张备忘单，但请查看此网站，了解更多关于使用javaSpring和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
}