Spring JdbcTemplate实践

Spring JdbcTemplate类是Spring提供的简化数据库操作的一个类，这个类使用了模板方法模式，简化了原生态JDBC的流程，结合了注解使用，更加灵活操作SQL。

使用Spring jdbcTemplate实现数据库操作步骤：

1、引入jar包：

commons-collections-3.2.1.jar
commons-dbcp-1.4.jar
commons-lang-2.5.jar
commons-pool-1.6.jar
ojdbc6.jar（Oracle连接数据库包）
org.springframework.beans-3.1.2.RELEASE.jar
org.springframework.context-3.1.2.RELEASE.jar
org.springframework.core-3.1.2.RELEASE.jar
org.springframework.jdbc-3.1.2.RELEASE.jar
org.springframework.transaction-3.1.2.RELEASE.jar

2、编辑操作Java类：

AppletFile.java

public class AppletFile implements Serializable {

	private static final long serialVersionUID = 79020313609643208L;

	private int A_Id;// 路径ID
	private String A_FileName;// 文件名
	private String A_FilePath;// 文件路径
	private String A_Creater;// 创建人
	private Date A_Date;

	public AppletFile() {
		super();
	}

	public AppletFile(int a_Id, String a_FileName, String a_FilePath, String a_Creater, Date a_Date) {
		super();
		A_Id = a_Id;
		A_FileName = a_FileName;
		A_FilePath = a_FilePath;
		A_Creater = a_Creater;
		A_Date = a_Date;
	}

	public int getA_Id() {
		return A_Id;
	}

	public void setA_Id(int a_Id) {
		A_Id = a_Id;
	}

	public String getA_FileName() {
		return A_FileName;
	}

	public void setA_FileName(String a_FileName) {
		A_FileName = a_FileName;
	}

	public String getA_FilePath() {
		return A_FilePath;
	}

	public void setA_FilePath(String a_FilePath) {
		A_FilePath = a_FilePath;
	}

	public String getA_Creater() {
		return A_Creater;
	}

	public void setA_Creater(String a_Creater) {
		A_Creater = a_Creater;
	}

	public Date getA_Date() {
		return A_Date;
	}

	public void setA_Date(Date a_Date) {
		A_Date = a_Date;
	}

	@Override
	public String toString() {
		return "AppletFile [A_Id=" + A_Id + ", A_FileName=" + A_FileName + ", A_FilePath=" + A_FilePath + ", A_Creater="
				+ A_Creater + ", A_Date=" + A_Date + "]";
	}

}

AppletFileDao接口：

public interface AppletFileDao {
	/**
	 * 增加文件
	 * 
	 * @param appletFile
	 */
	public void addFile(AppletFile appletFile);

	/**
	 * 修改文件
	 * 
	 * @param appletFile
	 */
	public boolean editFile(AppletFile appletFile);
	
	/**
	 * 查询数据
	 * 
	 * @param A_Id：主键ID
	 * @return
	 */
	public AppletFile findFile(int A_Id);

}

AppletFileDao的实现：AppletFileDaoImpl

@Repository
public class AppletFileDapImpl implements AppletFileDao {

	private JdbcTemplate jdbcTemplate;

	@Override
	public void addFile(AppletFile appletFile) {
		jdbcTemplate.update("insert into AppletFile values(?,?,?,?,?)",
				new Object[] { appletFile.getA_Id(), appletFile.getA_FileName(), appletFile.getA_FilePath(),
						appletFile.getA_Creater(), appletFile.getA_Date() },
				new int[] { Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.DATE });

	}

	@Override
	public boolean editFile(AppletFile appletFile) {
		try {
			jdbcTemplate.update("update AppletFile set A_FileName = ? where A_Id = ?",
					new Object[] { appletFile.getA_FileName(), appletFile.getA_Id() },
					new int[] { Types.VARCHAR, Types.INTEGER });
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	@Override
	public AppletFile findFile(int A_Id) {
		try {
			return (AppletFile) jdbcTemplate.queryForObject("select * from AppletFile where A_Id = ?",
					new Object[] { A_Id }, new int[] { Types.INTEGER }, new RowMapper<AppletFile>() {
						public AppletFile mapRow(ResultSet rs, int arg1) throws SQLException {
							AppletFile student = new AppletFile(rs.getInt(1), rs.getString(2), rs.getString(3),
									rs.getString(4), rs.getDate(5));
							return student;
						}
					});
		}
		// 根据Id查询信息抛异常, 不管什么原因, 认为查询不到该信息, 返回null
		catch (DataAccessException e) {
			return null;
		}
	}
	//JdbcTemplate的get和set方法
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

AppletFileService接口：

public interface AppletFileService {
	/**
	 * 增加文件
	 * 
	 * @param appletFile
	 */
	public void addFile(AppletFile appletFile);

	/**
	 * 修改文件
	 * 
	 * @param appletFile
	 */
	public boolean editFile(AppletFile appletFile);

	/**
	 * 查询数据
	 * 
	 * @param A_Id：主键ID
	 * @return
	 */
	public AppletFile findFile(int A_Id);
}

AppletService实现方法：AppletServiceImpl

@Service
public class AppletServiceImpl implements AppletFileService {

	@Autowired
	private AppletFileDao appeltDao;

	@Override
	public void addFile(AppletFile appletFile) {
		appeltDao.addFile(appletFile);
	}

	@Override
	public boolean editFile(AppletFile appletFile) {
		return appeltDao.editFile(appletFile);
	}

	@Override
	public AppletFile findFile(int A_Id) {
		return appeltDao.findFile(A_Id);
	}

}

4、在classpath目录下引入Spring.xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:p="http://www.springframework.org/schema/p"
	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-3.2.xsd
	http://www.springframework.org/schema/mvc
	http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd">

	<context:component-scan base-package="com.wild.service" />
	<context:component-scan base-package="com.wild.dao" />

	<!-- 配置数据源 -->
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close" p:driverClassName="oracle.jdbc.driver.OracleDriver"
		p:url="jdbc:oracle:thin:@//数据库地址" p:username="用户名"
		p:password="密码" />
		
	<!-- 配置jdbcTemplate模板 -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	<!-- 定义dao的实现 -->
	<bean id="appletFileDapImpl" class="com.wild.dao.impl.AppletFileDapImpl">
		<!-- 给jdbcTemplate植入引用 -->
		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
	</bean>
</beans>

5、Juit的Test类

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:resource/Spring.xml")
public class ConnTest {
	@Autowired
	private DataSource dataSource;

	@Autowired
	private AppletFileService appletFileService;

	@Test
	public void testConn() {
		Connection conn;
		try {
			conn = dataSource.getConnection();
			assertNotNull(conn);
			System.out.println(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Test
	public void TestData() {
		AppletFile appletFile = new AppletFile();
		appletFile.setA_Id(10000);
//		appletFile.setA_FileName("你好啊，中国！");
//		appletFile.setA_FilePath("23.5°");
//		appletFile.setA_Creater("WildInfo");
//		appletFile.setA_Date(new Date());
//		appletFileService.addFile(appletFile);
		System.out.println("文件信息"+appletFileService.findFile(appletFile.getA_Id()));
	}

}

6、数据库文件：

create table AppletFile(
       A_id int primary key,
       A_FileName varchar2(100),
       A_FilePath varchar2(100),
       A_Creater varchar2(100),
       A_Date Date
);
select * from AppletFile where A_Id = 10000

select * from AppletFile;
insert into AppletFile values(10000,'E://Test.doc','E','SN56895',to_date('2017-10-20 10:20:20','yyyy-MM-dd HH24:ss:MI'));



commit;