Spring JDBCTemplates:使用 Join 执行查询 [英] Spring JDBCTemplates: execute a query with Join
问题描述
我正在开发一个用于数据库查询的应用程序，使用 Spring Boot 和 JDBCTemplates.
I'm developing an app for DB querying, using Spring Boot and JDBCTemplates.
问题是这样的:如果我必须在单个表上询问数据库，我没有问题.但是，如果我有一个加入，我该如何执行此任务?
The problem is this: if I have to ask the db on a single table, I have no problems. But, if I have a join, how can I perform this task?
更具体地说，创建表的 SQL 命令如下:
More specifically, the SQL commands to create tables are these:
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类是这些:
The correspondings java class are these:
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(所以，这些是我必须用来执行加入的变量).
The constraint is that firewall_Items.firewall_id = firewall.id (so, these are the variables that i must use to perform join).
现在，如果我想执行这个查询:
Now, if i want perform this query:
SELECT info, src
FROM firewalls, firewall_items
WHERE firewall_items.firewall_id = firewalls.id;
我的java代码必须是怎样的，使用jdbctemplate?我是否应该向防火墙类添加一个集合来收集 FirewallsItems 的对象，例如 ArrayList?
How my java code must be, using jdbctemplate? Should i add to firewall class a collection to collect object of FirewallsItems, like an ArrayList?
注意1:我必须使用jdbctemplate 项目规范.我无法使用 Hibernate 或其他工具.
Note1: i must use jdbctemplate project specifications. I can't use Hibernate or other instruments.
注2:我知道行映射器和结果集是什么，我将它们与单个表的查询一起使用.我需要的是了解如何将它们用于带有连接的查询，就像示例中的那样.
Note2: I know what rowmapper and resultset are, i regolary use them with query on a single table. What i nedd is to understand how to use them for a query with join, like that of the example.
非常感谢您的回复！
推荐答案
在查询表之前，您应该使用 JOIN 关键字来联接它们.像这样:
you should use the JOIN keyword to join your tables before you query them. Like so:
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 是您检索到的对象.你决定那是什么.
Where Item is your retrieved object. You decide what that is.
查看这篇文章了解更多信息
回应您的进一步询问.上面是jdbcTemplate的修改用法，下面可以找到你需要的类.这要求您拥有 Spring.我假设如果您使用 jdbcTemplate，那么您已经拥有 Spring.
In response to your further inquiry. Above is the amended usage of jdbcTemplate, below you can find the classes you need. This requires you to have Spring. I've assumed that if you're using jdbcTemplate you already have Spring.
下面是一个备忘单，但请看这个 站点并了解有关使用 java Spring 和 jdbcTemplates 查询数据库的更多信息.
Below is a cheat sheet, but please look at this site and learn more about querying databases with java Spring and jdbcTemplates.
行映射器的正确实现如下:
The correct implementation for a row mapper is like so :
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
}
这篇关于Spring JDBCTemplates:使用 Join 执行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
