spring与jdbc集成

一，先导个包，建立数据库

然后进行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&amp;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);
    }

这样就实现了数据库的增删改查。