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