一,先导个包,建立数据库
然后进行add path 建立联系
建立数据库
xml文件放进src文件
二,配置dbcp池
在spring 容器中定义,(xml)
<!--获取数据源-->
<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>
注意:在xml中 name=“driverClassName” 与driver不同
&与符号 xml文件写法
三,建立dao和service,pojo。
dao
public interface AccountDao {
//根据id查询
public Account selectById(int aid);
//查全部
public List<Account> selectAll();
//通过id,修改acount,返回值int指影响的行数
public int changeById(Account account);
//添加一条数据。
public int addAccount(Account account);
//通过id删除一个数据
public int deleteAccount(int aid);
}
service
public interface AccountService {
public Account selectById(int aid);
public List<Account> selectAll();
public int changeById(Account account);
public int addAccount(Account account);
public int deleteAccount(int aid);
}
pojo
不能建立有参的构造函数
public class Account implements Serializable {
private int aid;
private double abalance;
public void setAid(int aid) {
this.aid = aid;
}
public void setAbalance(double abalance) {
this.abalance = abalance;
}
public int getAid() {
return aid;
}
public double getAbalance() {
return abalance;
}
四,在spring容器中定义。
<!--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>
五,在daoImpl中设置 jdbcTemplate,并提供set方法
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
六,重写dao方法
1.通过id查询一个的方法。只有查询才会使用 RowMapper,
RowMapper是每行返回一条记录
@Override
public Account selectById(int aid) {
String sql="select * from account where 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 ("acount"));
return account;
}
};
Account account1= jdbcTemplate.queryForObject (sql, new Object[]{aid},rowMapper);
return account1;
}
2.查询全部。
@Override
public List<Account> selectAll() {
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 ("acount"));
return account;
}
};
List<Account> list= jdbcTemplate.query(sql,rowMapper);
return list;
}
3.通过id,修改信息
注意返回值为int类型,如果返回1说明修改成功,为0的情况,错误多为return 后面没有改写
@Override
public int changeById(Account account) {
String sql="update account set acount=? where aid=?";
//给占位符赋值
Object[] objects={account.getAbalance (),account.getAid ()};
int num= jdbcTemplate.update (sql, objects);
return num;
}
4.增添与删除信息
@Override
public int addAccount(Account account) {
String sql="insert into account (acount) values(?)";
Object[] objects={account.getAbalance ()};
return jdbcTemplate.update (sql,objects);
}
@Override
public int deleteAccount(int aid) {
String sql="delete from account where aid=?";
Object[] objects={aid};
return jdbcTemplate.update (sql,objects);
}
七,service层调用。
private AccountDao accountDao;
public void setAccountDao(AccountDao accountDao) {
this.accountDao = accountDao;
}
@Override
public Account selectById(int aid) {
return accountDao.selectById (aid);
}
@Override
public List<Account> selectAll() {
return accountDao.selectAll ();
}
@Override
public int changeById(Account account) {
return accountDao.changeById (account);
}
@Override
public int addAccount(Account account) {
return accountDao.addAccount (account);
}
@Override
public int deleteAccount(int aid) {
return accountDao.deleteAccount (aid);
}
八,创建测试类。
@Test
public void text(){
ApplicationContext app=new ClassPathXmlApplicationContext ("applicationContext.xml");
AccountService accountService= (AccountService) app.getBean ("accountService");
Account account=accountService.selectById (1);
System.out.println (account.getAid ()+"\t"+account.getAbalance ());
}
@Test
public void text2(){
ApplicationContext app=new ClassPathXmlApplicationContext ("applicationContext.xml");
AccountService accountService= (AccountService) app.getBean ("accountService");
List<Account> list = accountService.selectAll ();
for (Account a:list
) {
System.out.println (a.getAid ()+"\t"+a.getAbalance ());
}
}
@Test
public void text3(){
ApplicationContext app=new ClassPathXmlApplicationContext ("applicationContext.xml");
AccountService accountService= (AccountService) app.getBean ("accountService");
Account account=new Account ();
account.setAid (1);
account.setAbalance (1000.232);
int num= accountService.changeById (account);
System.out.println (num);
}
@Test
public void text4(){
ApplicationContext app=new ClassPathXmlApplicationContext ("applicationContext.xml");
AccountService accountService= (AccountService) app.getBean ("accountService");
Account account=new Account ();
account.setAbalance (250);
int num= accountService.addAccount (account);
System.out.println (num);
}
@Test
public void text5(){
ApplicationContext app=new ClassPathXmlApplicationContext ("applicationContext.xml");
AccountService accountService= (AccountService) app.getBean ("accountService");
Account account=new Account ();
int num= accountService.deleteAccount (3);
System.out.println (num);
}
这样就实现了数据库的增删改查。