利用kettle的自定义java给sqlserver复制表
来源：互联网 发布：excel拆分合并软件 编辑：程序博客网 时间：2024/05/17 01:24
利用kettle的自定义java给sqlserver复制表
eclipse代码:
package copyTable;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;public class CopyTable { String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; String copyFromUrl; String copyFromDatabase; String copyFromUserName; String copyFromPassword; String copyToUrl; String copyToDatabase; String copyToUserName; String copyToPassword; /** * 构造方法接收两个数据库的连接信息 * @param copyFromUrl 示例：localhost * @param copyFromDatabase * @param copyFromUserName * @param copyFromPassword * @param copyToUrl 示例：127.0.0.1 * @param copyToDatabase * @param copyToUserName * @param copyToPassword *//* CopyTable(String copyFromUrl,String copyFromDatabase,String copyFromUserName,String copyFromPassword, String copyToUrl,String copyToDatabase,String copyToUserName,String copyToPassword ){ this.copyFromUrl = copyFromUrl; this.copyFromDatabase = copyFromDatabase; this.copyFromUserName = copyFromUserName; this.copyFromPassword = copyFromPassword; this.copyToUrl = copyToUrl; this.copyToDatabase = copyToDatabase; this.copyToUserName = copyToUserName; this.copyToPassword = copyToPassword; }*/ /** * 初始化 * @param copyFromUrl 示例：127.0.0.1 * @param copyFromDatabase * @param copyFromUserName * @param copyFromPassword * @param copyToUrl 示例：127.0.0.1 * @param copyToDatabase * @param copyToUserName * @param copyToPassword */ public void Init(String copyFromUrl,String copyFromDatabase,String copyFromUserName,String copyFromPassword, String copyToUrl,String copyToDatabase,String copyToUserName,String copyToPassword){ this.copyFromUrl = copyFromUrl; this.copyFromDatabase = copyFromDatabase; this.copyFromUserName = copyFromUserName; this.copyFromPassword = copyFromPassword; this.copyToUrl = copyToUrl; this.copyToDatabase = copyToDatabase; this.copyToUserName = copyToUserName; this.copyToPassword = copyToPassword; } /** * 获得源数据库的连接 * @return Connection * @throws ClassNotFoundException * @throws SQLException */ public Connection getConFrom() throws ClassNotFoundException, SQLException{ Class.forName(this.driverName); String copyFromUrls = "jdbc:sqlserver://"+this.copyFromUrl+":1433;Database="+this.copyFromDatabase; Connection fromConnection = DriverManager.getConnection(copyFromUrls,this.copyFromUserName,this.copyFromPassword); return fromConnection; } /** * 获得目标数据库的连接，并打开Ad Hoc服务 * @return Connection * @throws ClassNotFoundException * @throws SQLException */ public Connection getConTo() throws ClassNotFoundException, SQLException{ Class.forName(this.driverName); String copyToUrls = "jdbc:sqlserver://"+this.copyToUrl+":1433;Database="+this.copyToDatabase; Connection ToConnection = DriverManager.getConnection(copyToUrls,this.copyToUserName,this.copyToPassword); String s1 = "EXEC sp_configure 'show advanced options',1 "+ "RECONFIGURE "+ "EXEC sp_configure 'Ad Hoc Distributed Queries',1 "+ "RECONFIGURE"; PreparedStatement stat = ToConnection.prepareCall(s1); stat.execute(); return ToConnection; } /** * 获得源数据库的表名 * @return List */ public List getTableName(){ try { Class.forName(this.driverName); this.copyFromUrl = this.copyFromUrl+";Database="+this.copyFromDatabase; Connection fromConnection = getConFrom(); List TableNameList = new ArrayList(); String sql = "SELECT Name FROM SysObjects Where XType='U' ORDER BY Name"; ResultSet rSet = fromConnection.createStatement().executeQuery(sql); while(rSet.next()){ TableNameList.add(rSet.getString("Name")); } rSet.close(); fromConnection.close(); return TableNameList; } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } /** * 复制表结构 * @return String */ public String copyAllTableStructure(){ List list = getTableName(); String resultString = ""; try { Connection conTo = getConTo(); Statement statement = conTo.createStatement(); String sql = "SELECT Name FROM SysObjects Where XType='U' ORDER BY Name"; for(int i = 0 ; i < list.size() ; i++){ String tableNameString = (String) list.get(i); String s = "SELECT * INTO "+this.copyToDatabase+".dbo."+tableNameString +" FROM opendatasource( 'SQLOLEDB', 'Data Source="+this.copyFromUrl +";User ID="+this.copyFromUserName+";Password="+this.copyFromPassword+"')." +this.copyFromDatabase+".dbo."+tableNameString+" WHERE 1=2"; ResultSet rs = null; rs = statement.executeQuery(sql); boolean b = true; while(rs!=null&&rs.next()){ String nameString = rs.getString("Name"); if(nameString.equals(tableNameString)){ b = false; } } if(b){ statement.execute(s); resultString = resultString + " 已创建"+tableNameString+"表"; }else{ resultString = resultString + " 表" +tableNameString +"已存在"; } } statement.close(); conTo.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } System.out.println(resultString); return resultString; } /** * 复制表结构及其数据 * @return String */ public String copyAllTable(){ List list = getTableName(); String resultString = ""; try { Connection conTo = getConTo(); Statement statement = conTo.createStatement(); String sql = "SELECT Name FROM SysObjects Where XType='U' ORDER BY Name"; for(int i = 0 ; i < list.size() ; i++){ String tableNameString = (String) list.get(i); String s = "SELECT * INTO "+this.copyToDatabase+".dbo."+tableNameString +" FROM opendatasource( 'SQLOLEDB', 'Data Source="+this.copyFromUrl +";User ID="+this.copyFromUserName+";Password="+this.copyFromPassword+"')." +this.copyFromDatabase+".dbo."+tableNameString; ResultSet rs = null; rs = statement.executeQuery(sql); boolean b = true; while(rs!=null&&rs.next()){ String nameString = rs.getString("Name"); if(nameString.equals(tableNameString)){ b = false; } } if(b){ statement.execute(s); resultString = resultString + " 已复制"+tableNameString+"表"; }else{ resultString = resultString + " 表" +tableNameString +"已存在"; } } statement.close(); conTo.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } System.out.println(resultString); return resultString; } /** * 复制指定的表的结构 * @param tableName * @return */ public String copyTableStructureFromTableName(String tableName){ String resultString = ""; try { Connection conTo = getConTo(); Statement statement = conTo.createStatement(); String sql = "SELECT Name FROM SysObjects Where XType='U' ORDER BY Name"; String s = "SELECT * INTO "+this.copyToDatabase+".dbo."+tableName +" FROM opendatasource( 'SQLOLEDB', 'Data Source="+this.copyFromUrl +";User ID="+this.copyFromUserName+";Password="+this.copyFromPassword+"')." +this.copyFromDatabase+".dbo."+tableName+" where 1=2"; ResultSet rs = null; rs = statement.executeQuery(sql); boolean b = true; while(rs!=null&&rs.next()){ String nameString = rs.getString("Name"); if(nameString.equals(tableName)){ b = false; } } if(b){ statement.execute(s); resultString = resultString + " 已复制"+tableName+"表"; }else{ resultString = resultString + " 表" +tableName +"已存在"; } statement.close(); conTo.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } System.out.println(resultString); return resultString; } /** * 复制指定的表的结构及数据 * @param tableName * @return */ public String copyTableFromTableName(String tableName){ String resultString = ""; try { Connection conTo = getConTo(); Statement statement = conTo.createStatement(); String sql = "SELECT Name FROM SysObjects Where XType='U' ORDER BY Name"; String s = "SELECT * INTO "+this.copyToDatabase+".dbo."+tableName +" FROM opendatasource( 'SQLOLEDB', 'Data Source="+this.copyFromUrl +";User ID="+this.copyFromUserName+";Password="+this.copyFromPassword+"')." +this.copyFromDatabase+".dbo."+tableName; ResultSet rs = null; rs = statement.executeQuery(sql); boolean b = true; while(rs!=null&&rs.next()){ String nameString = rs.getString("Name"); if(nameString.equals(tableName)){ b = false; } } if(b){ statement.execute(s); resultString = resultString + " 已复制"+tableName+"表"; }else{ resultString = resultString + " 表" +tableName +"已存在"; } statement.close(); conTo.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } System.out.println(resultString); return resultString; } public void close(){ String s2 = "EXEC sp_configure 'Ad Hoc Distributed Queries',0 "+ "RECONFIGURE "+ "EXEC sp_configure 'show advanced options',0 "+ "RECONFIGURE"; Connection toConn; try { toConn = this.getConTo(); PreparedStatement stat = toConn.prepareCall(s2); stat.execute(); stat.close(); toConn.close(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }}kettle的java代码：
import copyTable.CopyTable;public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws KettleException{ Object[] r = getRow(); if (r == null) { setOutputDone(); return false; } if (first) { first = false; } CopyTable cTable = new CopyTable(); //似乎这里无法用java的构造方法 cTable.Init("127.0.0.1", "InfoReport", "sa","yutu@123", "127.0.0.1", "test", "sa", "123"); String resuStr = cTable.copyAllTableStructure(); get(Fields.In, "resultString").setValue(r, resuStr); putRow(data.outputRowMeta, r); return true;}源码下载
阅读全文
0 0
- 利用kettle的自定义java给sqlserver复制表
- kettle调用自定义java类
- Kettle变量和自定义java代码的实例应用
- Kettle变量和自定义java代码的实例应用
- java程序中给Kettle传参数
- sqlserver数据库之间的表的复制
- sqlserver数据库之间的表的复制
- sqlserver复制表出现的问题
- SQLServer表结构的复制方法
- kettle之------ 多表数据复制
- 利用java实现文件的复制
- 如何给sqlserver 的表添加注释
- SQLSERVER 复制表结构
- sqlserver 复制表结构
- sqlserver 复制表数据
- sqlserver 表复制
- sqlserver复制表命令
- 给Xcode增加复制行、删除行快捷键的方法-自定义复制行模式
- C++生成AI以及AI默认Animation，AI播放Montage，AIController控制移动的实现
- Promise 对象的使用方法
- RabbitMQ消息队列-Centos7下安装RabbitMQ3.6.1
- python 用列表递归创建二叉树
- synergy工具常见错误处理方式
- 利用kettle的自定义java给sqlserver复制表
- 实操《深入浅出React和Redux》第一期
- 王坚博士专访 | 揭开国家 AI 创新平台“城市大脑”的神秘面纱
- CSS——响应式布局之@media
- 初级Python中map函数的运用以及列表转字符串的方法
- python-安装pip
- Centos7使用yum安装MySQL5.6
- css伪类hover新的应用技巧思考+fixed屏幕居中
- 浅谈科研中作中的心得体会-讲座心得
