java访问mysql 数据库 表 的列名称
INSERT IGNORE INTO `box_phone_warning_imei` SELECT * FROM `box_log`.`box_local_phone_log` WHERE id <> v_id and imei = v_group and (mac <> v_group2 or createTime<>v_group3);
现在为了用table里的字段名 替换* ，达到下面的结果，以避免box_phone_warning_imei和box_local_phone_log字段不一致时导致数据拷贝失败。INSERT IGNORE INTO `box_phone_warning_imei` SELECT id,imei,mac,phoneNum,phoneModel,phoneVendor,appSuccessNum,appSuccessIds,appSuccessNames,appFalseNum,appFalseIds,appFalseNames,boxId,channelId,fatherChannelId,province,city,createTime,updateTime,statType,success,errorMsg,albumId,address,realIp FROM `box_log`.`box_local_phone_log` WHERE id <> v_id and imei = v_group and (mac <> v_group2 or createTime<>v_group3);
?
面对以上这么简单的需求不可能手动去，数据表里一个字段一个字段的copy下来组合成一个string吧，这样体力活不应该是猿人干的。用jdbc来帮忙吧。
?
package jdbc.test; import java.sql.*; /** * @author jone * */ public class JDBCDemo { static String user = "root"; static String password = "88888888"; static String url = "jdbc:mysql://localhost:3306/box_log_copy"; static String tableName=""; static String driver = "com.mysql.jdbc.Driver"; static Connection con = null; static { try { Class.forName(driver); initInfo(2); con = DriverManager.getConnection(url, user, password);} catch (Exception e) {e.printStackTrace();} } //想切换不同库里的表，配置以下url 和tableName就OK了。private static void initInfo(int type) {switch (type) {case 1: url = "jdbc:mysql://localhost:3306/box_oms_copy"; tableName = "box_local_phone_sale_sum";break;case 2: url = "jdbc:mysql://localhost:3306/box_log_copy"; tableName = "box_local_test_log";break;default:url = "jdbc:mysql://localhost:3306/box_log_copy"; tableName="box_mobile_log"; break;}} /** * @param args */ public static void main(String[] args) { String sqlstr = "select * from "+tableName; querySql(tableName, sqlstr); }private static void querySql(String tableName, String sqlstr) {Statement stmt = null; ResultSet rs = null; try{ stmt = con.createStatement(); // sqlstr = "insert into "+tableName+" values ('20140113','jone',01)"; // stmt.executeUpdate(sqlstr); rs = stmt.executeQuery(sqlstr); StringBuilder sb=new StringBuilder(); ResultSetMetaData rsmd = rs.getMetaData(); int j = 0; j = rsmd.getColumnCount(); for(int k = 0; k<j; k++) { // System.out.print(rsmd.getCatalogName(k+1)); System.out.print(rsmd.getColumnName(k+1)); System.out.print("\t"); sb.append(rsmd.getColumnName(k+1)).append(","); } System.out.println(); System.err.println(sb.deleteCharAt(sb.length()-1).toString()); // 以下注释打开后可以输出每行记录的值。// while(rs.next())// {// for(int i=0;i<j;i++)// {// System.out.print(rs.getString(i+1));// System.out.print("\t");// }// System.out.println();// } }catch(SQLException e2) { System.out.println("数据库存在异常！"); System.out.println(e2.toString()); } finally { try { colseConection(stmt, rs); } catch(SQLException e) { System.out.println(e.toString()); } }}private static void colseConection(Statement stmt, ResultSet rs)throws SQLException {if(rs != null) rs.close(); if(stmt != null) stmt.close(); if(con != null) con.close();} }?
?如果一个数据表结构是：
CREATE TABLE `box_local_test_log` ( `id` bigint(11) NOT NULL AUTO_INCREMENT , `imei` varchar(80) DEFAULT NULL , `mac` varchar(50) DEFAULT NULL , `phoneModel` varchar(80) DEFAULT NULL , `phoneVendor` varchar(50) DEFAULT NULL , `appId` int(11) DEFAULT NULL , `boxId` int(11) DEFAULT NULL , `channelId` int(11) DEFAULT NULL , `fatherChannelId` int(11) DEFAULT NULL, `province` varchar(30) DEFAULT NULL , `city` varchar(30) DEFAULT NULL , `createTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间', `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `statType` int(4) DEFAULT '2' , `success` bit(1) DEFAULT NULL, `errorMsg` varchar(1000) DEFAULT NULL , PRIMARY KEY (`id`,`createTime`)) ENGINE=MyISAM DEFAULT CHARSET=utf8
?执行后。输出的结果：
id,imei,mac,phoneModel,phoneVendor,appId,boxId,channelId,fatherChannelId,province,city,createTime,updateTime,statType,success,errorMsg
?这个表的列就组合成一个String了，其实以上没什么技术含量，只是个简单的demo，大家可以自己扩展得到自己想到的结果。
?
以上只需要mysql-connector-java-5.1.21.jar /附件中有。
?
?
?
?
?
?
?
?