Spring框架-thiteen(Spring-JdbcTemplate)

冷不防 2022-03-10 00:56 208阅读 0赞

# 13-【掌握】Spring-JdbcTemplate #

## 1、Spring对不同的持久化支持 ##

Spring为各种支持的持久化技术，都提供了简单操作的模板和回调  
![在这里插入图片描述][20190308214456639.png]

其实Spring的JDBCTemplate有点像DBUtils，但是有时候还没有DBUitls好用。这里来学习一下使用Spring的JDBCTemplate来玩一下CRUD。

## 2、使用JdbcTemplate需要的jar包 ##

在这里使用Spring的JDBCTemplate的时候先要把轮子拿过来  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDEzNzkw_size_16_color_FFFFFF_t_70]

除此之外，在Java中操作数据库怎么能不要对应的驱动包呢：  
![在这里插入图片描述][20190308214521237.png]

## 3、准备数据库 ##

/*
    Navicat MySQL Data Transfer
    
    Source Server         : localhost
    Source Server Version : 50528
    Source Host           : localhost:3306
    Source Database       : test
    
    Target Server Type    : MYSQL
    Target Server Version : 50528
    File Encoding         : 65001
    
    Date: 2019-03-08 17:44:00
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for sys_user
    -- ----------------------------
    DROP TABLE IF EXISTS `sys_user`;
    CREATE TABLE `sys_user` (
      `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
      `user_name` varchar(255) DEFAULT NULL COMMENT '用户姓名',
      `user_address` varchar(255) DEFAULT NULL COMMENT '用户地址',
      `user_birthday` date DEFAULT NULL COMMENT '出生日期',
      PRIMARY KEY (`user_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of sys_user
    -- ----------------------------
    INSERT INTO `sys_user` VALUES ('1', '小明1', '武汉1', '2019-01-03');
    INSERT INTO `sys_user` VALUES ('2', '小明2', '武汉2', '2019-01-03');
    INSERT INTO `sys_user` VALUES ('3', '小明3', '武汉3', '2019-01-03');
    INSERT INTO `sys_user` VALUES ('4', '小明4', '武汉4', '2019-01-03');
    INSERT INTO `sys_user` VALUES ('5', '小明5', '武汉5', '2019-01-03');
    INSERT INTO `sys_user` VALUES ('6', '小明6', '武汉6', '2019-01-03');
    INSERT INTO `sys_user` VALUES ('7', '小明7', '武汉7', '2019-01-03');
    INSERT INTO `sys_user` VALUES ('8', '小明8', '武汉8', '2019-01-03');
    INSERT INTO `sys_user` VALUES ('9', '小明9', '武汉9', '2019-01-03');
    INSERT INTO `sys_user` VALUES ('10', '小明10', '武汉10', '2019-01-03');

## 4、使用JdbcTemplate连接数据库 ##

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

## 5、上面代码的说明 ##

以上的代码所有的对象都是new 出来的

## 6、使用xml的方式去配置上面的的代码 ##

创建User  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDEzNzkw_size_16_color_FFFFFF_t_70 2]  
创建UserDao  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDEzNzkw_size_16_color_FFFFFF_t_70 3]  
创建UserDaoImpl  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDEzNzkw_size_16_color_FFFFFF_t_70 4]  
创建UserService  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDEzNzkw_size_16_color_FFFFFF_t_70 5]  
创建UserServiceImpl  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDEzNzkw_size_16_color_FFFFFF_t_70 6]  
**创建applicationContext.xml**

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
    	xmlns:context="http://www.springframework.org/schema/context"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
    	xsi:schemaLocation="http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd
                http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/context
    	http://www.springframework.org/schema/context/spring-context.xsd
            ">
    	
    	<bean id="dataSource" 
    	class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    		
    		<property name="driverClassName" value="com.mysql.jdbc.Driver">
    		</property>
    		<property name="url" value="jdbc:mysql://127.0.0.1:3306/test">
    </property>
    		<property name="username" value="root"></property>
    		<property name="password" value="123456"></property>
    	</bean>
    	
    	
    	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    		
    		<property name="dataSource" ref="dataSource"></property>
    	</bean>
    	
    	
    	<bean id="userDao"  class="com.sxt.dao.impl.UserDaoImpl">
    		
    		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
    	</bean>
    	
    	
    	<bean id="userService" class="com.sxt.service.impl.UserServiceImpl">
    		<property name="userDao" ref="userDao"></property>
    	</bean>
    </beans>

测试

![在这里插入图片描述][20190308214856814.png]

## 7、使用注解的方式去配置上面的的代码 ##

修改UserDaoImpl  
![在这里插入图片描述][2019030821492914.png]

修改UserServiceImpl  
![在这里插入图片描述][20190308214936230.png]

**修改applicationContext.xml**

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
    	xmlns:context="http://www.springframework.org/schema/context"
    	
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    	xmlns:aop="http://www.springframework.org/schema/aop"
    	xsi:schemaLocation="http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd
                http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/context
    	http://www.springframework.org/schema/context/spring-context.xsd
            ">
         
         <context:component-scan base-package="com.sxt.dao.impl">
         </context:component-scan>
         <context:component-scan base-package="com.sxt.service.impl">
         </context:component-scan>
    	
    	<bean id="dataSource" 
    	class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    		
    		<property name="driverClassName" value="com.mysql.jdbc.Driver">
    		</property>
    		<property name="url" value="jdbc:mysql://127.0.0.1:3306/test">
    		</property>
    		<property name="username" value="root"></property>
    		<property name="password" value="123456"></property>
    	</bean>
    	
    	
    	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    		
    		<property name="dataSource" ref="dataSource"></property>
    	</bean>
    
    </beans>

## 8、jdbcTemplate的API用法 ##

@Repository
    public class UserDaoImpl implements UserDao {
        
    	
    	@Autowired
    	private JdbcTemplate jdbcTemplate;
    	
    	@Override
    	public Integer queryUserCount() {
        
    		String sql="select count(1) from sys_user";
    		return this.jdbcTemplate.queryForObject(sql, Integer.class);
    	}
    
    	@Override
    	public void addUser(User user) {
        
    		String sql="insert into sys_user(user_name,user_address,user_birthday) 
    		values(?,?,?)";
    		Object[] args=
    		{
        user.getUserName(),user.getUserAddress(),user.getUserBirthday()};
    		jdbcTemplate.update(sql, args);
    				
    	}
    
    	@Override
    	public void updateUser(User user) {
        
    		String sql="update  sys_user set user_name=?,user_address=?,user_birthday=? 
    		where user_id=?";
    		Object[] args={
        user.getUserName(),user.getUserAddress(),
    		user.getUserBirthday(),user.getUserId()};
    		
    		jdbcTemplate.update(sql, args);
    	}
    
    	@Override
    	public void deleteUser(Integer userId) {
        
    		String sql="delete from  sys_user  where user_id=?";
    		Object[] args={
        userId};
    		jdbcTemplate.update(sql, args);
    	}
    
    	@Override
    	public User queryUserById(Integer userId) {
        
    		String sql="select * from sys_user where user_id=?";
    //		return jdbcTemplate.queryForObject(sql,new Object[]{userId}, User.class);  
    这种写法只能支持基本数据类型包装类和String
    		return jdbcTemplate.queryForObject(sql,new Object[]{
        userId},new 
    		RowMapper<User>() {
        
    			@Override
    			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        
    				Integer id=rs.getInt("user_id");
    				String userName=rs.getString("user_name");
    				String userAddress=rs.getString("user_address");
    				Date userBirthday=rs.getDate("user_birthday");
    				User user=new User(id, userName, userAddress, userBirthday);
    				return user;
    			}
    		});
    	}
    
    	@Override
    	public List<User> queryAllUser() {
        
    		String sql="select * from sys_user";
    		return jdbcTemplate.query(sql, new RowMapper<User>() {
        
    
    			@Override
    			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        
    				Integer id=rs.getInt("user_id");
    				String userName=rs.getString("user_name");
    				String userAddress=rs.getString("user_address");
    				Date userBirthday=rs.getDate("user_birthday");
    				User user=new User(id, userName, userAddress, userBirthday);
    				return user;
    			}
    		});
    	}
    
    	
    	@Override
    	public List<User> queryUserForPage(Integer currentPage, Integer pageSize) {
        
    		String sql="select * from sys_user limit ?,?";
    		Object [] args={
        (currentPage-1)*pageSize,pageSize};
    		return jdbcTemplate.query(sql, args,new RowMapper<User>() {
        
    
    			@Override
    			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        
    				Integer id=rs.getInt("user_id");
    				String userName=rs.getString("user_name");
    				String userAddress=rs.getString("user_address");
    				Date userBirthday=rs.getDate("user_birthday");
    				User user=new User(id, userName, userAddress, userBirthday);
    				return user;
    			}
    		});
    	}
    
    }

[20190308214456639.png]: /images/20220310/80a1ff688766407caa9f4b0bc13c34b2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDEzNzkw_size_16_color_FFFFFF_t_70]: /images/20220310/2ccd90867add40a087ade042d9f3cb7c.png
[20190308214521237.png]: /images/20220310/6fa183be0b584b0bb923beba98be17a6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDEzNzkw_size_16_color_FFFFFF_t_70 1]: /images/20220310/c8b07579f0c142e39793881adcfd3dbc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDEzNzkw_size_16_color_FFFFFF_t_70 2]: /images/20220310/97bb8a1575f2498790013cc16beb6545.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDEzNzkw_size_16_color_FFFFFF_t_70 3]: /images/20220310/48e416d65abb48348d61852fe05164db.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDEzNzkw_size_16_color_FFFFFF_t_70 4]: /images/20220310/ff5737fa430f4440922cf43b9f4d248c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDEzNzkw_size_16_color_FFFFFF_t_70 5]: /images/20220310/6534d55b4dab4723a7b87dc335734de6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDEzNzkw_size_16_color_FFFFFF_t_70 6]: /images/20220310/b7bd7e8e642a4393a55a479000de3715.png
[20190308214856814.png]: /images/20220310/22f1a9e32a35484f9090879b44068c98.png
[2019030821492914.png]: /images/20220310/d5ada479833640a39ee2315a7f07b5c1.png
[20190308214936230.png]: /images/20220310/5f89d0c25ad14e6c9913127b7874e0e5.png