Spring-JDBC，带一小例子

一、包结构（需要导的库）

 

二、entity

package org.e276.entity;

import java.sql.Date;
/**
 * 员工实体类
 * @author miao
 *
 */
public class Employee {

	private int id;
	private String name;
	private boolean sex;
	private Date birthday;
	private double salary;
	private int departId;

	public Employee() {
		super();
	}

	public Employee(int id, String name, boolean sex, Date birthday, double salary, int departId) {
		super();
		this.id = id;
		this.name = name;
		this.sex = sex;
		this.birthday = birthday;
		this.salary = salary;
		this.departId = departId;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public boolean isSex() {
		return sex;
	}

	public void setSex(boolean sex) {
		this.sex = sex;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	public double getSalary() {
		return salary;
	}

	public void setSalary(double salary) {
		this.salary = salary;
	}

	public int getDepartId() {
		return departId;
	}

	public void setDepartId(int departId) {
		this.departId = departId;
	}

	@Override
	public String toString() {
		return "Employee [id=" + id + ", name=" + name + ", sex=" + sex + ", birthday=" + birthday
				+ ", salary=" + salary + ", departId=" + departId + "]";
	}

}

 

三、dao接口

package org.e276.dao;

import java.util.List;
import java.util.Map;
import org.e276.entity.Employee;

/**
 * 实现所有的接口
 */
public interface EmployeeDao {

	// 查询所有的用户，使用RowMapper可映射多行数据
	public List<Employee> getAllEmployees();

	// 查询薪水在3000-5000之间的有条件查询，用RowCallbackHandler，也可以用RowMapper
	public List<Employee> getEmployeesBetweenSalary(double low, double up);

	// 女或男职员有多少个，单值数据查询
	public int getEmployeeCountBySex(boolean sex);

	// 一次插入多名职员
	public int[] saveEmployees(final Employee[] employees);

	// 删除员工
	public int deleteEmployee(int id);

	// 修改 e必须是final类型
	public int updateEmployee(final Employee e);

	// 根据ID得到职员，查询单值对象
	public Employee getEmployeeById(int id);

	// 查询工资小于money的员工
	public List<Employee> getEmployeeLessSalary(double money);

	// 多表链接查询，封装成Map
	public List<Map<String, Object>> findEmployeeInfo();

}

 

四、daoImpl实现类

package org.e276.dao.impl;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.e276.dao.EmployeeDao;
import org.e276.entity.Employee;
import org.e276.mapper.EmployeeMapper;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
/**
 * 实现类
 * @author miao
 *
 */
public class EmployeeDaoImpl extends NamedParameterJdbcDaoSupport implements EmployeeDao {

	/**
	 * 查询所有的用户，使用RowMapper可映射多行数据
	 */
	public List<Employee> getAllEmployees() {
		return super.getJdbcTemplate().query(
				"select id, name, sex, salary, birthday, depart_id from employee",
				new EmployeeMapper());
	}

	/**
	 * 查询薪水在3000-5000之间的有条件查询，用RowCallbackHandler，也可以用RowMapper
	 */
	public List<Employee> getEmployeesBetweenSalary(double low, double up) {
		return super
				.getJdbcTemplate()
				.query("select id, name, sex, salary, birthday, depart_id from employee  where salary between ? and ?",
						new EmployeeMapper(), low, up);
	}

	/**
	 * 女或男职员有多少个
	 */
	public int getEmployeeCountBySex(boolean sex) {
		return super.getJdbcTemplate()
				.queryForInt("select count(*) from employee where sex=?", sex);
	}

	/**
	 * 一次插入多名职员
	 */
	public int[] saveEmployees(final Employee[] employees) {
		String sql = "insert into employee(id, name, sex, salary, birthday, depart_id) values (emp_seq.nextval, ?, ?, ?, ?, ?)";
		return super.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int index) throws SQLException {
				Employee emp = employees[index];
				ps.setString(1, emp.getName());
				ps.setBoolean(2, emp.isSex());
				ps.setDouble(3, emp.getSalary());
				ps.setDate(4, emp.getBirthday());
				ps.setInt(5, emp.getDepartId());
			}

			@Override
			public int getBatchSize() {
				return employees.length;
			}
		});
	}

	/**
	 * 删除员工
	 */
	public int deleteEmployee(int id) {
		return super.getJdbcTemplate().update("delete employee where id = ?", id);
	}

	/**
	 * 修改员工 ， e必须是final类型
	 */
	public int updateEmployee(Employee employee) {
		return super.getNamedParameterJdbcTemplate().update(
				"update employee set name = :name, sex = :sex, salary = :salary where id = :id",
				new BeanPropertySqlParameterSource(employee));
	}

	/**
	 * 根据ID得到职员
	 */
	public Employee getEmployeeById(int id) {
		return super.getJdbcTemplate().queryForObject(
				"select id, name, sex, salary, birthday, depart_id from employee where id=?",
				new EmployeeMapper(), id);
	}

	/**
	 * 查询工资小于money的员工
	 */
	public List<Employee> getEmployeeLessSalary(double money) {
		return super.getJdbcTemplate().query(
				"select id, name, sex, salary, birthday, depart_id from employee where salary<?",
				new EmployeeMapper(), money);
	}

	/**
	 * 多表链接查询，封装成Map
	 */
	public List<Map<String, Object>> findEmployeeInfo() {
		String sql = "select department.name as dname, t.birthday as birthday, t.salary as salary, t.sex as sex, t.name as ename from employee t, department where department.id = t.depart_id";
		return super.getJdbcTemplate().queryForList(sql);
	}

}

 

