使用Spring连接数据库（增删改查）简单JDBC连接

使用Spring连接数据库（增删改查）

applicationContext.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       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
       http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
">
    <!--编写数据库配置-->
    <bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="dataSource">
        <property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
        <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl1"/>
        <property name="username" value=""/>
        <property name="password" value=""/>
     </bean>
    <!--编写jdbc模板-->
    <bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <!--扫描注解-->
    <context:component-scan base-package="cn.*"/>

</beans>

---

测试

---

创建一个测试增删改查的数据库表

create table account(id number(11) primary key,
username nvarchar2(20) not null,
balance number(10,2) )

---

编写pojo实体类

package cn.pojo;

public class Account {
    private int id;
    private String userName;
    private Double balance;

    public int getId() {
        return id;
    }

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

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public Double getBalance() {
        return balance;
    }

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

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

---

编写dao层接口

• 随便写几个代表性的增删改查

package cn.dao;

import cn.pojo.Account;

import java.util.List;

public interface AccountDao {
    public int addAccount(Account account);
    public int deleteAccount(int account_id);
    public int updateAccount(Account account);
    public Account getAccountById(int account_id);
    public List<Account> findAllAccount();
}

编写dao层接口实现类

package cn.dao.impl;

import cn.dao.AccountDao;
import cn.pojo.Account;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;
@Repository("accountDaoImpl")
public class AccountDaoImpl implements AccountDao {
    @Autowired
    JdbcTemplate jdbcTemplate;
    @Override
    public int addAccount(Account account) {
        String sql="insert into account(id,username,balance) VALUES(seq_account_id.nextval,?,?)";
        int result= jdbcTemplate.update(sql,account.getUserName(),account.getBalance());
        return result;
    }

    @Override
    public int deleteAccount(int account_id) {
        String sql="delete from account where id=?";
        int result= jdbcTemplate.update(sql,account_id);
        return result;
    }

    @Override
    public int updateAccount(Account account) {

        String sql="update account set username=?,balance=? where id=? ";
        int result= jdbcTemplate.update(sql,account.getUserName(),account.getBalance(),account.getId());
        return result;
    }

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

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

        return accountList;
    }
}

编写service层接口

package cn.service;

import cn.pojo.Account;

import java.util.List;

public interface AccountService {
        public int addAccount(Account account);
        public int deleteAccount(int account_id);
        public int updateAccount(Account account);
        public Account getAccountById(int account_id);
        public List<Account> findAllAccount();
}

---

编写service层接口实现类

package cn.service.impl;

import cn.dao.AccountDao;
import cn.pojo.Account;
import cn.service.AccountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
@Service
public class AccountServiceImpl implements AccountService {
    @Autowired
    AccountDao accountDao;
    @Override
    public int addAccount(Account account) {
        return accountDao.addAccount(account);
    }

    @Override
    public int deleteAccount(int account_id) {
        return accountDao.deleteAccount(account_id);
    }

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

    @Override
    public Account getAccountById(int account_id) {
        return accountDao.getAccountById(account_id);
    }

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

---

编写controller

因为没写页面，直接这样简单写了！！

package cn.controller;

import cn.dao.AccountDao;
import cn.pojo.Account;
import cn.service.AccountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;

@Controller("accountController")
public class AccountController {
    @Autowired
    private AccountService accountService;
    public  void add(){
        Account account=new Account();
        account.setUserName("王五");
        account.setBalance(10000.00);
        System.out.println(accountService.addAccount(account));
    }
    public  void delete(){
        System.out.println(accountService.deleteAccount(30));
    }
    public  void update(){
        Account account=new Account();
        account.setId(11);
        account.setBalance(18000.00);
        account.setUserName("王五");
        System.out.println(accountService.updateAccount(account));
    }
    public  void getAccountById(){
        System.out.println(accountService.getAccountById(11));
    }
    public  void findAllAccount(){
        System.out.println(accountService.findAllAccount());
    }
}

编写测试类

我就只展示一个测试代码（偷懒）

import cn.controller.AccountController;
import cn.pojo.Account;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;

public class TestAccountController {
    public static void main(String[] args) {
        ApplicationContext context=new ClassPathXmlApplicationContext("application4.xml");
        AccountController accountController= (AccountController) context.getBean("accountController");
        accountController.findAllAccount();
    }
}

---

结束！！！