spring：四、jdbc

包结构

1、导入依赖

  <!--spring jdbc依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.13</version>
        </dependency>

        <!--mysql依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>

<!--事务依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>4.3.7.RELEASE</version>
        </dependency>

<!--        druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>

2、属性配置文件jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
jdbc.username=root
jdbc.password=wsy

3、创建实体类（属性与数据库一致）

4、applicationContext配置文件配置数据源和jdbc模版

要先扫描properties文件

<!--    扫描properties文件-->
    <context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>

<!--    数据源-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${jdbc.driver}"></property>
        <!-- 连接数据库的url -->
        <property name="url" value="${jdbc.url}"></property>
        <!-- 连接数据库的用户名 -->
        <property name="username" value="${jdbc.username}"></property>
        <!-- 连接数据库的密码 -->
        <property name="password" value="${jdbc.password}"></property>

    </bean>


    <!-- 2.配置jdbc模板 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!-- 默认必须使用数据源 -->
        <property name="dataSource" ref="dataSource"></property>
    </bean>

5、创建dao层

实现dao接口

package com.dao.impl;

import com.dao.UserDao;
import com.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;


public class UserDaoImpl implements UserDao {

    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

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


    @Override
    public int addUser(User user) {
        String sql="insert into t_user(name,money) values(?,?)";
        return jdbcTemplate.update(sql,user.getName(),user.getMoney());
    }

    @Override
    public int updateUser(User user) {
        String sql="update  t_user set name=?,money=? where id=?";
        return jdbcTemplate.update(sql,user.getName(),user.getMoney(),user.getId());
    }

    @Override
    public int delUser(int id) {

        String sql="delete from t_user where id=?";
        return jdbcTemplate.update(sql,id);
    }

    @Override
    public List<User> querryAll() {
        String sql="select * from t_user";
        return jdbcTemplate.query(sql, new RowMapper<User>() {
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getInt("id")); // 假设User有一个setId方法
                user.setName(rs.getString("name")); // 假设User有一个setName方法
                // 设置其他User的属性...
                return user;
            }
        });
    }

    @Override
    public int addMoney(String userName, Double addmoney) {
        String sql="update  t_user set money=money+? where name=?";
        return jdbcTemplate.update(sql,addmoney,userName);
    }

    @Override
    public int delMoney(String userName, Double delmoney) {
        String sql="update  t_user set money=money-? where name=?";
        return jdbcTemplate.update(sql,delmoney,userName);
    }
}

7、创建bean对象，userDao采用set方式注入jdbcTemplate

<!--userDao-->
    <bean id="userDao" class="com.dao.impl.UserDaoImpl">
<!--        set注入-->
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>

8、测试dao

import com.dao.UserDao;
import com.pojo.User;
import jdk.nashorn.internal.ir.CallNode;
import org.junit.jupiter.api.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;

public class JdbcTest {



    @Test
    public void testAddUser(){
        ApplicationContext applicationContext =
                new ClassPathXmlApplicationContext("applicationConetxt.xml");
        UserDao userDao = (UserDao) applicationContext.getBean("userDao");
        int res = userDao.addUser(new User("wsy4", 5000d));
        System.out.println(res);
    }
    @Test
    public void testUpdateUser(){
        ApplicationContext applicationContext =
                new ClassPathXmlApplicationContext("applicationConetxt.xml");
        UserDao userDao = (UserDao) applicationContext.getBean("userDao");
        int res = userDao.updateUser(new User(1,"wsy3", 5000d));
        System.out.println(res);
    }
    @Test
    public void testDelUser(){
        ApplicationContext applicationContext =
                new ClassPathXmlApplicationContext("applicationConetxt.xml");
        UserDao userDao = (UserDao) applicationContext.getBean("userDao");
        int res = userDao.delUser(5);
        System.out.println(res);
    }
    @Test
    public void testQueryUser(){
        ApplicationContext applicationContext =
                new ClassPathXmlApplicationContext("applicationConetxt.xml");
        UserDao userDao = (UserDao) applicationContext.getBean("userDao");
        List<User> users = userDao.querryAll();
        for (int i = 0; i < users.size(); i++) {
            System.out.println(users.get(i));
        }
    }
}