JDBC（九）JDBCTemplate实例

NamedParameterJdbcTemplate和JdbcTemplate 用法类似。

1、配置：和hibernate、mybatis的配置基本一样，不同的仅为bean的配置。JDBCTemplate在spring中的配置为:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
		destroy-method="close">
		<property name="driverClass" value="${jdbc.driverClassName}"></property>
		<property name="jdbcUrl" value="${jdbc.url}"></property>
		<property name="user" value="${jdbc.username}"></property>
		<property name="password" value="${jdbc.password}"></property>
	</bean>

 

<bean id="jdbcTemplate"  
    class="org.springframework.jdbc.core.JdbcTemplate">  
    <property name="dataSource" ref="dataSource"></property>  
</bean>  

2、使用：

@Resource
	private JdbcTemplate jdbcTemplate;

即可使用。

3、实例：

pojo:

package com.domain;


public class User {
	
	private String id;
	
	private String ucode;
	
	private String name;
	
	private String sex;

	private String pwd;
	private String sid;
	private String school;
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getUcode() {
		return ucode;
	}

	public void setUcode(String ucode) {
		this.ucode = ucode;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}
	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	public String getSid() {
		return sid;
	}
	
	public void setSid(String sid) {
		this.sid = sid;
	}
	public String getSchool() {
		return school;
	}
	
	public void setSchool(String school) {
		this.school = school;
	}
	

}

RowMapperUser:

package com.domain;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

 
public class RowMapperUser implements RowMapper<User> {

	
	@Override
	public User mapRow(ResultSet rs, int arg1) throws SQLException {
		User dbuser = new User();
		dbuser.setId(rs.getString("id"));
		dbuser.setName(rs.getString("name"));
		dbuser.setPwd(rs.getString("pwd"));
		dbuser.setSchool(rs.getString("school"));
		dbuser.setSex(rs.getString("sex"));
		dbuser.setSid(rs.getString("sid"));
		dbuser.setUcode(rs.getString("ucode"));
		return dbuser;
	}

}

RowMapperRole:

public class RowMapperRole implements RowMapper<Role> {

	/* (non-Javadoc)
	 * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
	 */
	@Override
	public Role mapRow(ResultSet rs, int arg1) throws SQLException {
		Role role = new Role();
		role.setName(rs.getString("name"));
		role.setRid(rs.getString("rid"));
		return role;
	}

}

 

dao:

 

（1、）增、删、改：使用update方法，这里只举一个例子：

