文章地址: http://blog.csdn.net/5iasp/article/details/12206793
作者： javaboy2012
Email：yanek@163.com
qq: 1046011462
使用spring jdbc template简化jdbc数据库操作实例代码
包括如下几个类：
1. DAO接口
package com.test;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
public interface DAO {
public int getCount(String sql);
public String getResultValue(String sql, String column);
public List getResult(String sql);
public void update(String sql);
public void update(String sql,Object[] params);
public void delete(String sql);
public JdbcTemplate getJt();
}
2. DAO接口实现类 DAOImpl
package com.test;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
public class DAOImpl implements DAO{
protected final Log log = LogFactory.getLog(this.getClass());
private JdbcTemplate jt;
public int getCount(String sql) {
int count = 0;
try {
count = jt.queryForInt(sql);
} catch (DataAccessException e) {
log.info("!!!!!!!!!!!!!!!!!!!Exception:" + e);
}
return count;
}
public String getResultValue(String sql, String column) {
String value = "";
try {
SqlRowSet s = jt.queryForRowSet(sql);
while (s.next()){
value = s.getString(column);
}
} catch (DataAccessException e) {
log.info("!!!!!!!!!!!!!!!!!!!Exception:" + e);
}
return value;
}
public List getResult(String sql) {
List list = null;
try {
list = jt.queryForList(sql);
} catch (DataAccessException e) {
log.info("!!!!!!!!!!!!!!!!!!!Exception:" + e);
}
return list;
}
public void update(String sql) {
try {
jt.update(sql);
} catch (DataAccessException e) {
log.info("!!!!!!!!!!!!!!!!!!!Exception:" + e);
}
}
public void delete(String sql) {
try {
jt.execute(sql);
} catch (DataAccessException e) {
log.info("!!!!!!!!!!!!!!!!!!!Exception:" + e);
}
}
@Override
public void update(String sql, Object[] params) {
// TODO Auto-generated method stub
try {
jt.update(sql,params);
} catch (DataAccessException e) {
log.info("!!!!!!!!!!!!!!!!!!!Exception:" + e);
}
}
public void setJt(JdbcTemplate jt) {
this.jt = jt;
}
public JdbcTemplate getJt() {
return jt;
}
}
3. UserManager 接口
package com.test;
import java.util.List;
import java.util.Map;
public interface UserManager {
public void addUser(String name);
public void updateUser(String name,int id);
public void deleteUser(int id);
public String getUser(int id);
public User getUserByID(int id);
public List getUsers();
public List<User> getUserList();
public void init();
}
4. UserManagerImpl：UserManager 接口实现类
package com.test;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.RowMapper;
public class UserManagerImpl implements UserManager {
private DAO dao;
/**
* @param args
*/
public static void main(String[] args) {
}
public void addUser(String name)
{
/*
String sql="insert into t_test(name) values('"+name+"')";
dao.update(sql);
*/
String sql="insert into t_test(name) values(?)";
Object[] params = new Object[] {name};
dao.update(sql, params);
}
public List getUsers()
{
String sql = "select * FROM t_test";
List<Map> lists = dao.getResult(sql);
return lists;
}
public DAO getDao() {
return dao;
}
public void setDao(DAO dao) {
this.dao = dao;
}
@Override
public void init() {
// TODO Auto-generated method stub
}
@Override
public void updateUser(String name, int id) {
/*
String sql="update t_test set name='"+name+"' where id="+id;
dao.update(sql);
*/
/*
String sql="update t_test set name=? where id="+id;
Object[] params = new Object[] {name};
*/
String sql="update t_test set name=? where id=?";
Object[] params = new Object[] {name,new Integer(id)};
dao.update(sql, params);
}
@Override
public void deleteUser(int id) {
String sql="delete from t_test where id="+id;
dao.delete(sql);
}
@Override
public String getUser(int id) {
// TODO Auto-generated method stub
String sql="select name from t_test where id="+id;
String name=dao.getResultValue(sql, "name");
return name;
}
@Override
public User getUserByID(int id) {
User user=null;
String sql="select id,name from t_test where id="+id;
List<Map> lists = dao.getResult(sql);
if (lists.size()>0)
{
user=new User();
Map map=lists.get(0);
user.setId((Integer)map.get("id"));
user.setName((String)map.get("name"));
}
return user;
}
@Override
public List<User> getUserList() {
String sql = "select * FROM t_test";
List<Map> lists = dao.getResult(sql);
List<User> users=new ArrayList<User>();
if (lists.size()>0)
{
for(int i=0;i<lists.size();i++)
{
User user=new User();
Map map=lists.get(i);
user.setId((Integer)map.get("id"));
user.setName((String)map.get("name"));
users.add(user);
}
}
return users;
}
}
5. User类：实体类
package com.test;
public class User {
private String name;
private int id;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
}
6. SpringUtil ： 工具类
package com.test;
import java.util.List;
import java.util.Map;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
/**
* spring工具类
* @author
*
*/
public class SpringUtil {
/**
* @param args
*/
public static void main(String[] args) {
UserManager um= (UserManager)SpringUtil.getBean("userManager");
List<Map> users=um.getUsers();
for(int i=0;i<users.size();i++)
{
String t_id=users.get(i).get("id").toString();
String t_name=users.get(i).get("name").toString();
System.out.println(t_id+"-"+t_name);
}
}
private static ApplicationContext ctx = new ClassPathXmlApplicationContext(
"applicationContext.xml");
public static Object getBean(String beanName) {
return ctx.getBean(beanName);
}
}
7. 测试类： Test
package com.test;
import java.util.List;
import java.util.Map;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class Test {
/**
* @param args
*/
public static void main(String[] args) {
/*
ApplicationContext ctx = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
UserManager um=(UserManager)ctx.getBean("userManager");
*/
UserManager um= (UserManager)SpringUtil.getBean("userManager");
//新增
/*
String name="test";
um.addUser(name);
*/
//列表
List<Map> users=um.getUsers();
for(int i=0;i<users.size();i++)
{
String t_id=users.get(i).get("id").toString();
String t_name=users.get(i).get("name").toString();
System.out.println(t_id+"-"+t_name);
}
//修改
//um.updateUser("test6", 6);
//删除
//um.deleteUser(1);
//获取某个字段
//String name2=um.getUser(2);
//System.out.println(name2+"-"+name2);
// 获取对象列表
List<User> users2=um.getUserList();
for(int i=0;i<users2.size();i++)
{
int t_id2=users2.get(i).getId();
String t_name2=users2.get(i).getName();
System.out.println(t_id2+"-"+t_name2);
}
//获取对象
User u=um.getUserByID(2);
System.out.println(u.getId()+"-"+u.getName());
}
}
8. Spring 配置文件：applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<!-- DB -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName">
<value>com.mysql.jdbc.Driver</value>
</property>
<property name="url">
<value>jdbc:mysql://127.0.0.1/myweb?useUnicode=true&characterEncoding=gbk</value>
</property>
<property name="username">
<value>root</value>
</property>
<property name="password">
<value>root</value>
</property>
</bean>
<bean id="jdbcTemplate"
class="org.springframework.jdbc.core.JdbcTemplate" abstract="false"
lazy-init="false" autowire="default" dependency-check="default">
<property name="dataSource">
<ref bean="dataSource" />
</property>
</bean>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource">
<ref bean="dataSource" />
</property>
</bean>
<bean id="springDAOProxy"
class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
<property name="proxyInterfaces">
<list>
<value>com.test.DAO</value>
</list>
</property>
<property name="target">
<ref bean="DAO"/>
</property>
<property name="transactionManager">
<ref bean="transactionManager"/>
</property>
<property name="transactionAttributes">
<props>
<prop key="insert*">PROPAGATION_REQUIRED</prop>
<prop key="update*">PROPAGATION_REQUIRED</prop>
<prop key="delete*">PROPAGATION_REQUIRED</prop>
</props>
</property>
</bean>
<bean id="DAO" class="com.test.DAOImpl">
<property name="jt">
<ref bean="jdbcTemplate" />
</property>
</bean>
<bean name="userManager" class="com.test.UserManagerImpl" init-method="init">
<property name="dao">
<ref bean="DAO" />
</property>
</bean>
</beans>
9. web环境下调用：
web.xml配置：
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>/WEB-INF/classes/applicationContext.xml</param-value>
</context-param>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
</web-app>
测试jsp文件：
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="org.springframework.web.context.WebApplicationContext"%>
<%@ page import="com.test.*"%>
<%@ page import="org.springframework.web.context.support.WebApplicationContextUtils"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%
WebApplicationContext ctx = WebApplicationContextUtils.getWebApplicationContext(this.getServletContext());
UserManager um = (UserManager) ctx.getBean("userManager");
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>spring jdbc test</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<br>
<%
List<User> users2=um.getUserList();
for(int i=0;i<users2.size();i++)
{
int t_id2=users2.get(i).getId();
String t_name2=users2.get(i).getName();
%>
<%=t_id2 %>-<%=t_name2 %> <br>
<%
System.out.println(t_id2+"-"+t_name2);
}
%>
</body>
</html>
资源下载地址(不需要下载积分)：http://download.csdn.net/detail/5iasp/6344527