import java.sql.CallableStatement;
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.CallableStatementCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
public
class
SpringJDBCDAO
{
private
class
PersonRowMapper implements RowMapper
{
public
Object mapRow(ResultSet rs,
int
index) throws SQLException
{
Person person
=
new
Person();
person.setId(
new
Integer(rs.getInt(
"
id
"
)));
person.setFirstName(rs.getString(
"
first_name
"
));
person.setLastName(rs.getString(
"
last_name
"
));
return
person;
}
}
private
JdbcTemplate jdbcTemplate;
public
void
processStoredProcedure()
{
CallableStatementCallback cb
=
new
CallableStatementCallback()
{
public
Object doInCallableStatement(CallableStatement cs)
throws SQLException
{
cs.execute();
return
null
;
}
}
;
jdbcTemplate.execute(
"
{ ARCHIVE_STUDENTS }
"
, cb);
}
public
List getAllPersons()
{
String sql
=
"
select id, first_name, last_name from person
"
;
return
jdbcTemplate.query(sql,
new
RowMapperResultReader(
new
PersonRowMapper()));
}
public
String getLastNameForId(Integer id)
{
String sql
=
"
select last_name from person where id = ?
"
;
return
(String) jdbcTemplate.queryForObject(sql,
new
Object[]
{ id }
,
String.
class
);
}
public
int
getNumberOfPersons()
{
return
jdbcTemplate.queryForInt(
"
select count(*) from person
"
);
}
public
Person getPerson1(final Integer id)
{
String sql
=
"
select id, first_name, last_name from person where id = ?
"
;
final Person person
=
new
Person();
final Object[]
params
=
new
Object[]
{ id }
;
jdbcTemplate.query(sql,
params
,
new
RowCallbackHandler()
{
public
void
processRow(ResultSet rs) throws SQLException
{
person.setId(
new
Integer(rs.getInt(
"
id
"
)));
person.setFirstName(rs.getString(
"
first_name
"
));
person.setFirstName(rs.getString(
"
last_name
"
));
}
}
);
return
person;
}
public
Person getPerson2(final Integer id)
{
String sql
=
"
select id, first_name, last_name from person where id = ?
"
;
final Person person
=
new
Person();
final Object[]
params
=
new
Object[]
{ id }
;
List list
=
jdbcTemplate.query(sql,
params
,
new
RowMapperResultReader(
new
PersonRowMapper()));
return
(Person) list.
get
(
0
);
}
public
int
insertPerson(Person person)
{
String sql
=
"
insert into person (id, firstName, lastName) values (?, ?, ?)
"
;
Object[]
params
=
new
Object[]
{ person.getId(), person.getFirstName(),
person.getFirstName() }
;
int
[] types
=
new
int
[]
{ Types.INTEGER, Types.VARCHAR, Types.VARCHAR }
;
JdbcTemplate jdbcTemplate
=
null
;
return
jdbcTemplate.update(sql,
params
, types);
}
public
int
[] updatePersons(final List persons)
{
String sql
=
"
insert into person (id, firstName, lastName) 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.setInt(
0
, person.getId().intValue());
ps.setString(
1
, person.getFirstName());
ps.setString(
2
, person.getLastName());
}
}
;
return
jdbcTemplate.batchUpdate(sql, setter);
}
}
============
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import javax.sql.DataSource;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.
object
.MappingSqlQuery;
import org.springframework.jdbc.
object
.SqlUpdate;
public
class
SpringJDBCDAOWithObject
{
class
InsertPerson extends SqlUpdate
{
public
InsertPerson(DataSource ds)
{
setDataSource(ds);
setSql(
"
insert into person (id, firstName, lastName) values (?, ?, ?)
"
);
declareParameter(
new
SqlParameter(Types.NUMERIC));
declareParameter(
new
SqlParameter(Types.VARCHAR));
declareParameter(
new
SqlParameter(Types.VARCHAR));
compile();
}
public
int
insert(Person person)
{
Object[]
params
=
new
Object[]
{ person.getId(),
person.getFirstName(), person.getLastName() }
;
return
update(
params
);
}
}
private
class
PersonByIdQuery extends MappingSqlQuery
{
public
PersonByIdQuery(DataSource ds)
{
super(ds,
"
select id, first_name, last_name from person
"
+
"
where id = ?
"
);
declareParameter(
new
SqlParameter(
"
id
"
, Types.INTEGER));
compile();
}
public
Object mapRow(ResultSet rs,
int
rowNumber) throws SQLException
{
Person person
=
new
Person();
person.setId((Integer) rs.getObject(
"
id
"
));
person.setFirstName(rs.getString(
"
first_name
"
));
person.setLastName(rs.getString(
"
last_name
"
));
return
person;
}
}
private
InsertPerson insertPerson;
private
PersonByIdQuery personByIdQuery;
public
Person getPerson(Integer id)
{
Object[]
params
=
new
Object[]
{ id }
;
return
(Person) personByIdQuery.execute(
params
).
get
(
0
);
}
public
int
insertPerson(Person person)
{
return
insertPerson.insert(person);
}
}
posted on 2006-05-01 23:59
船长 阅读(734)
评论(0) 编辑 收藏