Spring JDBC

Spirng  JDBC

一：采用Maven 引入 jar:

<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>${spring.version}</version>
</dependency>
		

		<!-- MySQL database driver -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.15</version>
		</dependency>

引入 spring jdbc  和  mysql 驱动；

二：数据库中建表；采用 mysql数据库；

DROP TABLE IF EXISTS `stock`;
CREATE TABLE `stock` (
  `STOCK_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `STOCK_CODE` VARCHAR(10) NOT NULL,
  `STOCK_NAME` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`STOCK_ID`) USING BTREE,
  UNIQUE KEY `UNI_STOCK_NAME` (`STOCK_NAME`),
  UNIQUE KEY `UNI_STOCK_ID` (`STOCK_CODE`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

三：构建对应的类：

package com.myapp.core.jdbc;

public class Stock {
	private int stock_id;
	private String stock_name;
	private String stock_code;

	public int getStock_id() {
		return stock_id;
	}

	public void setStock_id(int stock_id) {
		this.stock_id = stock_id;
	}

	public String getStock_name() {
		return stock_name;
	}

	public void setStock_name(String stock_name) {
		this.stock_name = stock_name;
	}

	public String getStock_code() {
		return stock_code;
	}

	public void setStock_code(String stock_code) {
		this.stock_code = stock_code;
	}

}

四：构建对应的 dao

package com.myapp.core.jdbc;

public interface StockDao {
	 public  void  insert(Stock stock);
	 
	 public  Stock  findStockById(int id);
}

package com.myapp.core.jdbc;

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

import javax.sql.DataSource;

public class StockDaoImpl  implements StockDao{
 
	private  DataSource  dataSource;
	
	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	
	Connection  conn = null;
	PreparedStatement  prep = null;
	@Override
	public void insert(Stock stock) {
		// TODO Auto-generated method stub
		
	String  sql ="insert into  stock  values(?,?,?)";
	try{
		
		conn = dataSource.getConnection();
		
		prep = conn.prepareStatement(sql);
		prep.setInt(1, stock.getStock_id());
		prep.setString(2, stock.getStock_code());
		prep.setString(3, stock.getStock_name());
		
		boolean  flag = prep.execute();
		
		System.out.println(flag);
		
	}catch(Exception  e){
		e.printStackTrace();
	}finally{
		try{
			
			if(prep != null){
				prep.close();
			}
			if(conn != null){
				conn.close();
			}
		}catch(Exception  ex){
			ex.printStackTrace();
		}
	}
}
   
	@Override
	public Stock findStockById(int id) {
		// TODO Auto-generated method stub
		String  sql = "select * from  stock  where  stock_id = ?";
		ResultSet  rs = null;
		Stock  stock = null;
		try{
			
			conn = dataSource.getConnection();
			
			prep = conn.prepareStatement(sql);
			prep.setInt(1, id);
			rs = prep.executeQuery();
			
			stock = new  Stock();
			while(rs.next()){
				stock.setStock_id(id);
				stock.setStock_code(rs.getString(2));
				stock.setStock_name(rs.getString("stock_name"));
				
			}
			
		}catch(Exception  e){
			e.printStackTrace();
		}finally{
			try{
				
				if(prep != null){
					prep.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception  ex){
				ex.printStackTrace();
			}
		}
	  
		return  stock;
		
		
	}

}

五：对应的spring xml 配置注入

  <!-- setting  the  datasource -->
  
  <bean id="stockDao" class="com.myapp.core.jdbc.StockDaoImpl">
       <property name="dataSource" ref="dataSource" />
  </bean>
  
  
  
  
  <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/mybase" />
		<property name="username" value="root" />
		<property name="password" value="root" />
	</bean>

六：构建测试类：

package com.myapp.core.jdbc;

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

public class MainTest {
   public static void main(String[] args) {
	   
	ApplicationContext  context = new   ClassPathXmlApplicationContext("resource/datasources.xml");  
	
	 StockDao    stockDao =  (StockDao)context.getBean("stockDao");
	
	/* Stock  stock  = new  Stock();
	        stock.setStock_id(61);
	        stock.setStock_code("code1");
	        stock.setStock_name("name1");*/
	      
	 
	 // stockDao.insert(stock);
	 
	  Stock  stock =stockDao.findStockById(51);
	  System.out.println("id:"+stock.getStock_id()+","+stock.getStock_code()+","+stock.getStock_name());
	 
}
}

七：maven 编译 引入jar：

E:\workspace\Spring3Example>mvn  compile

E:\workspace\Spring3Example>mvn  eclipse:eclipse

以上是通过mvn编译，再转换为eclipse项目。

八：运行结果

id:51,code,name

从数据库中取出了相应的数据。

九：扩展：使用  PropertyPlaceholderConfigurer  通过占位符(placeholder)进行配置 jdbc连接

1：定义database.properties文件

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybase
jdbc.username=root
jdbc.password=root

2：通过配置文件定位database.properties文件的位置：

  <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <property name="location">
    <value>resource/database.properties</value>
    </property>
  </bean>

3：通过占位符配置数据源bean

 

  <bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="${jdbc.driverClassName}" />
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
	</bean>

4：测试结果：

id:51,code,name

一样可以查询出结果，测试成功，over

解决疑问：

为什么用

PropertyPlaceholderConfigurer类可以加载定位文件

PropertyPlaceholderConfigurer实现了接口BeanFactoryPostProcessor
所有实现了 BeanFactoryPostProcessor接口的实现类会在其他bean是创建之前完成加载和创建，如果一个对象实现了BeanFactoryPostProcessor接口，会在优先进行加载处理。
Application contexts can auto-detect BeanFactoryPostProcessor beans in their bean definitions and apply them before any other beans get created.
当spring 容器启动是时候，会自动检测那些bean实现了
BeanFactoryPostProcessor接口，进行优先处理。


关于 BeanFactoryPostProcessor的详细讲解 here