使用Spring进行JDBC操作详细案例

叁歲伎倆 2023-02-09 05:23 43阅读 0赞

Spring是一个一站式企业开发框架  
1.表现层：springMVC  
2.DAO层：SpringJDBC  
3.Service层 ：处理事务，日志等等

Spring对象也支持JDBC,对JDBC只进行了薄薄的一层封装  
问题： Java开发已经有JDBC,为什么Spring还要支持JDBC操作呢?  
最重要的原因: Spring操作JDBC能自动管理事务

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTg4NzI3NQ_size_16_color_FFFFFF_t_70]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTg4NzI3NQ_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTg4NzI3NQ_size_16_color_FFFFFF_t_70 2]

### 操作步骤 ###

1,创建数据库 spring\_jdbc  
2.创建数据表 t\_user

CREATE TABLE `t_user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      `email` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

### 创建对应的domain对象 ###

public class User {
        
    	private Integer id;
    	private String name;
    	private String email;
    	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 String getEmail() {
        
    		return email;
    	}
    	public void setEmail(String email) {
        
    		this.email = email;
    	}
    	@Override
    	public String toString() {
        
    		return "User [id=" + id + ", name=" + name + ", email=" + email + "]";
    	}
    	public User(Integer id, String name, String email) {
        
    		super();
    		this.id = id;
    		this.name = name;
    		this.email = email;
    	}
    	public User() {
        
    		super();
    		// TODO Auto-generated constructor stub
    	}
    	
    }

### 创建DAO ###

package com.ywq.spring.dao;
    
    import java.util.List;
    
    import com.ywq.spring.pojo.User;
    
    public interface UserDao {
        
    	
    	void insert(User user);
    	
    	void deleteByPrimaryKey(Integer id);
    	
    	void updateByPrimaryKey(User user);
    	
    	User selectByPrimaryKey(Integer id);
    	
    	List<User> selectList();
    }

### 创建Service ###

public class UserServiceImpl implements UserService {
        
    	
    	private UserDao dao;
    	
    	@Override
    	public void save(User user) {
        
    		dao.save(user);
    	}
    
    	@Override
    	public void delete(Integer id) {
        
    		dao.delete(id);
    	}
    
    	@Override
    	public void update(User id) {
        
    		dao.update(id);
    	}
    
    	@Override
    	public User findById(Integer id) {
        
    		// TODO Auto-generated method stub
    		return dao.findById(id);
    	}
    
    	@Override
    	public List<User> list() {
        
    		// TODO Auto-generated method stub
    		return dao.list();
    	}
    
    	public void setDao(UserDao dao) {
        
    		this.dao = dao;
    	}
    }

### Spring配置文件 ###

package com.ywq.spring.config;
    
    import javax.sql.DataSource;
    
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.ComponentScan;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.PropertySource;
    import org.springframework.context.annotation.Scope;
    import org.springframework.jdbc.core.JdbcTemplate;
    
    import com.alibaba.druid.pool.DruidDataSource;
    
    /*
     * @Configuration 
     * 把当前类作为Spring框架的配置文件 等价于 applicationContext.xml
     * 
     * @ComponentScan 配置包扫描位置
     * 
     * 
     * @PropertySource 读取.properties配置文件
     * 
     */
    @Configuration
    @ComponentScan("com.ywq.spring")//等价于 <context:component-scan base-package="com.ywq.spring"/>
    //@ComponentScan(basePackages = {"com.ywq.spring.dao","com.ywq.spring.service","com.ywq.spring.controller"} )
    @PropertySource("classpath:db.properties")//<context:property-placeholder location="classpath:db.properties"/>
    public class SpringConfig {
        
    	/*
    	 * @Value 读取 Spring加载 .properties 配置文件 key 对应的值
    	 * 
    	 * @Value("${key}")
    	 * 	 注解贴在 成员变量上面，自动会把读取出来key对应的值赋值给成员变量
    	 * 	如果不读取值，可以直接写值
    	 * 
    	 * @Value("com.mysql.jdbc.Driver");
    	 */
    	
    	@Value("${jdbc.driverClassName}") //等价于 <property name="username" value="${jdbc.username}"/>
    	//@Value("com.mysql.jdbc.Driver")
    	private String driverClassName;
    	
    	@Value("${jdbc.url}")
    	private String url;
    	
    	@Value("${jdbc.username}")
    	private String username;
    	
    	@Value("${jdbc.password}")
    	private String password;
    	
    	@Value("${jdbc.maxActive}")
    	private Integer maxActive;
    	
    	
    	
    	@Bean(name = "dataSource",initMethod = "init",destroyMethod = "close")
    	//@Scope("singleton")
    	//等价于 <bean  id="dataSouce" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
    	public DataSource getDataSource() {
        
    		//创建阿里巴巴连接池对象
    		DruidDataSource ds = new DruidDataSource();
    		ds.setDriverClassName(driverClassName);
    		ds.setUrl(url);
    		ds.setUsername(username);
    		ds.setPassword(password);
    		ds.setMaxActive(maxActive);
    		return ds;
    	}
    	
    	/**
    	 * 返回jdbcTemplate对象的 配置
    	 */
    	@Bean
    	@Scope("prototype")//每一个dao中需要一个独立jdbcTeample对象，所以是多例
    	public JdbcTemplate getJdbcTemplate() {
        
    		JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource());
    		return jdbcTemplate;
    	}
    
    }

### DAO实现类\`\` ###

// 引入JDBCTemplate 模板类
    	private JdbcTemplate jdbcTemplate;
    
    	//使用setter方式注入数据源即可
    	public void setDataSource(DataSource dataSource) {
        
    		this.jdbcTemplate = new JdbcTemplate(dataSource);
    	}
    
    	@Override
    	public void save(User user) {
        
    		this.jdbcTemplate.update("insert into t_user (name,email) values (?,?)",
    				user.getName(),user.getEmail());
    	}
    
    	@Override
    	public void delete(Integer id) {
        
    		this.jdbcTemplate.update("delete from t_user where id = ?",id);
    
    	}
    
    	@Override
    	public void update(User user) {
        
    		this.jdbcTemplate.update("update t_user set name = ?,email = ? where id = ?",
    				user.getName(),user.getEmail(),user.getId());
    	}
    
    	@Override
    	public User findById(Integer id) {
        
    		String sql = "select * from t_user where id = ?";
    		User user = this.jdbcTemplate.queryForObject(sql,new Object[] {
        id}, new RowMapper<User>() {
        
    			@Override
    			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        
    				User user = new User();
    				user.setName(rs.getString("name"));
    				user.setEmail(rs.getString("email"));
    				user.setId(id);
    				return user;
    			}
    		});
    		return user;
    	}
    
    	@Override
    	public List<User> list() {
        
    		String sql = "select * from t_user";
    		
    		List<User> users = this.jdbcTemplate.query(sql,new RowMapper<User>(){
        
    			@Override
    			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        
    				User user = new User();
    				user.setName(rs.getString("name"));
    				user.setEmail(rs.getString("email"));
    				user.setId(rs.getInt("id"));
    				return user;
    			}
    		});
    		
    		return users;
    	}

### 测试代码 ###

package com.ywq.spring.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.ywq.spring.SpringConfig;
    import com.ywq.spring.pojo.User;
    import com.ywq.spring.service.UserService;
    
    
    @RunWith(SpringJUnit4ClassRunner.class)
    @ContextConfiguration(classes = SpringConfig.class)
    public class UserServiceTest {
        
    	
    	@Resource
    	private UserService userService;
    
    	@Test
    	public void testInsert() {
        
    		User user = new User(null, "阿炳", "1561@qq.com");
    		userService.insert(user);
    	}
    
    	@Test
    	public void testDeleteByPrimaryKey() {
        
    		
    		userService.deleteByPrimaryKey(2);
    	}
    
    	@Test
    	public void testUpdateByPrimaryKey() {
        
    		
    		User user = new User(7, "阿龙", "longge@qq.com");
    		userService.updateByPrimaryKey(user);	
    	}
    
    	@Test
    	public void testSelectByPrimaryKey() {
        
    		User user = userService.selectByPrimaryKey(3);
    		System.out.println(user);
    	}
    
    	@Test
    	public void testSelectList() {
        
    		
    		List<User> users = userService.selectList();
    		for (User user : users) {
        
    			System.out.println(user);
    		}
    	}
    
    }

### 相关jar包 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTg4NzI3NQ_size_16_color_FFFFFF_t_70 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTg4NzI3NQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200518115042944.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTg4NzI3NQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTg4NzI3NQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200518115101196.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTg4NzI3NQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTg4NzI3NQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200518115116440.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTg4NzI3NQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTg4NzI3NQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200518120350512.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTg4NzI3NQ==,size_16,color_FFFFFF,t_70