SpringBoot笔记5---如何读写数据库之通过Query对象读写数据库

关于如何配置数据库以及需要添加哪些依赖包，在《SpringBoot笔记3---如何读写数据库之使用CrudRepository中提供的方法来读写数据库》中有详细说明，这里不再赘述。本文基于SpringBoot笔记3一文的基础上，说明如何通过Query对象来读写数据库。

1 新建UserPersistence类如下：

package com.gm.springboot_test.persistence;

import java.util.List;

import com.gm.springboot_test.entity.User;

public interface UserPersistence {
	public List<User> getByName(String name);
}

2 新建UserPersistenceImpl类如下：

package com.gm.springboot_test.persistence.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import com.gm.springboot_test.entity.User;
import com.gm.springboot_test.persistence.UserPersistence;

@Repository
public class UserPersistenceImpl implements UserPersistence {

	private static final String TABLE_NAME = "user";
	
	@PersistenceContext
    private EntityManager em;
	
	@Override
	public List<User> getByName(String name) {
		// TODO Auto-generated method stub
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("select id, name, age, sex from " + TABLE_NAME + " where name = ?1");
		
		Map<Integer, Object> sqlParams = new HashMap<Integer, Object>();
		sqlParams.put(1, name);
		
		Query query = em.createNativeQuery(sqlBuilder.toString());
		for (Map.Entry<Integer, Object> param : sqlParams.entrySet()) {
			query.setParameter(param.getKey(), param.getValue());
		}
		
		List<User> userList = new ArrayList();
		List resultList = query.getResultList();
		for (Object obj : resultList) {
			Object[] objs = (Object[]) obj;
			User user  = new User();
			user.setId((Integer) objs[0]);
			user.setName((String) objs[1]);
			user.setAge((Integer) objs[2]);
			user.setSex((String) objs[3]);
			
			userList.add(user);
		}
		
		return userList;
	}

}

3 修改UserController类如下：

package com.gm.springboot_test.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.gm.springboot_test.entity.User;
import com.gm.springboot_test.persistence.UserPersistence;

@Controller
@RequestMapping("/user")
public class UserController {
	@Autowired
	private UserPersistence userPersistence;
	
	@RequestMapping("getByName")
	@ResponseBody
	public String getByName(String name) {
		List<User> userList = userPersistence.getByName(name);
		String result = "";
		
		for (User user : userList) {
			result += user.toString() + "<br/>";
		}
		
		return result;
	}
}