Spring JDBCTemplate的常用方法

最新推荐文章于 2024-03-31 15:20:42 发布

W厚积薄发

最新推荐文章于 2024-03-31 15:20:42 发布

阅读量1.1k

点赞数

分类专栏： Spring 文章标签： Spring JdbcTemplate execute() update() query()

本文链接：https://blog.csdn.net/Altitude_/article/details/100518234

版权

Spring 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

一、execute（）方法

execute()负责完成执行sql语句。

1.导入spring 的基本框架包（5个），spring-jdbc.jar、spring-tx.jar和连接数据库的驱动包

2.设置spring的配置文件，其中dataSource来自spring-jdbc.jar，JdbcTemplate来自spring-core.jar

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd">
        
        <!-- org\springframework\jdbc\datasource\DriverManagerDataSource.class -->
        <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        	<!-- oracle配置 -->
        	<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
        	<property name="url" value="jdbc:oracle:thin:@localhost:1521:idts"/>
        	<!-- mysql配置 -->
        	<!-- <property name="driverClassName" value="jdbc:mysql://127.0.0.1/student" />
        	<property name="url" value="com.mysql.jdbc.Driver"></property> -->
        	<property name="username" value="spring"/>
        	<property name="password" value="spring"/>
        </bean>
        <!-- org\springframework\jdbc\core\JdbcTemplate.class -->
        <bean id= "jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        	<property name="dataSource" ref="dataSource"/>
        </bean>
</beans>

3.书写代码

package com.wx.test;

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



public class MyTest {
	public static void main(String[] args){
		ApplicationContext application = new ClassPathXmlApplicationContext("applicationContext.xml");
		JdbcTemplate jdbc = (JdbcTemplate)application.getBean("jdbcTemplate");
		String str = "drop table A";
		jdbc.execute(str);
		System.out.println("successful");
	}
}

4.测试结果

二、update（）方法

update负责插入、删除、更新操作。

由于在下面项目中使用注解进行开发，因为使用spring框架是4.0以上，所以需要增加spring-aop包

1.书写代码

package com.wx.domain;

public class Account {
	
	private String username;
	private Double blance;
	
	public Account(){}
	public Account(String username,Double blance){
	
		this.username= username;
		this.blance = blance;
	}
	
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public Double getBlance() {
		return blance;
	}
	public void setBlance(Double blance) {
		this.blance = blance;
	}
	@Override
	public String toString() {
		return "Account [username=" + username + ", blance="
				+ blance + "]";
	}
	
	
}

package com.wx.dao;

import com.wx.domain.Account;

public interface AccountDao {
	//增加数据
	public int insertIntoAccount(Account account);
	//删除数据
	public int deleteFromAccount(String username);
	//修改数据
	public int updateFromAccount(Account account);
}

package com.wx.dao.impl;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.wx.dao.AccountDao;
import com.wx.domain.Account;

@Repository("accountDao")
public class AccountDaoImpl implements AccountDao {

	@Resource(name="jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public int insertIntoAccount(Account account) {
		// TODO Auto-generated method stub
		String sql = "insert into account values(sys_guid(),?,?)";
		Object[] objs = new Object[]{account.getUsername(),account.getBlance()};
		return jdbcTemplate.update(sql,objs);
	}

	@Override
	public int deleteFromAccount(String username) {
		// TODO Auto-generated method stub
		String sql = "delete from account where username = ?";
		return jdbcTemplate.update(sql,username);
	}

	@Override
	public int updateFromAccount(Account account) {
		// TODO Auto-generated method stub
		String sql = "update account set balance = ? where username = ?";
		Object[] objs = new Object[]{account.getBlance(),account.getUsername()};
		return jdbcTemplate.update(sql,objs);
	}

}

package com.wx.service;

import com.wx.domain.Account;

public interface AccountService {
	//增加数据
	public int insertIntoAccount(Account account);
	//删除数据
	public int deleteFromAccount(String username);
	//修改数据
	public int updateFromAccount(Account account);
}

package com.wx.service.impl;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.wx.dao.AccountDao;
import com.wx.domain.Account;
import com.wx.service.AccountService;


@Service("accountService")
public class AccountServiceImpl implements AccountService {

