关于spring:Spring04Spring操作JdbcTemplate进行JDBC操作

Spring 进行 JDBC 操作

前言 ：传统的 JDBC 操作十分的繁琐, 为了使 JDBC 更加便于应用,Spring 整合了 JDBC，以此建设一个 JDBC 存取框架: JdbcTemplate

1. 开发环境

根本开发环境： 1.JDK:-8u101-windows-x64 2.Mysql:5.5 3.Maven 配置

注意事项：1.jdk-9.0.4 会报错：空指针异样尽量用 jdk1.8

2. 如果用 idea2018 或以上版本记得加上 pom.xml 的插件

pom.xml 配置

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    
    <groupId>org.example</groupId>
    <artifactId>spring_jdbc_zsgc</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.32</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.8.7</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.32</version>
        </dependency>
    </dependencies>
    
    <!-- 能够会报错不反对发现版本 -->
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.7</maven.compiler.source>
        <maven.compiler.target>1.7</maven.compiler.target>
    </properties>

    <!-- 设置插件 -->
    <build>
        <plugins>
            <!--JDK 编译插件 -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.2</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <!-- tomcat7 插件 -->
            <plugin>
                <groupId>org.apache.tomcat.maven</groupId>
                <artifactId>tomcat7-maven-plugin</artifactId>
                <version>2.2</version>
                <configuration>
                    <!--
                        解决 get 申请乱码
                    -->
                    <uriEncoding>utf-8</uriEncoding>
                    <port>80</port>
                    <path>/</path>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

2.Mysql 库名表名

2.1 数据库名 > 数据库字段

-- 建库
create database travel;
-- 建表
create table key_table(
    id         int  not null,
    `name`  varchar(20),
    dates     varchar(20)
);

3. 创立包以及接口和类

分层思维：

1. com.xxx

   1. dao(接口层)

      • keyTableDao(接口)

        • impl

          • keyTableDaoImpl(实现类)

   2. pojo

      1. keyTable(实体类)

   3. service(服务层)

      • keyTableService(接口)

        • impl
        • keyTableServiceImpl

4. 配置 beans.xml 以及 properties

​ beans.xml(配置文件)

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns="http://www.springframework.org/schema/beans"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans-4.2.xsd 
    http://www.springframework.org/schema/context 
    http://www.springframework.org/schema/context/spring-context-4.2.xsd 
    http://www.springframework.org/schema/aop 
    http://www.springframework.org/schema/aop/spring-aop-4.2.xsd ">

    <!-- 主动扫包注解 -->
    <context:component-scan base-package="com.xxx"></context:component-scan>
    <!-- 找到日志 -->
    <context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>

    <!-- 根本的连贯门路明码等 -->
    <bean id="dateSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driverClass}"/>
        <property name="jdbcUrl" value="${jdbc.jdbcUrl}"/>
        <property name="user" value="${jdbc.user}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <!-- 将 jdbc 注册到容器中 -->
    <bean id="JdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dateSource"/>
    </bean>
    
</beans>

​ jdbc.properties(属性文件)

jdbc.driverClass = com.mysql.jdbc.Driver
jdbc.jdbcUrl = jdbc:mysql://localhost:3306/travel
jdbc.user = root
jdbc.password = root

​ 注意事项 ：门路、账号、明码请用本人的。

5. 实现 dao 层

5.1pojo

package com.xxx.pojo;

public class keyTable {

    private int id;
    private String name;
    private String dates;

    public keyTable() {}

    public keyTable(int id, String name, String dates) {
        this.id = id;
        this.name = name;
        this.dates = dates;
    }
    
    @Override
    public String toString() {
        return "keyTable{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", dates='" + dates + '\'' +
                '}';
    }

    // get set
    public int getId() {return id;}

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

    public String getName() {return name;}

    public void setName(String name) {this.name = name;}

    public String getDates() {return dates;}

    public void setDates(String dates) {this.dates = dates;}
}

package com.xxx.dao;

import com.xxx.pojo.keyTable;

import java.util.List;

// 接口 
public interface keyTableDao {
    /*
    减少
    根据 id 删除
    批改
    根据 id 查问
     */     
     int AddTable(keyTable keyTable);
     
     int DeleteByIdTable(int id);
     
     int UpdateTable(keyTable keyTable);
     
     List<keyTable> FindById(int id);

}
// 实现

package com.xxx.dao.impl;

