dbmanager.java

旅游自助系统

		if(row == null || row.getTableName() == null){
			return 0;
		}
		
		String sql = "delete from " + table + " where id=" + row.getColumn("id") + ";";
		
		// test
		System.out.println(sql);
		
		Connection connection = getConnection();
		Statement stm = connection.createStatement();
		int affected = stm.executeUpdate(sql);
		
		stm.close();
		connection.close();
		
		return affected;
	}
	
	private static int execute(String sql, TableRow row, ColumnInfo info) throws SQLException{
		Connection connection = getConnection();
		PreparedStatement pstm = connection.prepareStatement(sql);
		
		int size = info.getColumnSize();
		int type = Types.VARCHAR;
		
		// notice that PreparedStatement's arguments is begin with 1
		for(int i = 0; i<size; i++){
			
			type = info.getType(i);
			
			if(row.getColumn(info.getName(i)) == null){
				pstm.setNull(i+1, type);
			}
			
			if(type == Types.VARCHAR || type ==Types.LONGVARCHAR){
				
				pstm.setString(i+1, (String)row.getColumn(info.getName(i)));
			}else if(type ==Types.BIGINT){
				
				//test
				System.out.println(row.getColumn(info.getName(i)));	
				
				pstm.setLong(i+1, Long.valueOf(row.getColumn(info.getName(i)).toString()));			
			}else if(type ==Types.TINYINT){
				String name = info.getName(i);
				pstm.setInt(i+1, (Integer)row.getColumn(info.getName(i)));	
			}else if(type == Types.DATE){
				pstm.setDate(i+1, (Date)row.getColumn(info.getName(i)));				
			}else if(type ==Types.BLOB){
				
				pstm.setBlob(i+1, (Blob)row.getColumn(info.getName(i)));				
			}else{
				// test
				System.out.println("Unsupported JDBC type: " + type);
				throw new IllegalArgumentException("Unsupported JDBC type: " + type);				
			}
		}
		
		//test
		System.out.println(pstm);
		
		int affected = pstm.executeUpdate();
		
		pstm.close();
		connection.close();
		
		return affected;
	}
	
	public static boolean excute(String sql) throws SQLException{
		Connection connection = getConnection();
		Statement stm = connection.createStatement();
		stm.execute(sql);
		stm.close();
		connection.close();
		return true;
	}

	/**
	 * @param table
	 * @throws SQLException 
	 * @throws DBTableFullException 
	 */
	public static Long getNewId(String table) throws SQLException, DBTableFullException {
		Connection connection = getConnection();
		String sql = "select max(id) from {0};";
		sql = MessageFormat.format(sql, new Object[]{table});		
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery(sql);
		rs.next();
		Long maxId = rs.getLong(1);
		long newId = maxId+1;		
		if (maxId == CfgManager.getLongProperty(table + ".end")) {			
			rebuildId(table);
			maxId = getNewId(table);
			newId = maxId + 1;			
		}else if(maxId == 0){
			newId = CfgManager.getLongProperty(table + ".start");	
		}
		rs.close();
		stm.close();
		return newId;
	}

	/**
	 * This method rebuild the table's id making them compacted and freeing the bigger ones.
	 * It only called when some object registers and the related database table's max id is in use.
	 * 
	 * Notice: this method is private, so we choose to use the primary sql query statement
	 * instead of using the query() method.
	 * 
	 * @param table
	 * @throws SQLException 
	 * @throws SQLException 
	 * @throws DBTableFullException 
	 */
	private static synchronized void rebuildId(String table) throws SQLException, DBTableFullException {
		
		if(!needRebuildId(table)){
			return;
		}
		
		CacheManager.clearCache();
		CacheManager.lockVisit(true);
		
		Connection connection = getConnection();
		Statement stm = connection.createStatement();

		String selectAmountSql = "select count(id) from client;";
		ResultSet res =  stm.executeQuery(selectAmountSql);
		res.next();
		int amount = res.getInt(1);
		
		String selectIdSql = "select id from " + table + ";";
		res = stm.executeQuery(selectIdSql);
		
		long count = CfgManager.getLongProperty(table + ".start");
		PreparedStatement pstm = connection.prepareStatement("update " + table + " set id=? where id=?;");
		res.next();
		for(int i = 0; i<amount; i++, count++, res.next()){
			pstm.setLong(1, count);
			pstm.setLong(2, res.getLong(1));
			pstm.executeUpdate();
		}
		
		stm.close();
		pstm.close();
		connection.close();
		
		CacheManager.lockVisit(false);
	}

	/**
	 * Notice: this method is private, so we choose to use the primary sql query statement
	 * instead of using the query() method.
	 * 
	 * @param table
	 * @return
	 * @throws SQLException 
	 * @throws DBTableFullException 
	 */
	private static boolean needRebuildId(String table) throws SQLException, DBTableFullException {
		
		Connection connection = getConnection();
		Statement stm = connection.createStatement();
		MessageFormat format = new MessageFormat("select {0}(id) from " + table + ";");
		
		ResultSet res = stm.executeQuery(format.format("count"));
		int count = res.getInt(1);
		res.close();
		
		if(count == 0){
			return false;
		}
		
		Long minId = CfgManager.getLongProperty(table + ".start");
		Long maxId = CfgManager.getLongProperty(table + ".end");
		if(count == maxId - minId + 1){
			throw new DBTableFullException(table + "'s id space is full!");
		}
		
		res = stm.executeQuery(format.format("max"));
		maxId = res.getLong(1);
		res.close();
		
		res = stm.executeQuery(format.format("min"));
		minId = res.getLong(1);
		res.close();
		
		stm.close();
		connection.close();
		
		return (count < maxId - minId + 1) || (minId != CfgManager.getLongProperty(table + ".start"));
	}

	private static Connection getConnection() throws SQLException {
		if (!initialized) {
			initialize();
		}

		String url = "jdbc:apache:common:dbcp:tshspool";
		Connection connection = null;
		try{
			connection = DriverManager.getConnection(url);
		}catch(SQLException se){
			// System.out.println("DriverManager.getConnection(" + url + ") not work!");
			connection = DriverManager.getConnection(
					CfgManager.getProperty("db.url"), 
					CfgManager.getProperty("db.username"),
					CfgManager.getProperty("db.password")
					);
		}
		
		return connection;
	}

	/**
	 * @param results
	 * @return
	 * @throws SQLException 
	 */
	public static List<String> getColumnName(ResultSet results) throws SQLException {
		List<String> columns = new ArrayList<String>();
		
		ResultSetMetaData meta = results.getMetaData();
		
		// the ResultSetMetaData and ResultSet's first index is 1, not 0
		int count = meta.getColumnCount() + 1;
		for(int i = 1; i<count; i++){
			columns.add(meta.getColumnName(i));
		}
		
		return columns;
	}

	/**
	 * @param args
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 */
	public static void main(String[] args) throws Exception {
		 initialize(); 
		 /*
		 Driver mysqlDriver = (Driver) Class.forName("com.mysql.jdbc.Driver").newInstance();
		 Connection connection = DriverManager.getConnection(CfgManager.getProperty("db.url"), "tshs", "tshs");
		 
		 Connection connection = getConnection();
		 Statement s = connection.createStatement();
		 ResultSet res = s.executeQuery("select max(id) from client;");
		 ResultSetMetaData meta = res.getMetaData();
		 System.out.println(meta.getCatalogName(1));
		 System.out.println(meta.getColumnLabel(1));
		 System.out.println(meta.getColumnName(1));
		 System.out.println(meta.getColumnTypeName(1));
		 s.close();
		 connection.close();*/
		 Connection connection = getConnection();
			Statement stm = connection.createStatement();
			
			String sql = "insert into sightspot values('1002', '香山', '北京', '/upload/1000.jpg', '香山是个好地方。');";
			// ResultSet rs =  stm.executeQuery(selectAmountSql);
			int res = stm.executeUpdate(sql);
			System.out.println(res);
			/*rs.next();
			int amount = rs.getInt(1);
			
			String selectIdSql = "select id from client;";
			ResultSet res = stm.executeQuery(selectIdSql);
			
			long count = CfgManager.getLongProperty("client.start");
			PreparedStatement pstm = connection.prepareStatement("update client set id=? where id=?;");
			res.next();
			for(int i = 0; i<amount; i++, count++, res.next()){
				pstm.setLong(1, count);
				pstm.setLong(2, res.getLong(1));
				pstm.executeUpdate();
			}*/
			
			stm.close();
			// pstm.close();
			connection.close();
	}

}