Spring JDBC的CRUD方法详解

来源：互联网发布：个体能开淘宝企业店铺编辑：程序博客网时间：2024/05/12 21:17

具体的CRUD方法及细节见代码：

package cn.tedu.test;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Timestamp;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Properties;import javax.sql.DataSource;import org.junit.Test;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;public class TestCase {public ApplicationContext getCtx(){String cfg = "spring-mvc.xml";ApplicationContext ctx = new ClassPathXmlApplicationContext(cfg);return ctx;}//@Test//测试：数据库连接配置文件读取是否正确。public void testJdbc(){ApplicationContext ctx=getCtx();Properties cfg = ctx.getBean("jdbc",Properties.class);System.out.println(cfg);}//@Testpublic void testDataSource(){ApplicationContext ctx=getCtx();DataSource ds = ctx.getBean("dataSource",DataSource.class);System.out.println(ds); }//@Testpublic void testJdbcTemplate(){ApplicationContext ctx=getCtx();JdbcTemplate tmpl=ctx.getBean("jdbcTemplate", JdbcTemplate.class);System.out.println(tmpl);String sql = "select 'Hello World!'" +" as s from dual";String str=tmpl.queryForObject(sql, String.class);System.out.println(str);}//@Test/** * 测试执行 Insert 语句 */public void testInsert(){String sql = "insert into " +"admin_info_lhh "+ "(admin_id, admin_code, " +" password, name,"+" telephone, email, enrolldate)"+" values (?,?,?,?,?,?,?)";ApplicationContext ctx = getCtx();JdbcTemplate jdbcTemplate=ctx.getBean("jdbcTemplate",JdbcTemplate.class);jdbcTemplate.update(sql, 100, 2012, "123","Jerry", "110", "jerry@tom.cn", new Date());}/** * 将当前结果行映射到一个对象 */RowMapper<Admin> rowmapper = new RowMapper<Admin>() {public Admin mapRow(ResultSet rs, int index)throws SQLException {int id=rs.getInt("admin_id");String code=rs.getString("admin_code");String name=rs.getString("name");String pwd = rs.getString("password");String tel=rs.getString("telephone");String email = rs.getString("email");Timestamp date =rs.getTimestamp("enrolldate");return new Admin(id, code, pwd,name, email, tel, date);}};//@Testpublic void testQuery(){String sql = "select * from " +" admin_info_lhh " +" where admin_id = ?";JdbcTemplate jdbcTemplate =getCtx().getBean("jdbcTemplate", JdbcTemplate.class);Admin admin = jdbcTemplate.queryForObject(sql,//SQL new Object[]{100}, //SQL参数rowmapper);//rs 的映射规则System.out.println(admin); }///@Testpublic void testQuery2(){String sql = "select * from " +" admin_info_lhh " +" where admin_id > ?";JdbcTemplate jdbcTemplate =getCtx().getBean("jdbcTemplate", JdbcTemplate.class);List<Admin> list = jdbcTemplate.query(sql,//SQL rowmapper, //rs 的映射规则new Object[]{0});//SQL参数System.out.println(list); }RowMapper<Map<String, Object>> toMap =   new RowMapper<Map<String,Object>>(){public Map<String, Object> mapRow(ResultSet rs, int index)throws SQLException {//将一行rs中的数据映射到Map对象中Map<String, Object> map=  new HashMap<String, Object>();map.put("id",rs.getInt("admin_id"));map.put("name",rs.getString("name"));return map;}};//@Testpublic void testQuery3(){String sql = "select admin_id, name" +" from admin_info_lhh ";JdbcTemplate jdbcTemplate =getCtx().getBean("jdbcTemplate",JdbcTemplate.class);List<Map<String, Object>> list=jdbcTemplate.query(sql, toMap);//jdbcTemplate.queryForList(sql);for (Map<String, Object> row : list){System.out.println(row); }//queryForList = query+toMap}@Testpublic void testDao(){AdminDao dao = getCtx().getBean("adminDao", AdminDao.class);Admin admin = dao.findById(100);System.out.println(admin); }}

总结：

1.queryForObject方法常用于获取一行数据，需传入rowmapper对象(映射关系)。

2.query方法可以获取全部数据，需传入rowmapper对象(映射关系)。

3.queryForList() 方法== query+toMap

4.如果只获取一行中某几列，可封装成Map对象

0 0