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>
@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;