Spring框架的JdbcTemplate使用

目录

• JdbcTemplate 概述
• 对JdbcTemplate进行分步演示
  • 1：测试数据源
  • 2：为IoC容器配置一个JdbcTemplate
  • 3：更新
  • 4：批量插入
  • 5：查询emp_id=5的记录，封装为一个Java对象返回。
  • 6：查询salary>4000的记录，封装为List集合返回
  • 7：查询最大的salary
  • 8：使用具名参数SQL插入一条员工记录，并以Map形式传入参数值。
  • 9：使用具名参数SQL插入一条员工记录，并以SqlparamSource传入参数值。

jdbc.user=root
jdbc.password=Hudie
jdbc.jdbcUrl=jdbc:mysql://localhost:3306/jdbctemplate
jdbc.driverClass=com.mysql.jdbc.Driver

	<!-- 引入外部配置文件 -->
	<context:property-placeholder location="classpath:dbconfig.properties" />
		
	<!-- 配置数据源 -->
	<bean id="dataSource"
		class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="user" value="${jdbc.user}"></property>
		<property name="password" value="${jdbc.password}"></property>
		<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
		<property name="driverClass" value="${jdbc.driverClass}"></property>
	</bean>

public class txTest {
	ApplicationContext ioc = new ClassPathXmlApplicationContext("ApplicationContext.xml");

	@Test
	public void test() throws SQLException {
		DataSource bean = ioc.getBean(DataSource.class);
		Connection connection = bean.getConnection();
		System.out.println(connection);
		connection.close();
	}
}

	<!-- Spring提供了一个JdbcTmplate来操作数据库 -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<constructor-arg name="dataSource" ref="dataSource"></constructor-arg>
	</bean>

public class txTest {
	ApplicationContext ioc = new ClassPathXmlApplicationContext("ApplicationContext.xml");
	JdbcTemplate jdbcTemplate= ioc.getBean(JdbcTemplate.class);

	@Test
	public void test2() {
		System.out.println(jdbcTemplate);
	}
}

	@Test
	public void test3() {
		String sql = "update employee set salary = ? where emp_id=?;";
		int update = jdbcTemplate.update(sql, 1300.00, 5);
		System.out.println("更新员工表,影响" + update + "行");
	}

	@Test
	public void test4() {

		String sql = "insert into employee (emp_name,salary) values(?,?)";
		List<Object[]> batchArgs = new ArrayList<Object[]>();
		batchArgs.add(new Object[] { "张三", 998.98 });
		batchArgs.add(new Object[] { "李四", 998.98 });
		batchArgs.add(new Object[] { "王五", 998.98 });
		batchArgs.add(new Object[] { "赵六", 998.98 });

		// List的长度就是sql语句执行的次数
		int[] is = jdbcTemplate.batchUpdate(sql, batchArgs);
		for (int i : is) {
			System.out.println(i);
		}
	}

package com.gql.bean;

public class Employee {
	private Integer empId;
	private String empName;
	private Double salary;
	//省略setter、getter与toString方法。
}

	@Test
	public void test5() {
		String sql = "select emp_id empId,emp_name empName,salary from employee where emp_id=?";
		// rowMapper:规定每一行记录和JavaBean的属性如何映射
		Employee employee = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Employee.class), 5);
		System.out.println(employee);
	}

	@Test
	public void test6() {
		String sql = "select emp_id empId,emp_name empName,salary from employee where salary>?";
		List<Employee> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Employee.class), 4000);
		for (Employee employee : list) {
			System.out.println(employee);
		}
	}

	@Test
	public void test7() {
		String sql = "select max(salary) from employee";
		Double object = jdbcTemplate.queryForObject(sql, Double.class);
		System.out.println("最高工资是:" + object);
	}

	<!-- 配置一个具名参数的Jdbctemplate -->
	<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
		<!-- 使用构造器注入一个数据源 -->
		<constructor-arg name="dataSource" ref="dataSource"></constructor-arg>
	</bean>

public class txTest {
	ApplicationContext ioc = new ClassPathXmlApplicationContext("ApplicationContext.xml");
	JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);
	NamedParameterJdbcTemplate namedJdbcTemplate = ioc.getBean(NamedParameterJdbcTemplate.class);

	@Test
	public void test9() {
		String sql = "insert into employee (emp_name,salary) values(:empName,:salary)";
		Map<String, Object> paramMap = new HashMap<>();
		// 将所有具名参数的值都放在map中
		paramMap.put("empName", "小红");
		paramMap.put("salary", 12000.00);
		int update = namedJdbcTemplate.update(sql, paramMap);
		System.out.println(update);
	}
}

	@Test
	public void test10() {
		String sql = "insert into employee (emp_name,salary) values(:empName,:salary)";
		Employee employee = new Employee();
		employee.setEmpName("小蓝");
		employee.setSalary(9999.00);
		int i = namedJdbcTemplate.update(sql, new BeanPropertySqlParameterSource(employee));
		System.out.println(i);
	}