springday05(jdbc、mvc)

最新推荐文章于 2025-04-11 20:26:35 发布

快乐咸鱼Y

最新推荐文章于 2025-04-11 20:26:35 发布

阅读量86

点赞数

分类专栏： spring ssm 文章标签： spring

本文链接：https://blog.youkuaiyun.com/yygyj/article/details/113521311

版权

spring 同时被 2 个专栏收录

9 篇文章

订阅专栏

ssm

8 篇文章

订阅专栏

本文介绍如何在Spring框架中整合JDBC进行数据库操作，并详细讲解了使用BeanPropertyRowMapper进行结果集映射的方法。此外，还介绍了两种实现事务管理的方式：一种是非注解方式，通过XML配置文件定义事务；另一种是注解方式，直接在业务逻辑层方法上添加注解。

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

1.回顾jdbc
2.spring整合jdbc
a.引入jar包
spring-jdbc-3.2.3、spring-tx-3.2.3、c3p0-0.9.1.2、mysql-connector-java-5.0.8
b.将数据源交给spring来管理
c.使用BeanPropertyRowMapper自动进行映射
BeanPropertyRowMapper内部可以使用指定的类进行反射（内省）来获知类内部信息的属性信息，自动映射到表的列，使用它时一定要注意，类的属性名要和对应表的列名必须对应，否则属性无法完成自动映射，BeanPropertyRowMapper底层通过反射来实现，相对于之前写的RowMapper效率比较低
代码实现：

package cn.tedu;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

//jdbc复习
public class Demo1 {
	public static void main(String[] args) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql:///springdb","root","root");
			ps=conn.prepareStatement("select * from user where id=?");
			ps.setInt(1, 2);
			rs=ps.executeQuery();
			while(rs.next()){
				String name=rs.getString("name");
				System.out.println(name);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					rs=null;
				}
			}
			if(ps!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					ps=null;
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					conn=null;
				}
			}
		}
	}
}

package cn.tedu;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;



//jdbc复习2
public class Demo2 {
	public static void main(String[] args) {
		ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");
		JdbcTemplate jdbcTemplate=(JdbcTemplate) context.getBean("jdbcTemplate");
		List<Map<String, Object>> list=jdbcTemplate.queryForList("select * from user where id=?", 1);
		System.out.println(list);
		
		
		/*ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");
		DataSource dataSource=(DataSource) context.getBean("dataSource");
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=dataSource.getConnection();
			ps=conn.prepareStatement("select * from user where id=?");
			ps.setInt(1, 1);
			rs=ps.executeQuery();
			while(rs.next()){
				String name=rs.getString("name");
				System.out.println(name);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					rs=null;
				}
			}
			if(ps!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					ps=null;
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					conn=null;
				}
			}
		}
*/
	}
}

package cn.tedu;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.rowset.SqlRowSet;


//jdbctemplate
public class Demo3 {
	private ApplicationContext context=null;
	@Before
	public void before(){
		context=new ClassPathXmlApplicationContext("applicationContext.xml");
	}
	@Test
	public void test01(){
		
		JdbcTemplate jdbcTemplate=(JdbcTemplate) context.getBean("jdbcTemplate");
		jdbcTemplate.update("insert into user values(?,?,?)", 4,"ddd",15);
		
	}
	@Test
	public void test02(){
		//ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");
		JdbcTemplate jdbcTemplate=(JdbcTemplate) context.getBean("jdbcTemplate");
		jdbcTemplate.update("update user set age=? where id=?",66,4);
		
	}
	@Test
	public void test03(){
		JdbcTemplate jdbcTemplate=(JdbcTemplate) context.getBean("jdbcTemplate");
		jdbcTemplate.update("delete from user where id=?",4);
		
	}
	@Test
	public void test04(){
		JdbcTemplate jdbcTemplate=(JdbcTemplate) context.getBean("jdbcTemplate");
		List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from user where id>=?", 2);
		System.out.println(list);
		
	}
	/**
	 * 查询一个map，仅限于结果是一条记录时使用
	 */
	@Test
	public void test05(){
		JdbcTemplate jdbcTemplate=(JdbcTemplate) context.getBean("jdbcTemplate");
		Map<String, Object> map = jdbcTemplate.queryForMap("select * from user where id=?", 1);
		System.out.println(map);
		
	}
	@Test
	public void test06(){
		JdbcTemplate jdbcTemplate=(JdbcTemplate) context.getBean("jdbcTemplate");
		SqlRowSet rs = jdbcTemplate.queryForRowSet("select * from user where id=?", 1);
		while(rs.next()){
			String name=rs.getString("name");
			System.out.println(name);
		}
		
	}
	/**
	 * 返回一个list
	 */
	@Test
	public void test07(){
		JdbcTemplate jdbcTemplate=(JdbcTemplate) context.getBean("jdbcTemplate");
		List<User> user=jdbcTemplate.query("select * from user where id=?",
				new RowMapper<User>() {
					@Override
					public User mapRow(ResultSet rs, int index)throws SQLException {
							User user=new User();
							user.setId(rs.getInt("id"));
							user.setName(rs.getString("name"));
							user.setAge(rs.getInt("age"));
						return user;
					}
			
				}, 2);
		System.out.println(user);
		
	}
	/**
	 * 返回一个bean
	 */
	@Test
	public void test08(){
		JdbcTemplate jdbcTemplate=(JdbcTemplate) context.getBean("jdbcTemplate");
		User user=jdbcTemplate.queryForObject("select * from user where id=?", new RowMapper<User>() {

			@Override
			public User mapRow(ResultSet rs, int index) throws SQLException {
				User user=new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setAge(rs.getInt("age"));
				return user;
			}
		}, 2);
		System.out.println(user);
	}
	/**
	 * 映射
	 */
	@Test
	public void test09(){
		JdbcTemplate jdbcTemplate=(JdbcTemplate) context.getBean("jdbcTemplate");
		List<User> list=jdbcTemplate.query("select * from user where id=?", new BeanPropertyRowMapper<User>(User.class), 2);
		System.out.println(list);
	}
}

