兴糖蹭饭笔记-SpringJdbc基础开发

JdbcTemplate简介

Spring在Jdbc对数据库操作的基础上对其深度封装。形成JdbcTemplate类，通过JdbcTemplate，我们可以将冗长的代码精简。又活用SpringAOP的特性，简化对数据库操作。

JdbcTemplate的增删改查

使用spring的注入功能，可以把dataSource注册到JdbcTemplate之中。

<!-- XML配置 -->
	<context:component-scan base-package="com.zzxtit.spring.jdbc"></context:component-scan>
    <!-- 通过context:property-placeholder标签引入资源配置文件自动注入注入配置dataSource -->
    <context:property-placeholder location="DBUtil.properties"/>
	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
		<property name="driverClassName" value="${mysql_driver}"></property>
		<property name="url" value="${mysql_url}"></property>
		<property name="username" value="${mysql_username}"></property>
		<property name="password" value="${mysql_password}"></property>
	</bean>
	<!-- 配置jdbcTemplate的Bean -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" value="#{dataSource}"></property>
	</bean>

Dao层实现类:

@Repository
public class UserDaoImpl implements UserDao{

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public void insertUserInfo(UserInfo ui) {
		String sql = "insert into user (id, password, user_name, salt) values(?, ?, ?, ?)"; 
		jdbcTemplate.update(sql,ui.getUserId(),PasswdUtil.encode(ui.getPassword(), ui.getSalt()),ui.getUserName(),ui.getSalt());
	}

	public UserInfo selectUserById(String userId) {
		String sql = "select * from user where id = ?";
		List<UserInfo> uiList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<UserInfo>(UserInfo.class), userId);
		if(uiList!=null && uiList.size()>0) {
		    //查询结果中userId为null，在此直接配置查询结果
 			uiList.get(0).setUserId(userId);
			return uiList.get(0);
		}else {
			return null;
		}
	}
	
	public int UpdateUserInfo(UserInfo ui) {
		String sql = "update user set user_name = ?, password = ?, salt = ? where id = ?";
		return jdbcTemplate.update(sql,ui.getUserName(),PasswdUtil.encode(ui.getPassword(), ui.getSalt()),ui.getSalt(),ui.getUserId());
	}

	public int deleteUserInfo(String userId) {
		String sql = "delete from user where id = ?";
		return jdbcTemplate.update(sql,userId);
	}

}

NamedParameterJdbcTemplate

当数据库中字段较多时，sql语句中的占位符数量较多，不易与字段对应，于是Spring除了提供JdbcTemplate核心类，还提供了基于JdbcTemplate实现的NamedParameterJdbcTemplate类用于支持命名参数绑定。

	@Autowired
	private NamedParameterJdbcTemplate namedJdbcTemplate;

	public void insertUserInfoByNamedJdbcTemplate(UserInfo ui) {
		String sql = "insert into user (id, password, user_name, salt) values(:userId, :password, :userName, :salt)";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		
		paramMap.put("userId", ui.getUserId());
		paramMap.put("password", PasswdUtil.encode(ui.getPassword(), ui.getSalt()));
		paramMap.put("userName", ui.getUserName());
		paramMap.put("salt", ui.getSalt());
		
		namedJdbcTemplate.update(sql, paramMap);
	}