`
jaesonchen
  • 浏览: 311492 次
  • 来自: ...
社区版块
存档分类
最新评论

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

 

 

分享到:
评论

相关推荐

    基于注解的Spring JdbcTemplate

    **基于注解的Spring JdbcTemplate** 在Java世界中,Spring框架是企业级应用开发的首选。Spring JDBC模絫提供了一种简洁的方式来处理数据库操作,而`Spring JdbcTemplate`是这个模絫的核心组件。本教程将深入探讨...

    Spring JdbcTemplate

    **Spring JdbcTemplate**是Spring框架中的一个核心组件,主要用于简化Java数据库访问。它提供了一种模板化的方式来执行SQL语句,使得开发人员可以避免编写大量的重复代码,专注于业务逻辑,而不是底层的数据库交互...

    Spring JdbcTemplate调用Oracle存储过程实现CRUD

    使用 Spring JdbcTemplate 调用 Oracle 存储过程实现 CRUD 在本文中,我们将讨论如何使用 Spring JdbcTemplate 调用 Oracle 存储过程来实现 CRUD(Create、Read、Update、Delete)操作。我们将首先编写 Oracle 存储...

    Spring JdbcTemplate 常用方法整理

    Spring的JdbcTemplate是Spring框架中用于简化数据库操作的工具类,它是基于JDBC但又抽象出了一层,避免了直接与数据库驱动API交互,从而提高了代码的可读性和可维护性。本文将深入探讨Spring JdbcTemplate的常用方法...

    Spring JDBCTemplate连接池jar包

    首先,我们要理解Spring JDBCTemplate的工作原理。它作为一个模板类,提供了执行SQL语句的方法,如`update()`, `query()`, `insert()`, `delete()`等。开发者只需要提供SQL语句和参数绑定,JDBCTemplate会自动处理...

    SpringJdbcTemplate封装工具类

    SpringJdbcTemplate是Spring框架中用于简化Java数据库访问的工具,它是Spring JDBC模块的核心。这个封装工具类的出现是为了提供一种更简洁、易于使用的接口来执行SQL操作,减轻开发者处理数据库连接、事务管理以及...

    spring-jdbcTemplate实例工程

    《深入解析Spring JdbcTemplate》 Spring JDBC Template是Spring框架中用于简化JDBC操作的一个核心组件,它是Spring对JDBC API的封装,旨在提供一个更加简洁、健壮且可测试的数据库访问层。在这个实例工程中,我们...

    Druid数据库连接池的SpringJDBCTemplate所需的jar包

    Druid数据库连接池的SpringJDBCTemplate所需的jar包,Druid数据库连接池的SpringJDBCTemplate所需的jar包,Druid数据库连接池的SpringJDBCTemplate所需的jar包,Druid数据库连接池的SpringJDBCTemplate所需的jar包,...

    使用Spring JDBCTemplate进行增删改查curd操作

    在Spring框架中,JdbcTemplate是用于简化数据库操作的重要工具,它是Spring JDBC模块的一部分。通过使用JdbcTemplate,开发者可以避免编写大量的重复代码,如手动管理连接、处理结果集等,从而专注于业务逻辑。本文...

    模仿spring jdbcTemplate的实现

    模仿spring jdbcTemplate的粗略实现,只有很小的参考价值,如果是java初学者可以使用这个封装好的工具进行数据库操作,只需要在db.properties里配置好driver,url等信息

    Spring JdbcTemplate.batchUpdate 例子

    在Spring框架中,`JdbcTemplate`是用于简化Java数据库连接(JDBC)操作的一个核心组件。这个类提供了很多方法来执行SQL查询、更新语句,包括批处理操作。本篇文章将详细探讨`batchUpdate`方法及其在实际开发中的应用...

    strut2+spring+springjdbctemplate做的简易登录系统

    Struts2、Spring和Spring JDBC Template是Java Web开发中常用的三个框架,它们分别负责不同的职责。Struts2作为MVC(Model-View-Controller)框架,主要处理前端请求和业务逻辑;Spring则是一个全面的后端框架,提供...

    Spring JdbcTemplate查询实例

    Spring JdbcTemplate是Spring框架中用于简化数据库操作的一个重要组件,它是Spring对JDBC的轻量级封装,旨在提供一种结构良好、易于使用的SQL执行机制,同时保持了JDBC的灵活性。在本实例中,我们将深入探讨Spring ...

    Spring JdbcTemplate例子

    Spring JdbcTemplate是Spring框架中的一个核心组件,主要用来简化数据库操作。它提供了一种模板方法设计模式,将SQL语句的执行与结果处理进行了抽象,使得开发者可以更加专注于业务逻辑,而无需关心底层数据访问的...

    Spring JdbcTemplate api

    根据给定的文件信息,以下是对“Spring JdbcTemplate API”的详细知识点解析: ### Spring JdbcTemplate API:数据库操作的模板模式 #### 概述 在Spring框架中,`JdbcTemplate`是一个用于简化JDBC编程的工具类,...

    spring jdbcTemplate

    《Spring JdbcTemplate 深入解析与实战应用》 在Java世界中,Spring框架以其强大的功能和灵活性,深受广大开发者的喜爱。其中,Spring JDBC Template是Spring提供的一个用于简化数据库操作的工具,它抽象了JDBC API...

    spring jdbcTemplate 注入到servlet

    在Java Web开发中,Spring框架提供了丰富的工具来简化数据库操作,其中之一就是`Spring JdbcTemplate`。`JdbcTemplate`是Spring对JDBC(Java Database Connectivity)的一层轻量级封装,它使得开发者能够更加方便地...

    SSH笔记-Spring JdbcTemplate

    SSH笔记-Spring JdbcTemplate是关于如何在Spring框架中利用JdbcTemplate进行数据库操作的教程。Spring是一个广泛应用的Java企业级应用框架,它提供了多种数据访问工具,而JdbcTemplate就是其中之一,用于简化JDBC...

    Spring jdbcTemplate pom.xml

    1.Spring4前 spring-jdbc包是独立的,4以后spring-jdbc 就已经没有了

Global site tag (gtag.js) - Google Analytics