spring数据库的开发

概述

这篇文章主要介绍Spring框架使用JDBC进行数据库操作的知识，首先介绍如何在Spring中配置JDBC,然后了解Spring JDBC核心类JdbcTemplate中常见方法的使用。

代码示例

1 导入相关的jar包

2 SpringJDBC的配置

在src目录下，创建配置文件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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
 	http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">
    <!-- 1配置数据源 -->
    <bean id="dataSource" class=
            "org.springframework.jdbc.datasource.DriverManagerDataSource">
        <!--数据库驱动 -->
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <!--连接数据库的url -->
        <!--如果Mysql数据库的端口为3306则可以不填写端口号 ，这里数据库名为spring-->
        <property name="url" value="jdbc:mysql://localhost/spring" />
        <!--连接数据库的用户名 -->
        <property name="username" value="root" />
        <!--连接数据库的密码 -->
        <property name="password" value="ROOT" />
    </bean>
    <bean id="jdbcTemplate"
          class="org.springframework.jdbc.core.JdbcTemplate">
        <!-- 默认必须使用数据源 -->
        <property name="dataSource" ref="dataSource" />
    </bean>

    <!--定义id为accountDao的Bean-->
    <bean id="accountDao" class="chapter04.AccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate" />
    </bean>
</beans>

3 execute

使用execute(String sql)创建表，建立JdbcTemplateTest测试类。

import java.util.List;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
public class JdbcTemplateTest {

//使用Excute()创建表	
   public static void main(String[] args) {
   	// TODO Auto-generated method stub
//加载配置文件
   	ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
//获取JdbcTemplate实例
   	JdbcTemplate jdbcTemplate=(JdbcTemplate) applicationContext.getBean("jdbcTemplate");
   	jdbcTemplate.execute("create table account("
   	+"id int primary key auto_increment,"
   	+"username varchar(50),"
   	+"balance double)");
   	System.out.println("account表创建成功！");
   }

4 update query

update可以完成插入、更新、删除数据库的操作,query完成对数据库的查询操作。

创建Account类

public class Account {
	private Integer id;
	private String username;
	private Double balance;
	public Integer getId() {
		return id;
	}
	public void setId(Integer 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;
	}
	public String toString() {
		return "Account[id=" +id +",username="+username+",balance="+balance+"]";
	}
}

创建接口AccountDao

import java.util.List;

public interface AccountDao {

	public int addAccount(Account account);//添加
	public int updateAccount(Account account);//更新
	public int deleteAccount(int id);//删除
	public Account findAccountById(int id);//
	public List<Account> findAllAccount();
}

创建AccountDao的实现类AccountDaoimpl

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
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(username,balance) value(?,?)";
		Object[] obj=new Object[] {
				account.getUsername(),
				account.getBalance()
		};
		int num =jdbcTemplate.update(sql,obj);
		return num;	
	}
	
	public int updateAccount(Account account) {
		String sql="update account set username=?,balance=? where id=?";
		Object[] obj=new Object[] {
				account.getUsername(),
				account.getBalance(),
				account.getId()
		};
	int num =jdbcTemplate.update(sql,obj);
	return num;
	}
	
	public int deleteAccount(int id) {
		String sql="delete from account where id=?";
		int num=jdbcTemplate.update(sql,id);
		return num;
	}
	
	public Account findAccountById(int id) {
		String sql="select * from account where id=?";
		RowMapper<Account> rowMapper=new BeanPropertyRowMapper<Account>(Account.class);
		return this.jdbcTemplate.queryForObject(sql, rowMapper,id);
	}
	
	public List<Account> findAllAccount(){
		String sql="select * from account";
		RowMapper<Account> rowMapper=new BeanPropertyRowMapper<Account>(Account.class);
		return this.jdbcTemplate.query(sql, rowMapper);
	}
}

测试方法

在JdbcTemplate类中分别创建5个测试方法。

@Test
     public void addAccountTest(){
           ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
           AccountDao accountDao=(AccountDao) applicationContext.getBean("accountDao");
           Account account=new Account();
           account.setUsername("hua");
           account.setBalance(6000.00);
           int num=accountDao.addAccount(account);
           if(num>0) System.out.println("成功插入了"+num+"条信息");
           else System.out.println("插入操作执行失败");
       }

@Test
	 	public void updateAccountTest() {
		 ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
		 AccountDao accountDao=(AccountDao)applicationContext.getBean("accountDao");
		 Account account =new Account();
		 account.setId(2);
		 account.setUsername("qing");
		 account.setBalance(4000.0);
		 int num=accountDao.updateAccount(account);
		 if(num>0) System.out.println("成功更新了"+num+"条信息");
	        else System.out.println("更新操作执行失败");		 
	 }
	 

    @Test
    	public void deleteAccount() {
   	 ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
   	 AccountDao accountDao=(AccountDao) applicationContext.getBean("accountDao");
   	 int Id=3;
   	 int num=accountDao.deleteAccount(Id);
   	 if(num>0) System.out.println("成功删除了"+num+"条信息");
           else System.out.println("删除操作执行失败");		 
    }

@Test
	 	public void findAccountById() {
	 		ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
			 AccountDao accountDao=(AccountDao) applicationContext.getBean("accountDao");
			 Account account=accountDao.findAccountById(1);
			 System.out.println(account);
	 	}

@Test
	public void findAllAccountTest() {
		ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
		 AccountDao accountDao=(AccountDao) applicationContext.getBean("accountDao");
		 List<Account> account=accountDao.findAllAccount();
		 for (Account act : account) {
			 System.out.println(act);
		 }
	}
}