spring之操作数据库之使用JdbcTemplate和JdbcDaoSupport
首先将以下依赖包添加到build path: 基本文件目录: 注意打开mysql服务,并在数据库中新建两个表:employees和departments,目录如下: Departmen t.java package com.gong.spring.jdbc; public class Department { private Integer id; String name; public Integer getId() { return id; } void setId(Integer id) { this.id = String getName() { name; } setName(String name) { this.name = name; } @Override String toString() { return "Department [id=" + id + ",name=" + name + "]"; } } Employee.java Employee { String lastName; String email; Integer dpetId; String getLastName() { lastName; } setLastName(String lastName) { this.lastName = String getEmail() { email; } setEmail(String email) { this.email = Integer getDpetId() { dpetId; } setDpetId(Integer dpetId) { this.dpetId = dpetId; } @Override return "Employee [id=" + id + ",lastName=" + lastName + ",email=" + email + ",dpetId=" + dpetId + "]"; } } 一、数据库连接配置 首先是配置数据源:spring是数据库的名字,root是数据库账号,123456是数据库密码 db.properties jdbc.user=root jdbc.password=123456 jdbc.driverClass=com.mysql.jdbc.Driver jdbc.jdbcUrl=jdbc:mysql:///spring jdbc.initPoolSize=5 jdbc.maxPoolSize=10 在applicationContext.xml中配置数据库连接 <?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/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd"> <!-- 导入资源文件 --> context:property-placeholder location="classpath:db.properties"/> 配置 C3P0 数据源 bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> property name="user" value="${jdbc.user}"></property="password"="${jdbc.password}"="jdbcUrl"="${jdbc.jdbcUrl}"="driverClass"="${jdbc.driverClass}"> ="initialPoolSize"="${jdbc.initPoolSize}"="maxPoolSize"="${jdbc.maxPoolSize}"> </bean> beans> 新建一个JUnit Test Case文件:JDBCTest.java。在里面测试数据库是否连接成功。 import java.sql.SQLException; javax.sql.DataSource; org.junit.jupiter.api.Test; org.springframework.context.ApplicationContext; org.springframework.context.support.ClassPathXmlApplicationContext; JDBCTest { private ApplicationContext ctx = null; { ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); } @Test void test() throws SQLException { DataSource dataSource = ctx.getBean(DataSource.); System.out.println(dataSource.getConnection()); } } 鼠标右键点击test()方法,选择run as JUnit-Test,若输出为: 则表明连接成功。 二、JdbcTemplate 配置 在applicationContext.xml中进行配置 context:component-scan base-package="com.gong.spring"context:component-scan> 配置 Spirng 的 JdbcTemplate ="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" ref="dataSource"> 然后在JDBCTest.java中测试相应的操作: java.sql.SQLException; java.util.ArrayList; java.util.List; org.springframework.context.support.ClassPathXmlApplicationContext; org.springframework.jdbc.core.BeanPropertyRowMapper; org.springframework.jdbc.core.JdbcTemplate; org.springframework.jdbc.core.RowMapper; ; JdbcTemplate jdbcTemplate; { ctx = ); jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate"); } 执行更新 @Test testUpdate() { String sql = "UPDATE employees SET last_name = ? where id = ?"; jdbcTemplate.update(sql,"tom",2); } 执行批量更新 最后一个参数是Object []的List类型 testBatchUpdate() { String sql = "INSERT INTO employees(last_name,email,dept_id) VALUES (?,?,?)"; List<Object[]> batchArgs = new ArrayList<>(); batchArgs.add(new Object[]{"jack","jack@qq.com",1}); batchArgs.add(new Object[]{"bob","bob@qq.com",1)">new Object[]{"mike","mike@qq.com",3}); jdbcTemplate.batchUpdate(sql,batchArgs); } /** * 从数据库中获取一条记录,实际得到对应的一个对象 * 注意不是调用 queryForObject(String sql,Class<Employee> requiredType,Object... args) 方法! * 而需要调用 queryForObject(String sql,RowMapper<Employee> rowMapper,Object... args) * 1. 其中的 RowMapper 指定如何去映射结果集的行,常用的实现类为 BeanPropertyRowMapper * 2. 使用 SQL 中列的别名完成列名和类的属性名的映射. 例如 last_name lastName * 3. 不支持级联属性. JdbcTemplate 到底是一个 JDBC 的小工具,而不是 ORM 框架 */ @Test testQueryForObject(){ String sql = "SELECT id,last_name lastName,dept_id as "department.id" FROM employees WHERE id = ?"; RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.); Employee employee = jdbcTemplate.queryForObject(sql,rowMapper,1)">); System.out.println(employee); } * 查到实体类的集合 * 注意调用的不是 queryForList 方法 testQueryForList(){ String sql = "SELECT id,email FROM employees WHERE id > ?"); List<Employee> employees = jdbcTemplate.query(sql,5); System.out.println(employees); } * 获取单个列的值,或做统计查询 * 使用 queryForObject(String sql,Class<Long> requiredType) testQueryForObject2(){ String sql = "SELECT count(id) FROM employees"; long count = jdbcTemplate.queryForObject(sql,Long.); System.out.println(count); } @Test ); System.out.println(dataSource.getConnection()); } } 三、利用JdbcTemplate操作实际的类(使用基于注解的方式配置bean) EmployeeDao.java org.springframework.beans.factory.annotation.Autowired; org.springframework.jdbc.core.RowMapper; org.springframework.stereotype.Repository; @Repository EmployeeDao { @Autowired JdbcTemplate jdbcTemplate; Employee get(Integer id){ String sql = "SELECT id,email FROM employees WHERE id = ?"); Employee employee = jdbcTemplate.queryForObject(sql,id); employee; } } 在JDBCTest.java中进行测试 JdbcTemplate jdbcTemplate; EmployeeDao employeeDao; DepartmentDao departmentDao; { ctx = ); employeeDao = ctx.getBean(EmployeeDao.); departmentDao = ctx.getBean(DepartmentDao.); } @Test testEmployeeDao(){ System.out.println(employeeDao.get(1)); } 输出: 对于另外一种: DepartmentDao.java org.springframework.jdbc.core.support.JdbcDaoSupport; class DepartmentDao extends JdbcDaoSupport{ //使用这种方法需要注入DataSource @Autowired setDataSource2(DataSource dataSource){ setDataSource(dataSource); } Department get(Integer id){ String sql = "SELECT id,dept_name name FROM departments WHERE id = ?"; RowMapper<Department> rowMapper = new BeanPropertyRowMapper<>(Department.); getJdbcTemplate().queryForObject(sql,id); } } 在JDBCTest.java中进行测试: testDepartmentDao(){ System.out.println(departmentDao.get(1)); } 输出: 一般推荐使用JdbcTemplate (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |