Spring中使用JDBC做的增删改查

Spring中使用JDBC做的增删改查

    有三种方式使用JDBC。

1、使用JdbcTemplate类，实现使用JDBC ，参数是用object数组传递的

 

业务类：

public class UserDaoImpl implements UserDao{
//	定义一个Jdbc
	private JdbcTemplate jt;
//	必须有set 方法，因为是通过set方法装配的
	public void setJt(JdbcTemplate jt) {
		this.jt = jt;
	}
	@Override
	public void delete(int id) {
		// TODO Auto-generated method stub
		String sql="delete from user where id="+id;
		int temp=this.jt.update(sql);
		if(temp>0){
			System.out.println("删除成功！");
		}else{
			System.out.println("删除失败！");
		}
	}

	@Override
	public void insert(User entity) {
		// TODO Auto-generated method stub
		String sql="insert into user(name,age,sex) values(?,?,?)";
		Object obj[]={entity.getName(),entity.getAge(),entity.getSex()};
		int temp=this.jt.update(sql,obj);
		if(temp>0){
			System.out.println("插入成功！");
		}else{
			System.out.println("插入失败！");
		}
	}

	

	@Override
	public List<User> selectAll() {
		String sql="select * from user";
		List list=this.jt.query(sql,new RowMapper(){

			@Override
			public Object mapRow(ResultSet rs, int row) throws SQLException {
				// TODO Auto-generated method stub
				User user=new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setAge(rs.getInt("age"));
				user.setSex(rs.getString("sex"));				
				return user;
			}
			
		});
		System.out.println(list.size());
		// TODO Auto-generated method stub
		return list;
	}

	@Override
	public User selectById(int id) {
		// TODO Auto-generated method stub
		String sql="select id,name,age,sex from user where id="+id;
		User user=(User)jt.queryForObject(sql, new RowMapper(){

			@Override
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				User user=new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setAge(rs.getInt("age"));
				user.setSex(rs.getString("sex"));				
				return user;
			}
			
		});

		return user;
	}

	@Override
	public void update(User entity) {
		// TODO Auto-generated method stub
		String sql="update user set name=?,age=?,sex=? where id=?";
		Object obj[]={entity.getName(),entity.getAge(),entity.getSex(),entity.getId()};
		int temp=jt.update(sql,obj);
		if(temp>0){
			System.out.println("更新成功！");
		}else{
			System.out.println("更新失败！");
		}
	}

}

  

Xml中的部分代码：

	<!-- 分散配置 -->
	<context:property-placeholder location="jdbc.properties" />
	<!--  c3p0的数据源，设置它是为了配置jbdcTemplate-->
	<bean id="comboPooledDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<!-- 驱动程序 -->
		<property name="driverClass">
			<value>${driverClass}</value>
		</property>
		<!-- 用户名 -->
		<property name="user">
			<value>${username}</value>
		</property>
		<!-- 密码 -->
		<property name="password">
			<value>${password}</value>
		</property>
		<!-- url -->
		<property name="jdbcUrl">
			<value>${url}</value>
		</property>
		<!-- 最大池数 -->
		<property name="maxPoolSize">
			<value>${c3p0.pool.max}</value>
		</property>
		<!-- 最小池数 -->
		<property name="minPoolSize">
			<value>${c3p0.pool.min}</value>
		</property>
		<!-- 初始化的池数 -->
		<property name="initialPoolSize">
			<value>${c3p0.pool.init}</value>
		</property>
	</bean>
	<!-- 配置jdbcTemplate的bean ,设置它是为了设置业务bean中的jdbcTemplate参数-->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<constructor-arg ref="comboPooledDataSource" />
	</bean>
	<!-- 设置业务bean -->
	<bean id="userDaoImpl" class="cn.csdn.dao.UserDaoImpl">
		<property name="jt" ref="jdbcTemplate" />
	</bean>

  

2、使用NamedParameterJdbcTemplate类，实现JDBC，参数是用Map集合传递的

业务类：

