1 16 package dlog4j.util; 17 18 import java.sql.Connection ; 19 import java.sql.DriverManager ; 20 import java.sql.PreparedStatement ; 21 import java.sql.ResultSet ; 22 import java.sql.SQLException ; 23 import java.sql.Timestamp ; 24 25 30 public class Transfer { 31 32 public static void main(String [] args) throws SQLException { 33 transfer_reply(); 34 } 35 36 public static void transfer_reply() throws SQLException { 37 Connection conn = getNewConn(); 38 PreparedStatement ps = null; 39 ResultSet rs = null; 40 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)"; 41 try{ 42 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 old_user = rs.getString("log_r_author"); 49 String face = rs.getString("log_r_face"); 50 face = "faces" + face.substring(4); 51 String content = rs.getString("log_r_content"); 52 Timestamp 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 59 try{ 60 insertReply(userid,logid,face,content,wt); 61 System.out.println("reply:"+content+" inserted."); 62 }catch(Exception e){ 63 System.out.println("reply:"+content+" failed."); 64 } 65 } 66 }finally{ 70 conn.close(); 71 } 72 } 73 74 protected static int insertReply(int userid,int logid,String face,String content,Timestamp t) throws SQLException { 75 76 Connection conn = getNewConn(); 77 PreparedStatement ps = null; 78 try{ 79 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 { 101 int new_log_id = -1; 102 Connection conn = getNewConn(); 103 PreparedStatement ps = null; 104 ResultSet rs = null; 105 try{ 106 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 username) throws SQLException { 121 int userid= 3; 122 Connection conn = getNewConn(); 123 PreparedStatement ps = null; 124 ResultSet rs = null; 125 try{ 126 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 { 141 Connection conn1 = getOldConn(); 142 Connection conn2 = getNewConn(); 143 PreparedStatement ps = null; 144 ResultSet rs = null; 145 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)"; 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 user = rs.getString("log_author"); 152 String title = rs.getString("log_tittle"); 153 String content = rs.getString("log_content"); 154 Timestamp writeTime = rs.getTimestamp("written_Time"); 155 int vcount = rs.getInt("lv_count"); 156 int rcount = rs.getInt("lr_count"); 157 String author = rs.getString("author"); 158 String author_url = rs.getString("author_url"); 159 PreparedStatement 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 [] args) throws SQLException { 182 Connection conn = getOldConn(); 183 conn.close(); 184 System.out.println("测试旧版数据库连接成功."); 185 conn = getNewConn(); 186 conn.close(); 187 System.out.println("测试新版数据库连接成功."); 188 } 189 190 194 public static void transfer_user() throws SQLException { 195 196 Connection conn1 = getOldConn(); 197 Connection conn2 = getNewConn(); 198 PreparedStatement ps = null; 199 ResultSet rs = null; 200 String 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 name = rs.getString("username"); 207 String password = "ACEB1D8E96AB2739"; 208 String email = rs.getString("email"); 209 String home = rs.getString("homepage"); 210 String demo = rs.getString("underwrite"); 211 Timestamp regTime = rs.getTimestamp("reg_Time"); 212 PreparedStatement 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 getConnection(String ds) throws SQLException { 231 return DriverManager.getConnection("jdbc:odbc:"+ds); 232 } 233 public static Connection getNewConn() throws SQLException { 234 return getConnection("mydlog"); 235 } 236 public static Connection getOldConn() throws SQLException { 237 return getConnection("mydlog_old"); 238 } 239 static{ 240 try{ 241 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 242 }catch(Exception e){} 243 } 244 } 245 | Popular Tags |