JdbcTemplate实现增删改查操作

你的名字 2023-05-29 03:09 10阅读 0赞

### **JdbcTemplate介绍** ###

为了使 JDBC 更加易于使用,Spring 在 JDBCAPI 上定义了一个抽象层, 以此建立一个JDBC存取框架，Spring Boot Spring Data-JPA。

作为 SpringJDBC 框架的核心, JDBC 模板的设计目的是为不同类型的JDBC操作提供模板方法. 每个模板方法都能控制整个过程,并允许覆盖过程中的特定任务。

通过这种方式,可以在尽可能保留灵活性的情况下,将 [数据库][Link 1]存取的工作量降到最低。

### **JdbcTemplate方法介绍** ###

JdbcTemplate主要提供以下五类方法:

1、execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；

Execute、executeQuery、executeUpdate

2、update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句 SQL SERVCER(GO SQL语句 GO) ；

3、query方法及queryForXXX方法：用于执行查询相关语句；

4、call方法：用于执行存储过程、函数相关语句。

### **JdbcTemplate实现增删改查** ###

#### **JdbcTemplate添加数据** ####

##### 1. **使用配置实现** #####

###### 1.1 **创建实体类** ######

![www.wityx.com][] ![www.wityx.com][www.wityx.com 1]

public class Account {
        private  Integer accountid;
        private  String accountname;
        private Double balance;
    
        public Integer getAccountid() {
            return accountid;
        }
    
        public void setAccountid(Integer accountid) {
            this.accountid = accountid;
        }
    
        public String getAccountname() {
            return accountname;
        }
    
        public void setAccountname(String accountname) {
            this.accountname = accountname;
        }
    
        public Double getBalance() {
            return balance;
        }
    
        public void setBalance(Double balance) {
            this.balance = balance;
        }
    }

实体类

###### 1.2 **创建Dao层** ######

![www.wityx.com][] ![www.wityx.com][www.wityx.com 1]

//查询所有所有账户
    public List<Account> getAllAccounts();

查询方法

.3 **创建Dao层实现类并继承JdbcDaoSupport接口**

![www.wityx.com][] ![www.wityx.com][www.wityx.com 1]

public class AccountDaoImpl extends JdbcDaoSupport implements AccountDao {
    
    
        @Override
        public List<Account> getAllAccounts() {
            JdbcTemplate jdbcTemplate = getJdbcTemplate();
            String sql = "select * from  accounts";
    
            //RowMapper：接口  封装了记录的行映射关系
            List<Account> lists = jdbcTemplate.query(sql, new RowMapper<Account>() {
    
    
                @Override
                public Account mapRow(ResultSet resultSet, int i) throws SQLException {
                    //创建Account对象
                    Account account = new Account();
                    //从ResultSet中解数据保到Accounts对象中
                    account.setAccountid(resultSet.getInt("accountid"));
                    account.setAccountname(resultSet.getString("accountname"));
                    account.setBalance(resultSet.getDouble("balance"));
    
                    return account;
                }
            });
    
    return account;
    
    }

实现查询方法

###### **1.4创建Service层** ######

![www.wityx.com][] ![www.wityx.com][www.wityx.com 1]

//查询所有所有账户
    public List<Account> getAllAccounts();

查询方法

###### **1.5创建Service层实现类** ######

![www.wityx.com][] ![www.wityx.com][www.wityx.com 1]

AccountDao accountDao;
    @Override
    public List<Account> getAllAccounts() {
        List<Account> allAccounts = accountDao.getAllAccounts();
        return allAccounts;
    }

实现查询方法

###### **1.6 编写applicationContext.xml文件** ######

![www.wityx.com][] ![www.wityx.com][www.wityx.com 1]

<context:property-placeholder location="classpath:jdbc.properties"/>
    
    
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${jdbc.driver}"></property>
        <property name="url" value="${jdbc.url}"></property>
        <property name="username" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>
    
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    
    <bean id="accountDao" class="cn.spring.accounttest.dao.impl.AccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>
    
