一、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. 测试结果