首页 > 数据库 >Spring JdbcTemplate操作数据库

Spring JdbcTemplate操作数据库

时间：2023-12-22 19:23:43浏览次数：50  
标签：account Spring 数据库 Account accounts JdbcTemplate zjw import

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">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.zjw</groupId>
    <artifactId>day03_eesy_05annotationAOP</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>17</java.version>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <encoding>UTF-8</encoding>
        <spring.version>6.1.1</spring.version>
        <aspectjweaver.version>1.9.21</aspectjweaver.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>${aspectjweaver.version}</version>
        </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: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/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">

    <!--配置spring创建容器时要扫描的包-->
    <context:component-scan base-package="com.zjw"/>

    <!--配置spring开启注解AOP的支持-->
    <aop:aspectj-autoproxy/>

</beans>

第一种：注入JdbcTemplate对象操作数据库

package com.zjw.dao.impl;

import com.zjw.dao.IAccountDao;
import com.zjw.domain.Account;
import lombok.Setter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author zjw
 */
@Repository
public class AccountDaoImpl implements IAccountDao {

    @Setter
    private JdbcTemplate jdbcTemplate;

    @Override
    public Account findAccountById(Integer accountId) {
        List<Account> accounts = jdbcTemplate.query("SELECT * FROM account WHERE id=?", new BeanPropertyRowMapper<>(Account.class),accountId);
        return accounts.isEmpty()?null:accounts.get(0);
    }

    @Override
    public Account findAccountByName(String accountName) {
        List<Account> accounts = jdbcTemplate.query("SELECT * FROM account WHERE name=?", new BeanPropertyRowMapper<>(Account.class),accountName);
        if (accounts.isEmpty()){
            return null;
        }
        if (accounts.size()>1){
            throw new RuntimeException("结果集不唯一");
        }
        return accounts.get(0);
    }

    @Override
    public void updateAccount(Account account) {
        jdbcTemplate.update("UPDATE account SET name=?,money=? WHERE id=?",account.getName(),account.getMoney(),account.getId());
    }
}

第二种：通过注入DateSource对象创建JdbcTemplate

package com.zjw.dao.impl;

import lombok.Getter;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

/**
 * @author zjw
 */
public class JdbcDaoSupport {

    @Getter
    private JdbcTemplate jdbcTemplate ;

    public void setDataSource(DataSource dataSource){
        if (jdbcTemplate == null){
            jdbcTemplate = createJdbcTemplate(dataSource);
        }
    }

    private JdbcTemplate createJdbcTemplate(DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }

}

package com.zjw.dao.impl;

import com.zjw.dao.IAccountDao;
import com.zjw.domain.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;

import java.util.List;

/**
 * @author zjw
 */
public class AccountDaoImpl2 extends JdbcDaoSupport implements IAccountDao {

    @Override
    public Account findAccountById(Integer accountId) {
        List<Account> accounts = getJdbcTemplate().query("SELECT * FROM account WHERE id=?", new BeanPropertyRowMapper<Account>(Account.class),accountId);
        return accounts.isEmpty()?null:accounts.get(0);
    }

    @Override
    public Account findAccountByName(String accountName) {
        List<Account> accounts = getJdbcTemplate().query("SELECT * FROM account WHERE name=?", new BeanPropertyRowMapper<Account>(Account.class),accountName);
        if (accounts.isEmpty()){
            return null;
        }
        if (accounts.size()>1){
            throw new RuntimeException("结果集不唯一");
        }
        return accounts.get(0);
    }

    @Override
    public void updateAccount(Account account) {
        getJdbcTemplate().update("UPDATE account SET name=?,money=? WHERE id=?",account.getName(),account.getMoney(),account.getId());
    }
}

测试使用

直接在代码中创建JdbcTemplate对象

package com.zjw.jdbctemplate;


import com.zjw.domain.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.util.List;

/**
 * @author zjw
 */
public class JdbcTemplateDem01 {
    public static void main(String[] args) {
        //准备数据源
        DriverManagerDataSource dataSource = new DriverManagerDataSource();

        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/eesy_spring?useSSL=false&serverTimeZone=Asia/Shanghai");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        //1、创建JdbcTemplate对象
        JdbcTemplate jt = new JdbcTemplate(dataSource);
        //2、执行操作
        //插入
//        jt.execute("INSERT INTO account(name,money) values ('zbbb',1000)");
        //查询
        List<Account> accounts = jt.query("SELECT * FROM account ", new BeanPropertyRowMapper<Account>(Account.class));
        for (Account account : accounts) {
            System.out.println(account);
        }
    }
}

获取容器中的JdbcTemplate对象

package com.zjw.jdbctemplate;


import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * JdbcTemplate的最基本用法
 * @author zjw
 */
public class JdbcTemplateDem02 {
    public static void main(String[] args) {

        //1、获取容器
        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        //2、获取对象
        JdbcTemplate jdbcTemplate = ac.getBean("jdbcTemplate", JdbcTemplate.class);
        //3、执行操作
        jdbcTemplate.execute("INSERT INTO account(name,money) values ('zccc',1000)");
    }
}

JdbcTemplate的CRUD操作

package com.zjw.jdbctemplate;


import com.zjw.domain.Account;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * JdbcTemplate的CRUD操作
 *
 * @author zjw
 */
