【05】Spring JDBC

1. Spring 的 的 JDBC 的模板

• Spring 提供了很多持久层技术的模板类简化编程
  

1.1 引入相关开发包

	<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-jdbc</artifactId>
		<version>4.3.20.RELEASE</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>5.1.38</version>
	</dependency>

1.2 创建测试类

	import org.junit.Test;
	import org.springframework.jdbc.core.JdbcTemplate;
	import org.springframework.jdbc.datasource.DriverManagerDataSource;
	
	public class TestClass03 {
		@Test
		// JDBC 模板的基本使用：
		public void demo1() {
			DriverManagerDataSource dataSource = new DriverManagerDataSource();
			dataSource.setDriverClassName("com.mysql.jdbc.Driver");
			dataSource.setUrl("jdbc:mysql://localhost:3306/world");
			dataSource.setUsername("root");
			dataSource.setPassword("root");
			JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
			System.out.println(jdbcTemplate);
			jdbcTemplate.update("insert into account values (null,?,?)", "会希", 10000d);
		}
	}

2. Spring 中配置内置连接池

2.1 applicationContext.xml中配置内置连接池

    <!-- 配置 Spring 的内置连接池 -->
    <bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
   	  <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
  	  <property name="url" value="jdbc:mysql:///spring_day02"/>
   	  <property name="username" value="root"/>
  	  <property name="password" value="123"/>
    </bean>

2.2 将模板配置到 Spring 中

    <!-- 配置 JDBC 模板 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
       <property name="dataSource" ref="dataSource"/>
    </bean>

2.3 测试类

  	import javax.annotation.Resource; 
	import org.junit.Test;
	import org.junit.runner.RunWith;
	import org.springframework.jdbc.core.JdbcTemplate;
	import org.springframework.test.context.ContextConfiguration;
	import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
	
	@RunWith(SpringJUnit4ClassRunner.class)
	@ContextConfiguration("classpath:spring/applicationContext.xml")
	public class TestClass04 {
		@Resource(name = "jdbcTemplate")
		private JdbcTemplate jdbcTemplate;

		@Test
		public void demo1() {
			jdbcTemplate.update("insert into account values (null,?,?)", "凤姐", 10000d);
		}
	}

3. Spring 配置文件中读取属性文件（properties）

3.1 编写数据库配置文件

• db.properties

	jdbc.driver=com.mysql.jdbc.Driver
	jdbc.url=jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false
	jdbc.username=root
	jdbc.password=123456
	jdbc.initialPoolSize=5
	jdbc.maxPoolSize=30

3.2 引入外部的属性文件

1. 第一种

	<context:property-placeholder location="db.properties"/>

2. 第二种

	<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> 		
	   <property name="location" value="classpath:db.properties"/> 	
	</bean>

4. Spring 中配置DBCP连接池

4.1 引入jdbc连接池 jar 包

	<!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
	<dependency>
		<groupId>commons-dbcp</groupId>
		<artifactId>commons-dbcp</artifactId>
		<version>1.4</version>
	</dependency>

4.2 配置DBCP数据源

	<!-- DBCP数据源 -->
	<bean id="ds_dbcp" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="${jdbc.driver}"/>
		<property name="url" value="${jdbc.url}"/>
		<property name="username" value="${jdbc.username}"/>
		<property name="password" value="${jdbc.password}"/>
	</bean>

4.3 配置 Spring 的模板文件

	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="ds_dbcp"/>
	</bean>

5. Spring 中配置C3P0连接池

5.1 引入C3P0连接池 jar 包

	<!-- C3P0数据源 -->
	<bean id="ds_c3p0" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="${jdbc.driver}"/>
		<property name="jdbcUrl" value="${jdbc.url}"/>
		<property name="user" value="${jdbc.username}"/>
		<property name="password" value="${jdbc.password}"/>
	</bean>

5.2 配置C3P0数据源

	<!-- DBCP数据源 -->
	<bean id="ds_dbcp" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="${jdbc.driver}"/>
		<property name="url" value="${jdbc.url}"/>
		<property name="username" value="${jdbc.username}"/>
		<property name="password" value="${jdbc.password}"/>
	</bean>

