使用SpringJDBC訪問數(shù)據(jù)庫
2018-07-20 來源：open-open
引包的問題在此不做詳述，下面是viewspace-dao.xml中的關(guān)鍵配置
<!-- 掃描com.sunsharing.dao包下所有標(biāo)注@Repository的DAO組件 -->
<context:component-scan base-package="com.sunsharing.springdemo.dao"/>
<!--使用spring提供的PropertyPlaceholderConfigurer讀取數(shù)據(jù)庫配置信息.properties-->
<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:jdbc.properties"/>
</bean>
<!--數(shù)據(jù)源配置-->
<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"/>
domain層java代碼
package com.sunsharing.springdemo.domain;
/**
* Created by nyp on 2015/2/5.
*/
public class User {
//建立一個(gè)user對(duì)象，對(duì)應(yīng)數(shù)據(jù)庫中的各個(gè)屬性，并給出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;
}
}
dao層java代碼
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 {
//自動(dòng)注入jdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 使用jdbcTemplate查詢用戶
* @param userName 用戶名查詢條件
* @return 用戶名匹配的User對(duì)象
*/
public User findUserByNameJdbc(final String userName){
//為了使userName可以再內(nèi)部類中使用，必須聲明為final
String sqlStr="SELECT * FROM T_USER where USER_NAME= ?";
final User user = new User();
//通過匿名內(nèi)部類定義回調(diào)函數(shù) 將結(jié)果集數(shù)據(jù)中的數(shù)據(jù)抽取到User對(duì)象中
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 用戶對(duì)象
*
* */
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.getPassword(),user.getLastVisit(),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});
}
}
junit測(cè)試SpringJDBC操作數(shù)據(jù)庫代碼
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);
}
}
標(biāo)簽： 代碼 數(shù)據(jù)庫
版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請(qǐng)聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請(qǐng)與原作者聯(lián)系。
最新資訊
熱門推薦