五、spring boot 1.5.4 集成 jpa+hibernate+jdbcTemplate
1.pom添加依赖
<!-- spring data jpa,会注入tomcat jdbc pool/hibernate等 -->
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.42</version> </dependency>
2.添加数据源配置（DataSource啥的，一系列对象spring boot 都会给你注入的，配置配置即可！）
spring.datasource.url=jdbc:mysql://localhost/test?characterEncoding=utf8&useSSL=true spring.datasource.username=root spring.datasource.password=123456 spring.datasource.driver-class-name=com.mysql.jdbc.Driver #database pool config # Number of ms to wait before throwing an exception if no connection is available. spring.datasource.tomcat.max-wait=10000 # Maximum number of active connections that can be allocated from this pool at the same time. spring.datasource.tomcat.max-active=300 # Validate the connection before borrowing it from the pool. spring.datasource.tomcat.test-on-borrow=true # initial pool size spring.datasource.tomcat.initial-size=20
#=====================jpa config================================
#实体类维护数据库表结构的具体行为：update/create/create-drop/validate/none
spring.jpa.hibernate.ddl-auto=none
#打印sql语句
spring.jpa.show-sql=true
#格式化输出的json字符串
spring.jackson.serialization.indent_output=true
3.新建实体
@Entity @Table(name="user") public class User { @Id @Column(name="id") @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; @Column(name="number") private String number; @Column(name="name") private String name; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getNumber() { return number; } public void setNumber(String number) { this.number = number; } public String getName() { return name; } public void setName(String name) { this.name = name; } }
4.dao层
public interface UserDao{ User getById(int id); User getByNumber(String number); int addUser(User user); void deleteUserById(int id); User updateUser(User user); }
@Repository public class UserDaoImpl implements UserDao { @PersistenceContext private EntityManager entityManager; @Override public User getById(int id) { //find by primary key return this.entityManager.find(User.class,id); } @Override public User getByNumber(String number) { Query query = this.entityManager.createQuery("from User u where u.number=:number",User.class); query.setParameter("number",number); User user = (User)query.getSingleResult(); return user; } @Override public int addUser(User user) { this.entityManager.persist(user); //print the id System.out.println(user.getId()); return user.getId(); } @Override public void deleteUserById(int id) { User user = this.entityManager.find(User.class,id); //关联到记录，方可删除 this.entityManager.remove(user); } @Override public User updateUser(User user) { User userNew = this.entityManager.merge(user); return userNew; } }
5.service层
public interface UserService { User getById(int id); User getByNumber(String number); int addUser(User user,boolean throwEx); void deleteUserById(int id); User updateUser(User user); }
@Service @Transactional public class UserServiceImpl implements UserService { @Autowired private UserDao userDao; @Override @Transactional(readOnly = true) public User getById(int id) { return userDao.getById(id); } @Override @Transactional(readOnly = true) public User getByNumber(String number) { return userDao.getByNumber(number); } @Override public int addUser(User user,boolean throwEx) { int id= this.userDao.addUser(user); if(throwEx){ throw new RuntimeException("throw a ex"); } return id; } @Override public void deleteUserById(int id) { this.userDao.deleteUserById(id); } @Override public User updateUser(User user) { return this.userDao.updateUser(user); } }
6.controller层
@Controller("user1")
@RequestMapping("/jpa/user")
public class UserController {
/**
* 日志（slf4j->logback）
*/
private static final Logger logger = LoggerFactory.getLogger(UserController.class);
@Autowired
private UserService userService;
/**
* 返回text格式数据
* @param id 主键id
* @return 用户json字符串
*/
@RequestMapping("/get/id/{id}")
@ResponseBody
public String getUserById(@PathVariable("id")String id){
logger.info("request /user/get/id/{id}, parameter is "+id);
User user = userService.getById(Integer.parseInt(id));
return JSONObject.toJSONString(user);
}
/**
* 返回json格式数据
* @param number 编号
* @return 用户
*/
@RequestMapping("/get/number/{number}")
@ResponseBody
public User getUserByNumber(@PathVariable("number")String number){
User user = userService.getByNumber(number);
return user;
}
@RequestMapping("/add/{number}/{name}")
@ResponseBody
public String addUser(@PathVariable("number")String number,@PathVariable("name")String name,boolean throwEx){
User user = new User();
user.setNumber(number);
user.setName(name);
int id = -1;
try{
id = userService.addUser(user,throwEx);
}catch (RuntimeException ex){
System.out.println(ex.getMessage());
}
return String.valueOf(id);
}
@RequestMapping("/delete/{id}")
@ResponseBody
public void getUserById(@PathVariable("id")int id){
this.userService.deleteUserById(id);
}
@RequestMapping("/update/{id}/{number}/{name}")
@ResponseBody
public User addUser(@PathVariable("id")int id, @PathVariable("number")String number, @PathVariable("name")String name){
User user = new User();
user.setId(id);
user.setNumber(number);
user.setName(name);
return userService.updateUser(user);
}
}
7. spring data jpa新使用方式，更高级
1.dao @Repository public interface UserRepository extends JpaRepository<User, Integer> { /** * spring data jpa 会自动注入实现（根据方法命名规范） * @return */ User findByNumber(String number); @Modifying @Query("delete from User u where u.id = :id") void deleteUser(@Param("id")int id); } 2.service public interface UserService { User findById(int id); User findByNumber(String number); List<User> findAllUserByPage(int page,int size); User updateUser(User user,boolean throwEx); void deleteUser(int id); } @Service @Transactional public class UserServiceImpl implements UserService { @Autowired private UserRepository userRepository; @Override public User findById(int id) { return this.userRepository.findOne(id); } @Override public User findByNumber(String number) { return this.userRepository.findByNumber(number); } @Override public List<User> findAllUserByPage(int page,int size) { Pageable pageable = new PageRequest(page, size); Page<User> users = this.userRepository.findAll(pageable); return users.getContent(); } @Override public User updateUser(User user,boolean throwEx) { User userNew = this.userRepository.save(user); if(throwEx){ throw new RuntimeException("throw a ex"); } return userNew; } @Override public void deleteUser(int id) { this.userRepository.deleteUser(id); } } 3.controller @Controller("user2") @RequestMapping("/datajpa/user") public class UserController { /** * 日志（slf4j->logback） */ private static final Logger logger = LoggerFactory.getLogger(UserController.class); @Autowired private UserService userService; /** * 返回text格式数据 * @param id 主键id * @return 用户json字符串 */ @RequestMapping("/get/id/{id}") @ResponseBody public String getUserById(@PathVariable("id")String id){ logger.info("request /user/get/id/{id}, parameter is "+id); User user = userService.findById(Integer.parseInt(id)); return JSONObject.toJSONString(user); } /** * 返回json格式数据 * @param number 编号 * @return 用户 */ @RequestMapping("/get/number/{number}") @ResponseBody public User getUserByNumber(@PathVariable("number")String number){ User user = userService.findByNumber(number); return user; } @RequestMapping("/get/all/{page}/{size}") @ResponseBody public List<User> getAllUserByPage(@PathVariable("page")int page,@PathVariable("size")int size){ return this.userService.findAllUserByPage(page,size); } @RequestMapping("/update/{id}/{number}/{name}") @ResponseBody public User addUser(@PathVariable("id")int id, @PathVariable("number")String number, @PathVariable("name")String name,boolean throwEx){ User user = new User(); user.setId(id); user.setNumber(number); user.setName(name); User userNew = null; try{ userService.updateUser(user,throwEx); }catch (RuntimeException ex){ System.out.println(ex.getMessage()); } return userNew; } @RequestMapping("/delete/{id}") @ResponseBody public void getUserById(@PathVariable("id")int id){ this.userService.deleteUser(id); } }
8.注入jdbcTemplate和transactionTemplate,使用传统方式操作数据库，更加灵活，方法如下
@Autowired private JdbcTemplate jdbcTemplate; @Autowired private TransactionTemplate transactionTemplate; /** * 手动控制事物测试 * @param throwEx */ @Override public void testTransactionManually(boolean throwEx) { try { transactionTemplate.execute(new TransactionCallback<Boolean>() { /** * 事物代码 * * @param transactionStatus 事物状态 * @return 是否成功 */ @Override public Boolean doInTransaction(TransactionStatus transactionStatus) { User user = new User(); user.setId(1); int a = new Random().nextInt(10); //0-9 user.setNumber("10000u" + a); jdbcTemplate.update("UPDATE USER SET NUMBER=? WHERE ID=?", new Object[]{user.getNumber(), user.getId()}, new int[]{Types.VARCHAR, Types.INTEGER}); if (throwEx) { throw new RuntimeException("try throw exception"); //看看会不会回滚 } return true; } }); }catch (RuntimeException ex){ System.out.println(ex.getMessage()); } } /** * 手动执行jdbc测试 */ @Override public void testJdbcTemplate() { User user = new User(); int a = new Random().nextInt(10); //0-9 user.setNumber("10000i"+ a ); user.setName("name"+a); this.jdbcTemplate.update("INSERT into USER(NUMBER,NAME )VALUES (?,?)",user.getNumber(),user.getName()); }
至此，我已经讲了三种方式（jpa两种+jdbcTemplate）如何操作数据库了，你爱怎么用就怎么用，上述代码均是实践证明可行的！
项目源码：https://github.com/hdwang123/springboottest_onedb