	@Resource(name = "accountDao")
	private AccountDao accountDao;
	
	@Override
	public int insertIntoAccount(Account account) {
		// TODO Auto-generated method stub
		return accountDao.insertIntoAccount(account);
	}

	@Override
	public int deleteFromAccount(String username) {
		// TODO Auto-generated method stub
		return accountDao.deleteFromAccount(username);
	}

	@Override
	public int updateFromAccount(Account account) {
		// TODO Auto-generated method stub
		return accountDao.updateFromAccount(account);
	}

}

2. 测试类

package com.wx.test;

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

import com.wx.domain.Account;
import com.wx.service.AccountService;



public class MyTest {
	public static void main(String[] args){
		ApplicationContext application = new ClassPathXmlApplicationContext("applicationContext.xml");
//		JdbcTemplate jdbc = (JdbcTemplate)application.getBean("jdbcTemplate");
//		String str = "drop table A";
//		jdbc.execute(str);
//		System.out.println("successful");
		//向数据库中添加数据
		Account account = new Account("wx",10000.0);
		AccountService accountService = (AccountService)application.getBean("accountService");
		
		/*int insertFlag = accountService.insertIntoAccount(account);
		if(insertFlag>0){
			System.out.println("successful");
		}else{
			System.out.println("fail");
		}*/
		//修改数据
		/*account.setBlance(15000.0);
		int updateFlag = accountService.updateFromAccount(account);
		if(updateFlag>0){
			System.out.println("successful");
		}else{
			System.out.println("fail");
		}*/
		//删除数据
		account.setBlance(15000.0);
		int deleteFlag = accountService.deleteFromAccount(account.getUsername());
		if(deleteFlag>0){
			System.out.println("successful");
		}else{
			System.out.println("fail");
		}
	}
}

三、Query（）方法

处理对数据库的各种操作，其中常见的几种Query操作：

1.书写代码

package com.wx.domain;

public class Account {
	private String id;
	private String username;
	private Double balance;
	
	
	public Account(){}
	public Account(String username,Double balance){
	
		this.username= username;
		this.balance = balance;
	}
	
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public Double getBalance() {
		return balance;
	}
	public void setBalance(Double balance) {
		this.balance = balance;
	}
	@Override
	public String toString() {
		return "Account [id=" + id + ", username=" + username + ", balance="
				+ balance + "]";
	}
	
}

package com.wx.dao;

import java.util.List;
import java.util.Map;

import com.wx.domain.Account;

public interface AccountDao {
	//增加数据
	public int insertIntoAccount(Account account);
	//删除数据
	public int deleteFromAccount(String username);
	//修改数据
	public int updateFromAccount(Account account);
	//根据姓名查询信息
	public Account findAccountByName(String username);
	//查询所有的信息
	public List<Map<String,Object>> findAllAccount();
	//另一种查询所有信息
	public List<Account> findAllAccountOther();
}

package com.wx.dao.impl;

import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.wx.dao.AccountDao;
import com.wx.domain.Account;

@Repository("accountDao")
public class AccountDaoImpl implements AccountDao {

	@Resource(name="jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public int insertIntoAccount(Account account) {
		// TODO Auto-generated method stub
		String sql = "insert into account values(sys_guid(),?,?)";
		Object[] objs = new Object[]{account.getUsername(),account.getBalance()};
		return jdbcTemplate.update(sql,objs);
	}

	@Override
	public int deleteFromAccount(String username) {
		// TODO Auto-generated method stub
		String sql = "delete from account where username = ?";
		return jdbcTemplate.update(sql,username);
	}

	@Override
	public int updateFromAccount(Account account) {
		// TODO Auto-generated method stub
		String sql = "update account set balance = ? where username = ?";
		Object[] objs = new Object[]{account.getBalance(),account.getUsername()};
		return jdbcTemplate.update(sql,objs);
	}

	@Override
	public Account findAccountByName(String username) {
		// TODO Auto-generated method stub
		String sql = "select * from account where username = ?";
		RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
		return jdbcTemplate.queryForObject(sql, rowMapper,username);
	}

	@Override
	public List<Map<String, Object>> findAllAccount() {
		// TODO Auto-generated method stub
		String sql = "select * from account";
		RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
		return jdbcTemplate.queryForList(sql,new Object[]{});
	}

