艺人管理系统建立三个表三个表之间有联系,创建一个触发器和两个带参存储过程,Java实现功能和页面的程序代码
时间: 2024-02-13 18:01:34 浏览: 37
首先,我们需要创建三个表,分别是:artist(艺人表)、agency(经纪公司表)和artist_agency(艺人与经纪公司关联表)。
artist表的结构如下:
```
CREATE TABLE artist (
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
gender VARCHAR(10),
age INT,
phone VARCHAR(20),
email VARCHAR(255),
address VARCHAR(255)
);
```
agency表的结构如下:
```
CREATE TABLE agency (
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
phone VARCHAR(20),
email VARCHAR(255),
address VARCHAR(255)
);
```
artist_agency表的结构如下:
```
CREATE TABLE artist_agency (
id INT PRIMARY KEY,
artist_id INT NOT NULL,
agency_id INT NOT NULL,
start_date DATE,
end_date DATE,
FOREIGN KEY (artist_id) REFERENCES artist(id),
FOREIGN KEY (agency_id) REFERENCES agency(id)
);
```
接下来,我们创建一个触发器,用于在插入或更新artist_agency表时,自动更新artist表中的经纪公司信息。如果某个艺人的经纪公司发生变化,那么该艺人在artist表中的agency_id字段也需要更新。
```
CREATE TRIGGER update_artist_agency
AFTER INSERT OR UPDATE ON artist_agency
FOR EACH ROW
BEGIN
UPDATE artist SET agency_id = NEW.agency_id WHERE id = NEW.artist_id;
END;
```
接下来,我们创建两个带参存储过程,分别用于插入艺人和经纪公司信息。这里我们使用Java程序来执行这些存储过程。
插入艺人信息的存储过程:
```
CREATE PROCEDURE insert_artist(
IN name VARCHAR(255),
IN gender VARCHAR(10),
IN age INT,
IN phone VARCHAR(20),
IN email VARCHAR(255),
IN address VARCHAR(255)
)
BEGIN
INSERT INTO artist (name, gender, age, phone, email, address)
VALUES (name, gender, age, phone, email, address);
END;
```
插入经纪公司信息的存储过程:
```
CREATE PROCEDURE insert_agency(
IN name VARCHAR(255),
IN phone VARCHAR(20),
IN email VARCHAR(255),
IN address VARCHAR(255)
)
BEGIN
INSERT INTO agency (name, phone, email, address)
VALUES (name, phone, email, address);
END;
```
最后,我们使用Java程序来实现这些功能和页面。这里我们使用Spring Boot框架和Thymeleaf模板引擎来完成。具体代码实现可以参考下面的示例代码:
```
// Artist实体类
@Entity
@Table(name="artist")
public class Artist {
@Id
private Long id;
private String name;
private String gender;
private Integer age;
private String phone;
private String email;
private String address;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "agency_id")
private Agency agency;
// 省略getter和setter方法
}
// Agency实体类
@Entity
@Table(name="agency")
public class Agency {
@Id
private Long id;
private String name;
private String phone;
private String email;
private String address;
// 省略getter和setter方法
}
// ArtistAgency实体类
@Entity
@Table(name="artist_agency")
public class ArtistAgency {
@Id
private Long id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "artist_id")
private Artist artist;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "agency_id")
private Agency agency;
private LocalDate startDate;
private LocalDate endDate;
// 省略getter和setter方法
}
// ArtistController控制器类
@Controller
public class ArtistController {
@Autowired
private ArtistRepository artistRepository;
@Autowired
private AgencyRepository agencyRepository;
@GetMapping("/artists")
public String list(Model model) {
List<Artist> artists = artistRepository.findAll();
model.addAttribute("artists", artists);
return "artist/list";
}
@GetMapping("/artist/create")
public String createForm(Model model) {
model.addAttribute("artist", new Artist());
return "artist/form";
}
@PostMapping("/artist/create")
public String create(@ModelAttribute Artist artist) {
artistRepository.save(artist);
return "redirect:/artists";
}
@GetMapping("/agency/create")
public String createForm(Model model) {
model.addAttribute("agency", new Agency());
return "agency/form";
}
@PostMapping("/agency/create")
public String create(@ModelAttribute Agency agency) {
agencyRepository.save(agency);
return "redirect:/artists";
}
}
// ArtistRepository接口
public interface ArtistRepository extends JpaRepository<Artist, Long> {}
// AgencyRepository接口
public interface AgencyRepository extends JpaRepository<Agency, Long> {}
// application.properties配置文件
spring.datasource.url=jdbc:mysql://localhost:3306/artist_management_system?useSSL=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
spring.thymeleaf.mode=HTML
spring.thymeleaf.prefix=classpath:/templates/
spring.thymeleaf.suffix=.html
```
这样,我们就实现了一个简单的艺人管理系统,包括插入艺人和经纪公司信息、查询艺人列表等功能。