public class JdbcCorporateEventDao implements CorporateEventDao {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
}
配置文件
<?xml version="1.0" encoding="UTF-8"?><beansxmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"><beanid="corporateEventDao"class="com.example.JdbcCorporateEventDao"><propertyname="dataSource"ref="dataSource"/></bean><!-- the DataSource (parameterized for configuration via a PropertyPlaceHolderConfigurer) --><beanid="dataSource"destroy-method="close"class="org.apache.commons.dbcp.BasicDataSource"><propertyname="driverClassName"value="${jdbc.driverClassName}"/><propertyname="url"value="${jdbc.url}"/><propertyname="username"value="${jdbc.username}"/><propertyname="password"value="${jdbc.password}"/></bean></beans>
//jdbcTemplate.update适合于insert 、update和delete操作; /**
* (sql,Object...args)
*/publicvoidsave(User user) {
jdbcTemplate.update("insert into user_test(name,password) values(?,?)",
new Object[]{user.getUsername(),user.getPassword()});
}
/**
* (sql,Object...res,argsType)
*/@Overridepublicvoidsave(User user) {
jdbcTemplate.update(
"insert into user_test(name,password) values(?,?)",
new Object[]{user.getUsername(),user.getPassword()},
newint[]{java.sql.Types.VARCHAR,java.sql.Types.VARCHAR}
);
}
/**
* (sql,ps)
*/publicvoidsave( User user) {
jdbcTemplate.update("insert into user_test(name,password) values(?,?)",
new PreparedStatementSetter(){
@OverridepublicvoidsetValues(PreparedStatement ps) throws SQLException {
ps.setString(1, user.getUsername());
ps.setString(2, user.getPassword());
}
});
}
/**
* (sql,Object...args)
*/publicvoidsave(User user) {
jdbcTemplate.update("insert into user_test(name,password) values(?,?)",
new Object[]{user.getUsername(),user.getPassword()});
}
//返回插入的主键 public List save(final User user) {
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
@Overridepublic PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement("insert into user_test(name,password) values(?,?)", new String[] {"id"});
ps.setString(1, user.getUsername());
ps.setString(2, user.getPassword());
return ps;
}
},keyHolder);
return keyHolder.getKeyList();
}
/**
* (sql,ps)
*/@Overridepublicvoidupdate(final User user) {
jdbcTemplate.update(
"update user_test set name=?,password=? where id = ?",
new PreparedStatementSetter(){
@OverridepublicvoidsetValues(PreparedStatement ps) throws SQLException {
ps.setString(1, user.getUsername());
ps.setString(2, user.getPassword());
ps.setInt(3, user.getId());
}
}
);
}
/**
* (sql,Object...res,argsType)
*/@Overridepublicvoiddelete(User user) {
Assert.isNull(user, "user is not null");
jdbcTemplate.update(
"delete from user_test where id = ?",
new Object[]{user.getId()},
newint[]{java.sql.Types.INTEGER});
}
@DeprecatedpublicintqueryForInt1(){
return jdbcTemplate.queryForInt("select count(0) from user_test");
}
publicintqueryForInt2(User user){
return jdbcTemplate.queryForInt("select count(0) from tb_test1 where username = ?" ,
new Object[]{user.getUsername()});
}
//最全的参数3个 publicintqueryForInt3(User user){
return jdbcTemplate.queryForInt("select count(0) from user_testwhere username = ?" ,
new Object[]{user.getUsername()},
newint[]{java.sql.Types.VARCHAR});
}
//可以返回是一个基本类型的值 @Deprecated//因为没有查询条件,所以用处不大 public String queryForObject1(User user) {
return (String) jdbcTemplate.queryForObject("select username from tb_test1 where id = 100",
String.class);
}
//可以返回值是一个对象 @Deprecated//因为没有查询条件,所以用处不大 public User queryForObject2(User user) {
return (User) jdbcTemplate.queryForObject("select * from tb_test1 where id = 100", User.class); //class是结果数据的java类型
}
@Deprecated//因为没有查询条件,所以用处不大 public User queryForObject3(User user) {
return (User) jdbcTemplate.queryForObject("select * from tb_test1 where id = 100",
new RowMapper(){
@Overridepublic Object mapRow(ResultSet rs, int rowNum)throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
return user;
}
}
);
}
public User queryForObject4(User user) {
return (User) jdbcTemplate.queryForObject("select * from tb_test1 where id = ?",
new Object[]{user.getId()},
User.class); //class是结果数据的java类型 实际上这里是做反射,将查询的结果和User进行对应复制
}
public User queryForObject5(User user) {
return (User) jdbcTemplate.queryForObject(
"select * from tb_test1 where id = ?",
new Object[]{user.getId()},
new RowMapper(){
@Overridepublic Object mapRow(ResultSet rs,int rowNum)throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
return user;
}
}); //class是结果数据的java类型
}
@Overridepublic User queryForObject(User user) {
//方法有返回值 return (User) jdbcTemplate.queryForObject("select * from tb_test1 where id = ?",
new Object[]{user.getId()},
newint[]{java.sql.Types.INTEGER},
new RowMapper() {
@Overridepublic Object mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
return user;
}
}
);
}
@SuppressWarnings("unchecked")
public List<User> queryForList1(User user) {
return (List<User>) jdbcTemplate.queryForList("select * from tb_test1 where username = ?",
new Object[]{user.getUsername()},
User.class);
}
@SuppressWarnings("unchecked")
public List<String> queryForList2(User user) {
return (List<String>) jdbcTemplate.queryForList("select username from tb_test1 where sex = ?",
new Object[]{user.getSex()},
String.class);
}
@SuppressWarnings("unchecked")
//最全的参数查询 public List<User> queryForList3(User user) {
return (List<User>) jdbcTemplate.queryForList("select * from tb_test1 where username = ?",
new Object[]{user.getUsername()},
newint[]{java.sql.Types.VARCHAR},
User.class);
}
//通过RowCallbackHandler对Select语句得到的每行记录进行解析,并为其创建一个User数据对象。实现了手动的OR映射。 public User queryUserById4(String id){
final User user = new User();
//该方法返回值为void this.jdbcTemplate.query("select * from tb_test1 where id = ?",
new Object[] { id },
new RowCallbackHandler() {
@OverridepublicvoidprocessRow(ResultSet rs) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
}
});
return user;
}
@SuppressWarnings("unchecked")
@Overridepublic List<User> list(User user) {
return jdbcTemplate.query("select * from tb_test1 where username like '%?%'",
new Object[]{user.getUsername()},
newint[]{java.sql.Types.VARCHAR},
new RowMapper(){
@Overridepublic Object mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
return user;
}
});
}
//批量操作 适合于增、删、改操作 publicint[] batchUpdate(final List users) {
int[] updateCounts = jdbcTemplate.batchUpdate(
"update tb_test1 set username = ?, password = ? where id = ?",
new BatchPreparedStatementSetter() {
@OverridepublicvoidsetValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, ((User)users.get(i)).getUsername());
ps.setString(2, ((User)users.get(i)).getPassword());
ps.setLong(3, ((User)users.get(i)).getId());
}
@OverridepublicintgetBatchSize() {
return users.size();
}
}
);
return updateCounts;
}