Spring 数据访问那些事儿（一）spring + jdbc

从本篇文章开始，将会陆续为大家介绍一些spring访问数据的方式，从简单直接的JDBC到JdbcTemplate，再到一些复杂的ORM框架（如mybatis、hibernate）。每篇文章都跟着一个简单实例。作为系列的开篇，先从spring与jdbc谈起，数据库使用mysql。

1.customer表

CREATE TABLE customer(
	CUST_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
	NAME varchar(100) NOT NULL,
	AGE int(10) unsigned NOT NULL,
	PRIMARY KEY(CUST_ID)
)ENGINE=InnoDB AUTO_INCREMENT=2 

2.通过eclipse创建maven项目，目录结构如下：

3.项目依赖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>spring-jdbc</groupId>
  <artifactId>spring-jdbc</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  
  <dependencies>
      	<!-- Spring Core -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>4.2.1.RELEASE</version>
		</dependency>
		<!-- Spring context -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>4.2.1.RELEASE</version>
		</dependency>
  		<!-- Spring JDBC -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>4.2.1.RELEASE</version>
		</dependency>	
		<dependency>
	  		<groupId>commons-dbcp</groupId>
	  		<artifactId>commons-dbcp</artifactId>
	  		<version>1.4</version>
	  	</dependency>
		<!-- mysql驱动包 -->  
        <dependency>  
            <groupId>mysql</groupId>  
            <artifactId>mysql-connector-java</artifactId>  
            <version>5.1.29</version>  
        </dependency>  
  </dependencies>
</project>

4.model层

创建一个与customer表对应的实体类，以便存放 Customer对象

package org.thinkingingis.model;

public class Customer {
	private int id;
	private String name;
	private int age;
	
	public Customer(int id, String name, int age) {
		this.id = id;
		this.name = name;
		this.age = age;
	}

	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 int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}
}

5.DAO层

即数据访问对象层，负责实际与数据库打交道。

CustomerDAO

package org.thinkingingis.dao;

import org.thinkingingis.model.Customer;

public interface CustomerDAO {
	public void insert(Customer customer);
	public Customer findByCustomerId(int custId);
}

JdbcCustomerDAO

package org.thinkingingis.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.thinkingingis.dao.CustomerDAO;
import org.thinkingingis.model.Customer;

public class JdbcCustomerDAO implements CustomerDAO {
	
	private DataSource dataSource;
	public void setDataSource(DataSource dataSource){
		this.dataSource = dataSource;
	}
	
	@Override
	public void insert(Customer customer) {
		
		String sql = "INSERT INTO CUSTOMER (CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
		Connection conn = null;
		
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, customer.getId());
			ps.setString(2, customer.getName());
			ps.setInt(3, customer.getAge());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					
					e.printStackTrace();
				}
			}
		}
		
	}

	@Override
	public Customer findByCustomerId(int custId) {
		String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
		Connection conn = null;
		
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, custId);
			Customer customer = null;
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				customer = new Customer(
						rs.getInt("CUST_ID"), 
						rs.getString("NAME"), 
						rs.getInt("age")
				);
			}
			rs.close();
			ps.close();
			return customer;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					
					e.printStackTrace();
				}
			}
		}
		
		
	}

}

6.通过xml文件方式配置数据源与bean

这里将数据源配置与customerDAO的bean分离了，也可以将它们配置在一个文件中，后面的文章会给大家带来不同的配置方式。

Spring-Module.xml

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-4.0.xsd">

	<import resource="database/Spring-Datasource.xml" />
	<import resource="customer/Spring-Customer.xml" />

</beans>

Spring-Datasource.xml

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-4.0.xsd">

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/springmvcjdbc" />
		<property name="username" value="root" />
		<property name="password" value="123456" />
	</bean>

</beans>

Spring-Customer.xml

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-4.0.xsd">

	<bean id="customerDAO" class="org.thinkingingis.dao.impl.JdbcCustomerDAO">
		<property name="dataSource" ref="dataSource" />
	</bean>

</beans>

这里指定了 JdbcCustomerDAO spring核心容器在需要时会自动进行装配。

7.controller层 App.java

package org.thinkingingis.controller;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.thinkingingis.dao.CustomerDAO;
import org.thinkingingis.model.Customer;

public class App {
	public static void main(String[] args){
		ApplicationContext context = new ClassPathXmlApplicationContext("Spring-Module.xml");
		CustomerDAO customerDao = (CustomerDAO) context.getBean("customerDAO");
		Customer customer = new Customer(4, "can", 27);
		customerDao.insert(customer);
		
		Customer findCustomer = customerDao.findByCustomerId(1);
		System.out.println(findCustomer.getId());
		System.out.println(findCustomer.getName());
		System.out.println(findCustomer.getAge());
	}
}

8.结果截图

至此，通过spring + jdbc的方式访问数据库就完成啦，这种方式相比于直接通过jdbc访问数据库并没有简化操作，过程依然很冗长，下一篇将会为大家带来通过spring + JdbcTemplate的方式访问数据库。

（如遇到问题，请留言给作者，以便共同探讨gis知识。thinkingingis@qq.com）

Wechat公众号：ThinkingInGIS

欢迎大家关注：）