Spring 之 DAO JdbcTemplate工具的使用

mysql.sql

USE `test`;
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `EMPNO` int(11) NOT NULL AUTO_INCREMENT,
  `ENAME` varchar(30) DEFAULT NULL,
  `JOB` varchar(30) DEFAULT NULL,
  `MGR` int(11) DEFAULT NULL,
  `HIREDATE` varchar(30) DEFAULT NULL,
  `SAL` int(11) DEFAULT NULL,
  `DEPTNO` int(11) DEFAULT NULL,
  `COMM` int(11) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB AUTO_INCREMENT=7935 DEFAULT CHARSET=utf8;

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>cn.et</groupId>
  <artifactId>SpringJDBC</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <dependencies>
  	<!-- 数据库驱动 -->
  	<dependency>
	  <groupId>mysql</groupId>
	  <artifactId>mysql-connector-java</artifactId>
	  <version>5.1.26</version>
	</dependency>
  	<!-- Spring 提供JDBC模板 -->
  	<dependency>
	    <groupId>org.springframework</groupId>
	    <artifactId>spring-jdbc</artifactId>
	    <version>4.3.9.RELEASE</version>
	</dependency>
	<dependency>
	    <groupId>org.springframework</groupId>
	    <artifactId>spring-beans</artifactId>
	    <version>4.3.9.RELEASE</version>
	</dependency>
	<dependency>
	    <groupId>org.springframework</groupId>
	    <artifactId>spring-context</artifactId>
	    <version>4.3.9.RELEASE</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
	    <artifactId>spring-aop</artifactId>
	    <version>4.3.9.RELEASE</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
	    <artifactId>spring-tx</artifactId>
	    <version>4.3.9.RELEASE</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
	    <artifactId>spring-web</artifactId>
	    <version>4.3.9.RELEASE</version>
	</dependency>
	<dependency>
	    <groupId>org.springframework</groupId>
	    <artifactId>spring-aspects</artifactId>
	    <version>4.3.9.RELEASE</version>
	</dependency>
	<dependency>
	    <groupId>org.apache.logging.log4j</groupId>
	    <artifactId>log4j-core</artifactId>
	    <version>2.2</version>
	</dependency>
	<!-- JUnit Java语言的单元测试框架 -->
	<dependency>
	    <groupId>junit</groupId>
	    <artifactId>junit</artifactId>
	    <version>4.12</version>
	    <scope>test</scope>
	</dependency>
  </dependencies>
  <build>
  	<plugins>
  		<!-- 指定JDK版本和编码的插件 -->
  		<plugin>
  			<groupId>org.apache.maven.plugins</groupId>
  			<artifactId>maven-compiler-plugin</artifactId>
  			<configuration>
  				<source>1.7</source>
				<target>1.7</target>
				<encoding>UTF-8</encoding>
  			</configuration>
  		</plugin>
  	</plugins>
  </build>
</project>

package cn.et;

public class Employee {
	//EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `DEPTNO`
	private String empno;
	private String ename;
	private String job;
	private String mgr;
	private String hiredate;
	private String sal;
	private String deptno;
	private String comm;
	public String getEmpno() {
		return empno;
	}
	public void setEmpno(String empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public String getMgr() {
		return mgr;
	}
	public void setMgr(String mgr) {
		this.mgr = mgr;
	}
	public String getHiredate() {
		return hiredate;
	}
	public void setHiredate(String hiredate) {
		this.hiredate = hiredate;
	}
	public String getSal() {
		return sal;
	}
	public void setSal(String sal) {
		this.sal = sal;
	}
	public String getDeptno() {
		return deptno;
	}
	public void setDeptno(String deptno) {
		this.deptno = deptno;
	}
	public String getComm() {
		return comm;
	}
	public void setComm(String comm) {
		this.comm = comm;
	}
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((comm == null) ? 0 : comm.hashCode());
		result = prime * result + ((deptno == null) ? 0 : deptno.hashCode());
		result = prime * result + ((empno == null) ? 0 : empno.hashCode());
		result = prime * result + ((ename == null) ? 0 : ename.hashCode());
		result = prime * result + ((hiredate == null) ? 0 : hiredate.hashCode());
		result = prime * result + ((job == null) ? 0 : job.hashCode());
		result = prime * result + ((mgr == null) ? 0 : mgr.hashCode());
		result = prime * result + ((sal == null) ? 0 : sal.hashCode());
		return result;
	}
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		Employee other = (Employee) obj;
		if (comm == null) {
			if (other.comm != null)
				return false;
		} else if (!comm.equals(other.comm))
			return false;
		if (deptno == null) {
			if (other.deptno != null)
				return false;
		} else if (!deptno.equals(other.deptno))
			return false;
		if (empno == null) {
			if (other.empno != null)
				return false;
		} else if (!empno.equals(other.empno))
			return false;
		if (ename == null) {
			if (other.ename != null)
				return false;
		} else if (!ename.equals(other.ename))
			return false;
		if (hiredate == null) {
			if (other.hiredate != null)
				return false;
		} else if (!hiredate.equals(other.hiredate))
			return false;
		if (job == null) {
			if (other.job != null)
				return false;
		} else if (!job.equals(other.job))
			return false;
		if (mgr == null) {
			if (other.mgr != null)
				return false;
		} else if (!mgr.equals(other.mgr))
			return false;
		if (sal == null) {
			if (other.sal != null)
				return false;
		} else if (!sal.equals(other.sal))
			return false;
		return true;
	}
	@Override
	public String toString() {
		return "Employee [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate="
				+ hiredate + ", sal=" + sal + ", deptno=" + deptno + ", comm=" + comm + "]";
	}
}

