SpringBoot 集成H2 内存数据库解析

不点阅读：689 2021-03-31 13:19:17 评论：0

第一步：添加相关jar包依赖

        <!--h2--> 
		<dependency> 
			<groupId>com.h2database</groupId> 
			<artifactId>h2</artifactId> 
			<scope>runtime</scope> 
		</dependency> 
		<!--spring-data-jpa--> 
		<dependency> 
			<groupId>org.springframework.boot</groupId> 
			<artifactId>spring-boot-starter-data-jpa</artifactId> 
		</dependency> 
		<!--lombok--> 
        <dependency> 
            <groupId>org.projectlombok</groupId> 
            <artifactId>lombok</artifactId> 
        </dependency>

第二步：application.properties 配置文件添加H2 内存数据库配置

#h2配置 
#启用SQL语句的日志记录 
spring.jpa.show-sql=true 
#设置ddl模式 
spring.jpa.hibernate.ddl-auto=update 
##数据库连接设置 
#配置h2数据库的连接地址 
spring.datasource.url=jdbc:h2:mem:dbtest 
spring.datasource.username=sa 
spring.datasource.password=sa 
spring.datasource.driverClassName=org.h2.Driver 
##数据初始化设置 
#进行该配置后，每次启动程序，程序都会运行resources/db/schema.sql文件，对数据库的结构进行操作。 
spring.datasource.schema=classpath:db/schema.sql 
#进行该配置后，每次启动程序，程序都会运行resources/db/data.sql文件，对数据库的数据操作。 
spring.datasource.data=classpath:db/data.sql 
##h2 web console设置 
#表明使用的数据库平台是h2 
spring.datasource.platform=h2 
# 进行该配置后，h2 web consloe就可以在远程访问了。否则只能在本机访问。 
spring.h2.console.settings.web-allow-others=true 
#进行该配置，你就可以通过YOUR_URL/h2访问h2 web consloe。YOUR_URL是你程序的访问URl。 
spring.h2.console.path=/h2 
#进行该配置，程序开启时就会启动h2 web consloe。当然这是默认的，如果你不想在启动程序时启动h2 web consloe，那么就设置为false。 
spring.h2.console.enabled=true

第三步：添加H2内存数据库的数据结构和数据

resources/db/schema.sql

create table if not exists USER ( 
USE_ID int not null primary key auto_increment, 
USE_NAME varchar(100), 
USE_SEX varchar(1), 
USE_AGE NUMBER(3), 
USE_ID_NO VARCHAR(18), 
USE_PHONE_NUM VARCHAR(11), 
USE_EMAIL VARCHAR(100), 
CREATE_TIME DATE, 
MODIFY_TIME DATE, 
USE_STATE VARCHAR(1));

resourses/db/data.sql

INSERT INTO USER (USE_ID,USE_NAME,USE_SEX,USE_AGE,USE_ID_NO,USE_PHONE_NUM,USE_EMAIL,CREATE_TIME,MODIFY_TIME,USE_STATE) VALUES( 
1,'赵一','0',20,'142323198610051234','12345678910','qe259@163.com',sysdate,sysdate,'0'); 
INSERT INTO USER (USE_ID,USE_NAME,USE_SEX,USE_AGE,USE_ID_NO,USE_PHONE_NUM,USE_EMAIL,CREATE_TIME,MODIFY_TIME,USE_STATE) VALUES( 
2,'钱二','0',22,'142323198610051235','12345678911','qe259@164.com',sysdate,sysdate,'0'); 
INSERT INTO USER (USE_ID,USE_NAME,USE_SEX,USE_AGE,USE_ID_NO,USE_PHONE_NUM,USE_EMAIL,CREATE_TIME,MODIFY_TIME,USE_STATE) VALUES( 
3,'孙三','1',24,'142323198610051236','12345678912','qe259@165.com',sysdate,sysdate,'0');

第四步：添加H2内存数据库的实体映射和数据操作接口

实体对象:User

package com.zzg.entity; 
 
 
 
import java.util.Date; 
 
import javax.persistence.Column; 
import javax.persistence.Entity; 
import javax.persistence.GeneratedValue; 
import javax.persistence.GenerationType; 
import javax.persistence.Id; 
 
import lombok.Getter; 
import lombok.Setter; 
 
@Getter 
@Setter 
@Entity 
public class User { 
	@Id 
    @GeneratedValue(strategy = GenerationType.IDENTITY) // 自增长策略 
	private Integer useId; 
	 
	@Column 
	private String useName; 
	 
	@Column 
	private String useSex; 
	 
	@Column 
	private Integer useAge; 
	 
	@Column 
	private String useIdNo; 
	 
	@Column 
	private String usePhoneNum; 
	 
	@Column 
	private String useEmail; 
	 
	@Column 
	private Date createTime; 
	 
	@Column 
	private Date modifyTime; 
	 
	@Column 
	private String useState; 
}

操作接口：UserRepository

package com.zzg.dao; 
 
import org.springframework.data.jpa.repository.JpaRepository; 
import org.springframework.data.jpa.repository.JpaSpecificationExecutor; 
import org.springframework.stereotype.Repository; 
 
import com.zzg.entity.User; 
 
@Repository 
public interface UserRepository extends JpaRepository<User, Integer>, JpaSpecificationExecutor<User> { 
 
}

第五步：H2内存数据库查询功能验证

控制层定义

package com.zzg.controller; 
 
import java.util.ArrayList; 
import java.util.List; 
 
import javax.persistence.criteria.CriteriaBuilder; 
import javax.persistence.criteria.CriteriaQuery; 
import javax.persistence.criteria.Predicate; 
import javax.persistence.criteria.Root; 
 
import org.springframework.beans.factory.annotation.Autowired; 
import org.springframework.data.jpa.domain.Specification; 
import org.springframework.web.bind.annotation.GetMapping; 
import org.springframework.web.bind.annotation.PathVariable; 
import org.springframework.web.bind.annotation.RestController; 
 
import com.zzg.dao.UserRepository; 
import com.zzg.entity.User; 
 
@RestController 
public class UserController { 
	@Autowired 
	private UserRepository userRepository; 
	 
	 
	@GetMapping("/user/{id}") 
    public User findById(@PathVariable Integer id) { 
        return userRepository.findOne(new Specification<User>() { 
			 
			@Override 
			public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { 
				// TODO Auto-generated method stub 
				 List<Predicate> predicates = new ArrayList<Predicate>(); 
	             // 用户编号 
	             predicates.add(criteriaBuilder.equal(root.<Integer> get("useId"), id)); 
	                 
				return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction(); 
			} 
		}).orElse(null); 
    } 
 
}