上周需要做个导出sql server2008数据库的表结构到EXCEL的需求，考虑到数据库结构还会改变，也考虑到以后的复用，于是查了些资料，写了一个生成表结构的脚本，较之网上找到的版本加入了外键，外键参考表，外键参考列，是否唯一等字段，sql如下：SELECT
TABLENAME = Case When A.colorder=1 Then D.name Else '' End,
NO = A.colorder,
COLUMN_NAME = A.name,
COLUMN_DESC = cast(isnull(G.[value],'') as varchar(100)),
IS_IDENTITY = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,
PK = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE sysindexkeys.id = A.id AND sysindexkeys.colid=A.colid))) then '√' else '' end,
FK = Case When exists(select 1 from sysforeignkeys where fkeyid=A.id and fkey=A.colid and constid in
( select distinct(id)
from sysobjects
where
--OBJECT_NAME(parent_obj)='T_USER'
--and
xtype='F'
) ) then '√' else '' end,
REFER_TABLE = cast(isnull((select rtableName from
(select distinct(t2.rtableName)
from
(select col.name, f.constid as temp
from syscolumns col,sysforeignkeys f
where f.fkeyid=col.id
and f.fkey=col.colid
and f.constid in
( select distinct(id)
from sysobjects
where
--OBJECT_NAME(parent_obj)='T_USER'
--and
xtype='F'
and fkeyid=A.id
)
) as t1 ,
(select OBJECT_NAME(f.rkeyid) as rtableName,col.name, f.constid as temp
from syscolumns col,sysforeignkeys f
where f.rkeyid=col.id
and f.rkey=col.colid
and f.constid in
( select distinct(id)
from sysobjects
where
--OBJECT_NAME(parent_obj)='T_USER'
--and
xtype='F'
and fkeyid=A.id
)
) as t2
where t1.temp=t2.temp and t1.name=A.name) fkResult1),'') as varchar(100)),
REFER_COL = cast(isnull((select name from
(select distinct(t2.rtableName),t2.name
from
(select col.name, f.constid as temp
from syscolumns col,sysforeignkeys f
where f.fkeyid=col.id
and f.fkey=col.colid
and f.constid in
( select distinct(id)
from sysobjects
where
--OBJECT_NAME(parent_obj)='T_USER'
--and
xtype='F'
and fkeyid=A.id
)
) as t1 ,
(select OBJECT_NAME(f.rkeyid) as rtableName,col.name, f.constid as temp
from syscolumns col,sysforeignkeys f
where f.rkeyid=col.id
and f.rkey=col.colid
and f.constid in
( select distinct(id)
from sysobjects
where
--OBJECT_NAME(parent_obj)='T_USER'
--and
xtype='F'
and fkeyid=A.id
)
) as t2
where t1.temp=t2.temp and t1.name=A.name) fkResult2),'') as varchar(100)),
U = Case When exists(SELECT
1
FROM
sys.indexes idx
JOIN sys.index_columns idxCol
ON (idx.object_id = idxCol.object_id
AND idx.index_id = idxCol.index_id
AND idx.is_unique_constraint = 1)
JOIN sys.tables tab
ON (idx.object_id = tab.object_id)
JOIN sys.columns col
ON (idx.object_id = col.object_id
AND idxCol.column_id = col.column_id)
--JOIN sysobjects syso
--on syso.name=tab.name
WHERE
tab.name=D.name and col.column_id=A.colorder
) then '√' else '' end,
TYPE = B.name,
BYTE_NUM = A.Length,
LENGTH = COLUMNPROPERTY(A.id,A.name,'PRECISION'),
SCALE = cast(isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0) as varchar(100)),
ALLOW_NULL = Case When A.isnullable=1 Then '√'Else '' End,
DEFAULT_VALUE = cast(isnull(E.Text,'') as varchar(100)),
TABLE_DESC = Case When A.colorder=1 Then cast(isnull(F.value,'') as varchar(100)) Else '' End
FROM
syscolumns A
Left Join
systypes B
On
A.xusertype=B.xusertype
Inner Join
sysobjects D
On
A.id=D.id and D.xtype='U' and D.name<>'dtproperties'
Left Join
syscomments E
on
A.cdefault=E.id
Left Join
sys.extended_properties G
on
A.id=G.major_id and A.colid=G.minor_id
Left Join
sys.extended_properties F
On
D.id=F.major_id and F.minor_id=0
--where d.name='T_USER' --select specific table
--where d.name='T_USER'
Order By
A.id,A.colorder
以下为相应代码：
java代码如下：
/**
*
*/
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import TableStructureVo;
/**
* @author jianfeng
*
*/
public class GenTableStructureToExcel {
/**
*
*/
public GenTableStructureToExcel() {
// TODO Auto-generated constructor stub
}
/**
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {
//String excelOutputFullName="C:\\";
String excelOutputFullName="c:/DBStructure20120926.xls";
genTableStructureToExcel(excelOutputFullName);
}
private static void genTableStructureToExcel(String excelOutputFullName) throws IOException {
//getTableStructureList
List<TableStructureVo> tableStructureVoList=getTableStructureList();
//
Map<String,List<TableStructureVo>> tableStructureVoMap=formatTableStructureListToMap(tableStructureVoList);
createExcel(tableStructureVoMap,excelOutputFullName);
}
private static Map<String, List<TableStructureVo>> formatTableStructureListToMap(
List<TableStructureVo> tableStructureVoList) {
Map<String,List<TableStructureVo>> tableStructureVoMap=new TreeMap<String,List<TableStructureVo>>();
String tableName="";
List<TableStructureVo> tableStructureVoListTemp=new ArrayList<TableStructureVo>();
for(TableStructureVo tableStructureVo:tableStructureVoList){
if(!tableName.equals(tableStructureVo.getTableName()) && tableStructureVo.getTableName()!=null && !tableStructureVo.getTableName().trim().equals("")){
if(!tableName.equals("")){
tableStructureVoMap.put(tableName, tableStructureVoListTemp);
tableStructureVoListTemp=new ArrayList<TableStructureVo>();
}
tableName=tableStructureVo.getTableName();
}
tableStructureVoListTemp.add(tableStructureVo);
}
return tableStructureVoMap;
}
private static void createExcel(Map<String, List<TableStructureVo>> tableStructureVoMap, String excelOutputFullName) throws IOException {
try {
//"c:/test1.xls"
WritableWorkbook wb = Workbook.createWorkbook(new File(excelOutputFullName));
//String sheetName = "";
Iterator it = tableStructureVoMap.keySet().iterator();
while(it.hasNext()){
String key = (String) it.next();
List<TableStructureVo> value = tableStructureVoMap.get(key);
// create Excel worksheet
WritableSheet ws = wb.createSheet(key, 0);
// add Label
// BG color is set in WritableCellFormat
WritableCellFormat normalFormat = new WritableCellFormat(
new WritableFont(WritableFont.createFont("Arial"), 11,
WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE));
//set background
normalFormat.setBackground(jxl.format.Colour.GRAY_25);
Label llabel1 = new Label(0, 0, "NO",normalFormat);
Label llabel2 = new Label(1, 0, "COlUMN_NAME",normalFormat);
Label llabel3 = new Label(2, 0, "COLUMN_DESC",normalFormat);
Label llabel4 = new Label(3, 0, "IS_IDENTITY",normalFormat);
Label llabel5 = new Label(4, 0, "PK",normalFormat);
Label llabel6 = new Label(5, 0, "FK",normalFormat);
Label llabel7 = new Label(6, 0, "FK_REFER_TABLE",normalFormat);
Label llabel8 = new Label(7, 0, "FK_REFER_COL",normalFormat);
Label llabel9 = new Label(8, 0, "UNIQUE",normalFormat);
Label llabel10 = new Label(9, 0, "TYPE",normalFormat);
Label llabel11 = new Label(10, 0, "BYTENUM",normalFormat);
Label llabel12 = new Label(11, 0, "LENGTH",normalFormat);
Label llabel13 = new Label(12, 0, "SCALE",normalFormat);
Label llabel14 = new Label(13, 0, "ALLOW_NULL",normalFormat);
Label llabel15 = new Label(14, 0, "DEFAULT_VALUE",normalFormat);
//Label label14 = new Label(0, 1, value.get(0).getTableDesc());
ws.addCell(llabel1);
ws.addCell(llabel2);
ws.addCell(llabel3);
ws.addCell(llabel4);
ws.addCell(llabel5);
ws.addCell(llabel6);
ws.addCell(llabel7);
ws.addCell(llabel8);
ws.addCell(llabel9);
ws.addCell(llabel10);
ws.addCell(llabel11);
ws.addCell(llabel12);
ws.addCell(llabel13);
ws.addCell(llabel14);
ws.addCell(llabel15);
for(int i=0; i<value.size() ;i++){
Label label1 = new Label(0, i+1, value.get(i).getNo());
Label label2 = new Label(1, i+1, value.get(i).getColumnName());
Label label3 = new Label(2, i+1, value.get(i).getColumnDesc());
Label label4 = new Label(3, i+1, value.get(i).getIsIdentity());
Label label5 = new Label(4, i+1, value.get(i).getPk());
Label label6 = new Label(5, i+1, value.get(i).getFk());
Label label7 = new Label(6, i+1, value.get(i).getReferTable());
Label label8 = new Label(7, i+1, value.get(i).getReferCol());
Label label9 = new Label(8, i+1, value.get(i).getUnique());
Label label10 = new Label(9, i+1, value.get(i).getType());
Label label11 = new Label(10, i+1, value.get(i).getByteNum());
Label label12 = new Label(11, i+1, value.get(i).getLength());
Label label13 = new Label(12, i+1, value.get(i).getScale());
Label label14 = new Label(13, i+1, value.get(i).getAllowNull());
Label label15 = new Label(14, i+1, value.get(i).getDefaultValue());
//Label label14 = new Label(0, 1, value.get(0).getTableDesc());
ws.addCell(label1);
ws.addCell(label2);
ws.addCell(label3);
ws.addCell(label4);
ws.addCell(label5);
ws.addCell(label6);
ws.addCell(label7);
ws.addCell(label8);
ws.addCell(label9);
ws.addCell(label10);
ws.addCell(label11);
ws.addCell(label12);
ws.addCell(label13);
ws.addCell(label14);
ws.addCell(label15);
}
//set width of every column
ws.setColumnView(0,5);
ws.setColumnView(1,35);
ws.setColumnView(2,17);
ws.setColumnView(3,15);
ws.setColumnView(4,5);
ws.setColumnView(5,5);
ws.setColumnView(6,15);
ws.setColumnView(7,15);
ws.setColumnView(8,10);
ws.setColumnView(9,12);
ws.setColumnView(10,12);
ws.setColumnView(11,12);
ws.setColumnView(12,12);
ws.setColumnView(13,15);
ws.setColumnView(14,18);
//saveExcelSheet(key,value,wb);
}
wb.write();
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private static List<TableStructureVo> getTableStructureList() {
String sql = "SELECT "
+ "TABLENAME = Case When A.colorder=1 Then D.name Else '' End, "
+ "NO = A.colorder, "
+ "COLUMN_NAME = A.name, "
+ "COLUMN_DESC = cast(isnull(G.[value],'') as varchar(100)), "
+ "IS_IDENTITY = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End, "
+ "PK = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in ( "
+ " SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end, "
+ "FK = Case When exists(select 1 from sysforeignkeys where fkeyid=A.id and fkey=A.colid and constid in "
+ " ( select distinct(id) "
+ " from sysobjects "
+ " where "
+ " xtype='F' "
+ " ) ) then '√' else '' end, "
+ "REFER_TABLE = cast(isnull((select rtableName from "
+ " (select distinct(t2.rtableName) "
+ " from "
+ " (select col.name, f.constid as temp "
+ " from syscolumns col,sysforeignkeys f "
+ " where f.fkeyid=col.id "
+ " and f.fkey=col.colid "
+ " and f.constid in "
+ " ( select distinct(id) "
+ " from sysobjects "
+ " where "
+ " xtype='F' "
+ " and fkeyid=A.id "
+ " ) "
+ " ) as t1 , "
+ " (select OBJECT_NAME(f.rkeyid) as rtableName,col.name, f.constid as temp "
+ " from syscolumns col,sysforeignkeys f "
+ " where f.rkeyid=col.id "
+ " and f.rkey=col.colid "
+ " and f.constid in "
+ " ( select distinct(id) "
+ " from sysobjects "
+ " where "
+ " xtype='F' "
+ " and fkeyid=A.id "
+ " ) "
+ " ) as t2 "
+ " where t1.temp=t2.temp and t1.name=A.name) fkResult1),'') as varchar(100)), "
+ "REFER_COL = cast(isnull((select name from "
+ " (select distinct(t2.rtableName),t2.name "
+ " from "
+ " (select col.name, f.constid as temp "
+ " from syscolumns col,sysforeignkeys f "
+ " where f.fkeyid=col.id "
+ " and f.fkey=col.colid "
+ " and f.constid in "
+ " ( select distinct(id) "
+ " from sysobjects "
+ " where "
+ " xtype='F' "
+ " and fkeyid=A.id "
+ " ) "
+ " ) as t1 , "
+ " (select OBJECT_NAME(f.rkeyid) as rtableName,col.name, f.constid as temp "
+ " from syscolumns col,sysforeignkeys f "
+ " where f.rkeyid=col.id "
+ " and f.rkey=col.colid "
+ " and f.constid in "
+ " ( select distinct(id) "
+ " from sysobjects "
+ " where "
+ " xtype='F' "
+ " and fkeyid=A.id "
+ " ) "
+ " ) as t2 "
+ " where t1.temp=t2.temp and t1.name=A.name) fkResult2),'') as varchar(100)), "
+ "U = Case When exists(SELECT "
+ " 1 "
+ " FROM "
+ " sys.indexes idx "
+ " JOIN sys.index_columns idxCol "
+ " ON (idx.object_id = idxCol.object_id "
+ " AND idx.index_id = idxCol.index_id "
+ " AND idx.is_unique_constraint = 1) "
+ " JOIN sys.tables tab "
+ " ON (idx.object_id = tab.object_id) "
+ " JOIN sys.columns col "
+ " ON (idx.object_id = col.object_id "
+ " AND idxCol.column_id = col.column_id) "
+ " WHERE "
+ " tab.name=D.name and col.column_id=A.colorder "
+ " ) then '√' else '' end, "
+ "TYPE = B.name, "
+ "BYTE_NUM = A.Length, "
+ "LENGTH = COLUMNPROPERTY(A.id,A.name,'PRECISION'), "
+ "SCALE = cast(isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0) as varchar(100)), "
+ "ALLOW_NULL = Case When A.isnullable=1 Then '√'Else '' End, "
+ "DEFAULT_VALUE = cast(isnull(E.Text,'') as varchar(100)), "
+ "TABLE_DESC = Case When A.colorder=1 Then cast(isnull(F.value,'') as varchar(100)) Else '' End "
+ "FROM "
+ " syscolumns A "
+ "Left Join "
+ "systypes B "
+ "On "
+ " A.xusertype=B.xusertype "
+ "Inner Join "
+ "sysobjects D "
+ " On "
+ " A.id=D.id and D.xtype='U' and D.name<>'dtproperties' "
+ "Left Join "
+ "syscomments E "
+ "on "
+ " A.cdefault=E.id "
+ "Left Join "
+ "sys.extended_properties G "
+ "on "
+ "A.id=G.major_id and A.colid=G.minor_id "
+ "Left Join "
+ "sys.extended_properties F "
+ "On "
+ "D.id=F.major_id and F.minor_id=0 "
//+ "--where d.name='OrderInfo' --select specific table "
+ "Order By "
+ "A.id,A.colorder ";
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
List<TableStructureVo> tableStructureVoList = new ArrayList<TableStructureVo>();
try {
con = DBUtil.getCon();
pstmt = con.prepareStatement(sql);
//pstmt.setObject(1, tableName);
rs = pstmt.executeQuery();
while (rs.next()) {
TableStructureVo tableStructureVo = new TableStructureVo();
tableStructureVo.setTableName(rs.getString("TABLENAME"));
tableStructureVo.setNo(rs.getString("NO"));
tableStructureVo.setColumnName(rs.getString("COLUMN_NAME"));
tableStructureVo.setColumnDesc(rs.getString("COLUMN_DESC"));
tableStructureVo.setIsIdentity(rs.getString("IS_IDENTITY"));
tableStructureVo.setPk(rs.getString("PK"));
tableStructureVo.setFk(rs.getString("FK"));
tableStructureVo.setReferTable(rs.getString("REFER_TABLE"));
tableStructureVo.setReferCol(rs.getString("REFER_COL"));
tableStructureVo.setUnique(rs.getString("U"));
tableStructureVo.setType(rs.getString("TYPE"));
tableStructureVo.setByteNum(rs.getString("BYTE_NUM"));
tableStructureVo.setLength(rs.getString("LENGTH"));
tableStructureVo.setScale(rs.getString("SCALE"));
tableStructureVo.setAllowNull(rs.getString("ALLOW_NULL"));
tableStructureVo.setDefaultValue(rs.getString("DEFAULT_VALUE"));
tableStructureVo.setTableDesc(rs.getString("TABLE_DESC"));
tableStructureVoList.add(tableStructureVo);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, pstmt, con);
}
return tableStructureVoList;
}
}
TableStructureVo：
public class TableStructureVo extends BaseVo {
/**
*
*/
private static final long serialVersionUID = -8305334843129019509L;
private String tableName;
private String no;
private String columnName;
private String columnDesc;
private String isIdentity;
private String pk;
private String referTable;
private String referCol;
private String fk;
private String unique;
private String type;
private String byteNum;
private String length;
private String scale;
private String allowNull;
private String defaultValue;
private String tableDesc;
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getNo() {
return no;
}
public void setNo(String no) {
this.no = no;
}
public String getColumnName() {
return columnName;
}
public void setColumnName(String columnName) {
this.columnName = columnName;
}
public String getColumnDesc() {
return columnDesc;
}
public void setColumnDesc(String columnDesc) {
this.columnDesc = columnDesc;
}
public String getIsIdentity() {
return isIdentity;
}
public void setIsIdentity(String isIdentity) {
this.isIdentity = isIdentity;
}
public String getPk() {
return pk;
}
public void setPk(String pk) {
this.pk = pk;
}
public String getFk() {
return fk;
}
public void setFk(String fk) {
this.fk = fk;
}
public String getUnique() {
return unique;
}
public void setUnique(String unique) {
this.unique = unique;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getByteNum() {
return byteNum;
}
public void setByteNum(String byteNum) {
this.byteNum = byteNum;
}
public String getLength() {
return length;
}
public void setLength(String length) {
this.length = length;
}
public String getScale() {
return scale;
}
public void setScale(String scale) {
this.scale = scale;
}
public String getAllowNull() {
return allowNull;
}
public void setAllowNull(String allowNull) {
this.allowNull = allowNull;
}
public String getDefaultValue() {
return defaultValue;
}
public void setDefaultValue(String defaultValue) {
this.defaultValue = defaultValue;
}
public String getTableDesc() {
return tableDesc;
}
public void setTableDesc(String tableDesc) {
this.tableDesc = tableDesc;
}
public String getReferTable() {
return referTable;
}
public void setReferTable(String referTable) {
this.referTable = referTable;
}
public String getReferCol() {
return referCol;
}
public void setReferCol(String referCol) {
this.referCol = referCol;
}
}
DB 配置class：
import java.io.IOException;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.log4j.Logger;
import org.hibernate.cfg.Settings;
import org.hibernate.impl.SessionFactoryImpl;
import org.springframework.context.ApplicationContext;
import org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DBUtil implements Serializable {
/**
*
*/
private static final long serialVersionUID = -972643985270189678L;
private static final Logger logger = Logger.getLogger(DBUtil.class);
private static Properties properties;
private static final String DRIVER_CLASS;
private static final String URL;
private static final String USER_NAME;
private static final String PASS_WORD;
private DBUtil() {
}
static {
try {
properties = new Properties();
properties.load(DBUtil.class.getClassLoader().getResourceAsStream("database.properties"));
DRIVER_CLASS = properties.getProperty(Constant.DRIVER_CLASS);
URL = properties.getProperty(Constant.URL);
USER_NAME = properties.getProperty(Constant.USER_NAME);
PASS_WORD = properties.getProperty(Constant.PASS_WORD);
} catch (IOException e) {
throw new RuntimeException("Read properties file error!");
}
}
public static Connection getCon() {
Connection con = null;
try {
Class.forName(DRIVER_CLASS);
con = DriverManager.getConnection(URL, USER_NAME, PASS_WORD);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("Get database connection error!", e);
}
return con;
}
public static void close(ResultSet rs, PreparedStatement pstmt, Connection con) {
if (rs != null) {
try {
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("Close ResultSet Error!", e);
}
}
if (pstmt != null) {
try {
pstmt.close();
pstmt = null;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("Close PreparedStatement Error!", e);
}
}
if (con != null) {
try {
con.close();
con = null;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("Close Connection Error!", e);
}
}
}
public static void beginTransaction(Connection con) {
if (con != null) {
try {
con.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void commit(Connection con) {
if (con != null) {
try {
con.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void rollback(Connection con) {
if (con != null) {
try {
con.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void executeUpdate(String sql) {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = getCon();
beginTransaction(con);
pstmt = con.prepareStatement(sql);
pstmt.executeUpdate();
commit(con);
} catch (Exception e) {
e.printStackTrace();
rollback(con);
} finally {
close(null, pstmt, con);
}
}
public static void executeUpdate(String sql, Connection con) throws Exception {
PreparedStatement pstmt = null;
try {
pstmt = con.prepareStatement(sql);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
throw e;
}
}
public static void executeUpdate(String sql, Object... params) {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = getCon();
beginTransaction(con);
pstmt = con.prepareStatement(sql);
setParams(pstmt, params);
pstmt.executeUpdate();
commit(con);
} catch (Exception e) {
e.printStackTrace();
rollback(con);
} finally {
close(null, pstmt, con);
}
}
public static int executeUpdate(String sql, Connection con, Object... params) throws Exception {
PreparedStatement pstmt = null;
int rowCount = 0;
try {
pstmt = con.prepareStatement(sql);
setParams(pstmt, params);
rowCount = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
throw e;
} finally {
close(null, pstmt, null);
}
return rowCount;
}
public static void getConnectionInfo() {
ComboPooledDataSource cpds = null;
try {
ApplicationContext context = ApplicationContextHolder.getInstance().getApplicationContext();
SessionFactoryImpl sessionFactory = (SessionFactoryImpl) context.getBean("sessionFactory");
// SessionFactoryImpl bean = (SessionFactoryImpl)
// context.getBean("sessionFactory");
// AnnotationSessionFactoryBean bean = (AnnotationSessionFactoryBean)
// context.getBean("sessionFactory");
Class c = Class.forName(sessionFactory.getClass().getName());
Field field = c.getDeclaredField("settings");
field.setAccessible(true);
Settings setting = (Settings) field.get(sessionFactory);
field.setAccessible(false);
LocalDataSourceConnectionProvider connectionProvider = (LocalDataSourceConnectionProvider) setting.getConnectionProvider();
// dataSource = (ComboPooledDataSource)
// connectionProvider.getDataSource();
// C3p0DBPool c = null;
// org.hibernate.connection.C3P0ConnectionProvider cp = null;
cpds = (ComboPooledDataSource) connectionProvider.getDataSource();// (ComboPooledDataSource)
// bean.getDataSource();
logger.debug("Connections Busy/Idle/Total:" + cpds.getNumBusyConnections() + "/ " + cpds.getNumIdleConnections() + "/" + cpds.getNumConnections());
// logger.debug("cpds： " + cpds);
} catch (Exception e) {
e.printStackTrace();
}
}
/*
* public static ResultSet executeQuery(String sql, Connection con, Object...
* params) throws Exception { PreparedStatement pstmt = null; ResultSet
* rs=null; try { pstmt = con.prepareStatement(sql); setParams(pstmt, params);
* rs=pstmt.executeQuery(); while (rs.next()) {
* System.out.print(rs.getString("type") + "\r\n");
* if(rs.getString("type")!=null){ //isTableNameExist=true; } } } catch
* (Exception e) { e.printStackTrace(); throw e; }finally{ close(null, pstmt,
* null); } return rs; }
*/
private static void setParams(PreparedStatement pstmt, Object... params) throws SQLException {
if (pstmt != null && params != null) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
}
}
}
database.properties
# for local test
connection.url = jdbc:sqlserver://localhost:1433;DatabaseName=***20120611
connection.username = ***
connection.password = ***
#c3p0.min_pool_size=3
#c3p0.max_pool_size=300
#c3p0.initial_pool_size=30
#c3p0.max_idle_time=60
#c3p0.acquire_increment=3
c3p0.max_statements=0
c3p0.idle_connection_test_period=60
c3p0.acquire_retry_attempts=30
c3p0.break_after_acquire_failure=true
#c3p0.test_connection_on_checkout=false
c3p0.initial_pool_size = 30
c3p0.max_pool_size = 1000
c3p0.min_pool_size = 30
c3p0.acquire_increment = 3
c3p0.idle_connection_test_period = 60
//set to something slightly less than wait_timeout, preventing 'stale' connections from being handed out
c3p0.max_idle_time = 60
//get connection status
testConnectionOnCheckin = true
//auto test table name
automaticTestTable=C3P0TestTable
//set to something much less than wait_timeout, prevents connections from going stale
c3p0.idle_connection_test_period = 18000
//if you can take the performance 'hit', set to "true"
c3p0.test_connection_on_checkout = false
hibernate.connection.useUnicode=true
hibernate.connection.characterEncoding=UTF-8
hibernate.connection.pool_size=1
#hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect
hibernate.dialect=org.hibernate.dialect.SQLServerDialect
hibernate.current_session_context_class=thread
hibernate.cache.provider_class=org.hibernate.cache.NoCacheProvider
hibernate.show_sql=false
hibernate.format_sql=false
hibernate.use_sql_comments=false
hibernate.hbm2ddl.auto=validate
hibernate.default_batch_fetch_size=100
hibernate.max_fetch_depth=1
hibernate.jdbc.batch_size=20
#hibernate.query.factory_class=org.hibernate.hql.classic.ClassicQueryTranslatorFactory
hibernate.query.factory_class=org.hibernate.hql.ast.ASTQueryTranslatorFactory
以下为参考资料：
1.SQL script： 查询表结构.sql 查询表结构2.sql
3.http://www.linuxso.com/sql/18468.html
4.http://www.codesky.net/article/201009/144782.html
5.http://www.examw.com/Oracle/zhonghe/97477/
6.http://www.cnblogs.com/LeeYongze/archive/2012/07/19/2599338.html
6509
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
