idea 建立JPA项目（一）
一、建立项目
依赖项选择：
pom.xml:
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.5.2</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.example</groupId> <artifactId>JPADemo</artifactId> <version>0.0.1-SNAPSHOT</version> <name>JPADemo</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <excludes> <exclude> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </exclude> </excludes> </configuration> </plugin> </plugins> </build> </project>
二、设置数据源 application.properties
#应用名称
spring.application.name=springboot-demo
server.port=8888
server.servlet.context-path=/website
#编码格式
server.tomcat.uri-encoding=utf-8
#数据库相关配置
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url=jdbc:sqlserver://127.0.0.1:1433/springbootTestDB
spring.datasource.username=sa
spring.datasource.password=6617saSA
#指定连接池最大的空闲连接数量
spring.datasource.max-idle=10
#指定连接池等待连接返回的最大等待时间，毫秒单位
spring.datasource.max-wait=10000
#指定必须保持连接的最小值(For DBCP and Tomcat connection pools)
spring.datasource.min-idle=5
#指定启动连接池时，初始建立的连接数量
spring.datasource.initial-size=5
#session生命周期
server.servlet.session.timeout=30m
三、创建User的实体类（省略了getter&setter方法）
package com.example.jpademo.Entity; import lombok.Data; import javax.persistence.*; /*使用JPA注解配置映射关系,告诉JPA这是一个实体类（和数据表映射的类）*/ @Data @Entity @Table(name = "t_user") //@Table来指定和哪个数据表对应；如果省略，默认表名就是该类名的小写：user public class User { @Id //表示这个属性是数据表中的主键 @GeneratedValue(strategy = GenerationType.IDENTITY) //还是一个自增的主键 private Long id; @Column(name = "firstName", length = 50) private String firstName; @Column(name = "lastName", length = 50) private String lastName; @Column //省略的情况，默认列名就是属性名，均是小写 private String Email; //String数据库默认定义为varchar(255) protected User() {}; public User(String firstName,String lastName){ this.firstName=firstName; this.lastName=lastName; } }
使用JPA的一个好处就是，它能够根据实体类自动帮我们创建对应的数据库表，只需简单配置几步即可：
在application.properties文件中增加（jpa 也是spring下的一个属性，所以它也是在spring这个层级下的）：
#update:表的生成策略,第一次启动根据实体建立表结构，之后启动会根据实体的改变更新表结构，之前的数据都在。
spring.jpa.hibernate.ddl-auto=create
#在控制台输出SQL
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
运行主程序，错误！！！
四、安装Sql Server JDBC驱动
建立目录jdbc , 将mssql-jdbc-6.4.0.jre8.jar拷贝到该目录，加入库中：
运行仍然错误！
错误原因：数据源的url定义错误
错误：spring.datasource.url=jdbc:sqlserver://127.0.0.1:1433/springbootTestDB
正确：spring.datasource.url=jdbc:sqlserver://127.0.0.1:1433;DatabaseName=springbootTestDB
修改后运行结果：
建立的数据库：
说明：
JPA命名数据库字段的规则：
@Column(name = "firstName", length = 50) private String firstName;
虽然用 @Column 命名了数据库字段名称，但是数据库中仍然用 first_name 而不是 firstName ， 原因：驼峰命名的字段改为用下划线隔开的的方式。
如果改为
@Column(name = "firstname", length = 50) 改为小写，数据库字段名称就是 firstname
private String firstName;
默认Java对象的驼峰式命名对应数据库的下划线命名
五、创建接口(持久层接口）
JPA中有许多封装好的对数据库进行操作的方法，不需要再写sql语句，而是直接调用其中的方法，就可以完成对数据的操作。持久层接口需继承JpaRepository类。
package com.example.jpademo.Dao; import com.example.jpademo.Entity.User; import org.springframework.data.jpa.repository.JpaRepository; public interface UserRepository extends JpaRepository<User,Long> { }
六、修改入口类
package com.example.jpademo; import com.example.jpademo.Dao.UserRepository; import com.example.jpademo.Entity.User; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.boot.CommandLineRunner; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.context.annotation.Bean; @SpringBootApplication public class JpaDemoApplication { private static final Logger log= LoggerFactory.getLogger(JpaDemoApplication.class); public static void main(String[] args) { SpringApplication.run(JpaDemoApplication.class, args); } @Bean public CommandLineRunner demo(UserRepository respository) { return (args) -> { respository.save(new User("张","少华")); respository.save(new User("李","三立")); respository.save(new User("王","大海")); respository.save(new User("王","大海")); respository.save(new User("王","大海")); log.info("-----------------------------------"); for(Object user: respository.findAll()) { log.info(user.toString()); } log.info("-----------------------------------"); respository.findById(1L).ifPresent( user-> { log.info("User found with findBtId(1L):"); log.info(user.toString()); } ); }; } }
运行结果：
七、程序扩展：创建UserCrudRepository
package com.example.jpademo.Dao; import com.example.jpademo.Entity.User; import org.springframework.data.repository.CrudRepository; import java.util.List; public interface UserCrudReposity extends CrudRepository<User,Long> { List<User> findByLastName(String lastName); //根据JPA命名规则，自动生成SQL }
八、修改入口类的代码
package com.example.jpademo; import com.example.jpademo.Dao.UserCrudReposity; import com.example.jpademo.Dao.UserRepository; import com.example.jpademo.Entity.User; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.boot.CommandLineRunner; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.context.annotation.Bean; @SpringBootApplication public class JpaDemoApplication { private static final Logger log= LoggerFactory.getLogger(JpaDemoApplication.class); public static void main(String[] args) { SpringApplication.run(JpaDemoApplication.class, args); } @Bean public CommandLineRunner demo(UserCrudReposity respository) { return (args) -> { respository.save(new User("张","少华")); respository.save(new User("李","三立")); respository.save(new User("王","大海")); respository.save(new User("王","大海")); respository.save(new User("王","大海")); log.info(""); log.info("-----------------------------------"); for(Object user: respository.findAll()) { log.info(user.toString()); } log.info(""); log.info("-----------------------------------"); respository.findById(1L).ifPresent( user-> { log.info("User found with findBtId(1L):"); log.info(user.toString()); } ); log.info(""); log.info("\n\n------------ 以大海的查询结果 -----------------------"); for(Object user: respository.findByLastName("大海")) { log.info(user.toString()); } log.info("\n\n------------ 以大海的查询结果 -----------------------"); respository.findByLastName("大海").forEach(user->{ // 默认的命名规则 log.info(user.toString()); }); }; } }
运行结果：
九、JPA访问数据库的命名规则（命名查询）
public interface UserCrudReposity extends CrudRepository<User,Long> { List<User> findByLastName(String lastName); }
前面定义的接口中的方法：List<User> findByLastName(String lastName) 并没有实现代码，这里JPA采用了默认的命名规则，参见：
https://docs.spring.io/spring-data/jpa/docs/2.0.9.RELEASE/reference/html/#jpa.repositories 参见 5.3.2. 的Table 3
使用命名查询来声明实体查询是一种有效的方法，并且适用于少量查询。
十、使用 @Query注释创建自定义查询
package com.example.jpademo.Dao; import com.example.jpademo.Entity.User; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.CrudRepository; import java.util.List; public interface UserCrudReposity extends CrudRepository<User,Long> { List<User> findByLastName(String lastName); @Query("select u from User u where u.lastName like %?1% or u.Email like %?2%") List<User> findByEmailAndFirstName(String lastname , String email); }
十一、定义控制器
package com.example.jpademo.Controller; import com.example.jpademo.Dao.UserCrudReposity; import com.example.jpademo.Entity.User; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.*; import java.util.List; import java.util.Optional; @Controller @RequestMapping(path="/demo") public class UserController { //@Autowired是spring的自动装配，可用于构造器、变量域、方法、注解类型上。当需要从bean工厂中获取一个bean时， // Spring会自动为我们装配该bean中标记为@Autowired的元素。 @Autowired private UserCrudReposity userCrudReposity; @GetMapping(path="/add") @ResponseBody //在浏览器中输入 http://localhost:8888/website/demo/add?firstname=张&lastname=宇航， public String addNewUser(@RequestParam String firstname,@RequestParam String lastname) { User user=new User(firstname,lastname,""); userCrudReposity.save(user); return "saved"; } //http://localhost:8888/website/demo/finduser/大海 @GetMapping(path="/finduser/{lastname}") //1个占位符 @ResponseBody public String finduser(@PathVariable("lastname") String lastname) { List<User> userList= userCrudReposity.findByLastName(lastname); String users=""; for(User user:userList) { users+=user.toString()+" "; } return users; } //http://localhost:8888/website/demo/finduser?lastname=大海&email=139 @GetMapping(path="/finduser") //用请求参数 @ResponseBody public String finduser1(@RequestParam String lastname,@RequestParam String email) { List<User> userList= userCrudReposity.findByEmailAndFirstName(lastname,email); String users=""; for(User user:userList) { users+=user.toString()+" "; } return users; } //http://localhost:8888/website/demo/finduser/大海/139 @GetMapping(path="/finduser/{lastname}/{email}") //2个占位符 @ResponseBody public String finduser2(@PathVariable("lastname") String lastname, @PathVariable("email") String email) { List<User> userList= userCrudReposity.findByEmailAndFirstName(lastname,email); String users=""; for(User user:userList) { users+=user.toString()+" "; } return users; } }
十二、扩展
数据库访问：
package com.example.jpademo.Dao; import com.example.jpademo.Entity.User; import org.springframework.data.jpa.repository.Modifying; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.CrudRepository; import org.springframework.data.repository.query.Param; import org.springframework.transaction.annotation.Transactional; import java.util.List; public interface UserCrudReposity extends CrudRepository<User,Long> { List<User> findByLastName(String lastName); @Query("select u from User u where u.lastName like %?1% or u.Email like %?2%") List<User> findByEmailAndFirstName(String lastname , String email); @Query("select u from User u where u.firstName=?1 and u.lastName=?2") public List<User> queryUser(String fname,String lname); @Query("select u from User u where u.firstName=:fname and u.lastName=:lname") public List<User> queryUser1(@Param("fname") String firstname, @Param("lname") String lastname); @Transactional @Modifying(clearAutomatically = true) //@Modifying @Query("update User u set u.Email = ?1 where u.lastName=?2") int updateEmailBylastName(String email,String lastName); }
控制器：
package com.example.jpademo.Controller; import com.example.jpademo.Dao.UserCrudReposity; import com.example.jpademo.Entity.User; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.*; import java.util.List; import java.util.Optional; @Controller @RequestMapping(path="/demo") public class UserController { //@Autowired是spring的自动装配，可用于构造器、变量域、方法、注解类型上。当需要从bean工厂中获取一个bean时，Spring会自动为我们装配该bean中标记为@Autowired的元素。 //@Autowired-标记要通过 Spring 依赖注入自动装配的构造函数，字段或 setter 方法。 @Autowired private UserCrudReposity userCrudReposity; @GetMapping(path="/add") @ResponseBody //在浏览器中输入 http://localhost:8888/website/demo/add?firstname=张&lastname=宇航 public String addNewUser(@RequestParam String firstname,@RequestParam String lastname) { User user=new User(firstname,lastname,""); userCrudReposity.save(user); return "saved"; } //http://localhost:8888/website/demo/finduser/大海 @GetMapping(path="/finduser/{lastname}") //1个占位符 @ResponseBody public String finduser(@PathVariable("lastname") String lastname) { List<User> userList= userCrudReposity.findByLastName(lastname); String users=""; for(User user:userList) { users+=user.toString()+" "; } return users; } //http://localhost:8888/website/demo/finduser?lastname=大海&email=139 @GetMapping(path="/finduser") //用请求参数 @ResponseBody public String finduser1(@RequestParam String lastname,@RequestParam String email) { List<User> userList= userCrudReposity.findByEmailAndFirstName(lastname,email); String users=""; for(User user:userList) { users+=user.toString()+" "; } return users; } //http://localhost:8888/website/demo/finduser/大海/139 @GetMapping(path="/finduser/{lastname}/{email}") //2个占位符 @ResponseBody public String finduser2(@PathVariable("lastname") String lastname, @PathVariable(value = "email") String email) { List<User> userList= userCrudReposity.findByEmailAndFirstName(lastname,email); String users=""; for(User user:userList) { users+=user.toString()+" "; } return users; } @GetMapping(path="/queryUser") @ResponseBody public String queryUser(@RequestParam String firstname,@RequestParam String lastname) { List<User> userList= userCrudReposity.queryUser(firstname,lastname); String users=""; for(User user:userList) { users+=user.toString()+" "; } return users; } @GetMapping(path="/queryUser1") @ResponseBody public String queryUser1(@RequestParam String firstname,@RequestParam String lastname) { List<User> userList= userCrudReposity.queryUser1(firstname,lastname); String users=""; for(User user:userList) { users+=user.toString()+" "; } return users; } @GetMapping(path="/update") @ResponseBody public String update(@RequestParam String lastname,@RequestParam String email) { userCrudReposity.updateEmailBylastName(email,lastname); return "update"; } }
