JWorld@TW the best professional Java site in Taiwan
      註冊 | 登入 | 全文檢索 | 排行榜  

» JWorld@TW » Java Application Framework » Spring  

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友    訂閱主題
reply to topicthreaded modego to previous topicgo to next topic
本主題所含的標籤
無標籤
作者 CallableStatementCreator 執行時 被叫了二次(StoreProcedure)
leon456

三秒完成 不然Timeout



發文: 239
積分: 1
於 2008-12-26 16:06 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
OracleStoreProcedureAdapter.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
import javax.sql.DataSource;
 
import oracle.jdbc.driver.OracleTypes;
 
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
 
public class OracleStoreProcedureAdapter {
  private DataSourceTransactionManager transactinManager;
  private DefaultTransactionDefinition def;
  private JdbcTemplate jdbcTemplate;
  
  
  public void setDataSource(DataSource dataSource){
    this.def = new DefaultTransactionDefinition();
    this.def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
    this.jdbcTemplate = new JdbcTemplate(dataSource);
    this.transactinManager = new DataSourceTransactionManager(dataSource);
  }
  
  @SuppressWarnings("unchecked")
  public List<Map> callStoreProcedure(String packageName,String funcName,String[] arguments){
    TransactionStatus status = this.transactinManager.getTransaction(this.def);
    List<Map> datas = null;
    try{
      System.out.println("---Execute--");
       datas = (List<Map>) this.jdbcTemplate.execute(new ProCallableStatementCreator(packageName,funcName,arguments),   
                    new ProCallableStatementCallback());
       System.out.println("---End Execute--"); 
       return datas;
    }catch(DataAccessException e){
      e.printStackTrace();
      this.transactinManager.rollback(status);
      return datas;
    }
  }
  
  public List<List> callStoreProcedure(String packageName, String funcName,List<String[]> arguments) {
    TransactionStatus status = this.transactinManager.getTransaction(this.def);
    List<List> datas = null;
    try{
      datas = new ArrayList();
      for(String[] args:arguments){
        datas.add((List<Map>) this.jdbcTemplate.execute(new ProCallableStatementCreator(packageName,funcName,args),   
                      new ProCallableStatementCallback()));
      }
      return datas;
    }catch(DataAccessException e){
      e.printStackTrace();
      this.transactinManager.rollback(status);
      return datas = null;
    }
  }
  
  final class ProCallableStatementCreator implements CallableStatementCreator{
    private String packageName;
    private String funcName;
    private String[] arguments;
    
    public ProCallableStatementCreator(String packageName,String funcName,String[] arguments){
      this.packageName = packageName;
      this.funcName = funcName;
      this.arguments = arguments;
    }
    
    public CallableStatement createCallableStatement(Connection con)
        throws SQLException {
      
      String args = "";
        for(int i = 0;i<this.arguments.length;i++){
          if(this.arguments.length > 1){
            if(i != this.arguments.length-1)
              args +="'"+arguments[i]+"',";
            else
              args +="'"+arguments[i]+"'";
          }else{
            args +="'"+arguments[i]+"'";
          }
        }
      
      StringBuffer sb = new StringBuffer("{? = call ");
      sb.append(this.packageName+"."+this.funcName+"(");
      sb.append(args);
      sb.append(")}");
      System.out.println("SQL:"+sb.toString());
      CallableStatement cs = null;
      cs = con.prepareCall(sb.toString());
      cs.registerOutParameter (1, OracleTypes.CURSOR);
      return cs; 
    }
  }
  
  final class ProCallableStatementCallback implements CallableStatementCallback{
    
    @SuppressWarnings("unchecked")
    public List<Map> doInCallableStatement(CallableStatement cs)
        throws SQLException, DataAccessException {
      List<Map> datas = new ArrayList<Map>();
      cs.execute();
      ResultSet rs = (ResultSet) cs.getObject(1); 
      ResultSetMetaData rsMeta = rs.getMetaData();
      
      while (rs.next()) {
        Map data = new HashMap();
        for(int i = 1; i<=rsMeta.getColumnCount(); i++){
            data.put(rsMeta.getColumnLabel(i), rs.getObject(i));
        }
        datas.add(data);
      }
      return datas;
    }
  }
 
  
}


調用時,

1
2
3
4
String packageName = "test_010";
String funcName = "query_customer";
String[] arguments = new String[]{"Leon456","20"};
osp.callStoreProcedure(packageName, funcName, arguments)

OracleStoreProcedureAdapter.java 103行卻跑了二次
請問有大大 知道這個問題 的解決方法嗎??


leon456 edited on 2008-12-26 18:09
reply to postreply to post
叫別人小白的人是最白的人
個人的blog
最佳Java to Flex
作者 Re:CallableStatementCreator 被執行時 被叫了二次 [Re:leon456]
koji

秒速5センチメートル

站長

發文: 8415
積分: 19
於 2008-12-26 16:12 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
嗯..不能先用deubg mode或是印出stacktrace看看
到底是誰叫的嗎~?

你是如何判斷他跑了兩次勒?

koji


reply to postreply to post
JCConf Taiwan 2015 開始售票了!!
Facebook上的TWJUG社團,歡迎加入
作者 Re:CallableStatementCreator 執行時 被叫了二次 [Re:leon456]
leon456

三秒完成 不然Timeout



發文: 239
積分: 1
於 2008-12-26 16:39 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
我的程式 是由Flex端
叫了 callStoreProcedure(String packageName,String funcName,String[] arguments)
這個method
在 eclipse的 console 上
印了 OracleStoreProcedureAdapter.java 103行二次

調用時的第4行 前後 打印的部份 出現一次
所以會覺得被叫了二次


