001: /*
002: * This program is free software; you can redistribute it and/or modify
003: * it under the terms of the GNU General Public License as published by
004: * the Free Software Foundation; either version 2 of the License, or
005: * (at your option) any later version.
006: *
007: * This program is distributed in the hope that it will be useful,
008: * but WITHOUT ANY WARRANTY; without even the implied warranty of
009: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
010: * GNU Library General Public License for more details.
011: *
012: * You should have received a copy of the GNU General Public License
013: * along with this program; if not, write to the Free Software
014: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
015: */
016: package dlog4j.util;
017:
018: import java.sql.Connection;
019: import java.sql.DriverManager;
020: import java.sql.PreparedStatement;
021: import java.sql.ResultSet;
022: import java.sql.SQLException;
023: import java.sql.Timestamp;
024:
025: /**
026: * 用户转换工具
027: * 用于将asp版的数据导入到新的数据库结构中的工具基类
028: * @author liudong
029: */
030: public class Transfer {
031:
032: public static void main(String[] args) throws SQLException {
033: transfer_reply();
034: }
035:
036: public static void transfer_reply() throws SQLException {
037: Connection conn = getNewConn();
038: PreparedStatement ps = null;
039: ResultSet rs = null;
040: String 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)";
041: try {
042: //1.读出所有评论
043: ps = conn.prepareStatement("SELECT * FROM log_r");
044: rs = ps.executeQuery();
045: while (rs.next()) {
046: int r_id = rs.getInt("log_r_id");
047: int old_logid = rs.getInt("log_id");
048: String old_user = rs.getString("log_r_author");
049: String face = rs.getString("log_r_face");
050: face = "faces" + face.substring(4);
051: String content = rs.getString("log_r_content");
052: Timestamp wt = rs.getTimestamp("r_written_time");
053: int userid = getUserId(old_user);
054: int logid = getNewLogId(r_id);
055: if (logid == -1)
056: continue;
057: //System.out.println(r_id + " = " + logid);
058:
059: try {
060: insertReply(userid, logid, face, content, wt);
061: System.out.println("reply:" + content
062: + " inserted.");
063: } catch (Exception e) {
064: System.out.println("reply:" + content + " failed.");
065: }
066: }
067: //2.获取该评论者现在的userid
068: //3.获取该评论对应现在的logid
069: //4.插入新的表中
070: } finally {
071: conn.close();
072: }
073: }
074:
075: protected static int insertReply(int userid, int logid,
076: String face, String content, Timestamp t)
077: throws SQLException {
078:
079: Connection conn = getNewConn();
080: PreparedStatement ps = null;
081: try {
082: //1.读出所有评论
083: ps = conn
084: .prepareStatement("INSERT INTO dlog_reply VALUES(?,?,?,?,?,?,?,?,?,?)");
085: ps.setInt(1, r_id++);
086: ps.setInt(2, 1);
087: ps.setInt(3, userid);
088: ps.setInt(4, logid);
089: ps.setString(5, face);
090: ps.setString(6, content);
091: ps.setInt(7, 1);
092: ps.setInt(8, 0);
093: ps.setInt(9, 0);
094: ps.setTimestamp(10, t);
095: return ps.executeUpdate();
096: } finally {
097: ps.close();
098: conn.close();
099: }
100: }
101:
102: static int r_id = 1;
103:
104: public static int getNewLogId(int old_log_id) throws SQLException {
105: int new_log_id = -1;
106: Connection conn = getNewConn();
107: PreparedStatement ps = null;
108: ResultSet rs = null;
109: try {
110: //1.读出所有评论
111: ps = conn
112: .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=?");
113: ps.setInt(1, old_log_id);
114: rs = ps.executeQuery();
115: if (rs.next())
116: new_log_id = rs.getInt("logid");
117: } finally {
118: rs.close();
119: ps.close();
120: conn.close();
121: }
122: return new_log_id;
123: }
124:
125: public static int getUserId(String username) throws SQLException {
126: int userid = 3;
127: Connection conn = getNewConn();
128: PreparedStatement ps = null;
129: ResultSet rs = null;
130: try {
131: //1.读出所有评论
132: ps = conn
133: .prepareStatement("SELECT userid FROM dlog_user WHERE username=?");
134: ps.setString(1, username);
135: rs = ps.executeQuery();
136: if (rs.next())
137: userid = rs.getInt("userid");
138: } finally {
139: rs.close();
140: ps.close();
141: conn.close();
142: }
143: return userid;
144: }
145:
146: public static void transfer_log() throws SQLException {
147: Connection conn1 = getOldConn();
148: Connection conn2 = getNewConn();
149: PreparedStatement ps = null;
150: ResultSet rs = null;
151: String 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)";
152: try {
153: ps = conn1
154: .prepareStatement("SELECT * FROM log WHERE cat_id=5 order by log_id");
155: rs = ps.executeQuery();
156: int logid = 3;
157: while (rs.next()) {
158: String user = rs.getString("log_author");
159: String title = rs.getString("log_tittle");
160: String content = rs.getString("log_content");
161: Timestamp writeTime = rs.getTimestamp("written_Time");
162: int vcount = rs.getInt("lv_count");
163: int rcount = rs.getInt("lr_count");
164: String author = rs.getString("author");
165: String author_url = rs.getString("author_url");
166: PreparedStatement ps2 = conn2.prepareStatement(sql);
167: ps2.setInt(1, logid++);
168: if ("YY".equals(user))
169: ps2.setInt(2, 6);
170: else
171: ps2.setInt(2, 9);
172: ps2.setString(3, author);
173: ps2.setString(4, author_url);
174: ps2.setString(5, title);
175: ps2.setString(6, content);
176: ps2.setTimestamp(7, writeTime);
177: ps2.setInt(8, vcount);
178: ps2.setInt(9, rcount);
179: ps2.executeUpdate();
180: System.out.println("log " + title + " transfered.");
181: }
182: } finally {
183: conn1.close();
184: conn2.close();
185: }
186: }
187:
188: public static void test_connection(String[] args)
189: throws SQLException {
190: Connection conn = getOldConn();
191: conn.close();
192: System.out.println("测试旧版数据库连接成功.");
193: conn = getNewConn();
194: conn.close();
195: System.out.println("测试新版数据库连接成功.");
196: }
197:
198: /**
199: * 用户转换
200: * @throws SQLException
201: */
202: public static void transfer_user() throws SQLException {
203:
204: Connection conn1 = getOldConn();
205: Connection conn2 = getNewConn();
206: PreparedStatement ps = null;
207: ResultSet rs = null;
208: String sql = "INSERT INTO dlog_user(userid,siteid,username,password,displayName,email,homepage,resume,regtime,userrole) VALUES(?,1,?,?,?,?,?,?,?,2)";
209: try {
210: ps = conn1
211: .prepareStatement("SELECT * FROM user_mdb WHERE username<>'admin'");
212: rs = ps.executeQuery();
213: int userid = 2;
214: while (rs.next()) {
215: String name = rs.getString("username");
216: String password = "ACEB1D8E96AB2739";
217: String email = rs.getString("email");
218: String home = rs.getString("homepage");
219: String demo = rs.getString("underwrite");
220: Timestamp regTime = rs.getTimestamp("reg_Time");
221: PreparedStatement ps2 = conn2.prepareStatement(sql);
222: ps2.setInt(1, userid++);
223: ps2.setString(2, name);
224: ps2.setString(3, password);
225: ps2.setString(4, name);
226: ps2.setString(5, email);
227: ps2.setString(6, home);
228: ps2.setString(7, demo);
229: ps2.setTimestamp(8, regTime);
230: ps2.executeUpdate();
231: System.out.println("User " + name + " transfered.");
232: }
233: } finally {
234: conn1.close();
235: conn2.close();
236: }
237: }
238:
239: public static Connection getConnection(String ds)
240: throws SQLException {
241: return DriverManager.getConnection("jdbc:odbc:" + ds);
242: }
243:
244: public static Connection getNewConn() throws SQLException {
245: return getConnection("mydlog");
246: }
247:
248: public static Connection getOldConn() throws SQLException {
249: return getConnection("mydlog_old");
250: }
251:
252: static {
253: try {
254: Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
255: } catch (Exception e) {
256: }
257: }
258: }
|