Spring使用jdbc模板增删改查

<?xml version="1.0" encoding="UTF-8"?>
<!--
  Licensed to the Apache Software Foundation (ASF) under one
  or more contributor license agreements.  See the NOTICE file
  distributed with this work for additional information
  regarding copyright ownership.  The ASF licenses this file
  to you under the Apache License, Version 2.0 (the
  "License"); you may not use this file except in compliance
  with the License.  You may obtain a copy of the License at

   http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing,
  software distributed under the License is distributed on an
  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  KIND, either express or implied.  See the License for the
  specific language governing permissions and limitations
  under the License.
-->
<!-- $Id: pom.xml 642118 2008-03-28 08:04:16Z reinhard $ -->
<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/maven-v4_0_0.xsd">

  <modelVersion>4.0.0</modelVersion>
  <packaging>war</packaging>

  <name>login</name>
  <groupId>org.example</groupId>
  <artifactId>login</artifactId>
  <version>1.0-SNAPSHOT</version>

  <build>
    <plugins>
      <plugin>
        <groupId>org.mortbay.jetty</groupId>
        <artifactId>maven-jetty-plugin</artifactId>
        <version>6.1.7</version>
        <configuration>
          <connectors>
            <connector implementation="org.mortbay.jetty.nio.SelectChannelConnector">
              <port>8888</port>
              <maxIdleTime>30000</maxIdleTime>
            </connector>
          </connectors>
          <webAppSourceDirectory>${project.build.directory}/${pom.artifactId}-${pom.version}</webAppSourceDirectory>
          <contextPath>/</contextPath>
        </configuration>
      </plugin>
    </plugins>
  </build>

  <dependencies>
    <!--dependency>
      <groupId>org.example</groupId>
      <artifactId>[the artifact id of the block to be mounted]</artifactId>
      <version>1.0-SNAPSHOT</version>
    </dependency-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>5.2.8.RELEASE</version>
    </dependency>
    <!--spring基础包spring-beans-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>5.2.8.RELEASE</version>
    </dependency>
    <!--spring基础包spring-context-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.2.8.RELEASE</version>
    </dependency>
    <!--spring基础包spring-expression-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-expression</artifactId>
      <version>5.2.8.RELEASE</version>
    </dependency>
    <!--spring依赖包-->
    <dependency>
      <groupId>commons-logging</groupId>
      <artifactId>commons-logging</artifactId>
      <version>1.2</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-aop</artifactId>
      <version>5.2.8.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.aspectj</groupId>
      <artifactId>aspectjrt</artifactId>
      <version>1.9.1</version>
    </dependency>
    <dependency>
      <groupId>org.aspectj</groupId>
      <artifactId>aspectjweaver</artifactId>
      <version>1.9.6</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.2.18.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>5.2.18.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.11</version>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>
  </dependencies>

</project>

 

 

 

 

 

applicationContext.xml

<?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:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       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">
<!--配置数据源-->
    <bean id="dataSource" class=
            "org.springframework.jdbc.datasource.DriverManagerDataSource">
        <!-- 数据库驱动 -->
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <!-- 连接数据库url -->
        <property name="url" value="jdbc:mysql://localhost/spring?useUnicode=true&amp;characterEncoding=utf-8&amp;serverTimezone=Asia/Shanghai" />
        <property name="username" value="root"/><!-- 连接数据库用户名 -->
        <property name="password" value="1234"/><!-- 连接数据库密码 -->
    </bean>
<!--    配置jdbc模板-->
    <bean id="JdbcTemplate"
          class="org.springframework.jdbc.core.JdbcTemplate">
        <!-- 默认必须使用数据源 -->
        <property name="dataSource" ref="dataSource"/>
    </bean>


    <!--    注册一个bean-->
<!--    <bean name="userDao" class="com.niuzhuang.dao.impl.UserDaoImpl"/>-->
<!--    <bean name="xmlAdvice" class="com.niuzhuang.demo.XmlAdice"/>-->
    <bean id="accountDao" class="com.niuzhuang.dao.impl.AccountDaoImpl">
        <property name="jdbcTemplate" ref="JdbcTemplate"/>
    </bean>
<!--    &lt;!&ndash;    配置spring AOP&ndash;&gt;-->
<!--    <aop:config>-->
<!--&lt;!&ndash;        指定切入点&ndash;&gt;-->
<!--        <aop:pointcut id="pointcut" expression="execution(* com.niuzhuang.dao.impl.UserDaoImpl.*(..))"/>-->
<!--&lt;!&ndash;        指定切面&ndash;&gt;-->
<!--        <aop:aspect ref="xmlAdvice">-->
<!--&lt;!&ndash;            前置通知&ndash;&gt;-->
<!--            <aop:before method="before" pointcut-ref="pointcut"/>-->
<!--            &lt;!&ndash;            返回通知&ndash;&gt;-->
<!--            <aop:after-returning method="afteReturnning" pointcut-ref="pointcut"/>-->
<!--            &lt;!&ndash;            环绕通知&ndash;&gt;-->
<!--            <aop:around method="around" pointcut-ref="pointcut"/>-->
<!--            &lt;!&ndash;            异常通知&ndash;&gt;-->
<!--            <aop:after-throwing method="afterException" pointcut-ref="pointcut"/>-->
<!--            &lt;!&ndash;            后置通知&ndash;&gt;-->
<!--            <aop:after method="after" pointcut-ref="pointcut"/>-->

