我刚接触Spring,不知道如何联接多个表来返回某个结果。我尝试实现一个小型库应用程序,如下所示。
我的实体类-预订,客户,预订
java-图书馆中的书籍
@Entity
@Table(name = "books")
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", columnDefinition = "int")
private int id;
@NotNull(message = "Book name cannot be null")
@Column(name = "book_name", columnDefinition = "VARCHAR(255)")
private String bookName;
@Column(name = "author", columnDefinition = "VARCHAR(255)")
private String author;
// getters and setters
public Book() {}
public Book(String bookName, String author) {
this.bookName = bookName;
this.author = author;
}
}
@Entity
@Table(name = "customer", uniqueConstraints = {@UniqueConstraint(columnNames = {"phone"})})
public class Customer {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", columnDefinition = "int")
private int id;
@NotNull(message = "Customer name cannot be null")
@Column(name = "name", columnDefinition = "VARCHAR(255)")
private String name;
@Column(name = "phone", columnDefinition = "VARCHAR(15)")
private String phone;
@Column(name = "registered", columnDefinition = "DATETIME")
private String registered;
// getters and setters
public Customer() {}
public Customer(String name, String phone, String registered) {
this.name = name;
this.phone = phone;
this.registered = registered;
}
}
@Entity
@Table(name = "bookings")
public class Booking {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", columnDefinition = "int")
private int id;
@NotNull(message = "Book id cannot be null")
@Column(name = "book_id", columnDefinition = "int")
private int bookId;
@NotNull(message = "Customer id cannot be null")
@Column(name = "customer_id", columnDefinition = "int")
private int customerId;
@Column(name = "issue_date", columnDefinition = "DATETIME")
private String issueDate;
@Column(name = "return_date", columnDefinition = "DATETIME")
private String returnDate;
// getters and setters
public Booking() {}
public Booking(int bookId, int customerId, String issueDate) {
this.bookId = bookId;
this.customerId = customerId;
this.issueDate = issueDate;
}
}
books: +-----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | book_name | varchar(255) | NO | | NULL | | | author | varchar(255) | YES | | NULL | | +-----------+--------------+------+-----+---------+----------------+ id - primary key customer: +------------+--------------+------+-----+-------------------+-------------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+-------------------+-------------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | | NULL | | | registered | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | | phone | varchar(15) | YES | UNI | NULL | | +------------+--------------+------+-----+-------------------+-------------------+ id - primary key bookings: +-------------+----------+------+-----+-------------------+-------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+-------------------+-------------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | book_id | int(11) | NO | MUL | NULL | | | customer_id | int(11) | NO | MUL | NULL | | | issue_date | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | | return_date | datetime | YES | | NULL | | +-------------+----------+------+-----+-------------------+-------------------+ id - primary key book_id - foreign key references books.id customer_id - foreign key references customer.id
订票员:
@RestController
@RequestMapping("/bookings")
public class BookingController {
@Autowired
BookingService bookingService;
// some booking apis which return Booking objects
@GetMapping
public List<Booking> getAllBookingsBy(@RequestParam("phone") String phone,
@RequestParam("authors") List<String> authors) {
return bookingService.getAllBy(phone, authors);
}
@PostMapping
public Booking addBooking(@RequestBody Booking booking) {
bookingService.saveBooking(booking);
return booking;
}
}
预订服务类别:
@Service
public class BookingService {
@Autowired
private BookingRepository bookingRepository;
// some booking service methods
// get all bookings booked by a customer with matching phone number and books written by a given list of authors
public List<Booking> getAllBy(String phone, List<String> authors) {
return bookingRepository.queryBy(phone, authors);
}
public void saveBooking(Booking booking) {
bookingRepository.save(booking);
}
}
预订存储库类:
@Repository
public interface BookingRepository extends JpaRepository<Booking, Integer> {
// some booking repository methods
@Query(value = "SELECT * FROM bookings bs WHERE " +
"EXISTS (SELECT 1 FROM customer c WHERE bs.customer_id = c.id AND c.phone = :phone) " +
"AND EXISTS (SELECT 1 FROM books b WHERE b.id = bs.book_id AND b.author IN :authors)",
nativeQuery = true)
List<Booking> queryBy(@Param("phone") String phone,
@Param("authors") List<String> authors);
}
[
{
"id": 3,
"book_id": 5,
"customer_id": 2,
"issue_date": "2019-02-04 01:45:21",
"return_date": null
}
]
[
{
"id": 3,
"book_id": 5,
"customer_id": 2,
"issue_date": "2019-02-04 01:45:21",
"return_date": null,
"customer_name": "Cust 2",
"book_name": "Book_2_2",
}
]
@OneToOne
@JoinColumn(name = "book_id", insertable = false, updatable = false)
private Book book;
@OneToOne
@JoinColumn(name = "customer_id", insertable = false, updatable = false)
private Customer customer;
[
{
"id": 3,
"book_id": 5,
"book": {
"id": 5,
"book_name": "Book_2_2",
"author": "author_2"
},
"customer_id": 2,
"customer": {
"id": 2,
"name": "Cust 2",
"phone": "98765431",
"registered": "2019-02-04 01:13:16"
},
"issue_date": "2019-02-04 01:45:21",
"return_date": null
}
]
你的所作所为是错的。您正在返回Booking,并且您希望它被magicaly反序列化为一个包含连接信息的实体,比如Book Name。但是在存储库的select查询中,您已经选择了预订。在您的实现中,预订并不包含关于图书的信息。
首先,您需要将反序列化为JSON的内容和将用作spring数据持久层的内容分开。
@onetoone
/@onetomany
关系。@Query(value = "SELECT * FROM bookings bs WHERE " +
"bs.customer.phone = :phone) " +
"AND bs.book.author IN :authors)")
在Jenkins(Freestyle)中创建新项目时,可以在“源代码管理”下选择多个Git存储库。 我想创建一个构建后操作来将来自两个存储库的文件归档为工件。我在每个存储库上单击了“高级...”,以便为每个存储库命名,但当我检查工作区时,我仍然只能在那里看到最新的存储库。 关于如何在工作区上看到这两个回购,有什么提示吗?
我在删除联接表中引用的实体时遇到问题。以下是三个链接的enitie。 当我尝试使用CrudRepository从来宾表中删除来宾时,它会给我这个错误。 错误:表“guest”上的更新或删除违反了表“guest\u group\u join”上的外键约束“FKKOUGVMCU860MOUACR1SHJXY”。键(id)=(4)仍然从表“guest\u group\u join”中引用。 有人能帮忙吗
问题内容: 我如何将下面的MySQL查询写入Rails ActiveRecord 我知道如何在两个表上编写联接;但是,我对如何在3个表上使用联接不是很有信心。 问题答案: 要重写您在问题中遇到的SQL查询,我认为它应该类似于以下内容(尽管我很难完全可视化您的模型关系,所以这有点猜测): …这样该方法可以同时处理两个联接以及子句,最后是调用。 作为更多参考: 如果要将多个关联加入同一模型,则可以简单
这是我第一次尝试将更复杂的对象存储到数据库中。我需要一些关于数据库设计的帮助。 要存储并从数据库中重新生成的recipe对象 最复杂的部分是对象。每个食谱可以有不同数量的步骤和不同的配料分配给每一个步骤。 我做了另一个设计,其中与以及与相结合。我认为第一种布局更容易查询,因为我只需查看就可以通过进行搜索,但我不确定。有什么想法吗?
前面讲过,无向图的存储可以使用邻接表,但在实际使用时,如果想对图中某顶点进行实操(修改或删除),由于邻接表中存储该顶点的节点有两个,因此需要操作两个节点。 为了提高在无向图中操作顶点的效率,本节学习一种新的适用于存储无向图的方法—— 邻接多重表。 注意,邻接多重表仅适用于存储无向图或无向网。 邻接多重表存储无向图的方式,可看作是邻接表和十字 链表的结合。同邻接表和十字链表存储图的方法相同,都是独自
问题内容: 我正在尝试找出SQLAlchemy中正确的联接查询设置,但似乎无法解决。 我有以下表格设置(简化后,我省略了非必要字段): 因此,关系如下: 1:n Group Member 1:n Member Item 1:n Version Item 我想通过从数据库中选择具有特定版本的所有项目行来构建查询。然后,我想按组然后按成员订购。使用Flask / WTForm的输出应如下所示: 我想出