Spring整合JDBC实现用户的CRUD

添加依赖

<dependencies>
<!--导入spring的context坐标-->
 <dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-context</artifactId>
  <version>${spring.version}</version>
 </dependency>
<!--导入Jdbc模块依赖-->
 <dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-jdbc</artifactId>
  <version>${spring.version}</version>
 </dependency>
<!--导入Mysql驱动-->
 <dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.47</version>
 </dependency>
<!--导入C3P0连接池-->
 <dependency>
  <groupId>com.mchange</groupId>
  <artifactId>c3p0</artifactId>
  <version>0.9.5.2</version>
 </dependency>
<!--导入junit单元测试-->
 <dependency>
  <groupId>junit</groupId>
  <artifactId>junit</artifactId>
  <version>4.12</version>
  <scope>test</scope>
 </dependency>
</dependencies>

 

2、编写持久层和其实现类

import com.offcn.entity.Account;
import java.util.List;

public interface AccountDao {
    public void save(Account account);
    public void delete(Integer id);
    public void update(Account account);
    public Account findById(Integer id);
    public Long getTotalRecords();
    public List<Account> findAll();
}

import com.offcn.entity.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;

public class AccountDaoImpl implements AccountDao{

    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }


    @Override
    public void save(Account account) {
        String sql ="insert into account(name,money) values(?,?)";
        jdbcTemplate.update(sql, account.getName(),account.getMoney());
    }

    @Override
    public void delete(Integer id) {
        String sql ="delete from account where id = ? " ;
        jdbcTemplate.update(sql, id);
    }

    @Override
    public void update(Account account) {
        String sql ="update account set money = ? , name=? where id= ?";
        jdbcTemplate.update(sql, account.getMoney(),account.getName(), account.getId());
    }

    @Override
    public Account findById(Integer id) {
        String sql = "select * from account where id = ?";
        Account account = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Account>(Account.class), id);
        return account;
    }

    @Override
    public Long getTotalRecords() {
        Long conunt = jdbcTemplate.queryForObject("select count(*) from account" , Long.class);
        System.out.println(conunt);
        return conunt;
    }

    @Override
    public List<Account> findAll() {
        String sql = "select * from account";
        List<Account> accountList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Account>(Account.class));
        return accountList;
    }
}

3、编写实体类

public class Account {
    private Integer id;
    private String name;
    private String money;


    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 getMoney() {
        return money;
    }

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

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

4、编写业务层和其实现类

import com.offcn.entity.Account;

import java.util.List;

public interface AccountService {
    public void  save(Account account);

    public void delete(Integer id);

    public void update(Account account);

    public Account findById(Integer id);

    public Long getTotalRecords();

    public List<Account> findAll();
}

import com.offcn.dao.AccountDao;
import com.offcn.entity.Account;
import com.offcn.service.AccountService;
import java.util.List;

public class AccountServiceImpl implements AccountService {

    private AccountDao accountDao;

    public void setAccountDao(AccountDao accountDao) {
        this.accountDao = accountDao;
    }

    @Override
    public void save(Account account) {
       accountDao.save(account);
    }

    @Override
    public void delete(Integer id) {
        accountDao.delete(id);
    }

    @Override
    public void update(Account account) {
        accountDao.update(account);
    }

    @Override
    public Account findById(Integer id) {

        return accountDao.findById(id);
    }

    @Override
    public Long getTotalRecords() {
        return accountDao.getTotalRecords();
    }

    @Override
    public List<Account> findAll() {
        return accountDao.findAll();
    }
}

6、编写resourse中配置文件

<?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.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql:///demo_0914"/>
        <property name="user" value="root"/>
        <property name="password" value="root"/>
    </bean>

    <!--jdbcTemplate-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--通过构造器实现将dataSource注入到该bean对象中-->
        <constructor-arg name="dataSource" ref="dataSource"/>
    </bean>

    <!--accountDao-->
    <bean id="accountDao" class="com.offcn.dao.AccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
<!--    配置AccountServiceImpl对象-->
    <bean id="accountService" class="com.offcn.service.impl.AccountServiceImpl">
        <property name="accountDao" ref="accountDao"></property>
    </bean>
</beans>

7、编写测试类

import com.offcn.entity.Account;
import com.offcn.service.AccountService;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class AppTest {
    /**
     * 添加
     */
    @Test
    public void addPerson() {
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring.xml");
        AccountService service =(AccountService) context.getBean("accountService");
        Account account = new Account();
        account.setName("奥特曼");
        account.setMoney("10000");
        service.save(account);
    }

    /**
     * 根据ID删除
     */
    @Test
    public void deletePerson(){
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring.xml");
        AccountService accontService =(AccountService)context.getBean("accountService");
//        Account account = new Account();
        accontService.delete(3);
    }

    /**
     * 根据ID修改
     */
    @Test
    public void updatePerson(){
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring.xml");
        AccountService accountService=(AccountService) context.getBean("accountService");
        Account account = new Account();
        account.setName("李四");
        account.setMoney("5000");
        account.setId(4);
        accountService.update(account);
    }

    /**
     * 根据ID查询
     */
    @Test
    public void selectPerson(){
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring.xml");
        AccountService service = (AccountService)context.getBean("accountService");
        System.out.println(service.findById(1).toString());
    }

    /**
     * 查询所有
     */
    @Test
    public void selectPersonAll(){
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring.xml");
        AccountService service = (AccountService)context.getBean("accountService");
        System.out.println(service.findAll().toString());
    }

    /**
     * 查询总记录数
     */
    @Test
    public void selectGetTotal(){
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring.xml");
        AccountService service = (AccountService)context.getBean("accountService");
        System.out.println("表中的总记录数" + service.getTotalRecords());
    }
}