	@Override
	public List<Account> findAllAccountOther() {
		// TODO Auto-generated method stub
		String sql = "select * from account";
		RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
		return jdbcTemplate.query(sql,rowMapper);
	}

}

package com.wx.service;

import java.util.List;
import java.util.Map;

import com.wx.domain.Account;

public interface AccountService {
	//增加数据
	public int insertIntoAccount(Account account);
	//删除数据
	public int deleteFromAccount(String username);
	//修改数据
	public int updateFromAccount(Account account);
	//根据姓名查询信息
	public Account findAccountByName(String username);
	//查询所有的信息
	public List<Map<String,Object>> findAllAccount();
	//另一种查询所有信息
	public List<Account> findAllAccountOther();
}

package com.wx.service.impl;

import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.wx.dao.AccountDao;
import com.wx.domain.Account;
import com.wx.service.AccountService;


@Service("accountService")
public class AccountServiceImpl implements AccountService {

	@Resource(name = "accountDao")
	private AccountDao accountDao;
	
	@Override
	public int insertIntoAccount(Account account) {
		// TODO Auto-generated method stub
		return accountDao.insertIntoAccount(account);
	}

	@Override
	public int deleteFromAccount(String username) {
		// TODO Auto-generated method stub
		return accountDao.deleteFromAccount(username);
	}

	@Override
	public int updateFromAccount(Account account) {
		// TODO Auto-generated method stub
		return accountDao.updateFromAccount(account);
	}

	@Override
	public Account findAccountByName(String username) {
		// TODO Auto-generated method stub
		return accountDao.findAccountByName(username);
	}

	@Override
	public List<Map<String, Object>> findAllAccount() {
		// TODO Auto-generated method stub
		return accountDao.findAllAccount();
	}

	@Override
	public List<Account> findAllAccountOther() {
		// TODO Auto-generated method stub
		return accountDao.findAllAccountOther();
	}

}

2. 测试类

package com.wx.test;

import java.util.Iterator;
import java.util.List;
import java.util.Map;

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

import com.wx.domain.Account;
import com.wx.service.AccountService;



public class MyTest {
	public static void main(String[] args){
		ApplicationContext application = new ClassPathXmlApplicationContext("applicationContext.xml");
//		JdbcTemplate jdbc = (JdbcTemplate)application.getBean("jdbcTemplate");
//		String str = "drop table A";
//		jdbc.execute(str);
//		System.out.println("successful");
		//向数据库中添加数据
		Account account = new Account("wx3",13000.0);
		AccountService accountService = (AccountService)application.getBean("accountService");
		
		/*int insertFlag = accountService.insertIntoAccount(account);
		if(insertFlag>0){
			System.out.println("successful");
		}else{
			System.out.println("fail");
		}*/
		//修改数据
		/*account.setBlance(15000.0);
		int updateFlag = accountService.updateFromAccount(account);
		if(updateFlag>0){
			System.out.println("successful");
		}else{
			System.out.println("fail");
		}*/
		//删除数据
		/*account.setBlance(15000.0);
		int deleteFlag = accountService.deleteFromAccount(account.getUsername());
		if(deleteFlag>0){
			System.out.println("successful");
		}else{
			System.out.println("fail");
		}*/
		
		Account accountQuery = accountService.findAccountByName(account.getUsername());
		System.out.println(accountQuery);
		
		List<Map<String,Object>> lists = accountService.findAllAccount();
		for(Map<String,Object> map:lists){
			Iterator<String> iterator =  map.keySet().iterator();
			while(iterator.hasNext()){
				String key = iterator.next();
				Object obj = map.get(key);
				System.out.print(key+":"+obj+",");
			}
			System.out.println();
		}
	}
}

3. 测试结果

W厚积薄发

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Spring JDBCTemplate的常用方法

一、execute（）方法execute()负责完成执行sql语句。1.导入spring 的基本框架包（5个），spring-jdbc.jar、spring-tx.jar和连接数据库的驱动包 2.设置spring的配置文件，其中dataSource来自spring-jdbc.jar，JdbcTe...
复制链接

扫一扫

专栏目录