spring整合JDBC(增删查改)

拼搏现实的明天。 2022-03-22 06:51 325阅读 0赞

> spring中提供了一个可以操作数据库的对象.对象封装了jdbc技术.（JDBCTemplate => JDBC模板对象）

>  这里UserDaoImpl继承JdbcDaoSupport根据连接池创建JDBC模板

### 准备数据库：t\_user ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTY2Nzcy_size_16_color_FFFFFF_t_70][]

###  User.java ###

public class User {
    	private Integer id;
    	private String name;
    	private Integer age;
    	
    	
    	public Integer getId() {
    		return id;
    	}
    	public void setId(Integer id) {
    		this.id = id;
    	}
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public Integer getAge() {
    		return age;
    	}
    	public void setAge(Integer age) {
    		this.age = age;
    	}
    	@Override
    	public String toString() {
    		return "User [name=" + name + ", age=" + age + "]";
    	}
    	
    }

### UserDao.java(接口) ###

public interface UserDao {
    	public void save(User u);
    	public void delete(Integer id);
    	public void update(User u);
    	public User getByID(Integer id);
    	public List<User> getAll();
    }

### UserDaoImpl.java(实现类) ###

package spring.jdbc;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;
    
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.jdbc.core.support.JdbcDaoSupport;
    
    public class UserDaoImpl extends JdbcDaoSupport implements UserDao{
    
    	
    	
    	@Override
    	public void save(User u) {
    		String sql = "insert into t_user(name,age) values(?,?)";
    		super.getJdbcTemplate().update(sql, u.getName(),u.getAge());
    	}
    
    	@Override
    	public void delete(Integer id) {
    		String sql = "delete from t_user where id=?";
    		super.getJdbcTemplate().update(sql, id);
    	}
    
    	@Override
    	public void update(User u) {
    		String sql = "update t_user set name=? where id=?";
    		super.getJdbcTemplate().update(sql, u.getName(),u.getId());
    	}
    
    	@Override
    	public User getByID(Integer id) {
    		String sql = "select * from t_user where id=?";
    		return super.getJdbcTemplate().queryForObject(sql, new RowMapper<User>(){
    
    			@Override
    			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
    				User user = new User();
    				user.setId(rs.getInt("id"));
    				user.setName(rs.getString("name"));
    				user.setAge(rs.getInt("age"));
    				return user;
    			}
    			
    		}, id);
    		
    	}
    
    	@Override
    	public List<User> getAll() {
    		String sql = "select * from t_user";
    		List<User> userList = super.getJdbcTemplate().query(sql, new RowMapper<User>() {
    
    			@Override
    			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
    				User user = new User();
    				user.setId(rs.getInt("id"));
    				user.setName(rs.getString("name"));
    				user.setAge(rs.getInt("age"));
    				return user;
    			}
    		});
    		return userList;
    	}
    
    }

### db.properties ###

jdbc.jdbcUrl=jdbc:mysql:///web_spring
    jdbc.driverClass=com.mysql.jdbc.Driver
    jdbc.user=root
    jdbc.password=admin

## ##

### applicationContext.xml ###

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.springframework.org/schema/beans" 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.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd ">
        //指定spring读取db.properties配置文件
    	<context:property-placeholder location="classpath:db.properties"/>
    	//配置连接池
    	<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    		<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
    		<property name="driverClass" value="${jdbc.driverClass}"></property>
    		<property name="user" value="${jdbc.user}"></property>
    		<property name="password" value="${jdbc.password}"></property>
    	</bean>
    	//配置UserDao
    	<bean name="userDao" class="spring.jdbc.UserDaoImpl">
    		<property name="dataSource" ref="dataSource"></property>
    	</bean>
    	
    </beans>

### 测试类Demo.java ###

package spring.jdbc;
    
    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;
    //创建容器
    @RunWith(SpringJUnit4ClassRunner.class)
    //指定配置文件
    @ContextConfiguration("classpath:applicationContext.xml")
    public class Demo {
    	//将userDao注入ud中
    	@Resource(name="userDao")
    	private UserDao ud;
    	
    	@Test
    	public void func1(){
    		User user = new User();
    		user.setAge(11);
    		user.setName("jack11");
    		ud.save(user);
    	}
    	
    	@Test
    	public void func2(){
    		
    		ud.delete(1);
    	}
    	
    	@Test
    	public void func3(){
    		
    		User user = new User();
    		user.setId(2);
    		user.setName("tom");
    		ud.update(user);
    	}
    	
    	@Test
    	public void func4(){
    		
    		System.out.println(ud.getByID(2));
    	}
    	
    	@Test
    	public void func5(){
    		
    		List<User> all = ud.getAll();
    		for(User u1:all){
    			System.out.println(u1);
    		}
    		
    	}
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTY2Nzcy_size_16_color_FFFFFF_t_70]: /images/20220322/8ad624140be84f4ca957699aaf7858b3.png