1）SpringJDBC下操作数据源： 为了学习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>
更多请见下节：http://www.mark-to-win.com/tutorial/frame_Spring_ExampleBasicDataSource.html
