依赖
< dependency>
< groupId> org.springframework.boot</ groupId>
< artifactId> spring-boot-starter-data-jpa</ artifactId>
</ dependency>
< dependency>
< groupId> mysql</ groupId>
< artifactId> mysql-connector-java</ artifactId>
</ dependency>
1. DAO层
import org. springframework. stereotype. Repository ;
import javax. persistence. EntityManager ;
import javax. persistence. ParameterMode ;
import javax. persistence. PersistenceContext ;
import javax. persistence. Query ;
import javax. persistence. StoredProcedureQuery ;
import java. util. List ;
@Repository
public class UserDao {
@PersistenceContext
private EntityManager entityManager;
public void addUser ( User user) {
entityManager. persist ( user) ;
}
public void deleteUser ( Long id) {
User user = entityManager. find ( User . class , id) ;
entityManager. remove ( user) ;
}
public void updateUser ( User user) {
entityManager. merge ( user) ;
}
public User getUserById ( Long id) {
return entityManager. find ( User . class , id) ;
}
public List < Object [ ] > getUserOrderInfo ( ) {
String sql = "SELECT u.*, o.* " +
"FROM user u " +
"INNER JOIN order o ON u.id = o.user_id" ;
Query query = entityManager. createNativeQuery ( sql) ;
return query. getResultList ( ) ;
}
public List < Object [ ] > getUserOrderSummary ( ) {
String sql = "SELECT u.name, COUNT(o.id), SUM(o.amount) " +
"FROM user u " +
"LEFT JOIN order o ON u.id = o.user_id " +
"GROUP BY u.id" ;
Query query = entityManager. createNativeQuery ( sql) ;
return query. getResultList ( ) ;
}
public List < Object [ ] > getUserOrderInfoByProcedure ( Long userId) {
StoredProcedureQuery query = entityManager. createStoredProcedureQuery ( "get_user_order_info" ) ;
query. registerStoredProcedureParameter ( "userId" , Long . class , ParameterMode . IN) ;
query. setParameter ( "userId" , userId) ;
return query. getResultList ( ) ;
}
public List < Object [ ] > getUserOrderInfoByView ( ) {
String sql = "SELECT * FROM user_order_info" ;
Query query = entityManager. createNativeQuery ( sql) ;
return query. getResultList ( ) ;
}
}
2. 服务层
import org. springframework. beans. factory. annotation. Autowired ;
import org. springframework. stereotype. Service ;
import java. util. List ;
@Service
public class UserService {
@Autowired
private UserDao userDao;
public void addUser ( User user) {
userDao. addUser ( user) ;
}
public void deleteUser ( Long id) {
userDao. deleteUser ( id) ;
}
public void updateUser ( User user) { userDao. updateUser ( user) ;
}
public User getUserById ( Long id) {
return userDao. getUserById ( id) ;
}
public List < Object [ ] > getUserOrderInfo ( ) {
return userDao. getUserOrderInfo ( ) ;
}
public List < Object [ ] > getUserOrderSummary ( ) {
return userDao. getUserOrderSummary ( ) ;
}
public List < Object [ ] > getUserOrderInfoByProcedure ( Long userId) {
return userDao. getUserOrderInfoByProcedure ( userId) ;
}
public List < Object [ ] > getUserOrderInfoByView ( ) {
return userDao. getUserOrderInfoByView ( ) ;
}
}
3. SQL脚本
CREATE TABLE user (
id BIGINT PRIMARY KEY AUTO_INCREMENT ,
name VARCHAR ( 50 ) NOT NULL ,
age INT NOT NULL ,
gender VARCHAR ( 10 ) NOT NULL
) ;
CREATE TABLE order (
id BIGINT PRIMARY KEY AUTO_INCREMENT ,
user_id BIGINT NOT NULL ,
amount DECIMAL ( 10 , 2 ) NOT NULL ,
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
FOREIGN KEY ( user_id) REFERENCES user ( id)
) ;
CREATE VIEW user_order_info AS
SELECT u. * , o. *
FROM user u
INNER JOIN order o ON u. id = o. user_id;
CREATE PROCEDURE get_user_order_info ( IN userId BIGINT )
BEGIN
SELECT u. * , o. *
FROM user u
INNER JOIN order o ON u. id = o. user_id
WHERE u. id = userId;
END ;
INSERT INTO user ( name, age, gender) VALUES ( '张三' , 20 , '男' ) ;
INSERT INTO user ( name, age, gender) VALUES ( '李四' , 25 , '女' ) ;
INSERT INTO user ( name, age, gender) VALUES ( '王五' , 30 , '男' ) ;
INSERT INTO order ( user_id, amount) VALUES ( 1 , 100.00 ) ;
INSERT INTO order ( user_id, amount) VALUES ( 2 , 200.00 ) ;
INSERT INTO order ( user_id, amount) VALUES ( 3 , 300.00 ) ;
4. YML文件
spring :
datasource :
url : jdbc: mysql: //localhost: 3306/test? useSSL=false&serverTimezone=UTC
username : root
password : 123456
driver-class-name : com.mysql.cj.jdbc.Driver
jpa :
database : mysql
show-sql : true
hibernate :
ddl-auto : update
properties :
hibernate :
dialect : org.hibernate.dialect.MySQL5Dialect
5. 实体类
import javax. persistence. * ;
import java. math. BigDecimal ;
import java. sql. Timestamp ;
@Entity
@Table ( name = "user" )
public class User {
@Id
@GeneratedValue ( strategy = GenerationType . IDENTITY)
private Long id;
@Column ( name = "name" , nullable = false )
private String name;
@Column ( name = "age" , nullable = false )
private Integer age;
@Column ( name = "gender" , nullable = false )
private String gender;
}
@Entity
@Table ( name = "order" )
public class Order {
@Id
@GeneratedValue ( strategy = GenerationType . IDENTITY)
private Long id;
@ManyToOne ( fetch = FetchType . LAZY)
@JoinColumn ( name = "user_id" , nullable = false )
private User user;
@Column ( name = "amount" , nullable = false )
private BigDecimal amount;
@Column ( name = "create_time" , nullable = false )
private Timestamp createTime;
}