User.java:
package com.sikiedu.bean;
public class User {
private Integer u_id;
private String u_username;
private String u_password;
public Integer getU_id() {
return u_id;
}
public void setU_id(Integer u_id) {
this.u_id = u_id;
}
public String getU_username() {
return u_username;
}
public void setU_username(String u_username) {
this.u_username = u_username;
}
public String getU_password() {
return u_password;
}
public void setU_password(String u_password) {
this.u_password = u_password;
}
@Override
public String toString() {
return "User [u_id=" + u_id + ", u_username=" + u_username + ", u_password=" + u_password + "]";
}
}
UserDao.java:
package com.sikiedu.dao;
import java.util.List;
import com.sikiedu.bean.User;
public interface UserDao {
// 增
void saveUser(User u);
//删
void deleteUserById(Integer id);
//改
void updateUser(User u);
//查
//根据id查找用户
User selectUserById(Integer id);
//查找全部用户list
List<User> selectAllUser();
//查找用户数量
Integer selectUserCount();
}
UserDaolmpl.java:
package com.sikiedu.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.sikiedu.bean.User;
public class UserDaolmpl extends JdbcDaoSupport implements UserDao {
private static ComboPooledDataSource dataSource;
@Override
public void saveUser(User u) {
String sql="insert into user values(null,?,?)";
getJdbcTemplate().update(sql,u.getU_username(),u.getU_password());
}
@Override
public void deleteUserById(Integer id) {
String sql="delete from user where id=?";
getJdbcTemplate().update(sql,id);
}
@Override
public void updateUser(User u) {
String sql="update user set username=?,password=? where id=?";
getJdbcTemplate().update(sql,u.getU_username(),u.getU_password(),u.getU_id());
}
@Override
public User selectUserById(Integer id) {
String sql="select * from user where id=?";
User user= getJdbcTemplate().queryForObject(sql, new RowMapper<User>(){
@Override
public User mapRow(ResultSet rs, int index) throws SQLException {
User u=new User();
u.setU_id(rs.getInt("id"));
u.setU_username(rs.getString("username"));
u.setU_password(rs.getString("password"));
return u;
}
},id);
return user;
}
@Override
public List<User> selectAllUser() {
String sql="select * from user";
List<User> list=getJdbcTemplate().query(sql, new RowMapper<User>(){
@Override
public User mapRow(ResultSet rs, int index) throws SQLException {
User u=new User();
u.setU_id(rs.getInt("id"));
u.setU_username(rs.getString("username"));
u.setU_password(rs.getString("password"));
return u;
}
});
return list;
}
@Override
public Integer selectUserCount() {
String sql="select count(*) from user";
return getJdbcTemplate().queryForObject(sql, Integer.class);
}
}
applicationContext2.xml:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">
<context:property-placeholder location="db.properties"/>
<!-- 配置dataSource -->
<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driverClass}"></property>
<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
<property name="user" value="${jdbc.user}"></property>
<!-- 注意:这里name="user",不是“username”!!!切记 -->
<property name="password" value="${jdbc.password}"></property>
</bean>
<bean name="userDao" class="com.sikiedu.dao.UserDaolmpl">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
db.properties(这是一个file文件)
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql://localhost:3306/web01
jdbc.user=root
jdbc.password=root
测试代码:
JdbcTest.java:
package com.sikiedu.test;
import java.util.List;
import javax.annotation.Resource;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import com.sikiedu.bean.User;
import com.sikiedu.dao.UserDao;
import com.sikiedu.dao.UserDaolmpl;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext2.xml")
public class JdbcTest {
@Resource(name="userDao")
private UserDao ud;
@Test
public void Test1(){
User u=ud.selectUserById(5);
System.out.println(u);
}
@Test
public void Test2(){
User u=new User();
u.setU_username("ased");
u.setU_password("231");
ud.saveUser(u);
}
@Test
public void Test3(){
ud.deleteUserById(12);
}
@Test
public void Test4(){
User u=new User();
u.setU_username("王五五w");
u.setU_password("5555w");
u.setU_id(5);
ud.updateUser(u);
}
@Test
public void Test5(){
User u = ud.selectUserById(5);
System.out.println(u);
}
@Test
public void Test6(){
List<User> list = ud.selectAllUser();
for(User u:list){
System.out.println(u);
}
}
@Test
public void Test7(){
System.out.println(ud.selectUserCount());
}
}