spring jdbc 代码收藏

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);
    }
}

 

评论

1 楼 yi4521870 2008-10-16  

配置文件呢？？？？？？？？？