Spring CRUD

POM.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.company</groupId>
    <artifactId>SpringIOC_01</artifactId>
    <version>1.0-SNAPSHOT</version>
<build>
    <plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.8.1</version>
            <configuration>
                <target>1.8</target>
                <source>1.8</source>
                <encoding>utf-8</encoding>
            </configuration>
        </plugin>
    </plugins>
</build>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.12</version>
        </dependency>
        <dependency>
            <groupId>dom4j</groupId>
            <artifactId>dom4j</artifactId>
            <version>1.1</version>
        </dependency>
        <dependency>
            <groupId>jaxen</groupId>
            <artifactId>jaxen</artifactId>
            <version>1.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.7</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.7.0</version>
        </dependency>
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.4</version>
        </dependency>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>7.2.2.jre8</version>
        </dependency>
    </dependencies>

</project>

POJO目录下的Account.java

public class Account {
    private int id;
    private String name;
    private float money;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public float getMoney() {
        return money;
    }

    public void setMoney(float money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

dao目录下

AccountDao.java

public interface AccountDao {
    void saveAccount(Account account) throws SQLException;
    void updateAccount(Account account) throws SQLException;
    void deleteAccountById(int id) throws SQLException;
    Account queryAccountById(int id) throws SQLException;
    List<Account> queryAccountList() throws SQLException;
}

AccountDaoImpl.java

public class AccountDaoImpl implements AccountDao {
    private QueryRunner queryRunner;

    public void setQueryRunner(QueryRunner queryRunner) {
        this.queryRunner = queryRunner;
    }

    @Override
    public void saveAccount(Account account) throws SQLException {
        String sql="insert into account(name,money) values(?,?)";
        Object[] param={account.getName(),account.getMoney()};
        queryRunner.update(sql,param);
    }

    @Override
    public void updateAccount(Account account) throws SQLException {
        String sql="update account set name=?, money=? where id=?";
        Object[] param={account.getName(),account.getMoney(),account.getId()};
        queryRunner.update(sql,param);
    }

    @Override
    public void deleteAccountById(int id) throws SQLException {
        String sql="delete from account where id=?";
        queryRunner.update(sql,id);
    }

    @Override
    public Account queryAccountById(int id) throws SQLException {
        String sql="select * from account where id=?";
        Account account = queryRunner.query(sql, new BeanHandler<>(Account.class), id);
        return account;
    }

    @Override
    public List<Account> queryAccountList() throws SQLException {
        String sql="select * from account";
        List<Account> accountList = queryRunner.query(sql, new BeanListHandler<>(Account.class));
        return accountList;
    }
}

service目录下

AccountService.java

public interface AccountService {
    void saveAccount(Account account);
    void updateAccount(Account account);
    void deleteAccountById(int id);
    Account queryAccountById(int id);
    List<Account> queryAccountList();
}

AccountServiceImpl.java

public class AccountServiceImpl implements AccountService {
    private AccountDao accountDao;

    public AccountServiceImpl(AccountDao accountDao) {
        this.accountDao = accountDao;
    }

    @Override
    public void saveAccount(Account account) {
        try {
            accountDao.saveAccount(account);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateAccount(Account account) {
        try {
            accountDao.updateAccount(account);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteAccountById(int id) {
        try {
            accountDao.deleteAccountById(id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Account queryAccountById(int id) {
        Account account = null;
        try {
            account = accountDao.queryAccountById(id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return account;
    }

    @Override
    public List<Account> queryAccountList() {
        List<Account> accountList = null;
        try {
            accountList = accountDao.queryAccountList();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return accountList;
    }
}

Spring 配置文件

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="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"></property>
        <property name="jdbcUrl" value="jdbc:sqlserver://localhost:1433;DatabaseName=testdb"></property>
        <property name="user" value="sa"></property>
        <property name="password" value="123.abc"></property>
    </bean>
    <bean id="queryRunner" class="org.apache.commons.dbutils.QueryRunner">
        <constructor-arg name="ds" ref="datasource"></constructor-arg>
    </bean>
    <bean id="accountDao" class="com.company.dao.AccountDaoImpl">
        <property name="queryRunner" ref="queryRunner"></property>
    </bean>
    <bean id="accountService" class="com.company.service.AccountServiceImpl">
        <constructor-arg name="accountDao" ref="accountDao"></constructor-arg>
    </bean>
</beans>

测试

SpringCRUDTest.java

public class SpringCRUDTest {    
    @Test
    public void queryAccountByIdTest(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountService accountService = applicationContext.getBean("accountService", AccountService.class);
        Account account = accountService.queryAccountById(5);
        System.out.println(account);
        ((ClassPathXmlApplicationContext)applicationContext).close();
    }

    @Test
    public void queryAccountListTest(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountService accountService = applicationContext.getBean("accountService", AccountService.class);
        List<Account> accountList = accountService.queryAccountList();
        accountList.forEach(System.out::println);
        ((ClassPathXmlApplicationContext)applicationContext).close();
    }

    @Test
    public void saveAccountTest(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountService accountService = applicationContext.getBean("accountService", AccountService.class);
        Account account=new Account();
        account.setName("september");
        account.setMoney(250f);
        accountService.saveAccount(account);
        ((ClassPathXmlApplicationContext)applicationContext).close();
    }

    @Test
    public void updateAccountTest(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountService accountService = applicationContext.getBean("accountService", AccountService.class);
        Account account = new Account();
        account.setId(3);
        account.setName("october");
        account.setMoney(300f);
        accountService.updateAccount(account);
        ((ClassPathXmlApplicationContext)applicationContext).close();
    }

    @Test
    public void deleteAccountByIdTest(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountService accountService = applicationContext.getBean("accountService", AccountService.class);
        accountService.deleteAccountById(2);
        ((ClassPathXmlApplicationContext)applicationContext).close();
    }
}

 步骤：

1. 添加依赖 POM.xml
2. 添加POJO
3. 编写dao，service
4. 配置Spring配置文件