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:

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

運行結果：

test1-test1-test1