<!--        </aop:aspect>-->
<!--    </aop:config>-->
    <!--使用context命名空间，开启注解处理器-->
<!--    <context:component-scan base-package="com.niuzhuang"/>-->
<!--    &lt;!&ndash;将类配置到容器当中，让容器创建实例&ndash;&gt;-->
<!--    &lt;!&ndash;setter注入&ndash;&gt;-->
<!--    <bean id="hello" class="com.niuzhuang.HelloSpring">-->
<!--        &lt;!&ndash;给属性赋值&ndash;&gt;-->
<!--        <property name="name" value="张三"/>-->
<!--    </bean>-->
<!--    &lt;!&ndash;构造方法注入&ndash;&gt;-->
<!--    <bean id="user" class="com.niuzhuang.User">-->
<!--        <constructor-arg name="id" value="1"/>-->
<!--        <constructor-arg name="username" value="zs"/>-->
<!--        <constructor-arg name="password" value="1234"/>-->
<!--    </bean>-->
<!--    &lt;!&ndash;注入dao&ndash;&gt;-->
<!--    <bean id="userDao" class="com.niuzhuang.dao.impl.UserDaoImpl"/>-->
<!--    &lt;!&ndash;注入service&ndash;&gt;-->
<!--    <bean id="userService" class="com.niuzhuang.service.impl.UserServiceImpl">-->
<!--        <property name="userDao" ref="userDao"/>-->
<!--    </bean>-->
</beans>

 

Account.java

package com.niuzhuang.entity;

public class Account {
    private int id;
    private String name;
    private double balance;

    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 double getBalance() {
        return balance;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", balance=" + balance +
                '}';
    }
}

UserDao.interface

package com.niuzhuang.dao;

import com.niuzhuang.entity.Account;

import java.util.List;
//这是接口
public interface AccountDao {
    public int addAccount(Account account);
    public int update(Account account);
    public int deleteAccount(Account account);
    public Account findByIdAndName(int id,String name);
    public List<Account> findAll();
}

AccountDaoImpl.java

package com.niuzhuang.dao.impl;

import com.niuzhuang.dao.AccountDao;
import com.niuzhuang.entity.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.util.List;

public class AccountDaoImpl implements AccountDao {
    private JdbcTemplate jdbcTemplate;

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

    public int addAccount(Account account) {
        String sql = "insert into account(name,balance) values(?,?)";
        Object[] args = {account.getName(),account.getBalance()};
        int num = jdbcTemplate.update(sql,args);
        return num;
    }

    public int update(Account account) {
        String sql = "update account set name=?,balance=? where id=?";
        int num = jdbcTemplate.update(sql,account.getName(),account.getBalance(),account.getId());
        return num;
    }

    public int deleteAccount(Account account) {
        String sql = "delete from account where id=?";
        return jdbcTemplate.update(sql,account.getId());
    }

    public Account findByIdAndName(int id, String name) {
        String sql = "select*from account where id=? and name=?";
        RowMapper rm = new BeanPropertyRowMapper<Account>(Account.class);//用于将查询的每一条结果转为对象
        Account account = (Account) jdbcTemplate.queryForObject(sql,rm,id,name);
        return account;
    }

    public List<Account> findAll() {
        String sql = "select*from account";
        RowMapper rm = new BeanPropertyRowMapper<Account>(Account.class);//用于将查询的每一条结果转为对象
        return jdbcTemplate.query(sql,rm);
    }
}

 

TestAddAccount.java

package com.niuzhuang.test;

import com.niuzhuang.dao.AccountDao;
import com.niuzhuang.entity.Account;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestAddAccount {
    public static void main(String[] args) {
        ApplicationContext applicationContext =  new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountDao dao = applicationContext.getBean("accountDao",AccountDao.class);
        Account account = new Account();
        account.setBalance(94840.0);
        account.setName("里斯");
        int num = dao.addAccount(account);
        System.out.println("添加了"+num+"条记录");
    }
}

TestFindByIdAndName.java

package com.niuzhuang.test;

import com.niuzhuang.dao.AccountDao;
import com.niuzhuang.entity.Account;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;

public class TestFindByIdAndName {
    public static void main(String[] args) {
        ApplicationContext applicationContext =  new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountDao dao = applicationContext.getBean("accountDao",AccountDao.class);
//        System.out.println(dao.findByIdAndName(1,"1111"));
        List<Account> list = dao.findAll();
        if (list.size()>0){
            for (Account a:list){
                System.out.println(a);
            }
        }else{
            System.out.println("查询不到数据");
        }
    }
}

TestUpdate.java

package com.niuzhuang.test;

import com.niuzhuang.dao.AccountDao;
import com.niuzhuang.entity.Account;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestUpdate {
    public static void main(String[] args) {
        ApplicationContext applicationContext =  new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountDao dao = applicationContext.getBean("accountDao",AccountDao.class);
        Account account = new Account();
        account.setBalance(11111);
        account.setName("1111");
        account.setId(4);
        dao.update(account);
    }
}

 idea导入文件不显示项目结构：(12条消息) IntelliJ IDEA - open 项目后只显示项目中的文件，不显示项目结构_小菠萝测试笔记的博客-CSDN博客