JdbcTemplate和spring 实战
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 default-autowire ="autodetect">
<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://127.0.0.1:3306/test?characterEncoding=UTF-8&characterSetResults=UTF-8</value>
</property>
<property name="username">
<value>root</value>
</property>
<property name="password">
<value></value>
</property>
<property name="maxActive">
<value>10</value>
</property>
<property name="maxIdle">
<value>2</value>
</property>
</bean>
<bean id="TransactionManager"
/>
</property>
</bean>
<bean id ="JdbcTemplate"
class ="org.springframework.jdbc.core.JdbcTemplate">
<property name ="dataSource">
<ref bean ="dataSource" />
</property>
</bean>
</beans>
pojo User.java
import org.apache.commons.lang.builder.EqualsBuilder;
import org.apache.commons.lang.builder.HashCodeBuilder;
import org.apache.commons.lang.builder.ToStringBuilder;
import org.apache.commons.lang.builder.ToStringStyle;
public class User ...{
Integer id;
String username;
String password;
public Integer getId() ...{
return id;
}
public void setId(Integer id) ...{
this.id = id;
}
public String getUsername() ...{
return username;
}
public void setUsername(String username) ...{
this.username = username;
}
public String getPassword() ...{
return password;
}
public void setPassword(String password) ...{
this.password = password;
}
public String toString() ...{
return ToStringBuilder.reflectionToString( this ,
ToStringStyle.MULTI_LINE_STYLE);
}
public boolean equals(Object o) ...{
return EqualsBuilder.reflectionEquals( this , o);
}
public int hashCode() ...{
return HashCodeBuilder.reflectionHashCode( this );
}
}
demo 代码 Test.java
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
public class Test ...{
static Log log = LogFactory.getLog(Test.class);
/** *//**
* @param args
*/
public static void main(String[] args) ...{
// TODO Auto-generated method stub
ClassPathResource res = new ClassPathResource("applicationContext.xml");
XmlBeanFactory factory = new XmlBeanFactory(res);
JdbcTemplate jdbcTemplate = (JdbcTemplate) factory
.getBean("JdbcTemplate");
//使用JdbcTemplate的execute()方法执行SQL语句
jdbcTemplate.execute("desc USER");
User user = new User();
user.setId(new Integer(1));
//带参数的更新
jdbcTemplate.update("delete from USER where id=?", new Object[] ...{ "1" });
//如果是UPDATE或INSERT,可以用update()方法。
jdbcTemplate.update("INSERT INTO USER VALUES(''" +
user.getId() + "'', ''"+
user.getUsername() + "'', ''" +
user.getPassword() +
"'')");
jdbcTemplate.update("UPDATE USER SET username = ? WHERE id = ?",
new Object[] ...{ "beauty9235", "1" });
//使用JdbcTemplate进行查询时，使用queryForXXX()等方法
int count = jdbcTemplate.queryForInt("SELECT COUNT(*) FROM USER");
log.debug("total records : " + count);
String name = (String) jdbcTemplate.queryForObject(
"SELECT username FROM USER WHERE id = ?", new Object[] ...{ "1" },
java.lang.String.class);
log.debug("username : " + name);
List rows = jdbcTemplate.queryForList("SELECT * FROM USER");
Iterator it = rows.iterator();
while (it.hasNext()) ...{
Map userMap = (Map) it.next();
log.debug(userMap.get("id") + " ");
log.debug(userMap.get("username") + " ");
log.debug(userMap.get("password") + " ");
}
/**//* JdbcTemplate将我们使用的JDBC的流程封装起来，包括了异常的捕捉、SQL的执行、查询结果的转换等等。spring大量使用Template Method模式来封装固定流程的动作，XXXTemplate等类别都是基于这种方式的实现。
除了大量使用Template Method来封装一些底层的操作细节，spring也大量使用callback方式类回调相关类别的方法以提供JDBC相关类别的功能，使传统的JDBC的使用者也能清楚了解spring所提供的相关封装类别方法的使用。
JDBC的PreparedStatement
*/
jdbcTemplate.update(
"INSERT INTO USER(username,password) VALUES( ?, ?)",
new PreparedStatementSetter() ...{
public void setValues(java.sql.PreparedStatement ps)
throws SQLException ...{
ps.setString(1, "test");
ps.setString(2, "123");
}
});
final User user01 = new User();
jdbcTemplate.query("SELECT * FROM USER WHERE id = ?",
new Object[] ...{ "1" }, new RowCallbackHandler() ...{
public void processRow(ResultSet rs) throws SQLException ...{
user01.setId(new Integer(rs.getInt("id")));
user01.setUsername(rs.getString("username"));
user01.setPassword(rs.getString("password"));
}
});
log.debug(user01);
class UserRowMapper implements RowMapper ...{
public Object mapRow(ResultSet rs, int index) throws SQLException ...{
User user02 = new User();
user02.setId(new Integer(rs.getInt("id")));
user02.setUsername(rs.getString("username"));
user02.setPassword(rs.getString("password"));
return user02;
}
}
}
}
小说明一下：
上次用的要加spring.jar,commons-dbcp-1.2.1.jar,log4j-1.2.8.jar,commons-pool-1.2.jar,commons-lang-2.4.jar等