//增
public int addUser(User user) {
		
		String sql = "insert into user(id,ucode,name,sex,pwd,sid,school)"
				+ " VALUES (?,?,?,?,?,?,?)";
		int a = jdbcTemplate.update(sql, new Object[]{user.getId(), user.getUcode(),
				user.getName(), user.getSex(), user.getPwd(), user.getSid(),
				user.getSchool()});
		
		return a;
	}

	@Override
	public void updateLike(UserLike like) {
		String sql = "UPDATE user_like SET isLike = :isLike WHERE notifyId = :notifyId AND userId = :userId ";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("notifyId", like.getNotifyId());
		paramMap.put("userId", like.getUserId());
		paramMap.put("isLike", like.isLike());
		namedParameterJdbcTemplate.update(sql, paramMap);

 

（2、）查询单个字段：

public Integer getCount(Map<String, Object> map) {
		Integer count = null;
		Object[] obj=null;
		String role = (String) map.get("role");
		String school = (String) map.get("school");
		String sex = (String) map.get("sex");
		String sql = "select count(*) from "
				+ "(select u.id from user u left join userrole ur "
				+ " on u.id=ur.uid left join role r on r.rid=ur.rid "
				+ " where 1=1 ";
	
		// 无条件查询
		if (role == null && school == null && sex == null) {
			System.out.println("无条件查询");
			sql += "group by u.id) as a";
			obj=new Object[]{};
		}
		// 全条件查询
		if (role != null && school != null && sex != null) {
			sql += " and r.name=? and school=? and sex=? group by u.id) as a";
			System.out.println("全条件查询前");
			obj=new Object[]{role, school, sex};
		
			System.out.println("全条件查询后");
		}
		// 只查学校
		if (role == null && school != null && sex == null) {
			sql += " and  school=? group by u.id) as a";
			obj=new Object[]{school};
			
		}
		// 只查性别
		if (role == null && school == null && sex != null) {
			sql += " and sex=? group by u.id) as a";
			obj=new Object[]{sex};
			
		}
		// 只查权限
		if (role != null && school == null && sex == null) {
			sql += " and r.name=? group by u.id) as a";
			obj=new Object[]{role};
		
		}
		// 查权限和学校
		if (role != null && school != null && sex == null) {
			sql += " and r.name=? and school=? group by u.id) as a";
			obj=new Object[]{role, school};
			
		}
		// 查学校和性别
		if (role == null && school != null && sex != null) {
			sql += "  and school=? and sex=? group by u.id) as a";
			obj=new Object[]{school, sex};
			
		}
		// 查权限和性别
		if (role != null && school == null && sex != null) {
			sql += "  and r.name=? and sex=? group by u.id) as a";
			obj=new Object[]{role, sex};
			
		}
		count=jdbcTemplate.queryForObject(sql, obj,Integer.class);

		return count;
	}

public String getRidByName(String name) {
		
		String sql = "select rid from role where name=?";
		
		String rid=jdbcTemplate.queryForObject(sql, new Object[]{name},String.class);
		
		return rid;
	}

 

 

（3、）查询单个对象:

 

 

/**
	 * @Title: getUser
	 * @Description: <!--获取登录用户byId-->
	 * @param user
	 *            用户
	 * @return 返回用户信息
	 */
	public User getUser(User user) {
		
		String sql = "select * from user where id=?";
		
		User dbuser =jdbcTemplate.queryForObject(sql, new Object[]{user.getId()},new RowMapperUser());
		
		return dbuser;
	}

 

（4、）查询对象集合:

 

/**
	 * @Title: getUserRoles
	 * @Description: 获取用户的所有权限
	 * @param user
	 *            用户
	 * @return 返回权限列表
	 */
	public List<Role> getUserRoles(User user) {
		// new ArrayList<>();
		String sql = "select * from role r left join  "
				+ "userrole ur on r.rid=ur.rid left join user u "
				+ " on ur.uid=u.id where u.id=?";
		List<Role> list = jdbcTemplate.query(sql,
				new Object[] { user.getId() }, new RowMapperRole());

		return list;
	}

public List<User> selectHistoryUser(Date beforeTime, Integer size) {
		
		String sql = "select * from t_user where createTime < :beforeTime ORDER BY createTime  limit :size";
		Map<String, Object> paramMap = new HashMap<String, Object>(); 
		paramMap.put("beforeTime", beforeTime);
		paramMap.put("size", size);
		return namedParameterJdbcTemplate.query(sql, paramMap, new UserMapper());
	}

 

（5）批量插入：

JdbcTemplate：

@Autowired
private JdbcTemplate jdbcTemplate;
//批量插入操作：

public void batchSave(){
  List<Object[]> batchArgs=new ArrayList<Object[]>();
  batchArgs.add(new Object[]{1,"小明",21});
  batchArgs.add(new Object[]{2,"小红",22});
  batchArgs.add(new Object[]{3,"露西",23});
  String sql = "insert into user (username,password) values (?,?)";
  jdbcTemplate.batchUpdate(sql, batchArgs);
}
以上基本实现了批量插入功能，但是当数据库字段比较多的时候，再以?占位符的形式编码的话就可能不是那么好一 一对应了，这里spring还提供了SimpleJdbcTemplate(Spring3.1+ 以后被标记为过时，到Spring 4.3则被完全移除，后面这个完全能满足需求)和NamedParameterJdbcTemplate模板引擎。

NamedParameterJdbcTemplate:

相信使用过Hibernate的同学都知道，HQL中可以使用？或者:*的方式在外部配置查询参数。在 Spring JDBC 框架中，也提供了一种绑定 SQL 参数的方式，使用具名参数(named parameter)。

我们只需要在使用NamedParameterJdbcTemplate类中使用@Autowired进行注入即可：

@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
//批量插入操作：


public void batchSave(){
 List<User> list = new ArrayList<User>();
 //新增用户
 list.add(new AppStudent(1,"张三",21));
 list.add(new AppStudent(1,"李四",22));
 list.add(new AppStudent(1,"王二麻子",23));
 //批量转数组
 SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
 String sql = "INSERT INTO app_student(class_id,name,age) VALUES (:classId,:name,:age)";
 namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
}

（6）、in查询：

public List<UserComment> selectHistoryComment(List<Long> userIds) {
       String sql = "select * from user_comment  where userId in (:userIds )";		
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("userIds", userIds);
		List<UserComment> userCommentList = namedParameterJdbcTemplate.query(sql, parameters,new UserCommentMapper());
		return userCommentList ;
}