国产精品1区2区3区4区_ JDBC 讀取sqlserver2008所有用戶(hù)表的表結構

JDBC 讀取sqlserver2008所有用戶(hù)表的表結構

數據結構 JDBC SQL Java Oracle

Java代碼

package com;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
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;
/**
* 還在改進(jìn)中。。。。。下面將要對比sqlserver2008和oracle類(lèi)型的轉換
* 從sqlserver取出來(lái)的列類(lèi)型變換成oracle的，然后在oracle創(chuàng )建表
* 接著(zhù)在拼接創(chuàng )建表結構的時(shí)候拼接insert語(yǔ)句，類(lèi)型轉換想做成xml格式。
* 以后方便不同數據庫的類(lèi)型轉換。
* @author HJ
*2010-7-31 0:53
*/
public class SqlServerTest {
private static String JDBCURL= "jdbc:jtds:sqlserver://localhost:1433;DatabaseName=Prison";
private static String USER = "sa";
private static String PWD = "123456";
private static Connection con = null;
private static Map<String,String> type= new HashMap<String, String>();
static{
try {
if(type != null){
type.put("int", "number");
type.put("text", "blob");
type.put("nvarchar", "varchar2");
}
Class.forName("net.sourceforge.jtds.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String[] args) {
SqlServerTest t = new SqlServerTest();
List<String> names = t.getAllTables("mssql", "");
for (int i = 0; i < names.size(); i++) {
System.out.println(names.get(i));
System.out.println(t.getTableStruct(names.get(i),"mssql"));
}
}
/**
* 獲取連接
* @return
*/
public static Connection getConnection(){
if(con == null){
try {
con = DriverManager.getConnection(JDBCURL,USER,PWD);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return con;
}
/**
* 獲取某種數據庫中中用戶(hù)的所有表
* @param type
* @param database
* @return
*/
public List<String> getAllTables(String type,String database){
List<String> list = null;
if("mssql".equals(type)){
//查詢(xún)所有用戶(hù)表
StringBuffer sql = new StringBuffer("Select NAME FROM ")
.append(database)
.append("..SysObjects Where XType='U' orDER BY Name");
Statement sta = null;
ResultSet rs = null;
try {
sta = SqlServerTest.getConnection().createStatement();
rs = sta.executeQuery(sql.toString());
while(rs.next()){
if(list == null){
list = new ArrayList<String>();
}
list.add(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(rs != null)
rs.close();
if(sta != null)
sta.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return list;
}
/**
* 獲取表結構
* @param tableName 表名
* @param dataType 數據庫類(lèi)型
* @return
*/
public String getTableStruct(String tableName,String dataType){
StringBuilder sb = null;
if("mssql".equals(dataType)){
sb = new StringBuilder("drop table if exists ")
.append(tableName).append(";")
.append("\n")
.append("create table ")
.append(tableName)
.append("(")
.append("\n\t");
String sql = "select * from "+tableName;
Statement sta = null;
ResultSet rs = null;
ResultSet key = null;
ResultSetMetaData ma = null;
DatabaseMetaData meta = null;
try {
meta =SqlServerTest.getConnection().getMetaData();//取得數據庫連接元數據
//判斷表中是否有主鍵列
key = meta.getPrimaryKeys(null, null, tableName);
String keyColumn = null;
if(key.next()){
keyColumn = key.getString(4);
}
sta = SqlServerTest.getConnection().createStatement();
rs = sta.executeQuery(sql.toString());
ma = rs.getMetaData();
int countColumn = ma.getColumnCount();
for(int i = 1;i<=countColumn; i++){
sb.append(ma.getColumnName(i)).append("\t").append(
ma.getColumnTypeName(i));
//判斷長(cháng)度是否為0
if(!"0".equals(ma.getColumnDisplaySize(i))){
sb.append("(").append(
ma.getColumnDisplaySize(i)).append(")");
}
//System.out.println(keyColumn);
//判斷是否為主鍵
if(ma.getColumnName(i).equals(keyColumn)){
sb.append(" primary key");
}
// 判斷字段是否能為空
if(ma.isNullable(i) == ResultSetMetaData.columnNoNulls) {
sb.append(" ").append("not null");
}
// 最后一列去掉逗號
if (i != countColumn) {
sb.append(", \n\t");
} else {
sb.append("\n");
}
}
sb.append(")");
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(rs != null)
rs.close();
if(sta != null)
sta.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return sb.toString();
}
}

本站僅提供存儲服務(wù)，所有內容均由用戶(hù)發(fā)布，如發(fā)現有害或侵權內容，請點(diǎn)擊舉報。

欧美性猛交XXXX免费看蜜桃,成人网18免费韩国,亚洲国产成人精品区综合,欧美日韩一区二区三区高清不卡,亚洲综合一区二区精品久久

JDBC 讀取sqlserver2008所有用戶(hù)表的表結構