1.jdbc.properties文件

jdbc.user=rootjdbc.password=1230jdbc.driverClass=com.mysql.jdbc.Driverjdbc.jdbcUrl=jdbc:mysql:///springjdbc.initPoolSize=5jdbc.maxPoolSize=10

2.applicationcontext.xml配置文件

3.实现类

package com.atguigu.spring.jdbc;public class Employee {		private Integer id;	private String lastName;	private String email;		private Integer dpetId;	public Integer getId() {		return id;	}	public void setId(Integer id) {		this.id = id;	}	public String getLastName() {		return lastName;	}	public void setLastName(String lastName) {		this.lastName = lastName;	}	public String getEmail() {		return email;	}	public void setEmail(String email) {		this.email = email;	}	public Integer getDpetId() {		return dpetId;	}	public void setDpetId(Integer dpetId) {		this.dpetId = dpetId;	}	@Override	public String toString() {		return "Employee [id=" + id + ", lastName=" + lastName + ", email="				+ email + ", dpetId=" + dpetId + "]";	}	}package com.atguigu.spring.jdbc;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.core.BeanPropertyRowMapper;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import org.springframework.stereotype.Repository;@Repositorypublic class EmployeeDao {		@Autowired	private JdbcTemplate jdbcTemplate;		public Employee get(Integer id){		String sql = "SELECT id, last_name lastName, email FROM employees WHERE id = ?";		RowMapper
    
      rowMapper = new BeanPropertyRowMapper<>(Employee.class);		Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, id);				return employee;	}}package com.atguigu.spring.jdbc;import javax.sql.DataSource;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.core.BeanPropertyRowMapper;import org.springframework.jdbc.core.RowMapper;import org.springframework.jdbc.core.support.JdbcDaoSupport;import org.springframework.stereotype.Repository;/** * 不推荐使用 JdbcDaoSupport, 而推荐直接使用 JdbcTempate 作为 Dao 类的成员变量 */@Repositorypublic class DepartmentDao extends JdbcDaoSupport{	@Autowired	public void setDataSource2(DataSource dataSource){		setDataSource(dataSource);	}	public Department get(Integer id){		String sql = "SELECT id, dept_name name FROM departments WHERE id = ?";		RowMapper
     
       rowMapper = new BeanPropertyRowMapper<>(Department.class);		return getJdbcTemplate().queryForObject(sql, rowMapper, id);	}	}package com.atguigu.spring.jdbc;public class Department {	private Integer id;	private String name;	public Integer getId() {		return id;	}	public void setId(Integer id) {		this.id = id;	}	public String getName() {		return name;	}	public void setName(String name) {		this.name = name;	}	@Override	public String toString() {		return "Department [id=" + id + ", name=" + name + "]";	}}
     
    

4.测试

