JdbcTemplate query查询方法
JdbcTemplate 中有很多的查询方法,整理几个比较常用的方法。
1、queryFor*方法很好用,简单强大。
2、query查询方法中带有回调接口有三个:ResultSetExtractor、RowCallbackHandler、RowMapper 使用ResultSetExtractor时,一般是直接new ResultSetExtractor(),然后在extractData(ResultSet rs)方法中实现自己的内容,最后返回Object结果。 使用RowCallbackHandler时,也是new RowCallbackHandler(),然后在processRow(ResultSet rs)方法中实现自己代码,并且将内容保存在上下文变量中,因为此方法没有返回类型(void) 使用RowMapper时,new RowMapper(), 然后在mapRow(ResultSet rs, int rowNum)实现自己代码,并返回Object结果。
3、ResultSetExtractor一次处理多个结果,而RowCallbackHandler、RowMapper只处理单行结果,具体内容可参照下面的代码,代码都是经过测试的,绝对好使。
- @SuppressWarnings("unchecked")
- public static void main(String[] args) {
- try {
- BasicDataSource datasource = new BasicDataSource();
- datasource.setDriverClassName("com.mysql.jdbc.Driver");
- datasource.setUrl("jdbc:mysql://127.0.0.1:3306/xzx");
- datasource.setUsername("root");
- datasource.setPassword("root");
- JdbcTemplate jt = new JdbcTemplate(datasource);
- // query1(jt);
- // query2(jt);
- // query3(jt);
- // query4(jt);
- // query5(jt);
- // query6(jt);
- // query7(jt);
- // query8(jt);
- query9(jt);
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- @SuppressWarnings("unchecked")
- private static void query9(JdbcTemplate jt) {
- List list1=new ArrayList();
- String sql = "select * from userinfo where id<?";
- list1=jt.execute(sql,new PreparedStatementCallback() {
- public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
- ps.setInt(1, 20);
- ResultSet rs=ps.executeQuery();
- Userinfo userinfo = new Userinfo();
- List list=new ArrayList();
- while (rs.next()) {
- userinfo.setId(rs.getInt("id"));
- userinfo.setUsername(rs.getString("username"));
- userinfo.setPassword(rs.getString("password"));
- userinfo.setCrateDate(rs.getDate("createDate"));
- list.add(userinfo);
- }
- return list;
- }
- });
- System.out.println(list1.size());
- }
- /**
- * queryForInt(String sql)
- *
- * queryFor**(String sql)
- *
- * @param jt
- */
- private static void query8(JdbcTemplate jt) {
- int i = jt.queryForInt("select count(*) from userinfo");
- System.out.println(i);
- }
- /**
- * query(String sql, PreparedStatementSetter pss, ResultSetExtractor<T> rse)
- *
- * query(String sql, PreparedStatementSetter pss, RowCallbackHandler rch)
- *
- * query(String sql, PreparedStatementSetter pss, RowMapper<T> rowMapper)
- *
- * @param jt
- */
- @SuppressWarnings("unchecked")
- private static void query7(JdbcTemplate jt) {
- List list1 = new ArrayList();
- String sql = "select * from userinfo where id<? and username=?";
- list1 = jt.query(sql, new PreparedStatementSetter() {
- public void setValues(PreparedStatement ps) throws SQLException {
- ps.setInt(1, 20);
- ps.setString(2, "user4");
- }
- }, new ResultSetExtractor() {
- public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
- List list = new ArrayList();
- while (rs.next()) {
- Userinfo u = new Userinfo();
- u.setId(rs.getInt("id"));
- u.setUsername(rs.getString("username"));
- u.setPassword(rs.getString("password"));
- u.setCrateDate(rs.getDate("createDate"));
- list.add(u);
- }
- return list;
- }
- });
- System.out.println(list1.size());
- }
- /**
- * query(String sql, Object[] args, ResultSetExtractor<T> rse) query(String
- * sql, Object[] args, int[] argTypes, ResultSetExtractor<T> rse)
- *
- * query(String sql, Object[] args, RowMapper<T> rowMapper) query(String
- * sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper)
- *
- * query(String sql, Object[] args, RowCallbackHandler rch) query(String
- * sql, Object[] args, int[] argTypes, RowCallbackHandler rch)
- *
- * argTypes:java.sql.Type中有定义
- *
- * @param jt
- */
- @SuppressWarnings("unchecked")
- private static void query6(JdbcTemplate jt) {
- List list1 = new ArrayList();
- String sql = "select * from userinfo where id<? and username=?";
- list1 = jt.query(sql, new Object[] { 15, "user4" }, new ResultSetExtractor() {
- public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
- List list = new ArrayList();
- while (rs.next()) {
- Userinfo u = new Userinfo();
- u.setId(rs.getInt("id"));
- u.setUsername(rs.getString("username"));
- u.setPassword(rs.getString("password"));
- u.setCrateDate(rs.getDate("createDate"));
- list.add(u);
- }
- return list;
- }
- });
- System.out.println(list1.size());
- }
- /**
- * query(final String sql, final ResultSetExtractor<T> rse) query(String
- * sql, RowCallbackHandler rch) query(String sql, RowMapper<T> rowMapper)
- *
- * @param jt
- */
- @SuppressWarnings("unchecked")
- private static void query5(JdbcTemplate jt) {
- List list1 = new ArrayList();
- String sql = "select * from userinfo where id<20";
- list1 = jt.query(sql, new ResultSetExtractor() {
- public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
- List list = new ArrayList();
- while (rs.next()) {
- Userinfo u = new Userinfo();
- u.setId(rs.getInt("id"));
- u.setUsername(rs.getString("username"));
- u.setPassword(rs.getString("password"));
- u.setCrateDate(rs.getDate("createDate"));
- list.add(u);
- }
- return list;
- }
- });
- System.out.println(((Userinfo) list1.get(4)).getUsername());
- }
- /**
- * query(PreparedStatementCreator psc, RowMapper<T> rowMapper) 多次调用RowMapper
- *
- * @param jt
- */
- static int i = 0;
- static int j = 0;
- static int k = 0;
- @SuppressWarnings("unchecked")
- public static void query4(JdbcTemplate jt) {
- List list1 = new ArrayList();
- list1 = jt.query(new PreparedStatementCreator() {
- public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
- System.out.println("i=" + (++i));
- String sql = "select * from userinfo where id<?";
- PreparedStatement ps = con.prepareStatement(sql);
- ps.setInt(1, 20);
- return ps;
- }
- }, new RowMapper() {
- public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
- System.out.println("j=" + (++j));
- Userinfo u = new Userinfo();
- u.setId(rs.getInt("id"));
- u.setUsername(rs.getString("username"));
- u.setPassword(rs.getString("password"));
- u.setCrateDate(rs.getDate("createDate"));
- return u;
- }
- });
- System.out.println(list1.size());
- }
- /**
- * query(PreparedStatementCreator psc, RowCallbackHandler rch)
- *
- * @param jt
- */
- @SuppressWarnings("unchecked")
- public static void query3(JdbcTemplate jt) {
- final List list = new ArrayList();
- jt.query(new PreparedStatementCreator() {
- public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
- System.out.println("i=" + (++i));
- String sql = "select * from userinfo where id<?";
- PreparedStatement ps = con.prepareStatement(sql);
- ps.setInt(1, 20);
- return ps;
- }
- }, new RowCallbackHandler() {
- public void processRow(ResultSet rs) throws SQLException {
- System.out.println("j=" + (++j));
- while (rs.next()) {
- Userinfo u = new Userinfo();
- u.setId(rs.getInt("id"));
- u.setUsername(rs.getString("username"));
- u.setPassword(rs.getString("password"));
- u.setCrateDate(rs.getDate("createDate"));
- list.add(u);
- }
- }
- });
- System.out.println(list.size());
- }
- /**
- * query(PreparedStatementCreator psc, ResultSetExtractor<T> rse)
- *
- * @param jt
- */
- @SuppressWarnings("unchecked")
- public static void query2(JdbcTemplate jt) {
- Userinfo u = new Userinfo();
- u = jt.query(new PreparedStatementCreator() {
- public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
- System.out.println("i=" + (++i));
- String sql = "select * from userinfo where id=?";
- PreparedStatement ps = con.prepareStatement(sql);
- ps.setInt(1, 20000);
- return ps;
- }
- }, new ResultSetExtractor() {
- public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
- System.out.println("j=" + (++j));
- Userinfo userinfo = new Userinfo();
- while (rs.next()) {
- userinfo.setId(rs.getInt("id"));
- userinfo.setUsername(rs.getString("username"));
- userinfo.setPassword(rs.getString("password"));
- userinfo.setCrateDate(rs.getDate("createDate"));
- }
- return userinfo;
- }
- });
- System.out.println(u.getCrateDate());
- }
- /**
- * query( PreparedStatementCreator psc, final PreparedStatementSetter pss,
- * final ResultSetExtractor<T> rse)
- *
- * @param jt
- */
- @SuppressWarnings("unchecked")
- public static void query1(JdbcTemplate jt) {
- Userinfo u = new Userinfo();
- u = jt.query(new PreparedStatementCreator() {
- public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
- System.out.println("i=" + (++i));
- String sql = "select * from userinfo where id=?";
- return con.prepareStatement(sql);
- }
- }, new PreparedStatementSetter() {
- public void setValues(PreparedStatement ps) throws SQLException {
- System.out.println("j=" + (++j));
- ps.setInt(1, 20000);
- }
- }, new ResultSetExtractor() {
- public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
- System.out.println("k=" + (++k));
- Userinfo userinfo = new Userinfo();
- while (rs.next()) {
- userinfo.setId(rs.getInt("id"));
- userinfo.setUsername(rs.getString("username"));
- userinfo.setPassword(rs.getString("password"));
- userinfo.setCrateDate(rs.getDate("createDate"));
- }
- return userinfo;
- }
- });
- System.out.println(u.getCrateDate());
- }