【SSM_Spring】学习笔记05

最新推荐文章于 2023-02-15 16:57:18 发布

原创最新推荐文章于 2023-02-15 16:57:18 发布 · 281 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#ssm_spring

ssm_spring 专栏收录该内容

10 篇文章

订阅专栏

这篇博客详细记录了Spring中使用JdbcTemplate操作数据库的步骤，包括创建User对象、UserDao接口及其实现，以及测试类。在增删改查操作中遇到DataIntegrityViolationException错误，原因是未设置主键自增。接着介绍了如何通过spring容器管理jdbcTemplate，以及利用JdbcDaoSupport简化Dao，并展示了Spring读取配置文件的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

spring与jdbc

一、用Spring中的JdbcTemplate操作数据库

1、创建web动态项目，导入基本包、c3p0连接池包、数据库包、事务包（jdbc\tx）

2、创建User对象，属性与数据库一一对应

public class User {
	private Integer u_id;
	private String u_username;
	private String u_password;
}

3、创建UserDao接口，以及实现其接口，在接口中使用JdbcTemplete.

public interface UserDao {
	//根据id查找用户
	User selectUserById(Integer id);
}

public class UserDaoImpl implements UserDao {

	private  static ComboPooledDataSource dataSource;
	static {
		try {
			//配置c3p0数据库
			dataSource = new ComboPooledDataSource();
			dataSource.setDriverClass("com.mysql.jdbc.Driver");
			dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/ssm_spring");
			dataSource.setUser("root");
			dataSource.setPassword("root");
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
	}
	@Override
	public User selectUserById(Integer id) {
		JdbcTemplate jTemplate = new JdbcTemplate(dataSource);
		String sql = "select * from user where u_id=?";
		User user = jTemplate.queryForObject(sql, 
				new RowMapper<User>() {
		          @Override
		        public User mapRow(ResultSet rSet, int index) throws SQLException {
		        	User user = new User();
		        	user.setU_id(rSet.getInt("u_id"));
		        	user.setU_username(rSet.getString("u_username"));
		        	user.setU_password(rSet.getString("u_password"));
		        	return user;
		        }
		}, id);
		return user;
	}

}

4、创建测试类

public class test {
	
	@Test
	public void test1() {
		UserDao uDao = new UserDaoImpl();
		User user = uDao.selectUserById(0);
		System.out.println(user);
	}
}

5、总结：简单的spring中的jdbcTemplate测试例子就写好了，总体其实跟spring_servlet有点相似，所以理解起来也不算困难。

二、JdbcTemplate的增删改查操作

1、修改UserDao接口；

public interface UserDao {
	
	//增
	void saveUser(User user);
	//删
	void deleteUserById(Integer id);
	//改
	void updateUser(User user);
	//查
	//根据id查找用户
	User selectUserById(Integer id);
	//查询所有用户列表
	List<User> selectAllUser();
	//查询用户数量
	Integer selectUserCount();
}

2、实现UserDaoImpl

public class UserDaoImpl implements UserDao {

