JDBC（九）JDBCTemplate实例

迷南。 2022-05-29 00:13 206阅读 0赞

NamedParameterJdbcTemplate和JdbcTemplate 用法类似。

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

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: 
    	 * @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 ;
    }