【Spring】链接数据库

添加Maven依赖：

　　　　 <!--spring-jdbc包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.10.RELEASE</version>
        </dependency>
        <!--spring-tx-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.2.10.RELEASE</version>
        </dependency>
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>

 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"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       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 id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <!-- 数据库驱动 -->
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <!-- 连接数据库url -->
        <property name="url" value="jdbc:mysql://localhost/spring?useUnicode=true&amp;characterEncoding=utf-8&amp;serverTimezone=Asia/Shanghai" />
        <property name="username" value="root"/><!-- 连接数据库用户名 -->
        <property name="password" value="1234"/><!-- 连接数据库密码 -->
    </bean>
    <!-- 配置jdbc模板 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!-- 默认必须使用数据源 -->
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!--使用context命名空间，开启注解处理器-->
    <context:component-scan base-package="com.niuzhuang"/>
    <bean id="userDao" class="com.niuzhuang.dao.impl.UserDaoImpl"/>
    <bean id="xmlAdvice" class="com.niuzhuang.demo.XmlAdvice"/>
    <bean id="accountDao" class="com.niuzhuang.dao.impl.AccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
    <!--开启自动代理支持-->
    <aop:aspectj-autoproxy/>
</beans>

 

 AccountDao.java

package com.niuzhuang.dao;

import com.niuzhuang.entity.Account;

public interface AccountDao {
    public int addAccount(Account account);
    public int updateAccount(Account account);
    public int deleteAccount(Account account);
}

AccountDaoImpl.java

package com.niuzhuang.dao.impl;

import com.niuzhuang.dao.AccountDao;
import com.niuzhuang.entity.Account;
import org.springframework.jdbc.core.JdbcTemplate;

public class AccountDaoImpl implements AccountDao {
    private JdbcTemplate jdbcTemplate;

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

    public int addAccount(Account account) {
        String sql = "insert into account(name,balance) values(?,?)";
        Object[] args = {account.getName(),account.getBalance()};
        int number = jdbcTemplate.update(sql,args);
        return number;
    }

    public int updateAccount(Account account) {
        String sql = "update account set name=?,balance=? where id=?";
        int number = jdbcTemplate.update(sql,account.getName(),account.getBalance(),account.getId());
        return number;
    }

    public int deleteAccount(Account account) {
        return 0;
    }
}

Account实体类：

 

 测试运行代码：

package com.niuzhuang;

import com.niuzhuang.dao.AccountDao;
import com.niuzhuang.entity.Account;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;


public class TestJdbcTemplate {
    //添加
    @Test
    public void add(){
        ApplicationContext ap = new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountDao accountDao = ap.getBean("accountDao", AccountDao.class);
        Account account = new Account();
        account.setName("张三");
        account.setBalance(10000.0);
        int num = accountDao.addAccount(account);
        System.out.println("添加了" + num + "条记录");
    }
    //修改
    @Test
    public void update(){
        ApplicationContext ap = new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountDao accountDao = ap.getBean("accountDao", AccountDao.class);
        Account account = new Account();
        account.setId(1);
        account.setName("重岳");
        account.setBalance(10000.0);
        int num = accountDao.updateAccount(account);
        System.out.println("修改了" + num + "条记录");
    }
    //删除
    @Test
    public void delete(){
        ApplicationContext ap = new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountDao accountDao = ap.getBean("accountDao", AccountDao.class);
        Account account = new Account();
        account.setId(1);
        int num = accountDao.deleteAccount(account);
        System.out.println("删除了" + num + "条记录");
    }
    //查询----通过id和name查询
    @Test
    public void findByIdAndName(){
        ApplicationContext ap = new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountDao accountDao = ap.getBean("accountDao", AccountDao.class);
        Account account = accountDao.findByIdAndName(1,"张三");
        System.out.println(account);
    }
    //查询所有
    @Test
    public void findAll(){
        ApplicationContext ap = new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountDao accountDao = ap.getBean("accountDao", AccountDao.class);
        List<Account> accountList = accountDao.findAll();
        if (accountList.size()>0){
            for (Account account:accountList) {
                System.out.println(account);
            }
        }else {
            System.out.println("查询不到数据");
        }
    }
}