spring下的jdbcTemplate对数据库的操作

spring下的jdbcTemplate对数据库的操作

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">
    <parent>
        <artifactId>Spring</artifactId>
        <groupId>com.msb</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>Demo2</artifactId>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <packaging>jar</packaging>
    <dependencies>
        <!--spring核心核心容器包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.3.5</version>
        </dependency>
        <!--junit测试文件包-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
            <scope>test</scope>
        </dependency>
        <!--spring切面包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>5.3.5</version>
        </dependency>
        <!--aop联盟包-->
        <dependency>
            <groupId>aopalliance</groupId>
            <artifactId>aopalliance</artifactId>
            <version>1.0</version>
        </dependency>
        <!--德鲁伊连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>
        <!--springJDBC包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.5</version>
        </dependency>
        <!--spring事务控制包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.3.5</version>
        </dependency>
        <!--spring orm 映射依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>5.3.5</version>
        </dependency>
        <!--Apache Commons日志包-->
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency><!--lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>
</project>

spring配置文件

<?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: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.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
">
    <!--spring注解扫描-->
    <context:component-scan base-package="com.msb" ></context:component-scan>
    <!--引入外部文件-->
    <context:property-placeholder location="classpath:jdbc.properties"/>
    <!--配置德鲁伊连接池-->
    <bean id="DataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="username" value="${jdbc_username}"></property>
        <property name="password" value="${jdbc_password}"></property>
        <property name="url" value="${jdbc_url}"></property>
        <property name="driverClassName" value="${jdbc_driver}"></property>
    </bean>
    <!--配置jdbcTemplate对象，并注入到德鲁伊数据池中-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="DataSource"></property>
    </bean>
    <!--开启自动生成代理对象-->
    <aop:aspectj-autoproxy/>
</beans>

jdbc配置文件

jdbc_username=root
jdbc_password=root
jdbc_driver=com.mysql.cj.jdbc.Driver
jdbc_url=jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true

service接口

package com.msb.service;

import com.msb.pojo.Emp;

import java.util.List;

public interface EmpService {
    int findEmpCount();
    Emp findByEmpno(int empno);
    List<Emp> findByDeptno(int deptno);
    int addEmp(Emp emp);
}

service接口实现类

package com.msb.service.impl;

import com.msb.dao.Empdao;
import com.msb.pojo.Emp;
import com.msb.service.EmpService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class EmpServiceImpl implements EmpService {
    @Autowired
    private Empdao empdao;//让spring自动调用实现empdao的方法和属性
    @Override
    public int findEmpCount() {
        //alt+回车；在dao接口中创建次方法findEmpdao
        return empdao.findEmpdao();
    }

    @Override
    public Emp findByEmpno(int empno) {
        return empdao.findByEmpdo(empno);
    }

    @Override
    public List<Emp> findByDeptno(int deptno) {
        return empdao.dindByDeptno(deptno);
    }

    @Override
    public int addEmp(Emp emp) {
        return empdao.addEmp(emp);
    }
}

实现类中自动创建的方法到dao层接口方法

package com.msb.dao;

import com.msb.pojo.Emp;

import java.util.List;

public interface Empdao {
    int findEmpdao();


    Emp findByEmpdo(int empno);

    List<Emp> dindByDeptno(int deptno);

    int addEmp(Emp emp);
}

dao层实现类

package com.msb.dao.impl;

import com.msb.dao.Empdao;
import com.msb.pojo.Emp;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class EmpDaoImpl implements Empdao {
    @Autowired
    private JdbcTemplate jdbcTemplate;//注入jdbcTemplate
    /**
     * 查询员工个数
     * @return 返回员工数量信息
     */
    @Override
    public int findEmpdao() {
        Integer integer = jdbcTemplate.queryForObject("select count(1) from emp", Integer.class);
        return integer;
    }

    @Override
    public Emp findByEmpdo(int empno) {
        BeanPropertyRowMapper<Emp> rowMapper = new BeanPropertyRowMapper<>(Emp.class);
        Emp emp = jdbcTemplate.queryForObject("select * from emp where empno=?", rowMapper, empno);
        return emp;
    }

    @Override
    public List<Emp> dindByDeptno(int deptno) {
        BeanPropertyRowMapper<Emp> rowMapper = new BeanPropertyRowMapper<>(Emp.class);
        List<Emp> empList = jdbcTemplate.query("select * from emp where deptno=?", rowMapper, deptno);
        return empList;
    }

    @Override
    public int addEmp(Emp emp) {
        Object obj[] ={emp.getEmpno(),emp.getEname(),emp.getJob(),emp.getMgr(),emp.getHiredate(),emp.getSal(),emp.getComm(),emp.getDeptno()};
        int update = jdbcTemplate.update("insert into emp values (?,?,?,?,?,?,?,?)", obj);
        return update;
    }
}

实体类，数据表的字段

package com.msb.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.stereotype.Component;
import java.io.Serializable;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Emp implements Serializable {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    //这里要使用util date
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Integer deptno;
}

测试调用方法

package com.msb;
import com.msb.pojo.Emp;
import com.msb.service.EmpService;
import lombok.Builder;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.Date;
import java.util.List;
public class test {
    @Test
    public void test1(){
        ApplicationContext context = new ClassPathXmlApplicationContext("Application.xml");
        EmpService empService = context.getBean(EmpService.class);
        //查询员工个数
        int empCount = empService.findEmpCount();
        System.out.println(empCount);
        //根据员工编号查询
        Emp byEmpno = empService.findByEmpno(7521);
        System.out.println(byEmpno);
        //添加一条数据
        Emp emp =new Emp(7899,"肖邦","Singer",4500,new Date(),4500.0,200.0,20);

        int addEmp = empService.addEmp(emp);
        if (addEmp==1){
            System.out.println("影响一条数据");
        }else {
            System.out.println("再检查检查");
        }
        //根据部门编号进行查询
        List<Emp> byDeptno = empService.findByDeptno(20);
        byDeptno.forEach(System.out::println);
    }
}