项目的数据信息导入mysql_项目mysql数据导入数据的Java程序

最近写的一个数据库导入数据的程序，有兴趣的同学可以参考一下：

这个程序是针对mysql数据库的，在本地或服务器上运行，主要的需求还是，针对项目的某些bug修复

后，客户的数据要搬到新表上来，避免新版本上线或发布后用户的数据丢失，里面有Json格式的字段，不太好处理。

package com.ibatis.test;

import java.io.BufferedWriter;

import java.io.FileWriter;

import java.io.IOException;

import java.security.interfaces.RSAKey;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.aspectj.weaver.patterns.ThisOrTargetAnnotationPointcut;

import ayou.util.DOC;

import com.cc.ovp.domain.PlayProfile;

import com.cc.ovp.domain.PlayerSkin;

import com.cc.ovp.domain.PlayerSkin.Ext;

public class PlayerskinToPlayerprofile {

private static Connection connection;

private static Statement statement;

private static ResultSet resultSet;

private ResultSetMetaData rsMetaData;

构造函数

public PlayerskinToPlayerprofile(){

String url = "jdbc:mysql://127.0.0.1:3306/xuan?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull";

String username = "root";

String password = "123456";

//加载驱动程序以连接数据库

try {

Class.forName( "org.gjt.mm.mysql.Driver" );

connection = DriverManager.getConnection(url, username, password );

}

//捕获加载驱动程序异常

catch ( ClassNotFoundException cnfex ) {

System.err.println("装载 JDBC/ODBC 驱动程序失败");

cnfex.printStackTrace();

}

//捕获连接数据库异常

catch ( SQLException sqlex ) {

System.err.println( "无法连接数据库" );

sqlex.printStackTrace();

}

}

///

@SuppressWarnings("unchecked")

private static void getTable2() throws IOException

{

try {

//执行SQL语句

//String query = inputQuery.getText();

statement = connection.createStatement();

resultSet = statement.executeQuery("select * from player_skin;");

//在表格中显示查询结果

//DOC[] docs= resultSet;

/* List list=(List)resultSet;

PlayerSkin palyerSkin=new PlayerSkin();

System.out.println("记录数===="+list.size());

for(int i=0; i

System.out.println(i+"========"+palyerSkin.getPlayerid()+"====="+palyerSkin.getUserid());

}*/

PlayerSkin palyerSkin2=new PlayerSkin();

PlayerSkin.Ext ext=new Ext();

BufferedWriter bw=new BufferedWriter(new FileWriter("home/qixuan/playerSkinExtToplayProfile.txt"));

int j=0;

while(resultSet.next()){

j++;

//System.out.println("======"+extTo);

try {

String extTo=resultSet.getString("ext");

ext=(PlayerSkin.Ext.fromJSON(extTo));

} catch (Exception e) {

try {

bw.write("userid---"+resultSet.getString("userid"));

bw.newLine();

bw.flush();

} catch (IOException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

// TODO Auto-generated catch block

e.printStackTrace();

}

System.out.println(j+"========"+resultSet.getString("playerid")

+"====="+resultSet.getString("userid")

+"===D==="+ext.getWatermark_show()

+"===E==="+ext.getWatermarklocation()

+"===r==="+ext.getWatermark_diaph()

+"===q==="+ext.getWatermarkurl1()

+"===y==="+ext.getWatermarkurl2()

+"===u==="+ext.getWatermarkurl3()

+"===j==="+ext.getAutoplay()

+"===w==="+ext.getDefinition());

//+"==EXT==="+resultSet.getString("ext"));

//System.out.println(j+"=========="+ext.getDisable_host()+"=========="+ext.getEnable_host());

DOC doc3 = new DOC();

String userid3= resultSet.getString("userid");

List playProfiles=selectDate2(userid3);

if(playProfiles.size()>0){

//System.out.println("===数据===="+playProfiles.get(0).getExt());

doc3 = com.cc.ovp.util.Ext.parseJson(playProfiles.get(0).getExt());

//doc3.put("definition", "1");

//doc3.put("width", "600");

//doc3.put("autoplay",ext.getAutoplay());

doc3.put("watermarklocation",ext.getWatermarklocation());

doc3.put("watermark_show",ext.getWatermark_show());

doc3.put("watermark_diaph",ext.getWatermark_diaph());

doc3.put("watermarkurl1",ext.getWatermarkurl1());

doc3.put("watermarkurl2",ext.getWatermarkurl2());

doc3.put("watermarkurl3",ext.getWatermarkurl3());

String ext3 = com.cc.ovp.util.Ext.setJson(doc3);

//System.out.println("ext3::::::::::::::"+ext3);

updateDate2(userid3,ext3);//更新数据

}else{

doc3.put("definition", "1");

doc3.put("width", "600");

doc3.put("height", "0");

doc3.put("autoplay","1");

doc3.put("watermarklocation",ext.getWatermarklocation());

doc3.put("watermark_show",ext.getWatermark_show());

doc3.put("watermark_diaph",ext.getWatermark_diaph());

doc3.put("watermarkurl1",ext.getWatermarkurl1());

doc3.put("watermarkurl2",ext.getWatermarkurl2());

doc3.put("watermarkurl3",ext.getWatermarkurl3());

String ext3 = com.cc.ovp.util.Ext.setJson(doc3);

//System.out.println("ext3::::::::::::::"+ext3);

insertDate2(resultSet.getString("userid"),ext3);//插入数据

}

}

bw.close();

resultSet.close();

statement.close();

//connection.close();

}

catch ( SQLException sqlex ) {

sqlex.printStackTrace();

}

}

查询play_profile表的数据

@SuppressWarnings("unchecked")

public static List selectDate2(String userid3) throws SQLException{

//statement=connection.createStatement();

PreparedStatement ps=connection.prepareStatement("select * from play_profile where userid=?");

ps.setString(1,userid3);

ResultSet rs=ps.executeQuery();

List list=new ArrayList();

while(rs.next()){

PlayProfile playProfile = new PlayProfile();

playProfile.setUserid(rs.getString("userid"));

playProfile.setAutoid(rs.getInt("autoid"));

playProfile.setExt(rs.getString("ext"));

playProfile.setStatus(rs.getInt("status"));

list.add(playProfile);

}

ps.close();

return list;

}

插入数据

public static void insertDate2(String userid,String ext3) throws SQLException{

PreparedStatement ps=connection.prepareStatement("insert into play_profile(userid,status,ext) values(?,?,?)");

ps.setString(1,userid);

ps.setInt(2, 60);

ps.setString(3, ext3);

ps.executeUpdate();

ps.close();

}

更新数据

public static void updateDate2(String userid,String ext3) throws SQLException{

PreparedStatement ps=connection.prepareStatement("update play_profile set ext=? where userid=?");

ps.setString(1, ext3);

ps.setString(2, userid);

ps.executeUpdate();

ps.close();

}

public static void main(String[] args) throws IOException{

PlayerskinToPlayerprofile ptp=new PlayerskinToPlayerprofile();

ptp.getTable2();

//System.out.println("程序入口");

}

}///

运行效果

这里只给出控制台运行情况：

分享到：

2013-09-25 13:40

浏览 214

评论