package com.atguigu.spring.jdbc;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.sql.DataSource;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.RowMapper;import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;import org.springframework.jdbc.core.namedparam.SqlParameterSource;public class JDBCTest {		private ApplicationContext ctx = null;	private JdbcTemplate jdbcTemplate;	private EmployeeDao employeeDao;	private DepartmentDao departmentDao;	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;		{		ctx = new ClassPathXmlApplicationContext("applicationContext.xml");		jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");		employeeDao = ctx.getBean(EmployeeDao.class);		departmentDao = ctx.getBean(DepartmentDao.class);		namedParameterJdbcTemplate = ctx.getBean(NamedParameterJdbcTemplate.class);	}		/**	 * 使用具名参数时, 可以使用 update(String sql, SqlParameterSource paramSource) 方法进行更新操作	 * 1. SQL 语句中的参数名和类的属性一致!	 * 2. 使用 SqlParameterSource 的 BeanPropertySqlParameterSource 实现类作为参数. 	 */	@Test	public void testNamedParameterJdbcTemplate2(){		String sql = "INSERT INTO employees(last_name, email, dept_id) "				+ "VALUES(:lastName,:email,:dpetId)";				Employee employee = new Employee();		employee.setLastName("XYZ");		employee.setEmail("xyz@sina.com");		employee.setDpetId(3);				SqlParameterSource paramSource = new BeanPropertySqlParameterSource(employee);		namedParameterJdbcTemplate.update(sql, paramSource);	}		/**	 * 可以为参数起名字. 	 * 1. 好处: 若有多个参数, 则不用再去对应位置, 直接对应参数名, 便于维护	 * 2. 缺点: 较为麻烦. 	 */	@Test	public void testNamedParameterJdbcTemplate(){		String sql = "INSERT INTO employees(last_name, email, dept_id) VALUES(:ln,:email,:deptid)";				Map
    
      paramMap = new HashMap<>();		paramMap.put("ln", "FF");		paramMap.put("email", "ff@atguigu.com");		paramMap.put("deptid", 2);				namedParameterJdbcTemplate.update(sql, paramMap);	}		@Test	public void testDepartmentDao(){		System.out.println(departmentDao.get(1));	}		@Test	public void testEmployeeDao(){		System.out.println(employeeDao.get(1));	}		/**	 * 获取单个列的值, 或做统计查询	 * 使用 queryForObject(String sql, Class
     
       requiredType) 	 */	@Test	public void testQueryForObject2(){		String sql = "SELECT count(id) FROM employees";		long count = jdbcTemplate.queryForObject(sql, Long.class);				System.out.println(count);	}		/**	 * 查到实体类的集合	 * 注意调用的不是 queryForList 方法	 */	@Test	public void testQueryForList(){		String sql = "SELECT id, last_name lastName, email FROM employees WHERE id > ?";		RowMapper
      
        rowMapper = new BeanPropertyRowMapper<>(Employee.class);		List
       
         employees = jdbcTemplate.query(sql, rowMapper,5);				System.out.println(employees);	}		/**	 * 从数据库中获取一条记录, 实际得到对应的一个对象	 * 注意不是调用 queryForObject(String sql, Class
        
          requiredType, Object... args) 方法!	 * 而需要调用 queryForObject(String sql, RowMapper
         
           rowMapper, Object... args) * 1. 其中的 RowMapper 指定如何去映射结果集的行, 常用的实现类为 BeanPropertyRowMapper * 2. 使用 SQL 中列的别名完成列名和类的属性名的映射. 例如 last_name lastName * 3. 不支持级联属性. JdbcTemplate 到底是一个 JDBC 的小工具, 而不是 ORM 框架 */ @Test public void testQueryForObject(){ String sql = "SELECT id, last_name lastName, email, dept_id as \"department.id\" FROM employees WHERE id = ?"; RowMapper
          
            rowMapper = new BeanPropertyRowMapper<>(Employee.class); Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, 1); System.out.println(employee); } /** * 执行批量更新: 批量的 INSERT, UPDATE, DELETE * 最后一个参数是 Object[] 的 List 类型: 因为修改一条记录需要一个 Object 的数组, 那么多条不就需要多个 Object 的数组吗 */ @Test public void testBatchUpdate(){ String sql = "INSERT INTO employees(last_name, email, dept_id) VALUES(?,?,?)"; List
           
             batchArgs = new ArrayList<>(); batchArgs.add(new Object[]{"AA", "aa@atguigu.com", 1}); batchArgs.add(new Object[]{"BB", "bb@atguigu.com", 2}); batchArgs.add(new Object[]{"CC", "cc@atguigu.com", 3}); batchArgs.add(new Object[]{"DD", "dd@atguigu.com", 3}); batchArgs.add(new Object[]{"EE", "ee@atguigu.com", 2}); jdbcTemplate.batchUpdate(sql, batchArgs); } /** * 执行 INSERT, UPDATE, DELETE */ @Test public void testUpdate(){ String sql = "UPDATE employees SET last_name = ? WHERE id = ?"; jdbcTemplate.update(sql, "Jack", 5); } @Test public void testDataSource() throws SQLException { DataSource dataSource = ctx.getBean(DataSource.class); System.out.println(dataSource.getConnection()); }}