public class UserDaoImpl2 implements UserDao{
//	定义一个Jdbc
	private NamedParameterJdbcTemplate npjt;
//	必须有set 方法，因为NamedParameterJdbcTemplate类是用set方法加载的
	public void setNpjt(NamedParameterJdbcTemplate npjt){
		this.npjt=npjt;
	}
	@Override
	public void delete(int id) {
		// TODO Auto-generated method stub
		String sql="delete from user where id=:id";
		Map<String,Object> map=new HashMap<String,Object>();
		map.put("id", id);
		int temp=this.npjt.update(sql,map);
		if(temp>0){
			System.out.println("删除成功！");
		}else{
			System.out.println("删除失败！");
		}
	}

	@Override
	public void insert(User entity) {
		// TODO Auto-generated method stub
		String sql="insert into user(name,age,sex) values(:name,:age,:sex)";
		Map<String,Object> map=new HashMap<String,Object>();
		map.put("name", entity.getName());
		map.put("age", entity.getAge());
		map.put("sex", entity.getSex());
		int temp=npjt.update(sql, map);
		if(temp>0){
			System.out.println("插入成功！");
		}else{
			System.out.println("插入失败！");
		}
	}
//
//	
//
	@Override
	public List<User> selectAll() {
		String sql="select * from user";
//		List list=this.npjt.queryForList(sql, new HashMap());
		List list=npjt.query(sql, new HashMap(),new RowMapper(){

			@Override
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				User user=new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setAge(rs.getInt("age"));
				user.setSex(rs.getString("sex"));				
				return user;
			}
			
		});
		System.out.println(list.size());
		return list;
	}
	
	@Override
	public User selectById(int id) {
		// TODO Auto-generated method stub
		String sql="select id,name,age,sex from user where id=1";
		
		Map<String,Object> map=new HashMap<String,Object>();		
		map.put("id", id);
		User user=(User)npjt.queryForObject(sql, map, new RowMapper(){

			@Override
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				User user=new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setAge(rs.getInt("age"));
				user.setSex(rs.getString("sex"));				
				return user;
			}
			
		});
		return user;	
	}
//
	@Override
	public void update(User entity) {
		// TODO Auto-generated method stub
		String sql="update user set name=:name,age=:age,sex=:sex where id=:id";
		Map<String,Object> map=new HashMap<String,Object>();
		map.put("name", entity.getName());
		map.put("age", entity.getAge());
		map.put("sex", entity.getSex());
		map.put("id", entity.getId());
		int temp=npjt.update(sql, map);
		if(temp>0){
			System.out.println("更新成功！");
		}else{
			System.out.println("更新失败！");
		}
	}

}

  xml中的代码：

<!-- 分散配置 -->
	<context:property-placeholder location="jdbc.properties" />
	<!--  c3p0的数据源，设置它是为了配置jbdcTemplate-->
	<bean id="comboPooledDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<!-- 驱动程序 -->
		<property name="driverClass">
			<value>${driverClass}</value>
		</property>
		<!-- 用户名 -->
		<property name="user">
			<value>${username}</value>
		</property>
		<!-- 密码 -->
		<property name="password">
			<value>${password}</value>
		</property>
		<!-- url -->
		<property name="jdbcUrl">
			<value>${url}</value>
		</property>
		<!-- 最大池数 -->
		<property name="maxPoolSize">
			<value>${c3p0.pool.max}</value>
		</property>
		<!-- 最小池数 -->
		<property name="minPoolSize">
			<value>${c3p0.pool.min}</value>
		</property>
		<!-- 初始化的池数 -->
		<property name="initialPoolSize">
			<value>${c3p0.pool.init}</value>
		</property>
	</bean>
	
	
	
	<!-- 配置jdbcTemplate的bean ,设置它是为了设置业务bean中的jdbcTemplate参数-->
	<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
	<constructor-arg ref="comboPooledDataSource"/>
	</bean>
	<!-- 设置业务bean -->
	<bean id="userDaoImpl" class="cn.csdn.dao.UserDaoImpl2">
		<property name="npjt" ref="namedParameterJdbcTemplate" />
	</bean>

  