public class JdbcTemplateDem03 {
    public static void main(String[] args) {

        //1、获取容器
        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        //2、获取对象
        JdbcTemplate jdbcTemplate = ac.getBean("jdbcTemplate", JdbcTemplate.class);
        //3、执行操作
//        jdbcTemplate.execute("INSERT INTO account(name,money) values ('zccc',1000)");
        //保存
//        jdbcTemplate.update("INSERT INTO account(name,money)values(?,?)","zeee",333F);
        //更新
//        jdbcTemplate.update("UPDATE account SET name=?,money=? WHERE id =? ","zeee",9999F,9);
        //删除
//        jdbcTemplate.update("DELETE FROM account WHERE id =? ",8);
        //查询所有
//        List<Account> accounts = jdbcTemplate.query("SELECT * FROM account WHERE money>?", new AccountRowMapper(), 1000f);
        //Spring 提供的封装BeanPropertyRowMapper
//        List<Account> accounts = jdbcTemplate.query("SELECT * FROM account WHERE money > ?", new BeanPropertyRowMapper<>(Account.class), 1000f);
//        List<Account> accounts = jdbcTemplate.query("SELECT * FROM account", new BeanPropertyRowMapper<>(Account.class));
//        for (Account account : accounts){
//            System.out.println(account);
//        }

        //查询一个
//        List<Account> accounts = jdbcTemplate.query("SELECT * FROM account WHERE id=?", new BeanPropertyRowMapper<>(Account.class), 1);
//        System.out.println(accounts.isEmpty()?"没有内容":accounts.get(0));
        //查询返回一行一列（使用聚合函数，但不加group by子句）
//        Long count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM account WHERE money>?", Long.class, 1000f);
//        System.out.println(count);
    }
}
class AccountRowMapper implements RowMapper<Account>{

    @Override
    public Account mapRow(ResultSet resultSet, int i) throws SQLException {
        Account account = new Account();
        account.setId(resultSet.getInt("id"));
        account.setName(resultSet.getString("name"));
        account.setMoney(resultSet.getFloat("money"));
        return account;
    }
}

调用Dao层操作数据库

package com.zjw.jdbctemplate;


import com.zjw.dao.IAccountDao;
import com.zjw.domain.Account;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * JdbcTemplate的最基本用法
 * @author zjw
 */
public class JdbcTemplateDem04 {
    public static void main(String[] args) {
        //1、获取容器
        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        //2、获取对象
        IAccountDao accountDao = ac.getBean("accountDao", IAccountDao.class);
//        IAccountDao accountDao = ac.getBean("accountDao2", IAccountDao.class);
        Account account = accountDao.findAccountById(1);
        System.out.println(account);
//        account.setMoney(3000F);
//        accountDao.updateAccount(account);
    }
}

标签：account,Spring,数据库,Account,accounts,JdbcTemplate,zjw,import
From： https://www.cnblogs.com/zjw-blog/p/17922226.html

相关文章

  • Spring 基于注解的AOP面向切面编程
    Spring基于注解的AOP面向切面编程源码代码实现pom.xml<?xmlversion="1.0"encoding="UTF-8"?><projectxmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:sc......
  • 为什么cmd中输入数据库备份命令后还让继续输入
    为什么cmd中输入数据库备份命令后还让继续输入出现上述条件是需要进入mysql的bin目录下执行，在mysql中不可实现。我的mysql目录是在这这样就好了，在mysql登录状态下是不能够操作备份的。......
  • Spring AOP面向切面编程 通知类型
    SpringAOP面向切面编程通知类型通知分为：前置通知执行方法之前通知后置通知执行方法之后通知异常通知相当于cache里面的内容最终通知相当于finally环绕通知前四种通知集合源码代码实现pom.xml<?xmlversion="1.0"encoding="UTF-8"?><projec......
  • 成为阿里云云大使，推广阿里云数据库PolarDB产品，赢取猫超卡及返佣礼金！
    ......
  • 金蝶数据库服务器注册
    一、本地安装路径  K3Cloud\ManageSite\App_Data\DataBase\PatternDbFile 找到 SqlServerBCPatternDB.bak和  SqlServerMCPatternDB.bak 两个文件移动到数据库服务器目录下。二、使用ssms工具还原数据库 三、打开 K3Cloud\ManageSite\App_Data找到 Common.confi......
  • Spring Security without the WebSecurityConfigurerAdapter
     ENGINEERING | ELEFTHERIASTEIN-KOUSATHANA | FEBRUARY21,2022 | ...InSpringSecurity5.7.0-M2we deprecated the WebSecurityConfigurerAdapter,asweencourageuserstomovetowardsacomponent-basedsecurityconfiguration.Toassistwiththet......
  • 将excel数据导入到SQL server数据库的详细过程
    原文链接：https://www.xjx100.cn/news/415146.html?action=onClick1.将要导入的excel表格数据如下，第一行数据默认为数据库表中的字段，所以这个必须要有，否则无法映射导入。如下图（只截部分数据） 2.打开MicrosoftSQLServerManagementStudio17数据库管理软件，选中要导入的mydb数......
  • Spring三级缓存和循环依赖
    2023年12月22日17:02:18今天咪宝想买迪士尼娃娃，但是我买不起，还得加油。 SpringBean注入方式有至少3种，1.构造方法注入2.set方法注入（@Autowired）3.prototype多例bean注入 构造器注入和prototype注入的循环依赖会直接报错，set方式注入循环依赖不会报错，spring使用3级缓存来......
  • Spring AOP面向切面编程
    SpringAOP面向切面编程AOP：全称是AspectOrientedProgramming即：面向切面编程。在运行时，动态地将代码切入到类的指定方法、指定位置上的编程思想就是面向切面的编程参考文档https://docs.qq.com/pdf/DTXZtQ0FFb05paUJS源码代码测试pom.xml<?xmlversion="1.0"encod......
  • spring项目中自定义注解
    使用BeanPostProcessorBeanPostProcessor是Spring框架提供的一个接口，用于在Spring容器中对Bean进行后处理。自定义注解后，可以实现一个BeanPostProcessor实现类，在BeanPostProcessor的postProcessAfterInitialization()方法中，使用ClassPathScanningCandidateResol......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99