五、RowMapper

package org.e276.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.e276.entity.Employee;
import org.springframework.jdbc.core.RowMapper;
/**
 * 实现RowMapper接口
 * @author miao
 *
 */
public class EmployeeMapper implements RowMapper<Employee> {

	@Override
	public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
		Employee employee = new Employee();
		employee.setId(rs.getInt("id"));
		employee.setName(rs.getString("name"));
		employee.setSex(rs.getBoolean("sex"));
		employee.setSalary(rs.getDouble("salary"));
		employee.setBirthday(rs.getDate("birthday"));
		employee.setDepartId(rs.getInt("depart_id"));
		return employee;
	}

}

 

六、Spring的配置文件

<?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:p="http://www.springframework.org/schema/p"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

	<!-- 数据源 -->
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver">
		</property>
		<property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl">
		</property>
		<property name="username" value="y2" />
		<property name="password" value="bdqn" />
	</bean>

	<!-- jdbcTemplate -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource" />
		<!-- 以下属性可选 -->
		<property name="queryTimeout" value="10" />
		<property name="maxRows" value="100" />
	</bean>
	
	<!-- dao -->
	<bean id="employeeDao" class="org.e276.dao.impl.EmployeeDaoImpl">
		<property name="jdbcTemplate" ref="jdbcTemplate" />
	</bean>

</beans>

 

七、测试类

package org.e276.test;

import java.sql.Date;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import org.e276.dao.EmployeeDao;
import org.e276.dao.impl.EmployeeDaoImpl;
import org.e276.entity.Employee;
import org.junit.After;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
/**
 * 测试类
 * @author miao
 *
 */
public class TestJDBC {

	// 上下文
	static ApplicationContext context;
	// 调用接口
	EmployeeDao employeeDao;

	@BeforeClass
	public static void setUpBeforeClass() throws Exception {
		context = new ClassPathXmlApplicationContext("applicationContext.xml");
	}

	@AfterClass
	public static void tearDownAfterClass() throws Exception {
		context = null;
	}

	@Before
	public void setUp() throws Exception {
		employeeDao = context.getBean(EmployeeDaoImpl.class);
	}

	@After
	public void tearDown() throws Exception {
		employeeDao = null;
	}

	/**
	 * 查询所有的用户
	 */
	@Test
	public void getAllEmployees() {
		List<Employee> employees = employeeDao.getAllEmployees();
		for (Employee employee : employees) {
			System.out.println(employee);
		}
	}

	/**
	 * 查询薪水在3000-5000之间的有条件查询
	 */
	public void getEmployeesBetweenSalary() {
		List<Employee> employees = employeeDao.getEmployeesBetweenSalary(3000d, 5000d);
		for (Employee employee : employees) {
			System.out.println(employee);
		}
	}

	/**
	 * 女或男职员有多少个
	 */
	public void getEmployeeCountBySex() {
		int men = employeeDao.getEmployeeCountBySex(true);
		System.out.println("男职员有：" + men + "个");
		int women = employeeDao.getEmployeeCountBySex(false);
		System.out.println("女职员有：" + women + "个");
	}

	/**
	 * 一次插入多名职员
	 */
	public void saveEmployees() {
		Employee[] employees = new Employee[] {
				new Employee(0, "小伙伴", true, new Date(43529434432L), 500d, 3),
				new Employee(0, "大伙伴", true, new Date(43522344432L), 2500d, 4),
				new Employee(0, "中伙伴", true, new Date(43529564432L), 1500d, 5) };
		int num[] = employeeDao.saveEmployees(employees);
		System.out.println(Arrays.toString(num));
	}

	/**
	 * 根据id删除员工
	 */
	public void deleteEmployee() {
		int num = employeeDao.deleteEmployee(1);
		if (num > 0) {
			System.out.println("删除成功！");
		} else {
			System.out.println("删除失败！");
		}
	}

	/**
	 * 根据id修改员工
	 */
	public void updateEmployee() {
		Employee employee = new Employee(3, "大PPPP孩", false, new Date(43529564432L), 3550d, 5);
		int count = employeeDao.updateEmployee(employee);
		if (count > 0) {
			System.out.println("更新成功！");
		} else {
			System.out.println("更新失败！");
		}
	}

	/**
	 * 根据ID得到职员
	 */
	public void getEmployeeById() {
		Employee employee = employeeDao.getEmployeeById(3);
		System.out.println("该职员是：" + employee);
	}

	/**
	 * 查询工资小于money的员工
	 */
	public void getEmployeeLessSalary() {
		List<Employee> employees = employeeDao.getEmployeeLessSalary(4000d);
		for (Employee employee : employees) {
			System.out.println("工资小于4000块的名单如下：");
			System.out.print("姓名：" + employee.getName() + "\t");
			System.out.println("工资：" + employee.getSalary() + "￥");
		}
	}

	/**
	 * 多表链接，封装成Map
	 */
	public void findEmployeeInfo() {
		List<Map<String, Object>> employees = employeeDao.findEmployeeInfo();
		for (Map<String, Object> emp : employees) {
			System.out.print(emp.get("ename") + "\t");
			System.out.print(emp.get("dname") + "\t");
			System.out.print(emp.get("sex") + "\t");
			System.out.print(emp.get("salary") + "\t");
			System.out.println(emp.get("birthday") + "\t");
			System.out.println("-------------------------------------------------------");
		}
	}
}

 

八、demo

Spring-JDBC.zip