Java tutorial
/** * Copyright (c) 2004-2011 Wang Jinbao(Julian Wong), http://www.ralasafe.com * Licensed under the MIT license: http://www.opensource.org/licenses/mit-license.php */ package org.ralasafe.util; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.ralasafe.RalasafeException; import org.ralasafe.db.Column; import org.ralasafe.db.DBPower; import org.ralasafe.db.Table; public class DBUtil { private static Log log = LogFactory.getLog(DBUtil.class); public static final String DB2 = "DB2"; public static final String ORACLE = "ORACLE"; public static final String MYSQL = "MYSQL"; public static final String SQLSERVER = "SQL SERVER"; public static final String OTHER_DATABASE = "OTHER"; public static boolean supportsLimit(Connection conn) { String dataBase = getDatabaseProductName(conn); if (dataBase.equals(DB2) || dataBase.equals(ORACLE) || dataBase.equals(MYSQL)) { return true; } else { return false; } } public static void close(Connection conn) { if (conn != null) { try { if (!conn.isClosed()) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } finally { conn = null; } } } public static void close(Statement stmt) { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { stmt = null; } } } public static void close(ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { rs = null; } } } public static void close(Statement stmt, Connection conn) { close(stmt); close(conn); } public static void close(ResultSet rs, Statement stmt, Connection conn) { close(rs); close(stmt); close(conn); } public static String insertSql(String tableName, String[] columnNames) { StringBuffer buff = new StringBuffer(); buff.append("INSERT INTO "); buff.append(tableName); buff.append(" ("); StringUtil.append(buff, columnNames); buff.append(") VALUES("); StringUtil.append(buff, columnNames, false, "?", ","); buff.append(")"); return buff.toString(); } public static String selectSql(String tableName, String[] columnNames) { StringBuffer buff = new StringBuffer(); buff.append("SELECT "); StringUtil.append(buff, columnNames); buff.append(" FROM "); buff.append(tableName); return buff.toString(); } public static String selectSql(Table table) { String tableAlias = " t" + table.getId(); StringBuffer buff = new StringBuffer(); buff.append("SELECT "); buff.append(columnsString(table.getColumns(), tableAlias)); buff.append(" FROM "); buff.append(table.getName() + tableAlias); return buff.toString(); } private static StringBuffer columnsString(Column[] columns, String tableAlias) { StringBuffer buff = new StringBuffer(); buff.append(columnString(columns[0], tableAlias)); for (int i = 1; i < columns.length; i++) { buff.append(",").append(columnString(columns[i], tableAlias)); } return buff; } private static String columnString(Column column, String tableAlias) { if (column.getFunction() == null || column.getFunction().equals("")) return tableAlias + "." + column.getName(); else return column.getFunction() + "(" + tableAlias + "." + column.getName() + ")"; } public static String updateSql(String tableName, String[] idColumnNames, String[] exceptIdColumnNames) { StringBuffer buff = new StringBuffer(); buff.append("UPDATE "); buff.append(tableName); buff.append(" SET "); StringUtil.append(buff, exceptIdColumnNames, true, "=?", ","); buff.append(" WHERE "); StringUtil.append(buff, idColumnNames, true, "=?", " AND "); return buff.toString(); } public static String deleteSql(String name, String[] idColumnNames) { StringBuffer buff = new StringBuffer(); buff.append("DELETE FROM "); buff.append(name); buff.append(" WHERE "); StringUtil.append(buff, idColumnNames, true, "=?", " AND "); return buff.toString(); } public static String createTableSql(String name, String[] columnNames, String[] columnSqlTypes) { StringBuffer buff = new StringBuffer(); buff.append("CREATE TABLE "); buff.append(name.toLowerCase()); buff.append("("); for (int i = 0; i < columnNames.length; i++) { if (i > 0) { buff.append(","); } buff.append(columnNames[i]); buff.append(" "); buff.append(columnSqlTypes[i]); } buff.append(")"); return buff.toString(); } public static void exec(Connection conn, String sql) throws SQLException { PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement(sql); pstmt.execute(); } finally { close(pstmt); } } public static int getMax(Table table, String columnName) throws SQLException { Connection conn = DBPower.getConnection(table.getId()); Statement stmt = null; ResultSet rs = null; try { int max = 0; String sql = "select MAX( " + columnName + " ) from " + table.getName(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if (rs.next()) { max = rs.getInt(1); } else { max = 0; } return max; } finally { DBUtil.close(rs, stmt, conn); } } // Sequence table name(save sequence value in table) private static final String RALASAFE_SEQUNCE = "ralasafe_sequence"; /** * Get next value of certain column of table. For example: user table's id column. * * @param table * @param columnName * @return * @throws SQLException */ public static int getSequenceNextVal(Table table, String columnName) throws SQLException { Integer currentValue = readSequenceCurrentValFromDatabase(table, columnName); int nextValue = currentValue.intValue() + 1; synchronizeIntoDatabase(table, columnName, nextValue); return nextValue; } private static void synchronizeIntoDatabase(Table table, String columnName, int synValue) throws SQLException { Connection conn = null; PreparedStatement pstmt = null; try { conn = DBPower.getConnection(table.getId()); pstmt = conn.prepareStatement("update " + RALASAFE_SEQUNCE + " set currentValue=? where name=?"); String name = table.getName() + "_" + columnName; pstmt.setInt(1, synValue); pstmt.setString(2, name); pstmt.executeUpdate(); } finally { DBUtil.close(pstmt, conn); } } private static Integer readSequenceCurrentValFromDatabase(Table table, String columnName) throws SQLException { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = DBPower.getConnection(table.getId()); Integer currentValue = null; pstmt = conn.prepareStatement("select currentValue from " + RALASAFE_SEQUNCE + " where name=?"); String name = table.getName() + "_" + columnName; pstmt.setString(1, name); rs = pstmt.executeQuery(); if (rs.next()) { currentValue = new Integer(rs.getInt(1)); } if (currentValue == null) { // there's no record in sequence table, then create a record int max = getMax(table, columnName); if (max < 0) { max = 0; } DBUtil.exec(conn, "insert into " + RALASAFE_SEQUNCE + "(currentValue,name) values(" + max + ",'" + name + "')"); return new Integer(max); } return currentValue; } finally { DBUtil.close(rs, pstmt, conn); } } public static String roleTableCreateSql(String appName) { String sqlRole = " CREATE TABLE " + appName.toLowerCase() + "_role ( id integer NOT NULL, name varchar(100) NOT NULL UNIQUE, " + "description varchar(500), PRIMARY KEY(id) ) "; return sqlRole; } public static String rolePrivilegeTableCreateSql(String appName) { String sqlRolePrivilege = " CREATE TABLE " + appName.toLowerCase() + "_roleprivilege ( roleid integer NOT NULL, privilegeid integer NOT NULL, " + "PRIMARY KEY(roleid, privilegeid), FOREIGN KEY(roleid) REFERENCES " + appName + "_role (id), FOREIGN KEY(privilegeid) REFERENCES " + appName + "_privilege (id)) "; return sqlRolePrivilege; } public static String privilegeTableCreateSql(String appName) { String sqlPrivilege = " CREATE TABLE " + appName.toLowerCase() + "_privilege ( id integer NOT NULL, pid integer, " + "description varchar(500), name varchar(100) NOT NULL UNIQUE, " + "isLeaf integer, display integer, decisionPolicyCombAlg integer, " + "queryPolicyCombAlg integer, type integer, constantName varchar(40), " + "url varchar(100), target varchar(20), orderNum integer, PRIMARY KEY(id) ) "; return sqlPrivilege; } public static String userRoleTableCreateSql(String appName, String userTypeName, String idColumnName, String idColumnType) { String tableName = appName + "_" + userTypeName + "_userrole"; String sqlUserRole = "CREATE TABLE " + tableName.toLowerCase() + " ( userid " + idColumnType + " NOT NULL, roleid integer NOT NULL, PRIMARY KEY(userid, roleid), " + "FOREIGN KEY(roleid) REFERENCES " + appName + "_role (id)) "; return sqlUserRole; } public static String roleTableDropSql(String appName) { String sqlRole = " DROP TABLE " + appName + "_role"; return sqlRole; } public static String rolePrivilegeTableDropSql(String appName) { String sqlRolePrivilege = " DROP TABLE " + appName + "_roleprivilege"; return sqlRolePrivilege; } public static String privilegeTableDropSql(String appName) { String sqlPrivilege = " DROP TABLE " + appName + "_privilege"; return sqlPrivilege; } public static String userRoleTableDropSql(String appName, String userTypeName) { String tableName = appName + "_" + userTypeName + "_userrole"; String sqlUserRole = "DROP TABLE " + tableName; return sqlUserRole; } public static String tableQueryCreateSql(String appName) { return "CREATE TABLE " + appName.toLowerCase() + "_query ( id int NOT NULL,name varchar(100) NOT NULL UNIQUE, " + "description varchar(500)," + "installDate date, " + "fileName varchar(40),pid int, isLeaf int," + "PRIMARY KEY(id) ) "; } public static String tableQueryDropSql(String appName) { return "DROP TABLE " + appName + "_query"; } public static String tableUserCategoryCreateSql(String appName) { return "CREATE TABLE " + appName.toLowerCase() + "_usercategory ( id int NOT NULL, name varchar(100) NOT NULL UNIQUE, " + "description varchar(500), " + "installDate date, " + "fileName varchar(40),pid int, isLeaf int," + "PRIMARY KEY(id) ) "; } public static String tableUserCategoryDropSql(String appName) { return "DROP TABLE " + appName + "_usercategory"; } public static String tableDecisionEntitlementCreateSql(String appName) { return "create table " + appName.toLowerCase() + "_decision_entitlement(" + "id int NOT NULL," + "privilegeId int," + "userCategoryId int," + "businessDataId int," + "effect varchar(100)," + "denyReason varchar(1000)," + "PRIMARY KEY(id) )"; } public static String tableDecisionEntitlementDropSql(String appName) { return "DROP TABLE " + appName + "_decision_entitlement"; } public static String tableQueryEntitlementCreateSql(String appName) { return "create table " + appName.toLowerCase() + "_query_entitlement(" + "id int NOT NULL," + "privilegeId int," + "userCategoryId int," + "queryId int," + "description varchar(500)," + "PRIMARY KEY(id) )"; } public static String tableQueryEntitlementDropSql(String appName) { return "DROP TABLE " + appName + "_query_entitlement"; } public static String tableBusinessDataCreateSql(String appName) { return "CREATE TABLE " + appName.toLowerCase() + "_businessdata ( id int NOT NULL,name varchar(100) NOT NULL UNIQUE, " + "description varchar(500)," + "installDate date, " + "fileName varchar(40),pid int, isLeaf int, " + "PRIMARY KEY(id) ) "; } public static String tableBusinessDataDropSql(String appName) { return "DROP TABLE " + appName + "_businessdata"; } public static String tableBackUpCreateSql(String appName) { return "CREATE TABLE " + appName.toLowerCase() + "_backup ( id int NOT NULL,createTime timestamp, " + "description varchar(500),content blob," + "PRIMARY KEY(id) ) "; } public static String tableBackupDropSql(String appName) { return "DROP TABLE " + appName + "_backup"; } public static String getLimitString(Connection conn, String sql, int first, int max) { String dataBase = getDatabaseProductName(conn); String result = sql; if (dataBase.equals(DB2)) { result = getDB2LimitString(sql, first, max); } else if (dataBase.equals(ORACLE)) { result = getOracleLimitString(sql, first, max); } else if (dataBase.equals(MYSQL)) { result = getMySQLLimitString(sql, first, max); } if (log.isDebugEnabled()) { log.debug("\n" + result); } return result; } private static String getMySQLLimitString(String sql, int first, int max) { return new StringBuffer(sql.length() + 20).append(sql).append(" limit " + first + ", " + max).toString(); } private static String getOracleLimitString(String sql, int first, int max) { sql = sql.trim(); boolean isForUpdate = false; if (sql.toLowerCase().endsWith(" for update")) { sql = sql.substring(0, sql.length() - 11); isForUpdate = true; } StringBuffer pagingSelect = new StringBuffer(sql.length() + 100); pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( "); pagingSelect.append(sql); // hibernate style // pagingSelect.append(" ) row_ ) where rownum_ <= " + (first + max) // + " and rownum_ > " + first); // better style pagingSelect.append(" ) row_ where rownum <= " + (first + max) + ") where " + " rownum_ >= " + (first + 1)); if (isForUpdate) { pagingSelect.append(" for update"); } return pagingSelect.toString(); } private static String getDB2LimitString(String sql, int first, int max) { int startOfSelect = sql.toLowerCase().indexOf("select"); StringBuffer pagingSelect = new StringBuffer(sql.length() + 100).append(sql.substring(0, startOfSelect)) // add the comment .append("select * from ( select ") // nest the main query in an // outer select .append(getRowNumber(sql)); // add the rownnumber bit into the // outer query select list if (hasDistinct(sql)) { pagingSelect.append(" row_.* from ( ") // add another (inner) nested // select .append(sql.substring(startOfSelect)) // add the main query .append(" ) as row_"); // close off the inner nested select } else { pagingSelect.append(sql.substring(startOfSelect + 6)); // add the // main // query } pagingSelect.append(" ) as temp_ where rownumber_ "); // add the restriction to the outer select pagingSelect.append("between " + (first + 1) + " and " + (first + max)); return pagingSelect.toString(); } private static boolean hasDistinct(String sql) { return sql.toLowerCase().indexOf("select distinct") >= 0; } /** * Render the <tt>rownumber() over ( .... ) as rownumber_,</tt> bit, that * goes in the select list */ private static String getRowNumber(String sql) { StringBuffer rownumber = new StringBuffer(50).append("rownumber() over("); int orderByIndex = sql.toLowerCase().indexOf("order by"); if (orderByIndex > 0 && !hasDistinct(sql)) { rownumber.append(sql.substring(orderByIndex)); } rownumber.append(") as rownumber_,"); return rownumber.toString(); } public static String getDatabaseProductName(Connection conn) { String dataBase; try { dataBase = conn.getMetaData().getDatabaseProductName(); dataBase = dataBase.toUpperCase(); } catch (SQLException e) { throw new RalasafeException(e); } if (dataBase.indexOf(DB2) >= 0) { return DB2; } else if (dataBase.indexOf(ORACLE) >= 0) { return ORACLE; } else if (dataBase.indexOf(MYSQL) >= 0) { return MYSQL; } else if (dataBase.indexOf(SQLSERVER) >= 0) { return SQLSERVER; } else { return OTHER_DATABASE; } } public static String getDefaultSchema(Connection conn) throws SQLException { String productName = getDatabaseProductName(conn); if (productName.equals(MYSQL) || productName.equals(SQLSERVER)) { return conn.getCatalog(); } else { DatabaseMetaData metaData = conn.getMetaData(); return metaData.getUserName(); } } public static void setCommitMode(Connection conn, boolean autoCommit) { if (conn != null) { try { conn.setAutoCommit(autoCommit); } catch (SQLException e) { } } } public static void rollback(Connection conn) { if (conn != null) { try { conn.rollback(); } catch (SQLException e) { } } } }