Spring04——Spring操作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);
        }
    }
        
}

完！