用Spring的JDBC直接操作数据库

开发工具为myeclipse10，数据库用的mysql5.5.24.

首先新建一个项目，然后新建两个user library:一个是spring,另一个是mysql驱动。

如图：

建好用户库后，然后将其添加到项目中，添加方法为：新建项目右键->buildpath->add libraries->user library，然后选择要添加的库添加即可。

添加好后如下图：

接下来建立数据库表，字段如下图:

数据库表建好后便开始建对应的class，如下：

package edu.xaut.jzd.spring;

public class Vehicle {
	private String vehicleNo;
	private String color;
	private int wheel;
	private int seat;
	
	public Vehicle() {
	}

	/**
	 * @param vehicleNo
	 * @param color
	 * @param wheel
	 * @param seat
	 */
	public Vehicle(String vehicleNo, String color, int wheel, int seat) {
		this.vehicleNo = vehicleNo;
		this.color = color;
		this.wheel = wheel;
		this.seat = seat;
	}

	/**
	 * @return the vehicleNo
	 */
	public String getVehicleNo() {
		return vehicleNo;
	}

	/**
	 * @param vehicleNo the vehicleNo to set
	 */
	public void setVehicleNo(String vehicleNo) {
		this.vehicleNo = vehicleNo;
	}

	/**
	 * @return the color
	 */
	public String getColor() {
		return color;
	}

	/**
	 * @param color the color to set
	 */
	public void setColor(String color) {
		this.color = color;
	}

	/**
	 * @return the wheel
	 */
	public int getWheel() {
		return wheel;
	}

	/**
	 * @param wheel the wheel to set
	 */
	public void setWheel(int wheel) {
		this.wheel = wheel;
	}

	/**
	 * @return the seat
	 */
	public int getSeat() {
		return seat;
	}

	/**
	 * @param seat the seat to set
	 */
	public void setSeat(int seat) {
		this.seat = seat;
	}
}

然后建立对表进行基本操作的DAO，如下：

/**
 * author:xautjzd
 * time:2013-1-28
 * function:定义对vehicle的增删查改接口
 */

package edu.xaut.jzd.spring;

public interface VehicleDao {
	public void create(Vehicle vehicle);     
	public void edit(Vehicle vehicle);
	public void delete(String vehicleNo);
	public Vehicle findByVehicleNo(String vehicleNo);
}

对DAO接口进行实现，如下：

package edu.xaut.jzd.spring;

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

import javax.sql.DataSource;

/**
 * @author administrator
 * 
 */
public class VehicleDaoImpl implements VehicleDao {
	private DataSource dataSource;

	/**
	 * @param dataSource
	 */
/*	public VehicleDaoImpl(DataSource dataSource) {
		this.dataSource = dataSource;
	}*/

	/**
	 * @param dataSource
	 *            the dataSource to set
	 */
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void create(Vehicle vehicle) {
		// TODO Auto-generated method stub
		String sql = "insert into vehicle values(?,?,?,?)";
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, vehicle.getVehicleNo());
			ps.setString(2, vehicle.getColor());
			ps.setInt(3, vehicle.getWheel());
			ps.setInt(4, vehicle.getSeat());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			// TODO: handle exception
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO: handle exception
					e.getMessage();
				}
			}
		}
	}

	@Override
	public void edit(Vehicle vehicle) {
		// TODO Auto-generated method stub
		String sql = "update vehicle set color=?,wheel=?,seat=? where vehicleNo=?";
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, vehicle.getColor());
			ps.setInt(2, vehicle.getWheel());
			ps.setInt(3, vehicle.getSeat());
			ps.setString(4, vehicle.getVehicleNo());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			// TODO: handle exception
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO: handle exception
					e.getMessage();
				}
			}
		}
	}

	@Override
	public void delete(String vehicleNo) {
		String sql = " delete from vehicle where vehicleNo=?";
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, vehicleNo);
			
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			// TODO: handle exception
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO: handle exception
					e.getMessage();
				}
			}
		}
	}

	@Override
	public Vehicle findByVehicleNo(String vehicleNo) {
		// TODO Auto-generated method stub
		String sql = "select * from vehicle where vehicleNo=?";
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, vehicleNo);
			
			Vehicle vehicle=null;
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				vehicle=new Vehicle(rs.getString(1),rs.getString(2),rs.getInt(3),rs.getInt(4));
			}
			rs.close();
			ps.close();
			return vehicle;
		} catch (SQLException e) {
			// TODO: handle exception
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO: handle exception
					e.getMessage();
				}
			}
		}
	}
}

接下来便进行配置工作了，新建一个beans.xml文件，内容如下：

<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="hello" class="edu.xaut.jzd.spring.Hello" >
       		<property name="message">
       			<value>How are you?</value>
       		</property>
       </bean>
       <bean id="hello1" class="edu.xaut.jzd.spring.Hello" p:message="Good morning" autowire="byName"/>
       <bean class="org.springframework.beans.factory.annotation.RequiredAnnotationBeanPostProcessor"></bean> -->
       <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
       		<property name="driverClassName">
       			<value>com.mysql.jdbc.Driver</value>
       		</property>
       		<property name="url">
       			<value>jdbc:mysql://localhost:3306/vehicle</value>
       		</property>
       		<property name="username">
       			<value>root</value>
       		</property>
       		<property name="password">
       			<value>yourpassword</value>
       		</property>
       </bean>
       
       <bean id="vehicleDao" class="edu.xaut.jzd.spring.VehicleDaoImpl" autowire="byType"></bean>
</beans>

最后新建一个测试类，看能对数据库进行增删查改。测试类如下：

package edu.xaut.jzd.spring;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
//import org.springframework.context.support.FileSystemXmlApplicationContext;

public class Main {
	public static void main(String[] args){
		ApplicationContext context=new ClassPathXmlApplicationContext("beans.xml");
		
		VehicleDao vehicleDao=(VehicleDao)context.getBean("vehicleDao");
		Vehicle vehicle=new Vehicle("0006","red",4,4);
		vehicleDao.create(vehicle);
		
		vehicle=vehicleDao.findByVehicleNo("0006");
		System.out.println("VehicleNo: "+vehicle.getVehicleNo());
	}
}