spring學習之jdbcTemplate

一、導入相應的jar包

在pom.xml中添加jar包的依賴：

<!-- https://mvnrepository.com/artifact/c3p0/c3p0 -->
		<dependency>
			<groupId>c3p0</groupId>
			<artifactId>c3p0</artifactId>
			<version>0.9.1.2</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>4.3.14.RELEASE</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.39</version>
		</dependency>
           

二、配置資料庫連接配接池

編寫db.properties檔案

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
jdbc.username=root
jdbc.password=123456
           

編寫applicationContext.xml配置檔案

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	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.xsd
        http://www.springframework.org/schema/aop
    	http://www.springframework.org/schema/aop/spring-aop.xsd">
	
	<!-- 讀取properties檔案 -->
	<context:property-placeholder location="classpath:db.properties" />
	<!-- 開啟spring注解 掃描 -->
	<context:component-scan base-package="com.chenchao" />
	<!-- 配置資料庫連接配接池 -->
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="${jdbc.driver}" />
		<property name="jdbcUrl" value="${jdbc.url}" />
		<property name="user" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
	</bean>
	<!-- 配置spring jdbcTemplate -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource" />
	</bean>
</beans>
           

三、編寫pojo

public class Dept {
	private int deptno;
	private String name;
	private String loc;

	public int getDeptno() {
		return deptno;
	}

	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}

	public String getName() {
		return name;
	}

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

	public String getLoc() {
		return loc;
	}

	public void setLoc(String loc) {
		this.loc = loc;
	}

	@Override
	public String toString() {
		return "Dept [deptno=" + deptno + ", name=" + name + ", loc=" + loc
				+ "]";
	}
}
           

四、編寫dao

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.chenchao.bean.Dept;
import com.chenchao.rowMapper.DeptRowMapper;

@Repository
public class DeptDao {

	@Autowired
	JdbcTemplate jdbcTemplate;
	
	public void findAll() {
		String sql = "select * from dept";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);	
		System.out.println(list);
	}
	
	public Dept findByDpetno(int deptno) {
		String sql = "select * from dept where deptno = ?";
		Dept dept = jdbcTemplate.queryForObject(sql, new DeptRowMapper(), 1);
		System.out.println(dept);
		return dept;
	}
}
           

增删改都是使用update()方法，查詢主要使用queryForList()和queryForObject()方法，queryForList傳回一個List集合，集合中存放的是map對象，map對象又對應着一個實體類對象；使用queryForObject()時需要編寫一個結果映射的類，該類實作RowMapper接口，在實作類中将結果集映射成相應的對象。

五、編寫RowMapper接口實作類

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

import org.springframework.jdbc.core.RowMapper;

import com.chenchao.bean.Dept;

public class DeptRowMapper implements RowMapper<Dept>{

	@Override
	public Dept mapRow(ResultSet rs, int rowNum) throws SQLException {
		Dept dept = new Dept();
		int deptno = rs.getInt("deptno");
		String name = rs.getString("dname");
		String loc = rs.getString("loc");
		dept.setDeptno(deptno );
		dept.setName(name);
		dept.setLoc(loc);
		return dept;
	}
}
           

六、測試

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.chenchao.dao.DeptDao;

public class DeptDaoTest {

	public static void main(String[] args) {
	
		ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
		DeptDao deptDao = context.getBean(DeptDao.class);
		//deptDao.findAll();
		deptDao.findByDpetno(1);
	}

}