Spring的数据库编程

本文已参与「新人创作礼」活动，一起开启掘金创作之路。 1.新建一个项目spring_jdbc

2.在java里新建一个包cn.edu.ldu

3.在新建的包再分别建几个包dao,entity,service:

dao包下的两个文件：

1).AccountDaoImpl.java:

package cn.edu.ldu.dao;
import cn.edu.ldu.entity.Account;
import org.springframework.jdbc.core.JdbcTemplate;
public class AccountDaoImpl implements IAccountDao {

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

    public int save(Account account){

        String sql="insert into account(username,balance) value(?,?)";
        Object[] obj=new Object[]{
                account.getUsername(),
                account.getBalance()
        };
        int num=this.jdbcTemplate.update(sql,obj);
        return num;
    }


    public int update(Account account){

        String sql="update account set username=?,balance=? where id=?";
        Object[] params=new Object[]{
                account.getUsername(),
                account.getBalance(),
                account.getId()

        };
        int num=this.jdbcTemplate.update(sql,params);
        return num;

    }

    public int delete(int id){

        String sql="delete from account where id=?";
        int num=this.jdbcTemplate.update(sql,id);
        return num;
    }
}

2).IAccountDao（接口）

package cn.edu.ldu.dao;

import cn.edu.ldu.entity.Account;

public interface IAccountDao {

    public int save(Account account);

    public int update(Account account);

    public int delete(int id);
}

entity包下的文件：（实体类）

package cn.edu.ldu.entity;

public class Account {

    private Integer id;
    private String username;
    private Double balance;

    public Double getBalance() {
        return balance;
    }

    public Integer getId() {
        return id;
    }

    public String getUsername() {
        return username;
    }

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

    public void setUsername(String username) {
        this.username = username;
    }

