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.Driverjdbc.jdbcUrl = jdbc:mysql://localhost:3306/traveljdbc.user = rootjdbc.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);        }    }        }

完！