package cn.tedu;

public class User{
	private int id;
	private String name;
	private int age;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
	}
	
}

spring整合jdbc项目
之前的案例中实现了自定义切面管理事务：
开发事务管理注释
开发事务管理切面
开发事务管理器
在事务管理中通过ThreadLocal保证每个线程各自使用各自的conn

spring提供了内置的事务管理机制，称之为声明式事务管理
spring提供的内置事务策略，只有在底层抛出的异常是运行时异常时，才会回滚，其它的异常不回滚，留给用户手动处理
也可以在配置中手动处理，哪些异常额外的回滚或者不回滚

增加事务
a.加入约束tx
b.配置事务管理器
c.配置事务切面
d.配置事务通知
e.配置关系图

注意：
add星可以匹配add开头的方法 REQUIRED代表开启事务 nosupported代表不开启事务
tx:method name=“add*” propagation=“REQUIRED”

可以利用注解实现事务（特别简便）
a.配置中开启注解方式配置事务
b.在方法上通过注解管理事务
c.注解也可以标注在接口上，也可以标注在实现类上，理论上应该标注在接口上，实现面向接口编程，但是实际开发中为了方便，许多人写在实现类上。
代码实现：

package cn.tedu.dao;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import cn.tedu.domain.User;
@Repository
public class UserDaoImpl implements UserDao{
	
	@Autowired
	private JdbcTemplate template=null;

	@Override
	public void addUser(User user) {
		template.update("insert into user values(?,?,?)", user.getId(),user.getName(),user.getAge());
	}

	@Override
	public void updateUser(User user) {
		template.update("update user set name=? where id=?",user.getName(),user.getId());
	}

	@Override
	public void delUser(int id) {
		template.update("delete from user where id=?",id);
	}

	@Override
	public User queryUser(int id) {
		return template.queryForObject("select * from user where id=?", new BeanPropertyRowMapper<User>(User.class), 2);
	}
}

package cn.tedu.domain;

public class User {
	private int id;
	private String name;
	private int age;
	public User(){}
	public User(int id, String name, int age) {
		super();
		this.id = id;
		this.name = name;
		this.age = age;
	}

	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
	}
	
}

package cn.tedu.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import cn.tedu.dao.UserDao;
import cn.tedu.domain.User;
//针对所有的异常实现事务
@Transactional(rollbackFor={java.lang.Throwable.class})
@Service
public class UserServiceImpl implements UserService{
	@Autowired
	UserDao userDao=null;
	@Override
	public void addUser(User user) {//throws Exception{
		userDao.addUser(user);
		//运行时异常，会执行事务
		int i=1/0;
		//编译时异常，不会执行事务
		//throw new Exception("编译时异常");
	}
	
	@Override
	public void updateUser(User user){
		userDao.updateUser(user);
	}
	@Override
	public void delUser(int id){
		userDao.delUser(id);
	}
	//不支持注解事务
	@Transactional(propagation=Propagation.NOT_SUPPORTED)
	@Override
	public User queryUser(int id){
		return userDao.queryUser(id);
	}
}

package cn.tedu.test;

import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import cn.tedu.domain.User;
import cn.tedu.web.UserServlet;

public class Test01 {
	private ApplicationContext context=null;
	UserServlet userServlet=null;
	@Before
	public void before(){
		context=new ClassPathXmlApplicationContext("applicationContext2.xml");
		userServlet=(UserServlet) context.getBean("userServlet");
	}
	@Test
	public void test01() {//throws Exception{
		userServlet.addUser(new User(4,"ddd",16));
	}
	@Test
	public void test02(){
		userServlet.updateUser(new User(4,"rrr",20));
	}
	@Test
	public void test03(){
		userServlet.delUser(4);
	}
	@Test
	public void test04(){
		System.out.println(userServlet.queryUser(3));
	}
}

package cn.tedu.web;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;

import cn.tedu.domain.User;
import cn.tedu.service.UserService;