import com.xxx.dao.keyTableDao;
import com.xxx.pojo.keyTable;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository("keyTableDao")
public class keyTableDaoImpl implements keyTableDao {@Resource(name = "JdbcTemplate")
    private JdbcTemplate template;


    @Override
    public int AddTable(keyTable keyTable) {String sql = "insert into key_table values(?,?,?)";
        int result = template.update(sql, keyTable.getId(), keyTable.getName(), keyTable.getDates());
        return result;
    }

    @Override
    public int DeleteByIdTable(int id) {
        String sql = "delete from key_table where id = ?";
        int result = template.update(sql,id);
        return result;
    }

    @Override
    public int UpdateTable(keyTable keyTable) {String name = keyTable.getName();
        int id = keyTable.getId();
        String sql =  "update key_table set name = ? where id = ?";
        int result = template.update(sql, name, id);
        return result;
    }

    @Override
    public List<keyTable> FindById(int id) {
        String sql = "select * from key_table where id = ?";
        List<keyTable> query = template.query(sql, new RowMapper<keyTable>() {
            @Override
            public keyTable mapRow(ResultSet rs, int rowNum) throws SQLException {keyTable k = new keyTable();
                k.setId(rs.getInt(1));
                k.setName(rs.getString(2));
                k.setDates(rs.getString(3));
                return k;
            }
        },id);

        return query;
    }
    
}

6. 实现 service 层

package com.xxx.service;

import com.xxx.pojo.keyTable;

import java.util.List;
// 接口 
public interface keyTableService {

     /*
    减少
    根据 id 删除
    批改
    根据 id 查问
     */

     int AddTable(keyTable keyTable);
     int DeleteByIdTable(int id);
     int UpdateTable(keyTable keyTable);
     List<keyTable> FindById(int id);

}


package com.xxx.service.impl;

import com.xxx.dao.keyTableDao;
import com.xxx.pojo.keyTable;
import com.xxx.service.keyTableService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
// 实现类
@Service("keyTableService") // spring 帮我创立的实现类
public class keyTableServiceImpl implements keyTableService{

    @Autowired // 注入 bean
    private keyTableDao keyTableDao;


    @Override
    public int AddTable(keyTable keyTable) {int result = keyTableDao.AddTable(keyTable);
        return result;
    }

    @Override
    public int DeleteByIdTable(int id) {int result = keyTableDao.DeleteByIdTable(id);
        return result;
    }

    @Override
    public int UpdateTable(keyTable keyTable) {int result = keyTableDao.UpdateTable(keyTable);
        return result;
    }

    @Override
    public List<keyTable> FindById(int id) {List<keyTable> list = keyTableDao.FindById(id);
        return list;
    }

}

7. 测试 jdcb 是否胜利

package com.test;

import com.xxx.pojo.keyTable;
import com.xxx.service.keyTableService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestKeyService {


    // 减少
    @Test
    public void AddTableTest(){ApplicationContext app = new ClassPathXmlApplicationContext("beans.xml");
        keyTableService keyTableService = (keyTableService) app.getBean("keyTableService");
        keyTable k = new keyTable();
        k.setId(3);
        k.setName("我是被减少的对象");
        k.setDates("2021-12-23");
        int result = keyTableService.AddTable(k);
        if (result > 0){System.out.println("执行胜利");
        } else {System.out.println("失败了");
        }
    }

    // 根据 id 删除
    @Test
    public void DeleteTableTest(){ApplicationContext app = new ClassPathXmlApplicationContext("beans.xml");
        keyTableService keyTableService = (keyTableService) app.getBean("keyTableService");
        int result = keyTableService.DeleteByIdTable(111);
        if (result > 0){System.out.println("执行胜利");
        } else {System.out.println("失败了");
        }
    }


    // 批改
    @Test
    public void UpdateTableTest(){ApplicationContext app = new ClassPathXmlApplicationContext("beans.xml");
        keyTableService keyTableService = (keyTableService) app.getBean("keyTableService");
        keyTable keyTable = new keyTable();
        keyTable.setName("批改了");
        keyTable.setId(17);
        int result = keyTableService.UpdateTable(keyTable);
        if (result > 0){System.out.println("执行胜利");
        } else {System.out.println("失败了");
        }
    }

    // 根据 id 查问
    @Test
    public void FindAddTableTest(){ApplicationContext app = new ClassPathXmlApplicationContext("beans.xml");
        keyTableService keyTableService = (keyTableService) app.getBean("keyTableService");
        List<keyTable> list = keyTableService.FindById(1);
        for (keyTable k:list) {System.out.println(k);
        }
    }
        
}

完！