Spring整合JDBC实现用户的CRUD

添加依赖

 
  org.springframework
  spring-context
  ${spring.version}
 

 
  org.springframework
  spring-jdbc
  ${spring.version}
 

 
  mysql
  mysql-connector-java
  5.1.47
 

 
  com.mchange
  c3p0
  0.9.5.2
 

 
  junit
  junit
  4.12
  test
 

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 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.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 findAll() {
        String sql = "select * from account";
        List accountList = jdbcTemplate.query(sql, new BeanPropertyRowMapper(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 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 findAll() {
        return accountDao.findAll();
    }
}

6、编写resourse中配置文件

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);
    }

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

    
    @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);
    }

    
    @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());
    }
}