SqlDialect get(DataSource dataSource) {
Connection connection = null;
try {
connection = dataSource.getConnection();
DatabaseMetaData metaData = connection.getMetaData();
String productName = metaData.getDatabaseProductName();
String productVersion = metaData.getDatabaseProductVersion();
List key = Arrays.asList(productName, productVersion);
SqlDialect dialect = map.get(key);
if (dialect == null) {
final SqlDialect.DatabaseProduct product =
SqlDialect.getProduct(productName, productVersion);
dialect = new SqlDialect(product, productName, metaData.getIdentifierQuoteString());
map.put(key, dialect);
}
connection.close();
connection = null;
return dialect;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// ignore
}
}
}
}
private void loadDatabaseMetadata() {
if (!databaseMetadataLoaded) {
String componentName = "UNKNOWN";
Connection con = null;
try {
componentName = getMetaComponent().getName();
con = DataSourceConnectionProvider.getByComponent(componentName).getConnection();
DatabaseMetaData metaData = con.getMetaData();
supportsSchemasInDataManipulation = metaData.supportsSchemasInDataManipulation();
Collection timeDateFunctions =
Strings.toCollection(metaData.getTimeDateFunctions().toUpperCase());
//
// another solution instead of the use of 'if' would be to use a xml with
// the information of the functions from each BBDD
if ("DB2 UDB for AS/400".equals(metaData.getDatabaseProductName())
|| "Oracle".equals(metaData.getDatabaseProductName())
|| "PostgresSQL".equals(metaData.getDatabaseProductName())) {
supportsTranslateFunction = true;
}
if ("Oracle".equals(metaData.getDatabaseProductName())
|| "PostgreSQL".equals(metaData.getDatabaseProductName())) {
supportsYearFunction = supportsMonthFunction = false;
} else {
supportsYearFunction = timeDateFunctions.contains("YEAR");
supportsMonthFunction = timeDateFunctions.contains("MONTH");
}
databaseMetadataLoaded = true;
} catch (Exception ex) {
log.warn(XavaResources.getString("load_database_metadata_warning"));
} finally {
try {
if (con != null) {
con.close();
}
} catch (SQLException e) {
log.warn(XavaResources.getString("close_connection_warning"));
}
}
}
}
private static Map<PwmAboutProperty, String> getConnectionDebugProperties(
final Connection connection) {
if (connection != null) {
try {
final Map<PwmAboutProperty, String> returnObj = new LinkedHashMap<>();
final DatabaseMetaData databaseMetaData = connection.getMetaData();
returnObj.put(PwmAboutProperty.database_driverName, databaseMetaData.getDriverName());
returnObj.put(PwmAboutProperty.database_driverVersion, databaseMetaData.getDriverVersion());
returnObj.put(
PwmAboutProperty.database_databaseProductName,
databaseMetaData.getDatabaseProductName());
returnObj.put(
PwmAboutProperty.database_databaseProductVersion,
databaseMetaData.getDatabaseProductVersion());
return Collections.unmodifiableMap(returnObj);
} catch (SQLException e) {
LOGGER.error("error rading jdbc meta data: " + e.getMessage());
}
}
return Collections.emptyMap();
}
static void truncateTable(String strTable) {
String DBMS = "";
try {
DatabaseMetaData metaData = conn.getMetaData();
DBMS = metaData.getDatabaseProductName().toLowerCase();
} catch (SQLException e) {
System.out.println("Problem determining database product name: " + e);
}
System.out.println("Truncating '" + strTable + "' ...");
try {
if (DBMS.startsWith("db2")) {
stmt.execute("TRUNCATE TABLE " + strTable + " IMMEDIATE");
} else {
stmt.execute("TRUNCATE TABLE " + strTable);
}
transCommit();
} catch (SQLException se) {
System.out.println(se.getMessage());
transRollback();
}
}
/**
* 初始化连接池
*
* @param props
* @param show_sql
*/
private static final void initDataSource(Properties dbProperties) {
try {
if (dbProperties == null) {
dbProperties = new Properties();
dbProperties.load(DBManager.class.getResourceAsStream(CONFIG_PATH));
}
// Class.forName(dbProperties.getProperty("jdbc.driverClass"));
for (Object key : dbProperties.keySet()) {
String skey = (String) key;
if (skey.startsWith("jdbc.")) {
String name = skey.substring(5);
cp_props.put(name, dbProperties.getProperty(skey));
if ("show_sql".equalsIgnoreCase(name)) {
show_sql = "true".equalsIgnoreCase(dbProperties.getProperty(skey));
}
}
}
dataSource = (DataSource) Class.forName(cp_props.getProperty("datasource")).newInstance();
if (dataSource.getClass().getName().indexOf("c3p0") > 0) {
// Disable JMX in C3P0
System.setProperty(
"com.mchange.v2.c3p0.management.ManagementCoordinator",
"com.mchange.v2.c3p0.management.NullManagementCoordinator");
}
log.info("Using DataSource : " + dataSource.getClass().getName());
BeanUtils.populate(dataSource, cp_props);
Connection conn = getConnection();
DatabaseMetaData mdm = conn.getMetaData();
log.info(
"Connected to " + mdm.getDatabaseProductName() + " " + mdm.getDatabaseProductVersion());
closeConnection();
} catch (Exception e) {
e.printStackTrace();
throw new DBException(e);
}
}
/** Method declaration */
private void refreshTree() {
tTree.removeAll();
try {
int color_table = Color.yellow.getRGB();
int color_column = Color.orange.getRGB();
int color_index = Color.red.getRGB();
tTree.addRow("", dMeta.getURL(), "-", 0);
String usertables[] = {"TABLE"};
ResultSet result = dMeta.getTables(null, null, null, usertables);
Vector tables = new Vector();
// sqlbob@users Added remarks.
Vector remarks = new Vector();
while (result.next()) {
tables.addElement(result.getString(3));
remarks.addElement(result.getString(5));
}
result.close();
for (int i = 0; i < tables.size(); i++) {
String name = (String) tables.elementAt(i);
String key = "tab-" + name + "-";
tTree.addRow(key, name, "+", color_table);
// sqlbob@users Added remarks.
String remark = (String) remarks.elementAt(i);
if ((remark != null) && !remark.trim().equals("")) {
tTree.addRow(key + "r", " " + remark);
}
ResultSet col = dMeta.getColumns(null, null, name, null);
while (col.next()) {
String c = col.getString(4);
String k1 = key + "col-" + c + "-";
tTree.addRow(k1, c, "+", color_column);
String type = col.getString(6);
tTree.addRow(k1 + "t", "Type: " + type);
boolean nullable = col.getInt(11) != DatabaseMetaData.columnNoNulls;
tTree.addRow(k1 + "n", "Nullable: " + nullable);
}
col.close();
tTree.addRow(key + "ind", "Indices", "+", 0);
ResultSet ind = dMeta.getIndexInfo(null, null, name, false, false);
String oldiname = null;
while (ind.next()) {
boolean nonunique = ind.getBoolean(4);
String iname = ind.getString(6);
String k2 = key + "ind-" + iname + "-";
if ((oldiname == null || !oldiname.equals(iname))) {
tTree.addRow(k2, iname, "+", color_index);
tTree.addRow(k2 + "u", "Unique: " + !nonunique);
oldiname = iname;
}
String c = ind.getString(9);
tTree.addRow(k2 + "c-" + c + "-", c);
}
ind.close();
}
tTree.addRow("p", "Properties", "+", 0);
tTree.addRow("pu", "User: "******"pr", "ReadOnly: " + cConn.isReadOnly());
tTree.addRow("pa", "AutoCommit: " + cConn.getAutoCommit());
tTree.addRow("pd", "Driver: " + dMeta.getDriverName());
tTree.addRow("pp", "Product: " + dMeta.getDatabaseProductName());
tTree.addRow("pv", "Version: " + dMeta.getDatabaseProductVersion());
} catch (SQLException e) {
tTree.addRow("", "Error getting metadata:", "-", 0);
tTree.addRow("-", e.getMessage());
tTree.addRow("-", e.getSQLState());
}
tTree.update();
}
protected void initColumns(ConnectionProvider cp) {
if (cp != null)
try {
DatabaseMetaData dmd = cp.getDatabaseMetaData();
String shortTableName = getName().getName();
ResultSet rs;
// rs = dmd.getColumns(cp.getConnection().getCatalog(),
// dmd.getUserName().trim(), shortTableName, "%");
rs = dmd.getColumns(cp.getConnection().getCatalog(), cp.getSchema(), shortTableName, "%");
int sqlType;
String sqlTypeName;
String colName, colNull, colSize, colDec;
if (rs != null) {
HashMap rset = new HashMap();
while (rs.next()) {
sqlType = rs.getInt("DATA_TYPE"); // NOI18N
sqlTypeName = rs.getString("TYPE_NAME").trim(); // NOI18N
colName = rs.getString("COLUMN_NAME").trim(); // NOI18N
colNull = Integer.toString(rs.getInt("NULLABLE")); // NOI18N
colSize = rs.getString("COLUMN_SIZE"); // NOI18N
colDec = rs.getString("DECIMAL_DIGITS"); // NOI18N
String dbProductName = dmd.getDatabaseProductName().trim();
// Oracle driver hacks
if (dbProductName.indexOf("Oracle") != -1) { // NOI18N
if (sqlType == 11 || ((sqlType == 1111) && sqlTypeName.startsWith("TIMESTAMP")))
sqlType = Types.TIMESTAMP;
if ((sqlType == 1111) && sqlTypeName.equals("FLOAT")) // NOI18N
sqlType = Types.DOUBLE;
if ((sqlType == 1111) && sqlTypeName.equals("BLOB")) // NOI18N
sqlType = Types.BLOB;
if ((sqlType == 1111) && sqlTypeName.equals("CLOB")) // NOI18N
sqlType = Types.CLOB;
if ((sqlType == 1111) && sqlTypeName.equals("NVARCHAR2")) // NOI18N
sqlType = Types.CHAR;
}
// MySQL driver hacks
if (dbProductName.indexOf("MySQL") != -1) { // NOI18N
if ((sqlType == 1111) && sqlTypeName.equalsIgnoreCase("BIT")) // NOI18N
sqlType = Types.BIT;
}
// workaround for i-net Oranxo driver
// value in int range is expected by JDBC API but 4294967296 is returned
try {
new Integer(colSize);
} catch (NumberFormatException exc) {
colSize = Integer.toString(Integer.MAX_VALUE);
}
ColumnElementImpl cei =
new ColumnElementImpl(colName, Integer.toString(sqlType), colNull, colSize, colDec);
ColumnElement ce = new ColumnElement(cei, (TableElement) element);
ColumnElement[] c = {ce};
changeColumns(c, DBElement.Impl.ADD);
}
rs.close();
}
} catch (Exception exc) {
if (Boolean.getBoolean("netbeans.debug.exceptions")) // NOI18N
exc.printStackTrace();
}
}
/* Clear all existing nodes from the tree model and rebuild from scratch.
*/
protected void refreshTree() {
DefaultMutableTreeNode propertiesNode;
DefaultMutableTreeNode leaf;
// First clear the existing tree by simply enumerating
// over the root node's children and removing them one by one.
while (treeModel.getChildCount(rootNode) > 0) {
DefaultMutableTreeNode child = (DefaultMutableTreeNode) treeModel.getChild(rootNode, 0);
treeModel.removeNodeFromParent(child);
child.removeAllChildren();
child.removeFromParent();
}
treeModel.nodeStructureChanged(rootNode);
treeModel.reload();
tScrollPane.repaint();
// Now rebuild the tree below its root
try {
// Start by naming the root node from its URL:
rootNode.setUserObject(dMeta.getURL());
// get metadata about user tables by building a vector of table names
String usertables[] = {"TABLE", "GLOBAL TEMPORARY", "VIEW"};
ResultSet result = dMeta.getTables(null, null, null, usertables);
Vector tables = new Vector();
// sqlbob@users Added remarks.
Vector remarks = new Vector();
while (result.next()) {
tables.addElement(result.getString(3));
remarks.addElement(result.getString(5));
}
result.close();
// For each table, build a tree node with interesting info
for (int i = 0; i < tables.size(); i++) {
String name = (String) tables.elementAt(i);
DefaultMutableTreeNode tableNode = makeNode(name, rootNode);
ResultSet col = dMeta.getColumns(null, null, name, null);
// sqlbob@users Added remarks.
String remark = (String) remarks.elementAt(i);
if ((remark != null) && !remark.trim().equals("")) {
makeNode(remark, tableNode);
}
// With a child for each column containing pertinent attributes
while (col.next()) {
String c = col.getString(4);
DefaultMutableTreeNode columnNode = makeNode(c, tableNode);
String type = col.getString(6);
makeNode("Type: " + type, columnNode);
boolean nullable = col.getInt(11) != DatabaseMetaData.columnNoNulls;
makeNode("Nullable: " + nullable, columnNode);
}
col.close();
DefaultMutableTreeNode indexesNode = makeNode("Indices", tableNode);
ResultSet ind = dMeta.getIndexInfo(null, null, name, false, false);
String oldiname = null;
// A child node to contain each index - and its attributes
while (ind.next()) {
DefaultMutableTreeNode indexNode = null;
boolean nonunique = ind.getBoolean(4);
String iname = ind.getString(6);
if ((oldiname == null || !oldiname.equals(iname))) {
indexNode = makeNode(iname, indexesNode);
makeNode("Unique: " + !nonunique, indexNode);
oldiname = iname;
}
// And the ordered column list for index components
makeNode(ind.getString(9), indexNode);
}
ind.close();
}
// Finally - a little additional metadata on this connection
propertiesNode = makeNode("Properties", rootNode);
makeNode("User: "******"ReadOnly: " + cConn.isReadOnly(), propertiesNode);
makeNode("AutoCommit: " + cConn.getAutoCommit(), propertiesNode);
makeNode("Driver: " + dMeta.getDriverName(), propertiesNode);
makeNode("Product: " + dMeta.getDatabaseProductName(), propertiesNode);
makeNode("Version: " + dMeta.getDatabaseProductVersion(), propertiesNode);
} catch (SQLException se) {
propertiesNode = makeNode("Error getting metadata:", rootNode);
makeNode(se.getMessage(), propertiesNode);
makeNode(se.getSQLState(), propertiesNode);
}
treeModel.nodeStructureChanged(rootNode);
treeModel.reload();
tScrollPane.repaint();
}
