Spring JDBC的CRUD方法详解

Spring JDBC的CRUD方法详解

具体的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);
	}
	//@Test
	public void testDataSource(){
		ApplicationContext ctx=getCtx();
		DataSource ds = ctx.getBean(
			"dataSource",DataSource.class);
		System.out.println(ds); 
	}
	//@Test
	public 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);
		}
	};
	
	//@Test
	public 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); 
	}
	
//	/@Test
	public 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;
		}
	};
	
	//@Test
	public 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
	}
	
	@Test
	public 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对象