    <bean id="accountService" class="cn.spring.accounttest.service.impl.AccountServiceImpl">
        <property name="accountDao" ref="accountDao"></property>
    </bean>

applicationContext.xml

###### 1.7编写测试类 ######

![www.wityx.com][] ![www.wityx.com][www.wityx.com 1]

@Test
    public void getAllAccount(){
        ApplicationContext  context=new ClassPathXmlApplicationContext("applicationContext.xml");
        //从spring容器中获取Service对象
        AccountService accountService = (AccountService)context.getBean("accountService");
        List<Account> allAccounts = accountService.getAllAccounts();
        for (Account account:allAccounts) {
            System.out.println("账户名："+account.getAccountname()+",余额为："+account.getBalance());
        }
    }

查询测试类

##### 2. **使用注解方式实现** #####

###### 2.1 **创建实体类** ######

![www.wityx.com][] 实体类

###### 2.2 **创建Dao层** ######

![www.wityx.com][] 查询方法

###### 2.3 **创建Dao层实现类** ######

![www.wityx.com][] ![www.wityx.com][www.wityx.com 1]

@Repository
    public class AccountDaoImpl implements AccountDao {
    
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        Account account = new Account();
        @Override
        public List<Account> getAllAccounts() {
    
            String sql = "select * from  accounts";
    
            
            //自动映射
            RowMapper<Account> rowMapper = new BeanPropertyRowMapper<>(Account.class);
            List<Account> query = jdbcTemplate.query(sql, rowMapper);
            for (Account account : query) {
                System.out.println(account);
            }
            return query;
            }
        }

Dao实现类

###### **2.4创建Service层** ######

![www.wityx.com][] 查询方法

###### **2.5创建Service层实现类** ######

![www.wityx.com][] 实现查询方法

###### **2.6编写applicationContext.xml文件** ######

![www.wityx.com][] ![www.wityx.com][www.wityx.com 1]

<context:component-scan base-package="cn.spring"/>
    
    
    <context:property-placeholder location="classpath:jdbc.properties"/>
    
    
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${jdbc.driver}"></property>
        <property name="url" value="${jdbc.url}"></property>
        <property name="username" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>
    
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

applicationContext.xml

###### **2.7编写测试类** ######

![www.wityx.com][] 查询测试类

#### **JdbcTemplate实现增删改操作** ####

**使用注解方式实现，配置式同添加操作**

##### **1.创建Dao层** #####

![www.wityx.com][] ![www.wityx.com][www.wityx.com 1]

//删除账户
    public int delAccount(int id);
    
    //添加用户
    public int addAccount(Account account);
    
    //修改账户
    public int updaAccount(Account account);

增删改方法

##### **2.创建Dao曾实现类** #####

![www.wityx.com][] ![www.wityx.com][www.wityx.com 1]

@Override
     public int delAccount(int id) {
    
         String sql="delete from accounts where accountid=2";
         int count = jdbcTemplate.update(sql);
         return count;
     }
    
    @Override
     public int addAccount(Account account) {
         String sql="insert into Accounts(accountname,balance) values(?,?)";
         int count = jdbcTemplate.update(sql,account.getAccountname(),account.getBalance());
         return count;
     }
    
     @Override
     public int updaAccount(Account account) {
         String sql="update accounts set accountname=?,balance=? where accountid=?";
         int count = jdbcTemplate.update(sql, account.getAccountname(),account.getBalance(),account.getAccountid() );
         return count;
     }

增删改方法实现类

##### 3. **创建Service层** #####

![www.wityx.com][] 增删改方法

##### 4. **创建Service层实现类** #####

![www.wityx.com][] 增删改方法实现类

##### 5. **编写applicationContext.xml文件** #####

![www.wityx.com][] applicationContext.xml

##### 6. **编写测试类** #####

![www.wityx.com][] ![www.wityx.com][www.wityx.com 1]

@Test
    public void delAccount(){
        ApplicationContext  context=new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountService accountService =(AccountService) context.getBean("accountServiceImpl");
        int i = accountService.delAccount(2);
        if (i>0){
            System.out.println("删除成功");
        }
    }
    
    @Test
    public void  addAccount(){
        ApplicationContext  context=new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountService accountServiceImpl = (AccountService) context.getBean("accountServiceImpl");
       Account account=new Account();
       account.setAccountname("刘磊");
       account.setBalance(Double.valueOf(784));
        int count = accountServiceImpl.addAccount(account);
        if (count>0){
            System.out.println("添加成功");
        }
    }
    
    @Test
    public void updaAcccount(){
        ApplicationContext  context=new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountService accountServiceImpl = (AccountService) context.getBean("accountServiceImpl");
        Account account=new Account();
        account.setAccountid(10);
        account.setAccountname("刘磊");
        account.setBalance(Double.valueOf(784));
        int count = accountServiceImpl.updaAccount(account);
        if (count>0){
            System.out.println("修改成功");
        }
    }

增删改测试类

[Link 1]: http://www.wityx.com/database/
[www.wityx.com]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[www.wityx.com 1]: https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif