使用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>