这篇文章主要讲一下使用JdbcTemplate连接数据库
我在本地使用的数据库是MySQL5.7
为了使用JdbcTemplate和连接上数据库，需要添加c3p0的依赖包和mysql-connectorJ的依赖包,我是使用maven添加的依赖：
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.3.7.RELEASE</version> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-core --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>4.3.7.RELEASE</version> </dependency> <!-- https://mvnrepository.com/artifact/com.mchange/c3p0 --> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.2</version> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>6.0.6</version> </dependency>在MySQL中使用的数据表为：下面写两个类：Department和employee
package 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 + "]"; } } package jdbc; public class Employee { private Integer id; private String lastName; private String email; private Integer deptId; 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 getDeptId() { return deptId; } public void setDeptId(Integer deptId) { this.deptId = deptId; } @Override public String toString() { return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", deptId=" + deptId + "]"; } } 然后写两个数据访问层的Dao类 package 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类的成员变量 * @author 86538 * */ @Repository public 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 department WHERE id = ?"; RowMapper<Department> rowMapper = new BeanPropertyRowMapper<>(Department.class); return getJdbcTemplate().queryForObject(sql, rowMapper, id); } } package jdbc; import java.util.List; 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; @Repository public class EmployeeDao { @Autowired private JdbcTemplate jdbcTemplate; public Employee get(Integer id) { String sql = "SELECT id, last_name lastName, email FROM employees WHERE id = ?"; RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);; Employee employees = jdbcTemplate.queryForObject(sql, rowMapper, id); return employees; } }在bean的配置文件中：
<?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" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd"> <context:component-scan base-package="jdbc"></context:component-scan> <!-- 导入资源文件 --> <context:property-placeholder location="classpath:db.properties"/> <!-- 配置c3p0数据源 --> <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> <property name="initialPoolSize" value="${jdbc.initPoolSize}"></property> <property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property> </bean> <!-- 配置Spring的JdbcTemplate --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> <!-- 配置 NameParameterJdbcTemplate,该对象可以使用具名参数，其没有无参数的构造器，所以必须为其构造器指定参数--> <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"> <constructor-arg ref="dataSource"></constructor-arg> </bean> </beans> property配置文件： jdbc.user=root jdbc.password=1234 jdbc.driverclass=com.mysql.cj.jdbc.Driver jdbc.jdbcurl=jdbc:mysql:///spring-4?serverTimezone=UTC&useSSL=true jdbc.initPoolSize=5 jdbc.maxPoolSize=10 然后再弄个junit的test文件测试一下效果： package jdbc; import static org.junit.Assert.*; 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); } /** * 使用具名参数时，可以使用NamedParameterJdbcTemplate.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,:deptId)"; Employee employee = new Employee(); employee.setLastName("XYZ"); employee.setEmail("xyz@sina.com"); employee.setDeptId(3); SqlParameterSource paramSource = new BeanPropertySqlParameterSource(employee); namedParameterJdbcTemplate.update(sql, paramSource); } /** * 可以为参数起名字，与？相比 * 1.好处：若有多个参数，则不用再去对应位置，直接对应参数名字，如last_name * 2.缺点：较为麻烦 */ @Test public void testNamedParameterJdbcTemplate() { String sql = "INSERT INTO employees(last_name, email, dept_id) VALUES(:ln,:email,:deptid)"; Map<String, Object> 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)); } /** * 获取单个列的值，或做统计查询 */ @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<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);; List<Employee> employees = jdbcTemplate.query(sql, rowMapper, 5); System.out.println(employees); } /** * 从数据库中获取一条记录，实际得到对应的对象 * 注意不是调用jdbcTemplate.queryForObject(sql, requiredType, args)方法 * 而是使用queryForObject(sql, rowMapper,1); * 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<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class); Employee employee = jdbcTemplate.queryForObject(sql, rowMapper,1); System.out.println(employee); } /** * 执行批量更新：批量的IUD * 最后一个参数是Object[]的list类型：因为修改一条记录需要一个Object的数组，那么多条就需要一个List */ public void testBatchUpdate() { String sql = "INSERT INTO employees(id,last_name, email, dept_id) VALUES(?,?,?,?)"; List<Object[]> batchArgs = new ArrayList<>(); batchArgs.add(new Object[]{6,"AA", "aa@atguigu.com", 1}); batchArgs.add(new Object[]{7,"BB", "bb@atguigu.com", 1}); batchArgs.add(new Object[]{8,"CC", "cc@atguigu.com", 1}); batchArgs.add(new Object[]{9,"DD", "dd@atguigu.com", 1}); batchArgs.add(new Object[]{10,"EE","ee@atguigu.com", 1}); jdbcTemplate.batchUpdate(sql, batchArgs); } /* * INSERT,UPDATE,DELETE */ //@Test public void testUpdata() { 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()); } 运行之后可以看到程序是可以正常运行的。如果出问题，一般是因为依赖的jar包没有成功加进来或者是加的版本不对，可以在maven里面尝试多几个版本，还有就是MySQL的版本问题，在SQL5中driverClass是com.mysql.jdbc.Driver，jdbcurl是jdbc:mysql:///databaseName，在SQL6中driverClass是com.mysql.cj.jdbc.Driver,jdbcurl需要加上服务器时间域serverTimezone和是否使用加密useSSL等配置信息，出问题的话自己看报错信息一般都能解决。