KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > dlog4j > util > Transfer


1 /*
2  * This program is free software; you can redistribute it and/or modify
3  * it under the terms of the GNU General Public License as published by
4  * the Free Software Foundation; either version 2 of the License, or
5  * (at your option) any later version.
6  *
7  * This program is distributed in the hope that it will be useful,
8  * but WITHOUT ANY WARRANTY; without even the implied warranty of
9  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10  * GNU Library General Public License for more details.
11  *
12  * You should have received a copy of the GNU General Public License
13  * along with this program; if not, write to the Free Software
14  * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
15  */

16 package dlog4j.util;
17
18 import java.sql.Connection JavaDoc;
19 import java.sql.DriverManager JavaDoc;
20 import java.sql.PreparedStatement JavaDoc;
21 import java.sql.ResultSet JavaDoc;
22 import java.sql.SQLException JavaDoc;
23 import java.sql.Timestamp JavaDoc;
24
25 /**
26  * 用户转换工具
27  * 用于将asp版的数据导入到新的数据库结构中的工具基类
28  * @author liudong
29  */

30 public class Transfer {
31     
32     public static void main(String JavaDoc[] args) throws SQLException JavaDoc {
33         transfer_reply();
34     }
35
36     public static void transfer_reply() throws SQLException JavaDoc{
37         Connection JavaDoc conn = getNewConn();
38         PreparedStatement JavaDoc ps = null;
39         ResultSet JavaDoc rs = null;
40         String JavaDoc sql = "INSERT INTO dlog_journal(logid,catid,siteid,userid,author,author_url,title,content,logtime,weather,useFace,useUbb,showFormerly,status,viewcount,replycount,moodlevel) VALUES(?,2,1,?,?,?,?,?,?,'sunny',1,1,0,0,?,?,3)";
41         try{
42             //1.读出所有评论
43
ps = conn.prepareStatement("SELECT * FROM log_r");
44             rs = ps.executeQuery();
45             while(rs.next()){
46                 int r_id = rs.getInt("log_r_id");
47                 int old_logid = rs.getInt("log_id");
48                 String JavaDoc old_user = rs.getString("log_r_author");
49                 String JavaDoc face = rs.getString("log_r_face");
50                 face = "faces" + face.substring(4);
51                 String JavaDoc content = rs.getString("log_r_content");
52                 Timestamp JavaDoc wt = rs.getTimestamp("r_written_time");
53                 int userid = getUserId(old_user);
54                 int logid = getNewLogId(r_id);
55                 if(logid==-1)
56                     continue;
57                 //System.out.println(r_id + " = " + logid);
58

59                 try{
60                     insertReply(userid,logid,face,content,wt);
61                     System.out.println("reply:"+content+" inserted.");
62                 }catch(Exception JavaDoc e){
63                     System.out.println("reply:"+content+" failed.");
64                 }
65             }
66             //2.获取该评论者现在的userid
67
//3.获取该评论对应现在的logid
68
//4.插入新的表中
69
}finally{
70             conn.close();
71         }
72     }
73     
74     protected static int insertReply(int userid,int logid,String JavaDoc face,String JavaDoc content,Timestamp JavaDoc t) throws SQLException JavaDoc{
75
76         Connection JavaDoc conn = getNewConn();
77         PreparedStatement JavaDoc ps = null;
78         try{
79             //1.读出所有评论
80
ps = conn.prepareStatement("INSERT INTO dlog_reply VALUES(?,?,?,?,?,?,?,?,?,?)");
81             ps.setInt(1, r_id++);
82             ps.setInt(2,1);
83             ps.setInt(3, userid);
84             ps.setInt(4, logid);
85             ps.setString(5, face);
86             ps.setString(6, content);
87             ps.setInt(7, 1);
88             ps.setInt(8, 0);
89             ps.setInt(9, 0);
90             ps.setTimestamp(10, t);
91             return ps.executeUpdate();
92         }finally{
93             ps.close();
94             conn.close();
95         }
96     }
97     
98     static int r_id = 1;
99     
100     public static int getNewLogId(int old_log_id) throws SQLException JavaDoc{
101         int new_log_id = -1;
102         Connection JavaDoc conn = getNewConn();
103         PreparedStatement JavaDoc ps = null;
104         ResultSet JavaDoc rs = null;
105         try{
106             //1.读出所有评论
107
ps = conn.prepareStatement("select l.logid from dlog_journal l, log ol,log_r r where r.log_id=ol.log_id and ol.log_tittle=l.title and ol.log_content=l.content and ol.written_time=l.logtime and r.log_r_id=?");
108             ps.setInt(1, old_log_id);
109             rs = ps.executeQuery();
110             if(rs.next())
111                 new_log_id = rs.getInt("logid");
112         }finally{
113             rs.close();
114             ps.close();
115             conn.close();
116         }
117         return new_log_id;
118     }
119
120     public static int getUserId(String JavaDoc username) throws SQLException JavaDoc{
121         int userid= 3;
122         Connection JavaDoc conn = getNewConn();
123         PreparedStatement JavaDoc ps = null;
124         ResultSet JavaDoc rs = null;
125         try{
126             //1.读出所有评论
127
ps = conn.prepareStatement("SELECT userid FROM dlog_user WHERE username=?");
128             ps.setString(1, username);
129             rs = ps.executeQuery();
130             if(rs.next())
131                 userid = rs.getInt("userid");
132         }finally{
133             rs.close();
134             ps.close();
135             conn.close();
136         }
137         return userid;
138     }
139     
140     public static void transfer_log() throws SQLException JavaDoc{
141         Connection JavaDoc conn1 = getOldConn();
142         Connection JavaDoc conn2 = getNewConn();
143         PreparedStatement JavaDoc ps = null;
144         ResultSet JavaDoc rs = null;
145         String JavaDoc sql = "INSERT INTO dlog_journal(logid,catid,siteid,userid,author,author_url,title,content,logtime,weather,useFace,useUbb,showFormerly,status,viewcount,replycount,moodlevel) VALUES(?,2,1,?,?,?,?,?,?,'sunny',1,1,0,0,?,?,3)";
146         try{
147             ps = conn1.prepareStatement("SELECT * FROM log WHERE cat_id=5 order by log_id");
148             rs = ps.executeQuery();
149             int logid = 3;
150             while(rs.next()){
151                 String JavaDoc user = rs.getString("log_author");
152                 String JavaDoc title = rs.getString("log_tittle");
153                 String JavaDoc content = rs.getString("log_content");
154                 Timestamp JavaDoc writeTime = rs.getTimestamp("written_Time");
155                 int vcount = rs.getInt("lv_count");
156                 int rcount = rs.getInt("lr_count");
157                 String JavaDoc author = rs.getString("author");
158                 String JavaDoc author_url = rs.getString("author_url");
159                 PreparedStatement JavaDoc ps2 = conn2.prepareStatement(sql);
160                 ps2.setInt(1, logid++);
161                 if("YY".equals(user))
162                     ps2.setInt(2, 6);
163                 else
164                     ps2.setInt(2, 9);
165                 ps2.setString(3, author);
166                 ps2.setString(4, author_url);
167                 ps2.setString(5, title);
168                 ps2.setString(6, content);
169                 ps2.setTimestamp(7, writeTime);
170                 ps2.setInt(8, vcount);
171                 ps2.setInt(9, rcount);
172                 ps2.executeUpdate();
173                 System.out.println("log " + title+ " transfered.");
174             }
175         }finally{
176             conn1.close();
177             conn2.close();
178         }
179     }
180     
181     public static void test_connection(String JavaDoc[] args) throws SQLException JavaDoc {
182         Connection JavaDoc conn = getOldConn();
183         conn.close();
184         System.out.println("测试旧版数据库连接成功.");
185         conn = getNewConn();
186         conn.close();
187         System.out.println("测试新版数据库连接成功.");
188     }
189         
190     /**
191      * 用户转换
192      * @throws SQLException
193      */

194     public static void transfer_user() throws SQLException JavaDoc {
195         
196         Connection JavaDoc conn1 = getOldConn();
197         Connection JavaDoc conn2 = getNewConn();
198         PreparedStatement JavaDoc ps = null;
199         ResultSet JavaDoc rs = null;
200         String JavaDoc sql = "INSERT INTO dlog_user(userid,siteid,username,password,displayName,email,homepage,resume,regtime,userrole) VALUES(?,1,?,?,?,?,?,?,?,2)";
201         try{
202             ps = conn1.prepareStatement("SELECT * FROM user_mdb WHERE username<>'admin'");
203             rs = ps.executeQuery();
204             int userid = 2;
205             while(rs.next()){
206                 String JavaDoc name = rs.getString("username");
207                 String JavaDoc password = "ACEB1D8E96AB2739";
208                 String JavaDoc email = rs.getString("email");
209                 String JavaDoc home = rs.getString("homepage");
210                 String JavaDoc demo = rs.getString("underwrite");
211                 Timestamp JavaDoc regTime = rs.getTimestamp("reg_Time");
212                 PreparedStatement JavaDoc ps2 = conn2.prepareStatement(sql);
213                 ps2.setInt(1, userid++);
214                 ps2.setString(2, name);
215                 ps2.setString(3, password);
216                 ps2.setString(4, name);
217                 ps2.setString(5, email);
218                 ps2.setString(6, home);
219                 ps2.setString(7, demo);
220                 ps2.setTimestamp(8, regTime);
221                 ps2.executeUpdate();
222                 System.out.println("User " + name+ " transfered.");
223             }
224         }finally{
225             conn1.close();
226             conn2.close();
227         }
228     }
229     
230     public static Connection JavaDoc getConnection(String JavaDoc ds) throws SQLException JavaDoc{
231         return DriverManager.getConnection("jdbc:odbc:"+ds);
232     }
233     public static Connection JavaDoc getNewConn() throws SQLException JavaDoc{
234         return getConnection("mydlog");
235     }
236     public static Connection JavaDoc getOldConn() throws SQLException JavaDoc{
237         return getConnection("mydlog_old");
238     }
239     static{
240         try{
241             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
242         }catch(Exception JavaDoc e){}
243     }
244 }
245
Popular Tags