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
船長 閱讀(728)
評論(0) 編輯 收藏