SpringJDBC下操作数据源dataSource

SpringJDBC下操作数据源:
马克- to-win:马克 java社区:防盗版实名手机尾号: 73203
为了学习TransactionManager,我们先学习一下SpringJDBC下操作数据源: org.apache.commons.dbcp.BasicDataSource。做以下实验时,要先导几个包。commons- collections-3.2.1.jar,commons-dbcp-1.4.jar,commons-pool-1.5.4.jar,mysql- connector-java-3.1.10-bin.jar,spring-jdbc-3.0.5.RELEASE.jar,spring-orm- 3.0.5.RELEASE.jar,spring-tx-3.0.5.RELEASE.jar,

例 2.2.1

先在web.xml中加入下面的语句:
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" >
         <property name="driverClassName"
                      value="com.mysql.jdbc.Driver"></property>
         <property name="url"
                      value="jdbc:mysql://localhost:3306/test"></property>
         <property name="username"
                      value="root"></property>
         <property name="password"
                      value="1234"></property>
    </bean>
    <bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg ref="dataSource" />
    </bean>



package service;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.annotation.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import com.NiutDAO;
import service.interfac.ILoginService;
import org.springframework.jdbc.core.RowCallbackHandler;

public class LoginServiceImpl implements ILoginService {
    @Resource
    private JdbcTemplate jt;
  

    public void login() {
        System.out.println("LoginServiceImpl");
        String sql = "SELECT COUNT(id) FROM register";
        Object[] params = new Object[] {};
        jt.query(sql, params, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                System.out.println(rs.getInt(1)) ;
            }
        });
    }  
}


运行结果:

LoginServiceImpl
10
after loginServic.login()


后记:processRow只能处理一行,想处理多行参考下段。
        final List objList = new ArrayList();
        jdbcTemplate.query(listSql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Map row = new HashMap();
                row.put(rs.getInt("id"), rs.getString("name"));
                objList.add(row);
            }
        });

例 2.2.1_1:(simpleJdbcTemplate的项目【比JdbcTemplate更好使用】):

TeacherPupil.java:

package com;

public class TeacherPupil  {
    public String toString() {
        return "pname:"+pname+"tid:"+tid+"\nname:"+name+"\nage:"+age;
    }
    private String pname;
    public String getPname() {
        return pname;
    }
    public void setPname(String pname) {
        this.pname = pname;
    }
    private Integer tid;
    private String name;
    private Integer age;

    public Integer getTid() {
        return tid;
    }
    public void setTid(Integer tid) {
        this.tid = tid;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
}

LoginServiceImpl.java:

package service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import service.interfac.ILoginService;

import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import com.Register;
import com.TeacherPupil;




public class LoginServiceImpl implements ILoginService {
    @Resource
    private JdbcTemplate jt1;
    public void setJt1(JdbcTemplate jt) {
        this.jt1 = jt;
    }
  
    @Resource
    private SimpleJdbcTemplate simpleJdbcTemplate;

    public void setSimpleJdbcTemplate(SimpleJdbcTemplate simpleJdbcTemplate) {
        this.simpleJdbcTemplate = simpleJdbcTemplate;
    }
  
    public void login() {
        System.out.println("LoginServiceImpl1111");
        String sql = "SELECT COUNT(id) FROM register";
        Object[] params = new Object[] {};
        jt1.query(sql, params, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                System.out.println(rs.getInt(1)) ;
            }
        });
        int res=this.simpleJdbcTemplate.queryForInt(sql);
        System.out.println("res is "+res) ;
//////////////////////////增      
        Register reg = new Register();
        reg.setId(61);
        reg.setName("abc");
        reg.setAge(28);
      
        String sqlInsert = "insert into register(id,name,age) values(:id,:name,:age)";
        simpleJdbcTemplate.update(sqlInsert.toString(), new BeanPropertySqlParameterSource(reg));
        System.out.println("insert ok ") ;
      

///////////////////改
        StringBuffer sqlgai = new StringBuffer();
        sqlgai.append("update register set name=? where id=?");
        simpleJdbcTemplate.update(sqlgai.toString(),"abc1", 61);
        System.out.println("改 ok ") ;
///////////////////改2
        StringBuffer sqlgai1 = new StringBuffer();
        sqlgai1.append("update register set name=? where id=?");
        simpleJdbcTemplate.update(sqlgai1.toString(),new Object[] { "abc2", 61 });
        System.out.println("改 ok1 ") ;      
///////////////////批量改
        StringBuffer sqlgaibatch = new StringBuffer();
        sqlgaibatch.append("update register set name=? where id=?");
      
        List<Object[]> parameters = new ArrayList<Object[]>();


        parameters.add(new Object[] { "qwe1",1 });
        parameters.add(new Object[] { "ert2",2 });
        simpleJdbcTemplate.batchUpdate(sqlgaibatch.toString(), parameters);
        System.out.println("批量改 ok ") ;
      
/////////////////////查询
        String sqldan = "select *  from register where id=?";
        Register regi=simpleJdbcTemplate.queryForObject(sqldan,new BeanPropertyRowMapper(Register.class),1);
        System.out.println("一个 regi "+ regi.getName()) ;
      
      
///////////////////查询多个      
        String sqlselec = "select * from register";
        List<Register> regis = simpleJdbcTemplate.query(sqlselec, new BeanPropertyRowMapper(Register.class));
        for(Object obj :regis){
            System.out.println(obj);
        }
        System.out.println("成功查询多个");
/////////////////////
/*一对多,专门做一个类TeacherPupil,接收.用底下的as 别名方法,就可以解决teacher和pupil表中都有同名的字段name的方法。这样就可以从不同的表中同时取值了。*/
        String sqlotm = "select pupil.name as pname, teacher.name,pupil.tid from pupil,teacher where tid=teacher.id and teacher.name='qixy';";
        List<TeacherPupil> teaPup = simpleJdbcTemplate.query(sqlotm, new BeanPropertyRowMapper(TeacherPupil.class));
        for(Object obj :teaPup){
            System.out.println(obj);
        }  
        System.out.println("成功一对多");
///////////////////查询多个带条件      
        String sqlselecc = "select * from register where id<:id";
        List<Register> regisc = simpleJdbcTemplate.query(sqlselecc, new BeanPropertyRowMapper(Register.class), new BeanPropertySqlParameterSource(reg));
        for(Object obj :regisc){
            System.out.println(obj);
        }  
//////////////////删
        String sqldel = " delete from register where id=?";
        simpleJdbcTemplate.update(sqldel, 61);

    }
  
    public static void main(String[] args)
    {
        ClassPathXmlApplicationContext cp=  new ClassPathXmlApplicationContext("applicationContext.xml");
        ILoginService ls=(ILoginService)cp.getBean("loginService");
        ls.login();
    }
}

applicationContext.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
    <bean id="loginService" class="service.LoginServiceImpl" >
        <property name="simpleJdbcTemplate">
            <ref bean="simpleJdbcTemplate" />
        </property>
    </bean>
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" >
         <property name="driverClassName"
                      value="com.mysql.jdbc.Driver"></property>
         <property name="url"
                      value="jdbc:mysql://localhost:3306/test"></property>
         <property name="username"
                      value="root"></property>
         <property name="password"
                      value="1234"></property>
    </bean>
    <bean id="simpleJdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate">
        <constructor-arg ref="dataSource" />
    </bean>
</beans>