5.3 配置 Spring 的模板文件

	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="ds_c3p0"/> 
	</bean>

6. Spring JdbcTemplate 实现数据库操作

6.1 增、删、改

	@RunWith(SpringJUnit4ClassRunner.class)
	@ContextConfiguration("classpath:spring/applicationContext.xml")
	public class TestJDBC {
	
		@Autowired
		private JdbcTemplate jdbcTemplate;
			
		@Test
		public void demo2() {
			try {
				int i = jdbcTemplate.update("insert into Grade(GradeName) values (?)", "新增的年级");
				System.out.println(i);
			} catch (DataAccessException e) {
				e.printStackTrace();
			}
		}
	}

6.2 查询单个值

	@RunWith(SpringJUnit4ClassRunner.class)
	@ContextConfiguration("classpath:spring/applicationContext.xml")
	public class TestJDBC {
	
		@Autowired
		private JdbcTemplate jdbcTemplate;
			
		@Test
		public void demo3() {
			// 只能获得基本数据类型，不能进行对象属性的封装
			String gradeName = jdbcTemplate.queryForObject("select GradeName from Grade where GradeId = 1", String.class);
			System.out.println(gradeName);
			// 查询总数
			int count = jdbcTemplate.queryForObject("select count(GradeId) from Grade", Integer.class);
			System.out.println(count);
		}
	}

6.3 查询单个对象

	@RunWith(SpringJUnit4ClassRunner.class)
	@ContextConfiguration("classpath:spring/applicationContext.xml")
	public class TestJDBC {
	
		@Autowired
		private JdbcTemplate jdbcTemplate;
			
		@Test
		public void demo4() {
			Grade grade = jdbcTemplate.queryForObject("select GradeID,GradeName from Grade where GradeId = 1", new RowMapper<Grade>() {
				@Override
				public Grade mapRow(ResultSet rs, int rowNum) throws SQLException {
					Grade grade = new Grade();
					grade.setGradeId(rs.getInt("gradeId"));
					grade.setGradeName(rs.getString("gradeName"));
					return grade;
				}
			});
			System.out.println(grade);
		}
	}

6.4 查询list（1）：匿名内部类

	@RunWith(SpringJUnit4ClassRunner.class)
	@ContextConfiguration("classpath:spring/applicationContext.xml")
	public class TestJDBC {
	
		@Autowired
		private JdbcTemplate jdbcTemplate;
			
		@Test
		public void demo5() {
			String sql = "select GradeID,GradeName from Grade";
			// 对对象属性的封装
			List<Grade> list = jdbcTemplate.query(sql, new RowMapper<Grade>() {
				@Override
				public Grade mapRow(ResultSet rs, int rowNum) throws SQLException {
					Grade grade = new Grade();
					grade.setGradeId(rs.getInt("gradeId"));
					grade.setGradeName(rs.getString("gradeName"));
					return grade;
				}
			});

			// 测试
			for (Grade grade : list) {
				System.out.println(grade);
			}
		}
	}

6.5 查询list（2）

	@RunWith(SpringJUnit4ClassRunner.class)
	@ContextConfiguration("classpath:spring/applicationContext.xml")
	public class TestJDBC {
	
		@Autowired
		private JdbcTemplate jdbcTemplate;
			
		@Test
		public void demo6() {
			String sql = "select GradeID,GradeName from Grade WHERE LENGTH(GradeName) > 10";
			// 对对象属性的封装
			List<Grade> list = jdbcTemplate.query(sql, new MyRowMapper());

			// 测试
			for (Grade grade : list) {
				System.out.println(grade);
			}
		}
	}

	/**
	 * 自定义的 RowMapper 实现类 -- 实现封装代码的重用
	 * @author Administrator
	 */
	class MyRowMapper implements RowMapper<Grade> {
		@Override
		public Grade mapRow(ResultSet rs, int rowNum) throws SQLException {
			Grade grade = new Grade();
			grade.setGradeId(rs.getInt("gradeId"));
			grade.setGradeName(rs.getString("gradeName"));
			return grade;
		}
	}