@Controller
public class UserServlet {
	@Autowired
	UserService userService=null;
	public void addUser(User user) {//throws Exception{
		userService.addUser(user);
	}
	public void updateUser(User user){
		userService.updateUser(user);
	}
	public void delUser(int id){
		userService.delUser(id);
	}
	public User queryUser(int id){
		return userService.queryUser(id);
	}
}

非注解实现事务的配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:util="http://www.springframework.org/schema/util"
	xmlns:aop="http://www.springframework.org/schema/aop"
	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-3.2.xsd
	http://www.springframework.org/schema/context 
	http://www.springframework.org/schema/context/spring-context-3.2.xsd
	http://www.springframework.org/schema/util
	http://www.springframework.org/schema/util/spring-util-3.2.xsd
	http://www.springframework.org/schema/aop
	http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
	http://www.springframework.org/schema/tx
	http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
	 "	>
	 <!-- 配置ioc -->
	 <context:component-scan base-package="cn.tedu.web"></context:component-scan>
	 <context:component-scan base-package="cn.tedu.service"></context:component-scan>
	 <context:component-scan base-package="cn.tedu.dao"></context:component-scan>
	 <!--配置aop  -->
	 <aop:aspectj-autoproxy></aop:aspectj-autoproxy>
	 <!--配置数据源bean  -->
	 <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
	 	<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
	 	<property name="jdbcUrl" value="jdbc:mysql:///springdb"></property>
	 	<property name="user" value="root"></property>
	 	<property name="password" value="root"></property>
	 </bean>
	 <!--配置JDBCTemplate  -->
	 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	 	<property name="dataSource" ref="dataSource"></property>
	 </bean>
	 <!--配置事务管理器  -->
	 <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
	 	<property name="dataSource" ref="dataSource"></property>
	 </bean>
	 <!--配置事务管理切面  环绕通知-->
	 <tx:advice id="txAdvice" transaction-manager="transactionManager">
	 	<tx:attributes>
	 		<tx:method name="add*" propagation="REQUIRED"
	 			rollback-for="java.lang.Throwable"
	 		/>
	 		<tx:method name="updateUser" propagation="REQUIRED"/>
	 		<tx:method name="delUser" propagation="REQUIRED"/>
	 	</tx:attributes>
	 </tx:advice>
	 <!-- 配置事务切面 -->
	 <aop:config>
	 	<aop:pointcut expression="execution(* cn.tedu.service..*.*(..))" id="pc01"/>
	 	<aop:advisor advice-ref="txAdvice" pointcut-ref="pc01"/>
	 </aop:config>
</beans>

注解实现事务的配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:util="http://www.springframework.org/schema/util"
	xmlns:aop="http://www.springframework.org/schema/aop"
	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-3.2.xsd
	http://www.springframework.org/schema/context 
	http://www.springframework.org/schema/context/spring-context-3.2.xsd
	http://www.springframework.org/schema/util
	http://www.springframework.org/schema/util/spring-util-3.2.xsd
	http://www.springframework.org/schema/aop
	http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
	http://www.springframework.org/schema/tx
	http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
	 "	>
	 <!-- 配置ioc -->
	 <context:component-scan base-package="cn.tedu.web"></context:component-scan>
	 <context:component-scan base-package="cn.tedu.service"></context:component-scan>
	 <context:component-scan base-package="cn.tedu.dao"></context:component-scan>
	 <!--配置aop  -->
	 <aop:aspectj-autoproxy></aop:aspectj-autoproxy>
	 <!--配置数据源bean  -->
	 <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
	 	<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
	 	<property name="jdbcUrl" value="jdbc:mysql:///springdb"></property>
	 	<property name="user" value="root"></property>
	 	<property name="password" value="root"></property>
	 </bean>
	 <!--配置JDBCTemplate  -->
	 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	 	<property name="dataSource" ref="dataSource"></property>
	 </bean>
	 <!-- 开启spring的注解方式配置事务 -->
	 <!--配置事务管理器  -->
	 <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
	 	<property name="dataSource" ref="dataSource"></property>
	 </bean>
	 <!-- 开启注解方式事务管理 -->
	 <tx:annotation-driven/>
	 
	 
	 
	 <!--配置事务管理切面  环绕通知-->
	 <!-- <tx:advice id="txAdvice" transaction-manager="transactionManager">
	 	<tx:attributes>
	 		<tx:method name="add*" propagation="REQUIRED"
	 			rollback-for="java.lang.Throwable"
	 		/>
	 		<tx:method name="updateUser" propagation="REQUIRED"/>
	 		<tx:method name="delUser" propagation="REQUIRED"/>
	 	</tx:attributes>
	 </tx:advice> -->
	 <!-- 配置事务切面 -->
	 <!-- <aop:config>
	 	<aop:pointcut expression="execution(* cn.tedu.service..*.*(..))" id="pc01"/>
	 	<aop:advisor advice-ref="txAdvice" pointcut-ref="pc01"/>
	 </aop:config> -->
</beans>