使用Spring实现mysql数据库的CRUD操作

旧城等待， 2022-01-23 10:27 464阅读 0赞

##  ##

## Spring完成对mysql数据库的crud操作的大致步骤： ##

##  ##

**用Spring的dataSourse类与数据库连接，把一个POJO bean类按MVC构架分成DAO数据持久化层和逻辑业务层service，用JdbcTemlate类完成数据库的添加修改删除查询操作。**

--------------------

**1、接下来我们一步步来，首先看最核心的applicationContext.xml的配置：**  
**（1）创建dataSource对象连接mysql数据库，和hibernate连接数据库差不多。**

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            
            <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
            <property name="url" value="jdbc:mysql:///employee?serverTimezone=GMT%2b8&useSSL=true"></property>
            <property name="username" value="root"></property>
            <property name="password" value="root"></property>
        </bean>

**（2）创建jdbcTemplate对象设置数据源。**

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            
            <property name="dataSource" ref="dataSource"></property>
        </bean>

**（3）使用mvc构架创建dao对象和service对象，并关联起来。**

<bean id="userDao" class="com.dao.UserDao">
            
            <property name="jdbcTemplate" ref="jdbcTemplate"></property>
        </bean>
        
        
        <bean id="userService" class="com.service.UserService">
            
            <property name="userDao" ref="userDao"></property>
        </bean>

**（4）applicationContext.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"
           xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
      	
      	
      	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            
            <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
            <property name="url" value="jdbc:mysql:///employee?serverTimezone=GMT%2b8&useSSL=true"></property>
            <property name="username" value="root"></property>
            <property name="password" value="root"></property>
        </bean>
      	
      	
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            
            <property name="dataSource" ref="dataSource"></property>
        </bean>
    
        
        <bean id="userDao" class="com.dao.UserDao">
            
            <property name="jdbcTemplate" ref="jdbcTemplate"></property>
        </bean>
        
        
        <bean id="userService" class="com.service.UserService">
            
            <property name="userDao" ref="userDao"></property>
        </bean>
      	
    </beans>

**2、编写相关文件。这些代码不复杂，这里不再阐述。**

**（1）UserService.java**

package com.service;
    
    import com.dao.UserDao;
    
    public class UserService { 
    
    	private UserDao userDao;
    
        public void setUserDao(UserDao userDao) { 
            this.userDao = userDao;
        }
    
        public void add(){ 
            userDao.add();
        }
        
        public void update(){ 
            userDao.update();
        }
        
        public void delete(){ 
            userDao.delete();
        }
        
        public void query(){ 
            userDao.query();
        }
    }

**（2）UserDao.java**

package com.dao;
    
    import java.util.List;
    import java.util.Map;
    import org.springframework.jdbc.core.JdbcTemplate;
    
    public class UserDao { 
    	//得到 jdbcTemplate 对象
        private JdbcTemplate jdbcTemplate;
        
        public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
            this.jdbcTemplate = jdbcTemplate;
        }
    
        public void add(){ 
            String sql = "insert into user values(?,?)";
            jdbcTemplate.update(sql, "小明", "123");
            List<Map<String,Object>> users= jdbcTemplate.queryForList("select * from user");//纪录查询结果
            System.out.println(users);
        }
        
        public void update(){ 
        	String sql = "update user set userpass=? where username=?";
        	jdbcTemplate.update(sql, "321", "小明");
        	List<Map<String,Object>> users= jdbcTemplate.queryForList("select * from user");
            System.out.println(users);
        }
        
        public void delete(){ 
            String sql = "delete from user where username=?";
            jdbcTemplate.update(sql, "小明");
            List<Map<String,Object>> users= jdbcTemplate.queryForList("select * from user");
            System.out.println(users);
        }
        
        public void query(){ 
        	String sql = "select * from user where username=?";
        	List<Map<String,Object>> users= jdbcTemplate.queryForList(sql,"小明");
        	System.out.println(users);
    
        }
    }

**3、单元测试文件（UserTest.java）**

package com.test;
    
    import static org.junit.Assert.*;
    
    import org.junit.Test;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    import com.service.UserService;
    public class UserTest { 
    	@Test
    	public void test() { 
    		fail("Not yet implemented");
    	}
    
    	ClassPathXmlApplicationContext l2 = new ClassPathXmlApplicationContext("applicationContext.xml");
    	UserService service = l2.getBean("userService",UserService.class);
    	
    	@Test
    	public void add(){ 
    		service.add();
    		l2.close();//关闭流
    	}
    	
    	@Test
    	public void update(){ 
    		service.update();
    		l2.close();
    	}
    	
    	@Test
    	public void delete(){ 
    		service.delete();
    		l2.close();
    	}
    	
    	@Test
    	public void query(){ 
    		service.query();
    		l2.close();
    	}
    }

**4、CRUD测试**  
**首先在数据库后台自己创建个表：**  
create table user(  
username varchar(20),  
userpass varchar(20)  
);

**（1）添加一条数据：**  
![在这里插入图片描述][20190603160343500.png]

**添加用户名为小明，密码为123**

public void add(){ 
            String sql = "insert into user values(?,?)";
            jdbcTemplate.update(sql, "小明", "123");
            List<Map<String,Object>> users= jdbcTemplate.queryForList("select * from user");//纪录查询结果
            System.out.println(users);
        }

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

**（2）修改数据：**

**右键update()单元测试。  
查询用户名为小明的用户，把密码修改为321。**

public void update(){ 
        	String sql = "update user set userpass=? where username=?";
        	jdbcTemplate.update(sql, "321", "小明");
        	List<Map<String,Object>> users= jdbcTemplate.queryForList("select * from user");
            System.out.println(users);
        }

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

**（3）删除数据**

**右键delete()单元测试。  
删除用户名为小明的信息。**

public void delete(){ 
            String sql = "delete from user where username=?";
            jdbcTemplate.update(sql, "小明");
            List<Map<String,Object>> users= jdbcTemplate.queryForList("select * from user");
            System.out.println(users);
        }

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

**（4）查询数据**

**添加几条不同的数据，然后再查找用户名为小明的数据。**

public void query(){ 
        	String sql = "select * from user where username=?";
        	List<Map<String,Object>> users= jdbcTemplate.queryForList(sql,"小明");
        	System.out.println(users);
    
        }

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

--------------------

[20190603160343500.png]: /images/20220123/ed011bea79ee4773816dd2fc02b007cb.png
[20190603160552310.png]: /images/20220123/858f48eff59d42e6a6f82f92b9861f78.png
[20190603160903921.png]: /images/20220123/5d1a2acc7cbc41839acf845b8fc49502.png
[20190603161522570.png]: /images/20220123/ea5d228db84043239016e22b9ccaa943.png
[20190603162252441.png]: /images/20220123/1fb00ad849d74de7938fd552866c7ab0.png