reply to postreply to post
叫別人小白的人是最白的人
個人的blog
最佳Java to Flex
作者 Re:CallableStatementCreator 執行時 被叫了二次 [Re:leon456]
koji

秒速5センチメートル

站長

發文: 8415
積分: 19
於 2008-12-26 16:45 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
所以你是說你101行的
System.out.println("SQL:"+sb.toString());
被叫了兩次

那callback的ProCallableStatementCallback
會被叫幾次?
如果只被叫一次的話,那應該還好...就怕他真的執行了兩次..

建議看能不能printstacktrace一下
就比較知道到底那邊叫的,為何叫兩次

koji


reply to postreply to post
JCConf Taiwan 2015 開始售票了!!
Facebook上的TWJUG社團,歡迎加入
作者 Re:CallableStatementCreator 執行時 被叫了二次 [Re:leon456]
leon456

三秒完成 不然Timeout



發文: 239
積分: 1
於 2008-12-26 18:05 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
解決了,原本真是我程式叫了二次
感謝Koji大大
公布改好的部份 供有需使用到 StoreProcedure的人參考囉
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
import javax.sql.DataSource;
 
import oracle.jdbc.driver.OracleTypes;
 
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
 
public class OracleStoreProcedureAdapter implements OracleStoreProcedure{
  private DataSourceTransactionManager transactinManager;
  private DefaultTransactionDefinition def;
  private JdbcTemplate jdbcTemplate;
  
  
  public void setDataSource(DataSource dataSource){
    this.def = new DefaultTransactionDefinition();
    this.def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
    this.jdbcTemplate = new JdbcTemplate(dataSource);
    this.transactinManager = new DataSourceTransactionManager(dataSource);
  }
  
  @SuppressWarnings("unchecked")
  public List<Map> callStoreProcedure(String packageName,String funcName,String[] arguments){
    TransactionStatus status = this.transactinManager.getTransaction(this.def);
    List<Map> datas = null;
    try{
      System.out.println("---Execute--");
       datas = (List<Map>) this.jdbcTemplate.execute(new ProCallableStatementCreator(packageName,funcName,arguments),   
                    new ProCallableStatementCallback());
       this.transactinManager.commit(status);//少加了這個?!
       System.out.println("---End Execute--");
       return datas;
    }catch(DataAccessException e){
      e.printStackTrace();
      this.transactinManager.rollback(status);
      return datas;
    }
  }
  
  @SuppressWarnings("unchecked")
  public List<List> callStoreProcedure(String packageName, String funcName,List<String[]> arguments) {
    TransactionStatus status = this.transactinManager.getTransaction(this.def);
    List<List> datas = null;
    try{
      datas = new ArrayList();
      for(String[] args:arguments){
        datas.add((List<Map>) this.jdbcTemplate.execute(new ProCallableStatementCreator(packageName,funcName,args),   
                      new ProCallableStatementCallback()));
      }
      this.transactinManager.commit(status);//少加了這個?!
      return datas;
    }catch(DataAccessException e){
      e.printStackTrace();
      this.transactinManager.rollback(status);
      return datas = null;
    }
  }
  
  final class ProCallableStatementCreator implements CallableStatementCreator{
    private String packageName;
    private String funcName;
    private String[] arguments;
    
    public ProCallableStatementCreator(String packageName,String funcName,String[] arguments){
      this.packageName = packageName;
      this.funcName = funcName;
      this.arguments = arguments;
    }
    
    public CallableStatement createCallableStatement(Connection con)
        throws SQLException {
      
      String args = "";
        for(int i = 0;i<this.arguments.length;i++){
          if(this.arguments.length > 1){
            if(i != this.arguments.length-1)
              args +="'"+arguments[i]+"',";
            else
              args +="'"+arguments[i]+"'";
          }else{
            args +="'"+arguments[i]+"'";
          }
        }
      
      StringBuffer sb = new StringBuffer("{? = call ");
      sb.append(this.packageName+"."+this.funcName+"(");
      sb.append(args);
      sb.append(")}");
      System.out.println("SQL:"+sb.toString());
      CallableStatement cs = null;
      cs = con.prepareCall(sb.toString());
      cs.registerOutParameter (1, OracleTypes.CURSOR);
      return cs; 
    }
  }
  
  final class ProCallableStatementCallback implements CallableStatementCallback{
    
    @SuppressWarnings("unchecked")
    public List<Map> doInCallableStatement(CallableStatement cs)
        throws SQLException, DataAccessException {
      List<Map> datas = new ArrayList<Map>();
      System.out.println("--CallableStatementCallback execute--");
      cs.execute();
      System.out.println("--End CallableStatementCallback execute--");
      ResultSet rs = (ResultSet) cs.getObject(1); 
      ResultSetMetaData rsMeta = rs.getMetaData();
      
      while (rs.next()) {
        Map data = new HashMap();
        for(int i = 1; i<=rsMeta.getColumnCount(); i++){
            data.put(rsMeta.getColumnLabel(i), rs.getObject(i));
        }
        datas.add(data);
      }
      return datas;
    }
  }
}

這個寫法 目的在Java 層只當做一個 跳板
前端在Flex
Business都在StoreProcedure
Dedug完不用重啟Tomcat

這個做 會不會有什麼缺點呢??
有無大大 有這樣架構經驗 可以幫忙提醒一下


leon456 edited on 2008-12-29 13:41
reply to postreply to post
叫別人小白的人是最白的人
個人的blog
最佳Java to Flex
» JWorld@TW »  Java Application Framework » Spring

reply to topicthreaded modego to previous topicgo to next topic
  已讀文章
  新的文章
  被刪除的文章
Jump to the top of page

JWorld@TW 本站商標資訊

Powered by Powerful JuteForum® Version Jute 1.5.8