spring JdbcTemplate

package com.jaeson.springstudy.dao.impl;

import java.io.*;
import java.util.Collection;
import java.util.Map;
import java.util.HashMap;
import java.util.List;
import java.util.ArrayList;
import java.util.UUID;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.PreparedStatement;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
//import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.jaeson.hibernatestudy.bean.Student;
import com.jaeson.hibernatestudy.bean.Clazz;
import com.jaeson.springstudy.dao.StudentDao;

@Repository("jdbcTemplateStudentDao")
public class JdbcTemplateStudentDaoImpl implements StudentDao {

	private static final Logger logger = LoggerFactory.getLogger(JdbcTemplateStudentDaoImpl.class);
	
	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedTemplate;
	private SimpleJdbcInsert simpleInsert;
	
	@Autowired
	public void setDataSource(DataSource dataSource) {

		this.jdbcTemplate = new JdbcTemplate(dataSource);
		this.namedTemplate = new NamedParameterJdbcTemplate(dataSource);
		this.simpleInsert = new SimpleJdbcInsert(dataSource)
								.withTableName("student")
								.usingColumns("name")
								.usingGeneratedKeyColumns("id");
	}
	
	private static final class StudentMapper implements RowMapper<Student> {

	    public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	
	    	Clazz clazz = new Clazz();
	    	if (rs.getString("clazz_id") != null && rs.getString("clazz_id").length() > 0) {
	    		clazz.setId(rs.getString("clazz_id"));
	    		clazz.setName(rs.getString("clazz_name"));
	    	}
	    	