	private  static ComboPooledDataSource dataSource;
	JdbcTemplate jTemplate = new JdbcTemplate(dataSource);
	static {
		try {
			//配置c3p0数据库
			dataSource = new ComboPooledDataSource();
			dataSource.setDriverClass("com.mysql.jdbc.Driver");
			dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/ssm_spring");
			dataSource.setUser("root");
			dataSource.setPassword("root");
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
	}
	@Override
	public User selectUserById(Integer id) {
		
		String sql = "select * from user where u_id=?";
		User user = jTemplate.queryForObject(sql, 
				new RowMapper<User>() {
		          @Override
		        public User mapRow(ResultSet rSet, int index) throws SQLException {
		        	User user = new User();
		        	user.setU_id(rSet.getInt("u_id"));
		        	user.setU_username(rSet.getString("u_username"));
		        	user.setU_password(rSet.getString("u_password"));
		        	return user;
		        }
		}, id);
		return user;
	}
	
	//保存用户
	@Override
	public void saveUser(User user) {
		String sql = "insert into user values(null,?,?)";
		jTemplate.update(sql, user.getU_username(),user.getU_password());
	}
	
	//根据id删除用户
	@Override
	public void deleteUserById(Integer id) {
		String sql = "delete from user where u_id=?";
		jTemplate.update(sql, id);
	}
	
	//修改用户信息
	@Override
	public void updateUser(User user) {
		String sql = "update user set u_username=?,u_password=? where u_id=?";
		jTemplate.update(sql, user.getU_username(),user.getU_password(),user.getU_id());
	}
	
	//查询所有用户列表
	@Override
	public List<User> selectAllUser() {
		String sql = "select * from user";
		return jTemplate.query(sql, new RowMapper<User>() {
		          @Override
		        public User mapRow(ResultSet rSet, int index) throws SQLException {
		        	User user = new User();
		        	user.setU_id(rSet.getInt("u_id"));
		        	user.setU_username(rSet.getString("u_username"));
		        	user.setU_password(rSet.getString("u_password"));
		        	return user;
		        }
		});
	
	}
	
	//查询用户数量
	@Override
	public Integer selectUserCount() {
		String sql = "select count(*) from user";
		Integer count = jTemplate.queryForObject(sql, Integer.class);
		return count;
	}

}

3、测试

	@Test
	public void test2() {
		UserDao uDao = new UserDaoImpl();
		User user = new User();
		user.setU_username("dunka97");
		user.setU_password("abcd");
		uDao.saveUser(user);
	}
	@Test
	public void test3() {
		UserDao uDao = new UserDaoImpl();
		uDao.deleteUserById(1);
	}
	@Test
	public void test4() {
		UserDao uDao = new UserDaoImpl();
		User user = new User();
		user.setU_id(0);
		user.setU_username("dunka");
		user.setU_password("1212");
		uDao.updateUser(user );
	}
	@Test
	public void test5() {
		UserDao uDao = new UserDaoImpl();
		uDao.selectAllUser();
	}
	@Test
	public void test6() {
		UserDao uDao = new UserDaoImpl();
		Integer count = uDao.selectUserCount();
		System.out.println(count);
	}

4、注意，其中出现了一个错误

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into user values(null,?,?)Column 'u_id' cannot be null; nested exception is com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Column 'u_id' cannot be null
at

从其中意思可以知道，我把一个不能为空的字段保存了一个null值属性，经过检查发现是我的数据表没有设计好，u_id没有设置自增，所以在主键上保存一个空值就会报错，把其修正就OK了。

三、使用spring容器管理jdbcTemplate

1、创建spring配置文件applicationContext.xml

其中，要分清楚其中的依赖关系是dao - > jdbcTemplate -> dataSource

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">
	<!-- 依赖关系 dao - > jdbcTemplate -> dataSource -->
	<!-- 配置dataSource -->
	<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="com.mysql.jdbc.Driver"/>
		<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/ssm_spring"/>
		<property name="user" value="root"/>
		<property name="password" value="root"/>
	</bean>
	
	<!-- 配置jdbcTemplate -->
	<bean name="JdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"/>
	</bean>
	
	<!-- 配置dao -->
	<bean name="userDao" class="com.dunka.dao.UserDaoImpl">
		<property name="jTemplate" ref="JdbcTemplate"/>
	</bean>
</beans>

2、修改UserDaoImpl中的jdbcTemplate,改为set方法，以及去掉自己连接数据库的部分

public class UserDaoImpl implements UserDao {

	JdbcTemplate jTemplate;
	
	public void setjTemplate(JdbcTemplate jTemplate) {
		this.jTemplate = jTemplate;
	}

	@Override
	public User selectUserById(Integer id) {
		
		String sql = "select * from user where u_id=?";
		User user = jTemplate.queryForObject(sql, 
				new RowMapper<User>() {
		          @Override
		        public User mapRow(ResultSet rSet, int index) throws SQLException {
		        	User user = new User();
		        	user.setU_id(rSet.getInt("u_id"));
		        	user.setU_username(rSet.getString("u_username"));
		        	user.setU_password(rSet.getString("u_password"));
		        	return user;
		        }
		}, id);
		return user;
	}
	
