使用spring jdbc template简化jdbc数据库操作实例代码

文章地址: http://blog.csdn.net/5iasp/article/details/12206793

作者： javaboy2012
Email：[e-mail]
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);}}@Overridepublic void update(String sql, Object[] params) {// TODO Auto-generated method stubtry {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;}@Overridepublic void init() {// TODO Auto-generated method stub}@Overridepublic 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);}@Overridepublic void deleteUser(int id) {String sql="delete from t_test where id="+id;dao.delete(sql);}@Overridepublic String getUser(int id) {// TODO Auto-generated method stubString sql="select name from t_test where id="+id;String name=dao.getResultValue(sql, "name");return name;}@Overridepublic 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;}@Overridepublic 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