实现类代码
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());
}
});