使用SpringJDBC访问数据库

<!-- 扫描com.sunsharing.dao包下所有标注@Repository的DAO组件 -->
<context:component-scan base-package="com.sunsharing.springdemo.dao"/>
<!--使用spring提供的PropertyPlaceholderConfigurer读取数据库配置信息.properties-->
<bean id="propertyConfigurer"
    class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <property name="location" value="classpath:jdbc.properties"/>
</bean>
<!--数据源配置-->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close"
    p:driverClassName="${jdbc.driverClassName}"
    p:url="${jdbc.url}"
    p:username="${jdbc.username}"
    p:password="${jdbc.password}"/>
<!--jdbcTemplate装配-->
<bean id="jdbcTemplate"
      class="org.springframework.jdbc.core.JdbcTemplate"
      p:dataSource-ref="dataSource"/>

package?com.sunsharing.springdemo.domain;
/**
?*?Created?by?nyp?on?2015/2/5.
?*/

public?class?User?{??
??//建立一个user对象，对应数据库中的各个属性，并给出set,get方法
????private?int?userId;
?
????private?String?userName;
??
????private?String?password;
?
????private?String?lastIp;?
???
???private?String?lastVisit;
????public?int?getUserId()?{
????????return?userId;
????}

????public?void?setUserId(int?userId)?{
????????this.userId?=?userId;
????}

????public?String?getUserName()?{
????????return?userName;
????}

????public?void?setUserName(String?userName)?{
????????this.userName?=?userName;
????}

????public?String?getPassword()?{
????????return?password;
????}

????public?void?setPassword(String?password)?{
????????this.password?=?password;
????}

????public?String?getLastIp()?{
????????return?lastIp;
????}

????public?void?setLastIp(String?lastIp)?{
????????this.lastIp?=?lastIp;
????}

????public?String?getLastVisit()?{
????????return?lastVisit;
????}
????public?void?setLastVisit(String?lastVisit)?{
????????this.lastVisit?=?lastVisit;
????}

}

package?com.sunsharing.springdemo.dao;
import?com.sunsharing.springdemo.domain.User;
import?org.springframework.beans.factory.annotation.Autowired;
import?org.springframework.jdbc.core.JdbcTemplate;
import?org.springframework.jdbc.core.RowCallbackHandler;
import?org.springframework.stereotype.Repository;
import?java.sql.ResultSet;
import?java.sql.SQLException;
import?java.util.List;

/**
?*?Created?by?nyp?on?2015/2/5.
?*/
//通过Repository?注入bean
@Repository
public?class?UserDao??{???
????//自动注入jdbcTemplate
????@Autowired
????private?JdbcTemplate?jdbcTemplate;??
???
????/**
?????*?使用jdbcTemplate查询用户
?????*?@param?userName?用户名查询条件
?????*?@return?用户名匹配的User对象
?????*/
?public?User?findUserByNameJdbc(final?String?userName){
????????????????????????????????????????????//为了使userName可以再内部类中使用，必须声明为final
?????String?sqlStr="SELECT?*?FROM?T_USER?where?USER_NAME=??";
?????final?User?user?=?new?User();
?????//通过匿名内部类定义回调函数?将结果集数据中的数据抽取到User对象中
?????jdbcTemplate.query(sqlStr,?new?Object[]{userName},?????????????new?RowCallbackHandler()?{
?????????????????@Override
?????????????????public?void?processRow(ResultSet?rs)?throws?SQLException?{
???????????????????user.setUserName(rs.getString("USER_NAME"));
???????????????????user.setLastVisit(rs.getString("LAST_VISIT"));
???????????????????user.setLastIp(rs.getString("LAST_IP"));
?????????????????}
?????????????}
?????);
??return??user;
?}
????/**
?????*?使用jdbcTemplate增加用户
?????*?@param?user?用户对象
?????*
?????*?*/
??????public?void?addUserJdbc(User?user){
??????????String?sqlStr="INSERT?INTO?t_user(user_name,password,last_visit,last_ip)"?+
??????????????????"VALUES(?,?,?)?";
??????????Object[]?args={user.getUserName(),user.getPassword(),user.getLastVisit(),user.getLastIp()};
??????????jdbcTemplate.update(sqlStr,args);
??????}
????/**
?????*?使用jdbcTemplate修改用户
?????*?@param?userId?用户ID
?????*?@param?user?用户
?????*?*/

??????public?void?updateUserJdbc(User?user,int?userId){
???????String?sqlStr="UPDATE?t_user?set?user_name=?,?password=?,?last_visit=?,?last_ip=??where?user_id=?";
???????Object[]?args={user.getUserName(),user.getLastIp(),userId};
???????????????jdbcTemplate.update(sqlStr,args);
??????}
????/**
?????*?使用jdbcTemplate删除用户
?????*?@param?userId?用户ID
?????*
?????*?*/
??????public?void?delUserJdbc(int?userId){
??????????String?sqlStr="DELETE?FROM?t_user?WHERE?user_id=?";
??????????jdbcTemplate.update(sqlStr,new?Object[]{userId});
??????}

}

package?com.sunsharing.springdemo.dao;
import?com.sunsharing.springdemo.domain.User;
import?com.sunsharing.component.utils.base.DateUtils;
import?org.junit.Test;
import?org.springframework.beans.factory.annotation.Autowired;
import?org.junit.runner.RunWith;
import?org.springframework.test.context.ContextConfiguration;
import?java.util.Date;
import?java.util.Iterator;
import?java.util.List;

import?static?org.testng.Assert.*;
/**
?*?Created?by?nyp?on?2015/2/5.
?*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:/viewspace-dao.xml"})
public?class?UserDaoTest{
????@Autowired
????private?UserDao?userDao;

??@Test
????public?void?addUserByJdbc(){
????????User?user=new?User();
????????user.setUserName("jdbcTest");
????????user.setPassword("123456");
????????user.setLastVisit(new?Date().toString());
????????user.setLastIp("1.1.1.1");
????????userDao.addUserJdbc(user);
????????assertEquals(user.getPassword(),"123456");
????}?
??@Test
????public?void?findUserByJdbc()?{
????????User?user?=?userDao.findUserByNameJdbc("jdbcTest");
????????System.out.println("username="+user.getUserName()+"?lastvisit="+user.getLastVisit());
????????assertNotNull(user);
????????assertEquals(user.getUserName(),"jdbcTest");
????}
?
?@Test
????public?void?updateUserByJdbc(){
????????User?user=new?User();
????????user.setUserName("jdbcTest1");
????????user.setPassword("1234561");
????????user.setLastVisit(new?Date().toString());
????????user.setLastIp("1.1.1.12");
????????userDao.updateUserJdbc(user,12);
????}
?@Test
????public??void?delUserByJdbc(){
????????userDao.delUserJdbc(3);
????}
}