3、使用SimpleJdbcTemplate类，实现JDBC，参数是用可变参数传递的

业务类：

public class UserDaoImpl3 implements UserDao{
//	定义一个Jdbc
	private SimpleJdbcTemplate sjt;
//	必须有set 方法
	public void setSjt(SimpleJdbcTemplate sjt){
		this.sjt=sjt;
	}
	
	@Override
	public void delete(int id) {
		// TODO Auto-generated method stub
		String sql="delete from user where id=?";		
		int temp=this.sjt.update(sql, id);
		if(temp>0){
			System.out.println("删除成功！");
		}else{
			System.out.println("删除失败！");
		}
	}

	@Override
	public void insert(User entity) {
		// TODO Auto-generated method stub
		String sql="insert into user(name,age,sex) values(?,?,?)";

		int temp=sjt.update(sql,entity.getName(),entity.getAge(),entity.getSex());
		if(temp>0){
			System.out.println("插入成功！");
		}else{
			System.out.println("插入失败！");
		}
	}

	@Override
	public List<User> selectAll() {
		String sql="select * from user";
		List list=this.sjt.queryForList(sql);

		System.out.println(list.size());
		return list;
	}

	@Override
	public User selectById(int id) {
		// TODO Auto-generated method stub
		String sql="select id,name,age,sex from user where id=?";
		User user=(User) sjt.queryForObject(sql, new ParameterizedRowMapper() {

			@Override
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				User user=new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setAge(rs.getInt("age"));
				user.setSex(rs.getString("sex"));				
				return user;
			}
		}, id);
		return user;
	}

	@Override
	public void update(User entity) {
		// TODO Auto-generated method stub
		/**第一种方法*/
//		String sql="update user set name=?,age=?,sex=? where id=?";
		
//		int temp=sjt.update(sql, entity.getName(),entity.getAge(),entity.getSex(),entity.getId());
	/**第二种方法*/	
		String sql="update user set name=:name,age=:age,sex=:sex where id=:id";
		Map<String,Object> map=new HashMap<String,Object>();
		map.put("name", entity.getName());
		map.put("age", entity.getAge());
		map.put("sex", entity.getSex());
		map.put("id", entity.getId());
		int temp=sjt.update(sql, map);
		if(temp>0){
			System.out.println("更新成功！");
		}else{
			System.out.println("更新失败！");
		}
	}

}

  xml中的部分代码：

<!-- 分散配置 -->
	<context:property-placeholder location="jdbc.properties" />
	<!--  c3p0的数据源，设置它是为了配置jbdcTemplate-->
	<bean id="comboPooledDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<!-- 驱动程序 -->
		<property name="driverClass">
			<value>${driverClass}</value>
		</property>
		<!-- 用户名 -->
		<property name="user">
			<value>${username}</value>
		</property>
		<!-- 密码 -->
		<property name="password">
			<value>${password}</value>
		</property>
		<!-- url -->
		<property name="jdbcUrl">
			<value>${url}</value>
		</property>
		<!-- 最大池数 -->
		<property name="maxPoolSize">
			<value>${c3p0.pool.max}</value>
		</property>
		<!-- 最小池数 -->
		<property name="minPoolSize">
			<value>${c3p0.pool.min}</value>
		</property>
		<!-- 初始化的池数 -->
		<property name="initialPoolSize">
			<value>${c3p0.pool.init}</value>
		</property>
	</bean>
	
	
	
	<!-- 配置jdbcTemplate的bean ,设置它是为了设置业务bean中的jdbcTemplate参数-->
	<bean id="simpleJdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate">
	<constructor-arg ref="comboPooledDataSource"/>
	</bean>
	<!-- 设置业务bean -->
	<bean id="userDaoImpl" class="cn.csdn.dao.UserDaoImpl3">
		<property name="sjt" ref="simpleJdbcTemplate" />
	</bean>