	//保存用户
	@Override
	public void saveUser(User user) {
		String sql = "insert into user values(null,?,?)";
		jTemplate.update(sql, user.getU_username(),user.getU_password());
	}
	
	//根据id删除用户
	@Override
	public void deleteUserById(Integer id) {
		String sql = "delete from user where u_id=?";
		jTemplate.update(sql, id);
	}
	
	//修改用户信息
	@Override
	public void updateUser(User user) {
		String sql = "update user set u_username=?,u_password=? where u_id=?";
		jTemplate.update(sql, user.getU_username(),user.getU_password(),user.getU_id());
	}
	
	//查询所有用户列表
	@Override
	public List<User> selectAllUser() {
		String sql = "select * from user";
		return jTemplate.query(sql, new RowMapper<User>() {
		          @Override
		        public User mapRow(ResultSet rSet, int index) throws SQLException {
		        	User user = new User();
		        	user.setU_id(rSet.getInt("u_id"));
		        	user.setU_username(rSet.getString("u_username"));
		        	user.setU_password(rSet.getString("u_password"));
		        	return user;
		        }
		});
	
	}
	
	//查询用户数量
	@Override
	public Integer selectUserCount() {
		String sql = "select count(*) from user";
		Integer count = jTemplate.queryForObject(sql, Integer.class);
		return count;
	}

}

3、测试类，使用注解

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class test {
	
	@Resource(name="userDao")
	UserDao uDao;
	@Test
	public void test1() {
		User user = uDao.selectUserById(1);
		System.out.println(user);
	}
	@Test
	public void test2() {
		
		User user = new User();
		user.setU_username("dunka111");
		user.setU_password("abcd");
		uDao.saveUser(user);
	}
	@Test
	public void test3() {
		
		uDao.deleteUserById(1);
	}
	@Test
	public void test4() {
		
		User user = new User();
		user.setU_id(2);
		user.setU_username("dunka");
		user.setU_password("666");
		uDao.updateUser(user );
	}
	@Test
	public void test5() {
		List<User> list = uDao.selectAllUser();
		for (User user : list) {
			System.out.println(user);
		}
	}
	@Test
	public void test6() {
	
		Integer count = uDao.selectUserCount();
		System.out.println(count);
	}
}

四、使用JdbcDaoSupport修改Dao和Spring读取配置文件

1、使用JdbcDaoSupport修改Dao

（1）使UserDaoImpl继承JdbcDaoSupport,把jdbcTemplate修改为getJdbcTemplate(),使用父类封装好的方法

public class UserDaoImpl extends JdbcDaoSupport implements UserDao {


	@Override
	public User selectUserById(Integer id) {
		
		String sql = "select * from user where u_id=?";
		User user = getJdbcTemplate().queryForObject(sql, 
				new RowMapper<User>() {
		          @Override
		        public User mapRow(ResultSet rSet, int index) throws SQLException {
		        	User user = new User();
		        	user.setU_id(rSet.getInt("u_id"));
		        	user.setU_username(rSet.getString("u_username"));
		        	user.setU_password(rSet.getString("u_password"));
		        	return user;
		        }
		}, id);
		return user;
	}
	
}

（2）配置文件中依赖关系就变成dao->datasourece

<!-- 配置dao -->
	<bean name="userDao" class="com.dunka.dao.UserDaoImpl">
		<property name="dataSource" ref="dataSource"/>
	</bean>

2、spring读取配置文件

（1）以数据库连接信息作为例子：db.properties

jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql://localhost:3306/ssm_spring
jdbc.user=root
jdbc.password=123456

(2)给spring配置文件添加spring-context约束，使用标签<context:property-placeholder>读取配置文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">
	
	<context:property-placeholder location="db.properties"/>
	<!-- 配置dataSource -->
	<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="${jdbc.driverClass}"/>
		<property name="jdbcUrl" value="${jdbc.jdbcUrl}"/>
		<property name="user" value="${jdbc.user}"/>
		<property name="password" value="${jdbc.password}"/>
	</bean>
</beans>

3、测试