	    	Student student = new Student();
	    	student.setId(rs.getString("id"));
	    	student.setName(rs.getString("name"));
	    	student.setSex(rs.getInt("sex"));
	    	student.setClazz(clazz);
	        return student;
	    }
	}
	
	@Override
	public Student findById(String id) {

		String sql = "select s.id as id, s.name as name, s.sex as sex, s.clazz_id as student_clazz_id, "
				+ " c.id as clazz_id, c.name as clazz_name "
				+ " from student s left outer join clazz c on s.clazz_id = c.id "
				+ " where s.id = ?";
		
		logger.info(sql);
		List<Student> result = this.jdbcTemplate.query(sql, new Object[]{id}, new StudentMapper());
		return (result == null || result.size() == 0) ? null : result.get(0);
	}
	public Student findByNamedParam(Student student) {
		
		String sql = "select s.id as id, s.name as name, s.sex as sex, s.clazz_id as student_clazz_id, "
				+ " c.id as clazz_id, c.name as clazz_name "
				+ " from student s left outer join clazz c on s.clazz_id = c.id "
				+ " where s.id = :id and s.sex = :sex";
		
		//另一种参数设置
		SqlParameterSource paramSource = new MapSqlParameterSource("id", student.getId())
												.addValue("sex", student.getSex());
		//return this.namedTemplate.queryForObject(sql, sqlParamSource, new StudentMapper());
		
		paramSource = new BeanPropertySqlParameterSource(student);
		List<Student> result = this.namedTemplate.query(sql, paramSource, new StudentMapper());
		return (result == null || result.size() == 0) ? null : result.get(0);
	}
	

	@Override
	public List<Student> findByQueryString(String queryString, Object... params) {
		
		throw new UnsupportedOperationException("findByQueryString unsupported");
	}

	@Override
	public void update(Student entity) {
		
		if (entity == null)
			throw new IllegalArgumentException("entity is null");
		
		String sql = "update student set "
				+ " name = ?, sex = ? ";
		Object[] values;
		if (entity.getClazz() != null && entity.getClazz().getId().length() > 0) {
			sql += ", clazz_id = ? where id = ? ";
			values = new Object[] {entity.getName(), entity.getSex(), entity.getClazz().getId(), entity.getId()};
		} else {
			sql += " where id = ? ";
			values = new Object[] {entity.getName(), entity.getSex(), entity.getId()};
		}
		
		logger.info(sql);
		this.jdbcTemplate.update(sql, values);
	}

	@Override
	public String save(Student entity) {
		
		if (entity == null)
			throw new IllegalArgumentException("entity is null");
		
		String uuid = entity.getId();
		if (uuid == null || uuid.length() == 0) {
			uuid = UUID.randomUUID().toString().replaceAll("-", "");
			entity.setId(uuid);
		}
		
		String sql = "insert into student (id, name, sex";
		Object[] values;
		if (entity.getClazz() != null && entity.getClazz().getId().length() > 0) {
			sql += ", clazz_id) values(?, ?, ?, ?) ";
			values = new Object[]{entity.getId(), entity.getName(), entity.getSex(), entity.getClazz().getId()};
		} else {
			sql += ") values(?, ?, ?) ";
			values = new Object[]{entity.getId(), entity.getName(), entity.getSex()};
		}
		
		logger.info(sql);
		/*
		this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, entity.getId());
				ps.setString(2, entity.getName());
				ps.setInt(3, entity.getSex());
			}
		});*/
		this.jdbcTemplate.update(sql, values);
		return uuid;
	}

	@Override
	public void saveOrUpdate(Student entity) {
		
		throw new UnsupportedOperationException("saveOrUpdate unsupported");
	}

	@Override
	public void delete(Student entity) {
		
		if (entity == null)
			throw new IllegalArgumentException("entity is null");
		this.deleteById(entity.getId());
	}

	@Override
	public void deleteById(String id) {
		
		if (id == null || id.length() == 0)
			throw new IllegalArgumentException("id is null");
		
		String sql = "delete from student where id = ?";
		
		logger.info(sql);
		this.jdbcTemplate.update(sql, new Object[]{id});
	}

	@Override
	public void studentMethod() {
		
		throw new UnsupportedOperationException("studentMethod unsupported");
	}
	
	@Override
	public List<Student> findByClassid(String id) {
		
		String sql = "select s.id as id, s.name as name, s.sex as sex, s.clazz_id as student_clazz_id, "
				+ " c.id as clazz_id, c.name as clazz_name "
				+ " from student s inner join clazz c on s.clazz_id = c.id "
				+ " where c.id = ?";
		return this.jdbcTemplate.query(sql, new Object[]{id}, new StudentMapper());
	}

	public int countAll(String clazzId) {
		
		return this.jdbcTemplate.queryForObject("select count(*) from student where clazz_id = ?", new Object[]{clazzId}, Integer.class);
	}
	
	/**
	 * 其它jdbc用法实例
	 */
	//取得数据库自动生成的主键id
	public int generateByKeyHolder() {
		
		final String sql = "insert into course (name) values (?)";
		final String name = "音乐课";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		this.jdbcTemplate.update(
		    new PreparedStatementCreator() {
		        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
		            PreparedStatement ps = connection.prepareStatement(sql, new String[] {"id"});
		            ps.setString(1, name);
		            return ps;
		        }
		    },
		    keyHolder);
		
		return keyHolder.getKey().intValue();
	}
	public int generateBySimpleInsert() {
		
		 Map<String, Object> parameters = new HashMap<String, Object>(2);
		 parameters.put("name", "体育课");
		 Number newId = simpleInsert.executeAndReturnKey(parameters);
		 return newId.intValue();
	}
	
	//批量更新
	public int[] batchUpdate(final List<Student> list) {
		
		String sql = "update student set name = ?, sex = ?, clazz_id = ? where id = ? ";
        int[] updateCounts = this.jdbcTemplate.batchUpdate(sql, 
        	new BatchPreparedStatementSetter() {
        		@Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
        			ps.setString(1, list.get(i).getName());
        			ps.setInt(2, list.get(i).getSex());
        			ps.setString(3, list.get(i).getClazz() == null ? null : list.get(i).getClazz().getId());
        		}
        		@Override
        		public int getBatchSize() {
        			return list.size();
        		}
        	});
        return updateCounts;
    }
	public int[] batchUpdateAnother(final List<Student> list) {
        List<Object[]> batch = new ArrayList<Object[]>();
        for (Student student : list) {
            Object[] values = new Object[] {
            	student.getName(),
            	student.getSex(),
            	student.getClazz() == null ? null : student.getClazz().getId()
            };
        	batch.add(values);
        }
        int[] updateCounts = jdbcTemplate.batchUpdate(
        	"update student set name = ?, sex = ?, clazz_id = ? where id = ? ",
        	batch);
        return updateCounts;
    }
	//多批次的批量更新
	public int[][] batchUpdateLimitSize(final Collection<Student> collection) {
		
        int[][] updateCounts = jdbcTemplate.batchUpdate(
        	"update student set name = ?, sex = ?, clazz_id = ? where id = ? ",
        	collection, 50, 
        	new ParameterizedPreparedStatementSetter<Student>() {
        		@Override
        		public void setValues(PreparedStatement ps, Student student) throws SQLException {
        			ps.setString(1, student.getName());
        			ps.setInt(2, student.getSex());
        			ps.setString(3, student.getClazz() == null ? null : student.getClazz().getId());
        		}
        	});
        return updateCounts;
    }

	//大对象字段处理
	public void insertLob() throws IOException {
		
		LobHandler lobHandler = new DefaultLobHandler();
		final File blobIn = new File("spring2004.jpg");
		final InputStream blobIs = new FileInputStream(blobIn);
		final File clobIn = new File("large.txt");
		final InputStream clobIs = new FileInputStream(clobIn);
		final InputStreamReader clobReader = new InputStreamReader(clobIs);
		this.jdbcTemplate.execute(
		    "INSERT INTO lob_table (id, a_clob, a_blob) VALUES (?, ?, ?)",
		    new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
		        protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
		            ps.setLong(1, 1L);
		            lobCreator.setClobAsCharacterStream(ps, 2, clobReader, (int)clobIn.length());
		            lobCreator.setBlobAsBinaryStream(ps, 3, blobIs, (int)blobIn.length());
		        }
		    }
		);
		blobIs.close();
		clobReader.close();
	}
	public List<?> getLob() {
		
		final LobHandler lobHandler = new DefaultLobHandler();
		List<Map<String, Object>> list = this.jdbcTemplate.query("select id, a_clob, a_blob from lob_table",
			new RowMapper<Map<String, Object>>() {
				public Map<String, Object> mapRow(ResultSet rs, int i) throws SQLException {
			    	Map<String, Object> result = new HashMap<String, Object>();
			    	String clobText = lobHandler.getClobAsString(rs, "a_clob");
			    	result.put("CLOB", clobText);
			    	byte[] blobBytes = lobHandler.getBlobAsBytes(rs, "a_blob");
			    	result.put("BLOB", blobBytes);
			    	return result;
			    }
			});
		return list;
	}
}