    public void setBalance(Double balance) {
        this.balance = balance;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", username='" + username + ''' +
                ", balance=" + balance +
                '}';
    }
}

service包下的文件：

1）AccountServiceImpl

package cn.edu.ldu.service;

import cn.edu.ldu.dao.AccountDaoImpl;
import cn.edu.ldu.dao.IAccountDao;
import cn.edu.ldu.entity.Account;

public class AccountServiceImpl implements IAccountService {


    private IAccountDao accountDao;
    public void setAccountDao(IAccountDao accountDao){
        this.accountDao=accountDao;
    }
    public int saveAccount(Account account){
        return accountDao.save(account);
    }

    public int deleteAccount(Integer accountId){
        return accountDao.delete(accountId);
    }

    public int updateAccount(Account account){
        return accountDao.update(account);
    }
}

2)IAccountService

package cn.edu.ldu.service;

import cn.edu.ldu.entity.Account;

public interface IAccountService {

    public int saveAccount(Account account);
    public int updateAccount(Account account);
    public int deleteAccount(Integer accountId);
}

配置文件---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://localhost/spring?useUnicode=true&amp;characterEncoding=utf-8&amp;serverTimezone=UTC"></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="accountDao" class="cn.edu.ldu.dao.AccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>

    <bean id="accountService" class="cn.edu.ldu.service.AccountServiceImpl">
        <property name="accountDao" ref="accountDao"/>
    </bean>
</beans>

写一个测试类，用于创建一个表：

JdbcTemplateTest.java:

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
public class JdbcTemplateTest {
    public static void main(String[] args) {
        ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
        JdbcTemplate jdbcTemplate = (JdbcTemplate) ac.getBean("jdbcTemplate");
        jdbcTemplate.execute("create table account("+"id int primary key auto_increment,"+"username varchar (50),"+"balance double)");
        System.out.println("账务表account创建成功");
    }
}

发现spring数据库下新建的一个account表已经建好了：

现在想要在数据库表插入一条数据：

写一个测试类 AccountServiceTest.java:

import cn.edu.ldu.entity.Account;
import cn.edu.ldu.service.IAccountService;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class AccountServiceTest {

    private ApplicationContext ac;
    private IAccountService accountService;

    @Before
    public void init(){
        ac=new ClassPathXmlApplicationContext("applicationContext.xml");
        accountService=(IAccountService)ac.getBean("accountService");
    }

    @Test
    public void saveTest(){
        Account account=new Account();
        account.setUsername("tom");
        account.setBalance(1000.00);

        int num=accountService.saveAccount(account);

        if (num>0){
            System.out.println("成功插入了"+num+"条数据!");
        }
        else {
            System.out.println("插入操作执行失败!");
        }

    }
    public void updateTest(){
        Account account=new Account();
        account.setId(1);
        account.setUsername("tom");
        account.setBalance(2000.00);

        int num=accountService.updateAccount(account);
        if (num>0){
            System.out.println("成功修改了"+num+"条数据");
        }
        else{
            System.out.println("修改操作执行失败");
        }

    }
}

效果图：

想要删除一条数据：

在AccountServiceTest增加删除的方法：

import cn.edu.ldu.entity.Account;
import cn.edu.ldu.service.IAccountService;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class AccountServiceTest {

    private ApplicationContext ac;
    private IAccountService accountService;

    @Before
    public void init(){
        ac=new ClassPathXmlApplicationContext("applicationContext.xml");
        accountService=(IAccountService)ac.getBean("accountService");
    }

    @Test
    public void saveTest(){

        Account account=new Account();
        account.setUsername("tom");
        account.setBalance(1000.00);

        int num=accountService.saveAccount(account);

        if (num>0){
            System.out.println("成功插入了"+num+"条数据!");
        }
        else {
            System.out.println("插入操作执行失败!");
        }

    }


    @Test
    public void updateTest(){
        Account account=new Account();
        account.setId(1);
        account.setUsername("tom");
        account.setBalance(2000.00);

        int num=accountService.updateAccount(account);
        if (num>0){
            System.out.println("成功修改了"+num+"条数据");
        }
        else{
            System.out.println("修改操作执行失败");
        }

    }

    @Test
    public void deleteTest(){
        int num=accountService.deleteAccount(1);
        if (num>0){
            System.out.println("成功删除了"+num+"条数据");
        }
        else {
            System.out.println("删除操作执行失败");
        }
    }
}

删除的效果图：

发现刚刚的数据已经被删除：

学习一下query()方法：

1.现在数据库表里插入几条数据：

2.在IAccountDao接口里编写查询方法：

声明findById()方法，通过id查询单个账户信息；

声明findAll()方法，用于查询所有账户信息。

package cn.edu.ldu.dao;
import cn.edu.ldu.entity.Account;
import java.util.List;
public interface IAccountDao {
    public int save(Account account);

    public int update(Account account);

    public int delete(int id);

    //通过id查询
    public Account findById(int id);

    //查询所有账户
    public List<Account> findAll();
}

3.实现查询方法findById()

在AccountDaoImpl类中，实现IAccountDao接口中的findById()方法，并调用queryForObject 方法进行条件查询

AccountDaoImpl接口:

package cn.edu.ldu.dao;

import cn.edu.ldu.entity.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.util.List;

public class AccountDaoImpl implements IAccountDao {

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

    public int save(Account account){

        String sql="insert into account(username,balance) value(?,?)";
        Object[] obj=new Object[]{
                account.getUsername(),
                account.getBalance()
        };
        int num=this.jdbcTemplate.update(sql,obj);
        return num;
    }


    public int update(Account account){

        String sql="update account set username=?,balance=? where id=?";
        Object[] params=new Object[]{
                account.getUsername(),
                account.getBalance(),
                account.getId()

        };
        int num=this.jdbcTemplate.update(sql,params);
        return num;

    }

    public int delete(int id){

        String sql="delete from account where id=?";
        int num=this.jdbcTemplate.update(sql,id);
        return num;
    }


    public Account findById(int id){

        String sql="select * from account where id=?";
        RowMapper<Account> rowMapper=new BeanPropertyRowMapper<Account>(Account.class);
        Account account=this.jdbcTemplate.queryForObject(sql,rowMapper,id);
        return account;
    }

    public List<Account> findAll() {

        String sql="select *from account";
        RowMapper<Account> rowMapper=new BeanPropertyRowMapper<Account>(Account.class);
        return this.jdbcTemplate.query(sql,rowMapper);
    }

}

AccountServiceImpl实现类：

package cn.edu.ldu.service;

import cn.edu.ldu.dao.AccountDaoImpl;
import cn.edu.ldu.dao.IAccountDao;
import cn.edu.ldu.entity.Account;

import java.util.List;

public class AccountServiceImpl implements IAccountService {


    private IAccountDao accountDao;
    public void setAccountDao(IAccountDao accountDao){
        this.accountDao=accountDao;
    }
    public int saveAccount(Account account){
        return accountDao.save(account);
    }

    public int deleteAccount(Integer accountId){
        return accountDao.delete(accountId);
    }

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

    public Account findAccountById(int i) {
        return accountDao.findById(i);
    }

    public int updateAccount(Account account){
        return accountDao.update(account);
    }
}

IAccountService接口：

package cn.edu.ldu.service;

import cn.edu.ldu.entity.Account;

import java.util.List;

public interface IAccountService {

    public int saveAccount(Account account);
    public int updateAccount(Account account);
    public int deleteAccount(Integer accountId);

    List<Account> findAllAccount();

    Account findAccountById(int i);
}

AccountServiceTest测试类：

import cn.edu.ldu.dao.AccountDaoImpl;
import cn.edu.ldu.dao.IAccountDao;
import cn.edu.ldu.entity.Account;
import cn.edu.ldu.service.IAccountService;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;

public class AccountServiceTest {

    private ApplicationContext ac;
    private IAccountService accountService;

    @Before
    public void init(){
        ac=new ClassPathXmlApplicationContext("applicationContext.xml");
        accountService=(IAccountService)ac.getBean("accountService");
    }

    @Test
    public void saveTest(){

        Account account=new Account();
        account.setUsername("tom");
        account.setBalance(1000.00);

        int num=accountService.saveAccount(account);

        if (num>0){
            System.out.println("成功插入了"+num+"条数据!");
        }
        else {
            System.out.println("插入操作执行失败!");
        }

    }


    @Test
    public void updateTest(){
        Account account=new Account();
        account.setId(1);
        account.setUsername("tom");
        account.setBalance(2000.00);

        int num=accountService.updateAccount(account);
        if (num>0){
            System.out.println("成功修改了"+num+"条数据");
        }
        else{
            System.out.println("修改操作执行失败");
        }

    }


    @Test
    public void deleteTest(){
        int num=accountService.deleteAccount(1);
        if (num>0){
            System.out.println("成功删除了"+num+"条数据");
        }
        else {
            System.out.println("删除操作执行失败");
        }
    }

    // 测试类增加条件查询方法
    @Test
    public void findByIdTest(){

        Account account=accountService.findAccountById(1);
        System.out.println(account);
    }

    // AccountServiceImpl类的条件查询方法

    public Account findAccountById(Integer accountId){
        AccountDaoImpl accountDao = new AccountDaoImpl();
        return accountDao.findById(accountId);
    }

    // AccountServiceImpl类的条件查询方法
    @Test
    public void findAllTest(){

        List<Account> accountList=accountService.findAllAccount();
        for (Account account:accountList){
            System.out.println(account);
        }
    }
// AccountServiceImpl类的查询方法
    public List<Account> findAllAccount(){
        AccountDaoImpl accountDao = new AccountDaoImpl();
        return accountDao.findAll();
    }


}

通过id进行查询：

全部查询出来：

本篇博客结束啦，欢迎各位大佬随时交流呀！