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;
}
}