package cn.et;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class JdbcTemplateCRUD{
	static JdbcTemplate jdbcTemplate;
	static{
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://localhost/test");
		dataSource.setUsername("root");
		dataSource.setPassword("123456");
		jdbcTemplate = new JdbcTemplate(dataSource);
	}
	@Test
	public void addEmployee(){
		System.out.println(jdbcTemplate);
		//"INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `DEPTNO`) VALUES ('7878', 'LUCHENG', 'CLERK', '7782', '2010-01-01', '15000', '10')";
		String sql = "INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `DEPTNO`) VALUES (?, ?, ?, ?, ?, ?, ?)";
		int rows = jdbcTemplate.update(sql,"7878","LUCHENG","CLERK","7782","2010-01-01","15000","10");
		System.out.println("受影响的记录条数"+rows);
	}
	
	@Test
	public void updateEmployee() {
		//"UPDATE `emp` SET `ENAME` = 'LU' WHERE `EMPNO` = '7878'" 
		String sql = "UPDATE `emp` SET `ENAME` = ?,`SAL` = ? WHERE `EMPNO` = ?";
		int rows = jdbcTemplate.update(sql,"LU","12000","7878");
		System.out.println("受影响的记录条数"+rows);
	}
	
	@Test
	public void deleteEmployee() {
		//"DELETE FROM `emp` WHERE `EMPNO` = '7878'" 
		String sql = "DELETE FROM `emp` WHERE `EMPNO` = ?";
		int rows = jdbcTemplate.update(sql,"7878");
		System.out.println("受影响的记录条数"+rows);
	}
	

	@Test
	public void seleteEmployeeObject() {
		//"SELECT * FROM emp WHERE EMPNO = 7878"
		String sql = "SELECT * FROM emp WHERE EMPNO = ?";
		Employee employee = jdbcTemplate.queryForObject(sql,new MyRowMapper(),"7878");
		System.out.println(employee);
	}
	
	@Test
	public void seleteEmployeeList() {
		String sql = "SELECT * FROM emp";
		List<Employee> list = jdbcTemplate.query(sql,new MyRowMapper());
		for(Employee e : list) {
			System.out.println(e);
		}
	}
}

class MyRowMapper implements RowMapper<Employee> {
	@Override
	public Employee mapRow(ResultSet rs, int arg1) throws SQLException {
		String ename = rs.getString("ENAME");
		String empno = rs.getString("EMPNO");
		String job = rs.getString("JOB");
		String mgr = rs.getString("MGR");
		String hiredate = rs.getString("HIREDATE");
		String sal = rs.getString("SAL");
		String deptno = rs.getString("DEPTNO");
		String comm = rs.getString("COMM");
		Employee employee = new Employee();
		employee.setEname(ename);
		employee.setComm(comm);
		employee.setDeptno(deptno);
		employee.setEmpno(empno);
		employee.setHiredate(hiredate);
		employee.setJob(job);
		employee.setSal(sal);
		employee.setMgr(mgr);
		return employee;
	}
}