使用Spring对数据库进行增 删 改 查
account里面两个属性int aid和double abalance
提供getter和setter方法,代码如下
package com.offcn.entity;
import java.io.Serializable;
public class Account implements Serializable {
private int aid;
private double abalance;
public int getAid() {
return aid;
}
public void setAid(int aid) {
this.aid = aid;
}
public double getAbalance() {
return abalance;
}
public void setAbalance(double abalance) {
this.abalance = abalance;
}
}
dao方法和dao实现类代码如下
package com.offcn.dao;
import com.offcn.entity.Account;
import java.util.List;
public interface AccountDao {
//根据id查询
Account selectById(int aid);
//查询所有
List<Account> selectAllAccount();
//修改 返回值是影响的行数
int updateAccount(Account account);
//增加
int insertAccount(Account account);
//删除
int deleteAccount(int aid);
}
package com.offcn.dao.impl;
import com.offcn.dao.AccountDao;
import com.offcn.entity.Account;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
public class AccountDaoImpl implements AccountDao {
//spring提供操作数据库的类
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Override
public Account selectById(int aid) {
String sql="select * from account where aid=? ";
Object[] objects={aid};
/*返回的每一行数据都在这个类里,第一个参数是返回的结果集,第二个参数是当前的索引*/
RowMapper<Account> rowMapper = new RowMapper<Account>() {
@Override
public Account mapRow(ResultSet resultSet, int i) throws SQLException {
Account account = new Account();
account.setAid(resultSet.getInt("aid"));
account.setAbalance(resultSet.getDouble("abalance"));
return account;
}
};
Account account1 = jdbcTemplate.queryForObject(sql, objects, rowMapper);
return account1;
}
@Override
public List<Account> selectAllAccount() {
String sql ="select * from account";
RowMapper<Account> rowMapper = new RowMapper<Account>() {
@Override
public Account mapRow(ResultSet resultSet, int i) throws SQLException {
Account account = new Account();
account.setAid(resultSet.getInt("aid"));
account.setAbalance(resultSet.getDouble("abalance"));
return account;
}
};
List<Account> list = jdbcTemplate.query(sql, rowMapper);
return list;
}
@Override
public int updateAccount(Account account) {
String sql = "update account set abalance=? where aid=?";
//给占位符赋值
Object[] objects={account.getAbalance(),account.getAid()};
int num = jdbcTemplate.update(sql, objects);
return num;
}
@Override
public int insertAccount(Account account) {
String sql ="insert into account(abalance) values(?)";
Object[] objects ={account.getAbalance()};
int num = jdbcTemplate.update(sql, objects);
return num;
}
@Override
public int deleteAccount(int aid) {
String sql = "delete from account where aid=?";
Object[] objects ={aid};
int num = jdbcTemplate.update(sql, objects);
return num;
}
}
sevice和service实现类代码如下
package com.offcn.service;
import com.offcn.entity.Account;
import java.util.List;
public interface AccountService {
/**
* 根据id查询单个
* @param aid
* @return
*/
public Account selectById(int aid);
/**
* 查询所有
* @return
*/
public List<Account> selectAllAccount();
/**
* 修改
* @param account
* @return
*/
public int updateAccount(Account account);
/**
* 增加
* @param account
* @return
*/
public int insertAccount(Account account);
/**
* 删除
* @param aid
* @return
*/
int deleteAccount(int aid);
}
package com.offcn.service.impl;
import com.offcn.dao.AccountDao;
import com.offcn.entity.Account;
import com.offcn.service.AccountService;
import java.util.List;
public class AccountServiceImpl implements AccountService {
private AccountDao accountDao;
public void setAccountDao(AccountDao accountDao) {
this.accountDao = accountDao;
}
/**
* 根据id查询单条信息
* @param aid
* @return
*/
@Override
public Account selectById(int aid) {
return accountDao.selectById(aid);
}
/**
* 查询所有
* @return
*/
@Override
public List<Account> selectAllAccount() {
return accountDao.selectAllAccount();
}
/**
* 修改
* @param account
* @return
*/
@Override
public int updateAccount(Account account) {
return accountDao.updateAccount(account);
}
/**
* 增加
* @param account
* @return
*/
@Override
public int insertAccount(Account account) {
return accountDao.insertAccount(account);
}
/**
* 删除
* @param aid
* @return
*/
@Override
public int deleteAccount(int aid) {
return accountDao.deleteAccount(aid);
}
}
配置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-3.2.xsd">
<!--获取数据源-->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<!--拿到连接数据库的信息-->
<property name="url" value="jdbc:mysql://localhost:3306/account?useUnicode=true&characterEncoding=utf-8"></property>
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</bean>
<!--spring提供给我们操作数据库的类-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--配置dao层-->
<bean id="accountDao" class="com.offcn.dao.impl.AccountDaoImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
<!--配置service层-->
<bean id="accountService" class="com.offcn.service.impl.AccountServiceImpl">
<property name="accountDao" ref="accountDao"></property>
</bean>
</beans>
测试类代码如下
package com.offcn.test;
import com.offcn.entity.Account;
import com.offcn.service.AccountService;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.List;
public class Test {
@org.junit.Test
public void test(){
ApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
AccountService accountService = (AccountService)app.getBean("accountService");
//根据id查询单条信息
Account account = accountService.selectById(1);
System.out.println(account.getAid()+"\t"+account.getAbalance());
//查询所有
List<Account> list = accountService.selectAllAccount();
for (Account o : list) {
System.out.println(o.getAid()+"\t"+o.getAbalance());
}
//修改
Account account1 =new Account();
account.setAbalance(11111);
account.setAid(1);
int num = accountService.updateAccount(account);
System.out.println(num);
//增加
Account account2 =new Account();
account.setAbalance(22222);
int num1 = accountService.insertAccount(account);
System.out.println(num1);
//删除
int num2 = accountService.deleteAccount(2);
System.out.println(num2);
}
}
注意事项:
1.只有查询才用到RowMapper这个对象(返回的每一行得到记录)
Account account1 = jdbcTemplate.queryForObject(sql, objects, rowMapper);
return account1;
}
@Override
public List<Account> selectAllAccount() {
String sql ="select * from account";
RowMapper<Account> rowMapper = new RowMapper<Account>() {
@Override
public Account mapRow(ResultSet resultSet, int i) throws SQLException {
Account account = new Account();
account.setAid(resultSet.getInt("aid"));
account.setAbalance(resultSet.getDouble("abalance"));
return account;
}
};
List<Account> list = jdbcTemplate.query(sql, rowMapper);
return list;
}
2.spring 给我们提供一个类来操作数据库 JdbcTemplate
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
3.& 在xml配置都是 &
4.所有的操作都不要忘记了注入