使用spring JdbcTemplate簡化jdbc數據庫操作實例代碼

jopen 11年前發布 | 59K 次閱讀 JDBC Java開發 JDBCTemplate

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

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1380596141494.html

JDBC Java開發 JDBCTemplate

使用spring JdbcTemplate簡化jdbc數據庫操作實例代碼

相關經驗

相關資訊

相關文檔

目錄