Spring注解+xml配置文件实现数据库的CRUD操作

男娘i 2022-12-01 11:56 190阅读 0赞

**问题引出**  
Spring为轻量级开源框架，以IOC（Inversion of Controll控制反转，用于解耦）和AOP（Aspect oriented Programming，面向切面编程，本质是动态代理，不修改源码的情况下进行功能增强）为内核。下面我们简单实现一下通过IOC思想实现数据库的增删改查操作  
实现步骤：  
**第一步：环境准备**  
1，maven中pox.xml需要导入的jar依赖

<dependencies>
        	
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>5.2.5.RELEASE</version>
            </dependency>
        	
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-test</artifactId>
                <version>5.2.5.RELEASE</version>
            </dependency>
        	
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
            </dependency>
        	
            <dependency>
                <groupId>org.yaml</groupId>
                <artifactId>snakeyaml</artifactId>
                <version>1.25</version>
            </dependency>
    
    		
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.47</version>
            </dependency>
        	
            <dependency>
                <groupId>c3p0</groupId>
                <artifactId>c3p0</artifactId>
                <version>0.9.1.2</version>
            </dependency>
        </dependencies>

\*\*第二步：JavaBean,Dao,Service 的创建  
1，Javabean

public class Account {
        private Integer id;
        private String name;
        private Double money;
    
        //get/set...
        //toString...
    }

2,dao接口 以及dao的实现类

public interface AccountDao {
        Account findById(Integer id) throws SQLException;
    
        List<Account> queryAll() throws SQLException;
    
        void save(Account account) throws SQLException;
    
        void edit(Account account) throws SQLException;
    
        void delete(Integer id) throws SQLException;
    }

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

@Repository("accountDao")
    public class AccountDaoImpl implements AccountDao {
        
        @Autowired
        private QueryRunner runner;
        
        @Override
        public Account findById(Integer id) throws SQLException {
            return runner.query("select * from account where id = ?", new BeanHandler<>(Account.class), id);
        }
    
        @Override
        public List<Account> queryAll() throws SQLException {
            return runner.query("select * from account", new BeanListHandler<>(Account.class));
        }
    
        @Override
        public void save(Account account) throws SQLException {
            runner.update("insert into account(id,name,money) values (?,?,?)", account.getId(), account.getName(), account.getMoney());
        }
    
        @Override
        public void edit(Account account) throws SQLException {
            runner.update("update account set name = ?, money = ? where id = ?", account.getName(), account.getMoney(), account.getId());
        }
        @Override
        public void delete(Integer id) throws SQLException {
            runner.update("delete from account where id = ?", id);
        }
    }

3,service层，接口与实现类

public interface AccountService {
        Account findById(Integer id) throws SQLException;
    
        List<Account> queryAll() throws SQLException;
    
        void save(Account account) throws SQLException;
    
        void edit(Account account) throws SQLException;
    
        void delete(Integer id) throws SQLException;
    }

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

@Service("accountService")
    public class AccountServiceImpl implements AccountService {
    
        @Autowired
        private AccountDao accountDao;
    
        @Override
        public Account findById(Integer id) throws SQLException {
            return accountDao.findById(id);
        }
    
        @Override
        public List<Account> queryAll() throws SQLException {
            return accountDao.queryAll();
        }
    
        @Override
        public void save(Account account) throws SQLException {
            accountDao.save(account);
        }
    
        @Override
        public void edit(Account account) throws SQLException {
            accountDao.edit(account);
        }
    
        @Override
        public void delete(Integer id) throws SQLException {
            accountDao.delete(id);
        }
    }

**第三步：Spring配置文件**

<?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.xsd
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context.xsd">
    
        
        <context:component-scan base-package="com.itheima"/>
    
        
        <bean id="runner" class="org.apache.commons.dbutils.QueryRunner">
            <constructor-arg name="ds" ref="dataSource"/>
        </bean>
        
        <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            <property name="driverClass" value="com.mysql.jdbc.Driver"/>
            <property name="jdbcUrl" value="jdbc:mysql:///spring"/>
            <property name="user" value="root"/>
            <property name="password" value="root"/>
        </bean>
    </beans>

**第四步：测试类**

public class AccountTest {
    
        @Test
        public void queryAll() throws SQLException {
            ApplicationContext app = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
            AccountService accountService = app.getBean("accountService", AccountService.class);
            List<Account> accounts = accountService.queryAll();
            for (Account account : accounts) {
                System.out.println(account);
            }
        }
    
        @Test
        public void findById() throws SQLException {
            ApplicationContext app = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
            AccountService accountService = app.getBean("accountService", AccountService.class);
            Account account = accountService.findById(2);
            System.out.println(account);
        }
    
        @Test
        public void save() throws SQLException {
            ApplicationContext app = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
            AccountService accountService = app.getBean("accountService", AccountService.class);
    
            Account account = new Account();
            account.setName("jerry");
            account.setMoney(2000d);
            accountService.save(account);
        }
    
        @Test
        public void edit() throws SQLException {
            ApplicationContext app = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
            AccountService accountService = app.getBean("accountService", AccountService.class);
    
            Account account = new Account();
            account.setId(2);
            account.setName("tom");
            account.setMoney(10000d);
    
            accountService.edit(account);
        }
    
        @Test
        public void delete() throws SQLException {
            ApplicationContext app = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
            AccountService accountService = app.getBean("accountService", AccountService.class);
    
            accountService.delete(3);
        }
    }

数据库  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpZXJlbmJpamkyMQ_size_16_color_FFFFFF_t_70_pic_center]  
结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpZXJlbmJpamkyMQ_size_16_color_FFFFFF_t_70_pic_center 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpZXJlbmJpamkyMQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/3c60c7966b8f434e96b578418c175954.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpZXJlbmJpamkyMQ_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/4a2279f7e7bf4e29a52abdea2066dcd7.png