Spring使用JdbcTemplate操作数据库

文章来源：http://www.blogjava.net/baoyaer/articles/154080.html

首先，假设如下SQL表中有数据username=test1,passwd=test1,address=test1

 

CREATE TABLE `login` (

  `username` varchar(10) default NULL,

  `passwd` varchar(10) default NULL,

  `address` varchar(10) default NULL

) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

 

配置文件：

<?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="dataSource" class="org.apache.commons.dbcp.BasicDataSource">

   <property name="driverClassName">

     <value>com.mysql.jdbc.Driver</value>

   </property>

   <property name="url">

     <value>jdbc:mysql://localhost:3306/javaee</value>

   </property>

   <property name="username">

     <value>root</value>

   </property>

   <property name="password">

     <value>1234</value>

   </property>

 </bean>

 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">

   <property name="dataSource">

     <ref local="dataSource"/>

   </property>

 </bean>

 

<bean id="personDAO" class="SpringJDBCSupport.ReadData.PersonDAO">

  <property name="jdbcTemplate">

    <ref local="jdbcTemplate"/>

  </property>

</bean> 

</beans>

 

 JavaBean:

package SpringJDBCSupport.ReadData; import com.mysql.jdbc.Driver; public class Person { private String name; private String password; private String address; public Person(){ } public Person(String name,String password,String address){ this.name=name; this.password=password; this.address=address; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String toString(){ return this.getName()+"-"+this.getPassword()+"-"+this.getAddress(); } } DAO： 其中getPersonByRowCallbackHandler方法根据username获得person对象 package SpringJDBCSupport.ReadData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.List; import org.springframework.jdbc.core.BatchPreparedStatementSetter; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowCallbackHandler; public class PersonDAO { private JdbcTemplate jdbcTemplate; public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } public int insertPersonUseUpdate(Person person){ String sql="insert into login values(?,?,?)"; Object[] params=new Object[]{ person.getName(), person.getPassword(), person.getAddress() }; return this.getJdbcTemplate().update(sql,params); } public int insertPersonUseExecute(Person person){ String sql="insert into login values(?,?,?)"; Object[] params=new Object[]{ person.getName(), person.getPassword(), person.getAddress() }; int[] types=new int[]{ Types.VARCHAR, Types.VARCHAR, Types.VARCHAR }; return this.getJdbcTemplate().update(sql,params,types); } public int[] updatePersonUseBatchUpdate( final List persons){ String sql="insert into login values(?,?,?)"; BatchPreparedStatementSetter setter=null; setter=new BatchPreparedStatementSetter(){ public int getBatchSize(){ return persons.size(); } public void setValues(PreparedStatement ps,int index) throws SQLException{ Person person=(Person)persons.get(index); ps.setString(1,person.getName()); ps.setString(2,person.getPassword()); ps.setString(3,person.getAddress()); } }; return this.getJdbcTemplate().batchUpdate(sql,setter); } public Person getPersonByRowCallbackHandler(String username){ String sql="select * from login where username=?"; final Person person=new Person(); final Object params[]=new Object[]{username}; this.getJdbcTemplate().query(sql,params,new RowCallbackHandler(){ public void processRow(ResultSet rs)throws SQLException{ person.setName(rs.getString("username")); person.setPassword(rs.getString("passwd")); person.setAddress(rs.getString("address")); } }); return person; } } 测试代码： package SpringJDBCSupport.ReadData; import java.io.File; import java.util.ArrayList; import java.util.List; import org.springframework.beans.factory.BeanFactory; import org.springframework.beans.factory.xml.XmlBeanFactory; import org.springframework.core.io.FileSystemResource; public class TestJDBCTemplate { public static String filePath=""; public static BeanFactory factory=null; public static void main(String[] args) { filePath=System.getProperty("user.dir")+File.separator+"SpringJDBCSupport"+File.separator+"ReadData"+File.separator+"hello.xml"; factory=new XmlBeanFactory(new FileSystemResource(filePath)); PersonDAO personDAO=(PersonDAO)factory.getBean("personDAO"); /* * 准备数据 */ Person p1=new Person("test1","test1","test1"); Person p2=new Person("test2","test2","test2"); Person p3=new Person("test3","test3","test3"); Person p4=new Person("test4","test4","test4"); Person p5=new Person("test5","test5","test5"); List persons=new ArrayList(); persons.add(p3); persons.add(p4); persons.add(p5); //使用jdbcTemplate.update方式 // personDAO.insertPersonUseUpdate(p1); //使用jdbcTemplate.execute方式 // personDAO.insertPersonUseExecute(p2); // //使用jdbcTemplate批处理方式 // personDAO.updatePersonUseBatchUpdate(persons); //使用RowCallbackHandler执行一次查询,并打印person信息 System.out.println(personDAO.getPersonByRowCallbackHandler("test1")); } }  

运行结果：

test1-test1-test1