JdbcTemplate的保存和查询方法

实现类代码

package com.imooc.springData.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.imooc.springData.dao.StudentDao;
import com.imooc.springData.entity.Student;

/**
 * @author
 * StudentDao访问接口实现类：通过Spring的jdbcTemplate的方式操作
 */
public class StudentDaoImpl implements StudentDao {
	//注入JdbcTemplate
	private JdbcTemplate jdbcTemplate;
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<Student> query() {
		final List<Student> students = new ArrayList<Student>();
		String sql ="select id, name, age from student ";
		
		jdbcTemplate.query(sql, new RowCallbackHandler(){

			public void processRow(ResultSet rs) throws SQLException {
				Student student = new Student();
				student.setId(rs.getInt("id"));
				student.setName(rs.getString("name"));
				student.setAge(rs.getInt("age"));
				students.add(student);
			}
			
		});
		return students;
	}

	public void save(Student student) {
		String sql ="insert into student(name,age) values(?,?)";
		jdbcTemplate.update(sql, student.getName(),student.getAge());
	}

}

junit进行测试代码

package com.imooc.springData.dao;

import java.util.List;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.imooc.springData.entity.Student;

public class StudentDaoImplTest {
	private ApplicationContext ctx = null;
	private StudentDao studentDao = null;
	@Before
	public void setup(){
		ctx = new ClassPathXmlApplicationContext("beans.xml");
		studentDao = (StudentDao) ctx.getBean("studentDao");
		System.out.println("setup");
	}
	
	@After
	public void tearDown(){
		ctx = null;
		System.out.println("tearDown");
	}
	@Test
	public void testQuery(){
		List<Student> students = studentDao.query();
		
		for(Student student : students){
			System.out.println("id:"+student.getId()
				+" , name:" +student.getName()
				+" , age:" + student.getAge());
		}
	}
	@Test
	public void testSave(){
		Student student = new Student();
		student.setName("test");
		student.setAge(30);
		
		studentDao.save(student);
	}
}

配置文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xsi:schemaLocation=" http://www.springframework.org/schema/beans 
	http://www.springframework.org/schema/beans/spring-beans-4.0.xsd">
	<!-- Spring 数据库连接 -->
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="username" value="root" />
		<property name="password" value="123456" />
		<property name="url" value="jdbc:mysql://127.0.0.1:3306/spring_data" />
	</bean>
	<!-- Spring jdbcTemplate配置 -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource" />
	</bean>
	
	<bean id="studentDao" class="com.imooc.springData.dao.impl.StudentDaoImpl">
		<property name="jdbcTemplate" ref="jdbcTemplate" />
	</bean>
</beans>

一种查询方法

private final String queryHolidaySql="select * from table where type=?";

List<String> holidList=jdbcTemplate.queryForList(queryHolidaySql, String.class, "00A");

一般的修改方法

	String sql = "update table set PASSWORD = ? WHERE ACCOUNT = ? AND PASSWORD = ?";
	res = jdbcTemplate.update(sql, new Object[] { userPwdNew, userAccount, userPwd });

RowMapper的查询方法

String sql = "
					+ "and t1.ACCOUNT = ?  AND t1.PASSWORD = ?";
		List<SysAccount> list = jdbcTemplate.query(sql, new Object[] { account,
				userPwd }, new RowMapper<SysAccount>() {
			List<MenuList> menuList = new ArrayList<MenuList>();
			@Override
			public SysAccount mapRow(ResultSet data, int arg1)
					throws SQLException {
				SysAccount sysAccount = new SysAccount();
				MenuList mList = new MenuList();
				mList.setMenuId(data.getString("MENU_ID"));
				mList.setMenuName(data.getString("MENU_NAME"));
				menuList.add(mList);
				sysAccount.setLoginAccount(data.getString("LOGIN_ACCOUNT"));
				sysAccount.setUserName(data.getString("USER_NAME"));
				sysAccount.setUserRole(data.getString("ROLE_ID"));
				sysAccount.setUserSex(data.getString("USER_SEX"));
				sysAccount.setDeptCode(data.getString("DEPT_CODE"));
				sysAccount.setDeptName(data.getString("DEPARTMENT"));
				sysAccount.setMenuList(menuList);
				return sysAccount;
			}
		});
		if (list.size() > 0) {
			return list.get(0);
		} else {
			return null;
		}

String sql = "SELECT A.USER_ACCT_ID, A.USER_NAME, A.DEPARTMENT, A.DEPT_CODE "
				+ "FROM SYS_ACCOUNT A WHERE A.LOGIN_ACCOUNT=?";
		return jdbcTemplate.queryForObject(sql, new Object[]{userAccount}, new RowMapper<SysUserSession>(){

			@Override
			public SysUserSession mapRow(ResultSet rs, int rowNum) throws SQLException {
				SysUserSession user = new SysUserSession();
				user.setDeptCode(rs.getString("DEPT_CODE"));
				user.setDeptName(rs.getString("DEPARTMENT"));
				user.setAccountId(rs.getString("USER_ACCT_ID"));
				user.setUserName(rs.getString("USER_NAME"));
				return user;
			}
			
		});

public List<BjCasematerial> getCasematerialByCaseno(String caseno) throws Exception {
		String sql = "SELECT CL.MATENAME, CL.TAKETYPE, CL.OPERSIGN, CL.NECESSITY, CL.FILE_ID FROM BJ_CASEMATERIAL CL WHERE CL.CASENO=?";
		return jdbcTemplate.query(sql, new Object[] { caseno }, new RowMapper<BjCasematerial>() {

			@Override
			public BjCasematerial mapRow(ResultSet rs, int rowNum) throws SQLException {
				BjCasematerial bjCasematerial = new BjCasematerial();
				bjCasematerial.setMatename(rs.getString("MATENAME"));
				bjCasematerial.setTaketype(rs.getString("TAKETYPE"));
				bjCasematerial.setMateState(rs.getString("OPERSIGN"));
				bjCasematerial.setNecessity(rs.getString("NECESSITY"));
				bjCasematerial.setFileId(rs.getString("FILE_ID"));
				return bjCasematerial;
			}
		});

修改方法

String updateSql = "UPDATE tableBJ SET SIGN=?,ETIME=?, GN=? "
					+ "RSIGN=? WHERE ID=?";

			return jdbcTemplate.update(updateSql, new PreparedStatementSetter() {

				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1, bjCasebaseinfo.getSign());
					ps.setString(2, bjCasebaseinfo.getCaddatetime());
					ps.setLong(3, bjCasebaseinfo.getgn());
					ps.setString(4, bjCasebaseinfo.getOsign());
					ps.setString(5, bjCasebaseinfo.getId());
				}

			});